APACHE POI autoSizeColumn with useMergedCells too slow

Question

I try to apply autoSizeColumn on an Excel sheet. I'm using POI 3.10.1.
I apply autoSizeColumn at the end but the problem is than the process is too slow/long anyway: On the sheet I've approximately 1000 lines and 20 columns... After 5 hours, I kill the process ...
I don't understand what is taking so long, 1000 lines and 20 columns doesn't appear so huge: Did I miss something? (nb: on a smaller file, it's working)
My simplified code below:

    Workbook vWorkbook = getWorkbook();

    Sheet vSheet = vWorkbook.createSheet("sheet");
    CreationHelper vHelper = vWorkbook.getCreationHelper();
    Drawing drawing = vSheet.createDrawingPatriarch();

    Set<CellRangeAddress> vRegions = new HashSet<CellRangeAddress>();

    //Parcours des lignes du document
    MatrixDocument vMatrixDocument = getMatrixDocument();
    List<MatrixRow> vListMatrixRows = vMatrixDocument.getRows();
    int maxColNb = 0;

    //Parcours des lignes de la grille.
    for (MatrixRow vMatrixRow : vListMatrixRows)
    {
        //(...)
        //create cells 
        //(...)
    }

    initColSpan(vListMatrixRows, vRegions);

    //Gestion des colSpan et des RowSpan
    for (CellRangeAddress vRegion : vRegions)
    {
        vSheet.addMergedRegion(vRegion);
    }

    for (int i = 0; i < maxColNb; ++i)
    {
        vSheet.autoSizeColumn(i, true);//Here the problem. spent more than 5 hour for 1000 lines and 20 columns
    }

I've already read threads below :

http://stackoverflow.com/questions/16943493/apache-poi-autosizecolumn-resizes-incorrectly

http://stackoverflow.com/questions/15740100/apache-poi-autosizecolumn-not-working-right

http://stackoverflow.com/questions/23366606/autosizecolumn-performance-effect-in-apache-poi

http://stackoverflow.com/questions/18984785/a-poi-related-code-block-running-dead-slow

http://stackoverflow.com/questions/28564045/apache-poi-autosizecolumn-behaving-weird

http://stackoverflow.com/questions/18456474/apache-poi-autosizecolumn-is-not-working

But none solve my issue.
Any idea ? PS : I tried to upload an example image of the Excel file but I don't find how to upload it.

@Gagravarr: I didn't try with POI 3.12. Did you seen any correction about this issue? As soon as possible I will try it. — 6w3n, May 22 '15 at 13:30
The [POI changelog is here](http://poi.apache.org/changes.html), there's a lot of fixes mentioned since 3.10! — Gagravarr, May 22 '15 at 16:11

score 0 · Answer 1 · answered May 27 '15 at 11:06

0

Even after using Apache POI 3.12, I was facing the same issue for auto-sizing. Also auto-size doesn't work in Unix/Linux. What I learnt from various forums is this: 1.You can try is using the SXSSF API, usually works much faster. 2. If not, then go for setColumnWidth method(I know its literally manual work for 20 columns)

answered May 27 '15 at 11:06

sudipta06

658
7
12

Autosizing does work just fine on Linux! You can run the Apache POI unit tests and see for yourself... – Gagravarr Jun 05 '15 at 10:19

score 0 · Answer 2 · answered Mar 13 '19 at 15:35

0

My solution was only use Autosize on the last Excel line, like this:

if (i >= abaExcel.Itens.Count)
    sheet.AutoSizeColumn(j);

answered Mar 13 '19 at 15:35

Taian

189
8
19

score 0 · Answer 3 · answered Jul 04 '22 at 17:16

Because merged regions cannot overlap without producing a corrupt document, POI may be checking the list of merged regions on a sheet for potential intersections before adding a merged region. This gives O(N) behavior for adding one region instead of the expected O(1).

addMergedRegion - with checking but slow
addMergedRegionUnsafe - without checking but fast

Documentation: read more about addMergedRegionUnsafe(...)

APACHE POI autoSizeColumn with useMergedCells too slow

3 Answers3