JAVA - Apache POI batch modify Excel File

Question

I am running a java application that modify large excel file. And I am experiencing the time it takes an update to take place..

for each cell I am running the update based on the changes as fallows

  public boolean updateCellData(ColumnName, RowNum, Data){
    FileInputStream fis = new FileInputStream(path);
        Workbook    workbook = WorkbookFactory.create(fis);

        row =  getRow(rowNum-1);
            if (row == null){
                row = sheet.createRow(rowNum-1);
            }
            cell = row.getCell(colNum);
            if (cell == null){
                cell = row.createCell(colNum);
            }
            cell.setCellValue(data);
            fileOut = new FileOutputStream(path);
            workbook.write(fileOut);
            fileOut.close();
    }

Is there any optimizations that I can make to my code?

Why are you re-reading/re-initializing the workbook on each cell update? — Perception
– Perception, Commented Jan 30, 2013 at 5:38
@Perception updating adhocly.. but lately the updates are higher.. any suggestions? — Njax3SmmM2x2a0Zf7Hpd
– Njax3SmmM2x2a0Zf7Hpd, Commented Jan 30, 2013 at 5:41
I would recommend splitting your logic to separate the 'update data' logic from the 'read/save' logic. That way you can decide to, in some cases, do a single quick update, and in others, batch up a bunch of updates before saving the workbook. — Perception
– Perception, Commented Jan 30, 2013 at 5:47
@Perception thanks. can you provide a sample like how to split the logic? — Njax3SmmM2x2a0Zf7Hpd
– Njax3SmmM2x2a0Zf7Hpd, Commented Jan 30, 2013 at 5:52
looks like SAN3 wrote some code up. Thats pretty close to what I would have written as well. — Perception
– Perception, Commented Jan 30, 2013 at 5:57

SANN3 · Accepted Answer · 2013-01-30 05:54:44Z

3

Open the excel once ,after all your update operation close the excel.

Example :

      FileInputStream fis =null;
     Workbook workbook=null;
    public void openWorkbook(){
         fis = new FileInputStream(path);
         workbook = WorkbookFactory.create(fis);
    }

    public boolean updateCellData(ColumnName, RowNum, Data){

            row =  getRow(rowNum-1);
                if (row == null){
                    row = sheet.createRow(rowNum-1);
                }
                cell = row.getCell(colNum);
                if (cell == null){
                    cell = row.createCell(colNum);
                }
                cell.setCellValue(data);
      }

    public void closeWorkbook(){
        fileOut = new FileOutputStream(path);
        workbook.write(fileOut);
        fileOut.close();
    }

answered Jan 30, 2013 at 5:54

SANN3

10.2k7 gold badges68 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JAVA - Apache POI batch modify Excel File

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related