# Fast edit of Excel file

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!");
}

• 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. – Marc-Andre Feb 14 '14 at 15:35
• @Marc-Andre good to know, i had it there so i could see how quickly each row was processed. – Sionnach733 Feb 14 '14 at 15:39
• 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. – Marc-Andre Feb 14 '14 at 15:41

## 2 Answers

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.

• How would reversing the loop help? – 200_success Feb 16 '14 at 10:39
• 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. – rolfl Feb 16 '14 at 14:39

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.

• I am not 100 % but this probably what already happening when the code is compiled. – Marc-Andre Feb 14 '14 at 19:01
• 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. – rolfl Feb 14 '14 at 19:15
• @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 . – Marc-Andre Feb 14 '14 at 19:24
• 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 – ArturoTena Feb 14 '14 at 23:26