Quantcast

How to user SXSSFWorkbook for updating existing excel file

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

How to user SXSSFWorkbook for updating existing excel file

mayankmails
Hello,

I am using latest version of POI 3.8 from poi-bin-3.8-beta4-20110826.

I have implemented excel export functionality using excel templates for XSSF and HSSF API.But in case of XSSF when i try to export large data like 150,000 then it throws java.lang.OutOfMemoryError: GC overhead limit exceeded exception.

While reading the documents i found SXSSFWorkbook approach.I have executed the simple example from SXSSF (Streaming Usermodel API).

Now as i am using template feature, I am trying to read the excel file and then modify it using SXSSF.I have modified SXSSF (Streaming Usermodel API) example as following:


 public static void main(String[] args) throws Throwable {
            File file = new File("E:/temp/sxssf1.xlsx");
            OPCPackage pkg = OPCPackage.open(new FileInputStream(file.getAbsolutePath()));
            XSSFWorkbook xssfwb = new XSSFWorkbook(pkg);
                Workbook wb = new SXSSFWorkbook(xssfwb,100); // keep 100 rows in memory, exceeding rows will be flushed to disk
                Sheet sh = wb.createSheet();
                for(int rownum = 0; rownum < 1000; rownum++){
                    Row row = sh.createRow(rownum);
                    for(int cellnum = 0; cellnum < 10; cellnum++){
                        Cell cell = row.createCell(cellnum);
                        String address = new CellReference(cell).formatAsString();
                        cell.setCellValue("StringTest"+cellnum);
                    }

                }

                // Rows with rownum < 900 are flushed and not accessible
                for(int rownum = 0; rownum < 900; rownum++){
                  Assert.assertNull(sh.getRow(rownum));
                }

                // ther last 100 rows are still in memory
                for(int rownum = 900; rownum < 1000; rownum++){
                    Assert.assertNotNull(sh.getRow(rownum));
                }
               
                java.io.FileOutputStream out = new java.io.FileOutputStream("E:/temp/sxssf2.xlsx");
                wb.write(out);
                out.close();
            }


Modified:
1) Read existing template sxssf1.xlsx using OPCPackage and XSSFWorkbook.
2) Create SXSSFWorkbook using XSSFWorkbook xssfwb.
3) try to create new sxssf2.xlsx using SXSSFWorkbook.

The output generated using this code is exact copy of sxssf1.xlsx.
But the expected output is copy of sxssf1.xlsx(in terms of properties set in template) and data added in 1000 rows as following:
StringTest0 StringTest1 StringTest2 StringTest3 StringTest4 StringTest5 StringTest6 StringTest7 StringTest8 StringTest9

My questions:
1) Can we use SXSSFWorkbook for reading and updating Excel file?
2) How to use SXSSFWorkbook for tempalte feature?
3) Can we use SXSSFSheet,SXSSFRow and SXSSFCell to write cell information and set cell properties?
4) Is this possible with current version or need to wait for next release?
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to user SXSSFWorkbook for updating existing excel file

mayankmails
Please help me by proving details for SXSSF questions i have asked in by previous post.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to user SXSSFWorkbook for updating existing excel file

Yegor Kozlov-4
In reply to this post by mayankmails
> My questions:
> 1) Can we use SXSSFWorkbook for reading and updating Excel file?
No. SXSSF is an API for writing data, not for reading.

> 2) How to use SXSSFWorkbook for tempalte feature?

Please the the latest build from trunk. We recently added support for
templates. There is a new constructor that accepts an existing
workbook:

   public SXSSFWorkbook(XSSFWorkbook workbook)


> 3) Can we use SXSSFSheet,SXSSFRow and SXSSFCell to write cell information
> and set cell properties?

What cell properties do you mean? SXSSF can write all basic types of
cell values: string, numbers, dates, etc.

Yegor

> 4) Is this possible with current version or need to wait for next release?
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-user-SXSSFWorkbook-for-updating-existing-excel-file-tp4954431p4954431.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to user SXSSFWorkbook for updating existing excel file

mayankmails
In reply to this post by mayankmails
Thanks for Response.

I want to know how we can user SXSSF API to read xlsx and and write values in it.In above example I have used XSSFWorkbook to read xlsx file and then  used SXSSFWorkbook to write data in it.

But it didn't worked.It just created copy of xlsx file i have read using XSSFWorkbook.

What is the correct approach to get the updated xlsx file.

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to user SXSSFWorkbook for updating existing excel file

Yegor Kozlov-4
SXSSF is designed for writing large volumes of data. If you need to
read and update content then give your JVM more memory and use XSSF

Yegor

On Mon, Nov 7, 2011 at 3:27 PM, mayankmails <[hidden email]> wrote:

> Thanks for Response.
>
> I want to know how we can user SXSSF API to read xlsx and and write values
> in it.In above example I have used XSSFWorkbook to read xlsx file and then
> used SXSSFWorkbook to write data in it.
>
> But it didn't worked.It just created copy of xlsx file i have read using
> XSSFWorkbook.
>
> What is the correct approach to get the updated xlsx file.
>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-user-SXSSFWorkbook-for-updating-existing-excel-file-tp4954431p4971019.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Loading...