How to speed up autosizing columns in apache POI?

26
votes

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?

2
javaperformanceexcelapache-poiautosize
sheet.autoSizeColumn(i, false) might be a little faster - developerwjk
It's slow because it has to calculate the width of that cell in every row of your document. Any chance you could change your logic so you write the first few rows, size based on just those, then add the rest of the rows in later? - Gagravarr
@developerwjk absolutely agree, according the source code of the poi library, it iterates through all merged regions every time it calculates the width of every single cell - antken
@Gagravarr unfortunately it was not possible in this case, because large cells could come in any row later on - antken

2 Answers

41
votes

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.

1
votes

The autoSizeColumn function itself works not perfect and some columns width not exactly fit the data inside. So, I found some solution that works for me.

  1. To avoid crazy calculations let give that to autoSizeColumn() function:
   sheet.autoSizeColumn(<columnIndex>);
  1. Now, our column autosized by library but we wont to add a little bit more to the current column width to make table looks fine:
   // get autosized column width
   int currentColumnWidth = sheet.getColumnWidth(<columnIndex>);

   // add custom value to the current width and apply it to column
   sheet.setColumnWidth(<columnIndex>, (currentColumnWidth + 2500));
  1. The full function could looks like:
   public void autoSizeColumns(Workbook workbook) {
        int numberOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            if (sheet.getPhysicalNumberOfRows() > 0) {
                Row row = sheet.getRow(sheet.getFirstRowNum());
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    int columnIndex = cell.getColumnIndex();
                    sheet.autoSizeColumn(columnIndex);
                    int currentColumnWidth = sheet.getColumnWidth(columnIndex);
                    sheet.setColumnWidth(columnIndex, (currentColumnWidth + 2500));
                }
            }
        }
    }

P.S. Thanks Ondrej Kvasnovsky for the function https://stackoverflow.com/a/35324693/13087091