Fast edit of Excel file

Question

I have this code to edit certain cells in my Excel file using Apache POI, but it is really slow. How can I improved the performance?

Ideally I would like to edit 20000 rows in less than one minute. At the moment it does ~100/min. Any suggestions would be great.

public static void main(String[] args) throws IOException, InvalidFormatException{
 InputStream inp = new FileInputStream("test.xls");
 FileOutputStream fileOut = new FileOutputStream("edited-test.xls");
 Workbook wb = WorkbookFactory.create(inp);
 Sheet sheet = wb.getSheetAt(0);
 for(int i=2;i <20002;i++){
 Row row = sheet.getRow(i);
 Cell cell4 = row.getCell(4); 
 cell4.setCellValue(i); 
 Cell cell6 = row.getCell(6);
 cell6.setCellValue("aa"+i); 
 Cell cell8 = row.getCell(8); 
 cell8.setCellValue("2"); 
 wb.write(fileOut);
 System.out.println(i);
 }
 fileOut.close();
 System.out.println("Done!");
 }

• Marc-Andre: Keep in mind that System.out.println(i) could slow your application. If you don't need it, I would suggest you to remove it.
• Sionnach733: @Marc-Andre good to know, i had it there so i could see how quickly each row was processed.
• Marc-Andre: It's not always decisive, but it could help. Try to execute the code by removing it and try with it to see if it does indeed make a difference.

Answer 1

First thing you should do is only write the file out once, not 20,000 times ;-)

Move the wb.write(fileOut); to be outside the loop.....

Additionally, there may be some improvement by reversing the loop:

for(int i=2;i <20002;i++){

can become:

for(int i=20001;i >= 2;i--){

This may make some memory management in the API faster.

• 200_success: How would reversing the loop help?
• rolfl: because often data structures are incrementally extended, and starting from the largest member and working backwards may mean that only one large allocation is done, instead of multiple smaller ones as the data set grows.

Answer 2

Replace:

cell6.setCellValue("aa"+i);

with:

cell6.setCellValue(aa.append(i).toString());
aa.setLength(2); // cut the StringBuilder to just "aa", keeping it's original capacity

and the write the following two lines before the for:

StringBuilder aa = new StringBuilder(7); // length of "aa" plus 5 digits for max value of the loop index
aa.append("aa");

-- Edited to add capacity optimization and use setLength() per comments.

• Marc-Andre: I am not 100 % but this probably what already happening when the code is compiled.
• rolfl: Agree with @Marc-Andre - but, an interesting observation, and worth noting that moving the new StringBuilder() outside the loop, and then reusing it with a aa.setLength(0); may have a small benefit.
• Marc-Andre: @rolfl As I said, I'm not that good in optimization, but maybe using setLength() would not help that much stackoverflow.com/a/5193094/2115680 .
• ArturoTena: I guess this string could be constructed with a array of chars[7], and then updating just the digit(s) that changed in the loop, but unfortunately I don't have time right now to build the full example. Sorry :P