Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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!");
    }
share|improve this question
3  
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 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 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 at 15:41
add comment

2 Answers

up vote 8 down vote accepted

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.

share|improve this answer
    
How would reversing the loop help? –  200_success Feb 16 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 at 14:39
add comment

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.

share|improve this answer
    
I am not 100 % but this probably what already happening when the code is compiled. –  Marc-Andre Feb 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 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 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 –  Arturo Tena Feb 14 at 23:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.