You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by mayankmails <ma...@gmail.com> on 2011/11/29 10:15:42 UTC
Approach to overcome Xssf performance problem.
Hello,
Objective: To write xlsx file with more than 150,000 records.
Problem: java.lang.OutOfMemoryError: GC overhead limit exceeded
Approach 1: Increase HEAP size.This is fine.
Approach 2:
I am trying another approach to overcome the XSSF performace issue.The basic
idea for this approach is to process the large inforation in chunks by
creating multiple xlsx files of let say 30,000 records and finally read all
thes files and create single xlsx file.Can this be done?Your thoughts on
this idea.
Reagrds,
Mayank
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Approach-to-overcome-Xssf-performance-problem-tp5031686p5031686.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
Re: Approach to overcome Xssf performance problem.
Posted by Alex Geller <ag...@4js.com>.
It is probably a naive suggestion but it may help to understand the problem.
Instead of updating an existing file you could read it using the event
interface and generate a new SXSSF document containing the original data,
modified data and new data. At the end of the process you rename the new
document to the old name.
Regards,
Alex
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Approach-to-overcome-Xssf-performance-problem-tp5031686p5032667.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
Re: Approach to overcome Xssf performance problem.
Posted by mayankmails <ma...@gmail.com>.
Please refer to last two discussions in my following post:
http://apache-poi.1045710.n5.nabble.com/How-to-user-SXSSFWorkbook-for-updating-existing-excel-file-td4954431.html
I am reading the template xls file and trying to generate xlsx output using
XSSF.But due to performance constraint, I am trying to find another
approach.
As per last comments in above post I mentioned,
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.
Due to this I am trying to find out approach to process large data in
chunks.Where, I will read template populate data and generate x number of
files.Now, I need to merge these files into Single file so that I can
overcome the limitation of OutOfMemmory. This is the point I am stuck at.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Approach-to-overcome-Xssf-performance-problem-tp5031686p5032171.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
Re: Approach to overcome Xssf performance problem.
Posted by Nick Burch <ni...@alfresco.com>.
On Tue, 29 Nov 2011, mayankmails wrote:
> Nick Any suggestion on overcoming problem in last reply.
Why not just use SXSSF and write it all in one go, rather than trying to
merge?
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Approach to overcome Xssf performance problem.
Posted by mayankmails <ma...@gmail.com>.
Nick Any suggestion on overcoming problem in last reply.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Approach-to-overcome-Xssf-performance-problem-tp5031686p5032067.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
Re: Approach to overcome Xssf performance problem.
Posted by mayankmails <ma...@gmail.com>.
I have tried using this with SXSSF as following:
ArrayList< File> fileList = new ArrayList<File>();
File file1 = new File("E:/temp/f1.xlsx");
File file2 = new File("E:/temp/f2.xlsx");
File file3 = new File("E:/temp/sxssf3.xlsx");
fileList.add(file1);
fileList.add(file2);
int lastRowProcessed = 0;
for (Iterator iterator = fileList.iterator(); iterator.hasNext();) {
File filename = (File) iterator.next();
OPCPackage pkg = OPCPackage.open(new
FileInputStream(filename.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();
int startCount = lastRowProcessed;
for(int rownum = startCount; rownum < 10+startCount; 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);
}
lastRowProcessed =rownum;
}
java.io.FileOutputStream out = new
java.io.FileOutputStream(file3);
wb.write(out);
out.close();
}
But the problem is wb.write(out); the final output of this code is data of
File 2 is present in sxssf3.xlsx
May be my approach is wrong. I want to merge file f1.xlsx and f2.xlsx to get
File sxssf3.xlsx.
But how can i maintain data for all the files to overcome memory issue?
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Approach-to-overcome-Xssf-performance-problem-tp5031686p5031756.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
Re: Approach to overcome Xssf performance problem.
Posted by Nick Burch <ni...@alfresco.com>.
On Tue, 29 Nov 2011, mayankmails wrote:
> Objective: To write xlsx file with more than 150,000 records.
>
> Problem: java.lang.OutOfMemoryError: GC overhead limit exceeded
Why not use SXSSF?
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org