How to speed up autosizing columns in apache POI?

Question

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?

Answer 1

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.

Answer 2

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>); 

2. 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)); 

3. 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