CODEWITHSUNDEEP
java
delta1881
delta1881

Reputation: 1

Excel Writing becomes slow after writing more than 5000 rows in JAVA

It will write slow in excel after writing 5000 rows in excel file.

Can anyone suggest that how can i speed up writing excelfile?

I'm scraping data from my website with selenium - java

When i start my code, Writing speed is fast, but it becomes slow after 1 hour. (CPU and RAM consumption are normal.)

This is the code for writing excel file.

public boolean setCellData(String sheetName,String colName,int rowNum, String data){
        try{
        fis = new FileInputStream(path); 
        workbook = new XSSFWorkbook(fis);

        if(rowNum<=0)
            return false;

        int index = workbook.getSheetIndex(sheetName);
        int colNum=-1;
        if(index==-1)
            return false;


        sheet = workbook.getSheetAt(index);


        row=sheet.getRow(0);
        for(int i=0;i<row.getLastCellNum();i++){
            //System.out.println(row.getCell(i).getStringCellValue().trim());
            if(row.getCell(i).getStringCellValue().trim().equals(colName)) {
                colNum=i;
                break;
            }
        }
        if(colNum==-1)
            return false;

        sheet.autoSizeColumn(colNum); 
        row = sheet.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();    

        }
        catch(Exception e){
            e.printStackTrace();
            return false;
        }
        return true;
    }

Upvotes: 0

Views: 285

Answers (2)

Andreas
Andreas

Reputation: 159185

Let's see:

  • Load entire worksheet into memory
  • Update one cell
  • Write entire worksheet back to file
  • Repeat all that for next row/cell

Now why would that be slow?
Because reading and writing files is slow.

You should:

  • Load the sheet once
  • Update all the rows/cells that need updating
  • Save the result back to file

Upvotes: 2

Sam Barnum
Sam Barnum

Reputation: 10744

Keep a reference to the sheet and row, instead of looking them up each time you set a value.

Upvotes: 0

Related Questions