mayankmails
2011-11-01 06:34:59 UTC
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.
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.