Search code examples
javaperformanceexcelapache-poiautosize

How to speed up autosizing columns in apache POI?

I use the following code in order to autosize columns in my spreadsheet:

for (int i = 0; i < columns.size(); i++) {
   sheet.autoSizeColumn(i, true);
   sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 600);
}

The problem is it takes more than 10 minutes to autosize each column in case of large spreadsheets with more than 3000 rows. It goes very fast for small documents though. Is there anything which could help autosizing to work faster?

Solution

  • Solution which worked for me:

    It was possible to avoid merged regions, so I could iterate through the other cells and finally autosize to the largest cell like this:

    int width = ((int)(maxNumCharacters * 1.14388)) * 256;
sheet.setColumnWidth(i, width);

    where 1.14388 is a max character width of the "Serif" font and 256 font units.

    Performance of autosizing improved from 10 minutes to 6 seconds.