poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mayankmails <mayankma...@gmail.com>
Subject How to user SXSSFWorkbook for updating existing excel file
Date Tue, 01 Nov 2011 06:34:59 GMT
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?

--
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Mime
View raw message