AutosizeColumns on SXSSFWorkbook

Question

Is it possible to autoSizeColumns on a streaming SXSSFWorkbook? I implemented an export functionality to export a list of objects to excel. At first I used the XSSFWorkbook (not streaming) and after all the cells were created, I autosized all the columns, resulting in a nice excel file.

For performance issues we wanted to change the workbook to the streaming version, but this resulted in a NullPointer at org.apache.poi.ss.util.SheetUtil.getCellWidth.

Is it possible to call autoSizeColumns for a SXSSFWorkbook?

Im using poi-ooxml 3.9, but I have the same issue in 3.8.

score 22 · Accepted Answer · edited Apr 01 '20 at 13:49

22

You need to make sure every cell has a value.

We use the following code to set a string value to a cell:

Cell c = row.createCell(i);
c.setCellValue(text == null ? "" : text );

** Cell should never be null values else it throws NullPointerException. Hence set the value as shown above.

Thanks a lot, this helped!!

edited Apr 01 '20 at 13:49

Seega

3,001
2
34
48

answered Jan 24 '13 at 09:05

cremersstijn

2,375
4
28
41

4

I used streaming api of poi 3.14, but encountered ' Could not auto-size column. Make sure the column was tracked prior to auto-sizing the column.' when I called autoSizeColumn. Did you ever meet it? – liam xu Apr 12 '16 at 08:15
37

I had this error also, liam xu. Call trackAllColumnsForAutoSizing() on the SXSSFSheet object before calling autoSizeColumn(columnIndex) and it will be fixed. – superbAfterSemperPhi Apr 29 '16 at 15:34
3

NOTE: As per documentation, track required columns before writing one or more rows to the sheet. i.e. call trackColumnForAutoSizing() first, then write rows using createRow(), and finally call autoSizeColumn(). Hope this comment adds some value. – Tarun Kumar Aug 23 '18 at 15:08

score 4 · Answer 2 · answered Oct 18 '19 at 09:30

Use sheet.isColumnTrackedForAutoSizing(0); for first and subsequently used for other column, i have faced exception whenever code executed autoSizeColumn(0) get executed. by using above code i have resolved the issue and it's good to expand the column width too based on the text.

score 4 · Answer 3 · answered May 26 '22 at 13:38

4

sheet.trackAllColumnsForAutoSizing();

answered May 26 '22 at 13:38

Lof

251
1
3
7

score 0 · Answer 4 · answered Jun 27 '22 at 15:58

0

sheet.trackAllColumnsForAutoSizing(), this works but it is truly not a good approach. Because if you are working with lakhs of data, it delays the process of writing to file by huge margin

answered Jun 27 '22 at 15:58

Ravi Chhetri

11
2

score -2 · Answer 5 · edited Apr 01 '20 at 13:47

-2

Error: NullPointerException on org.apache.poi.ss.util.SheetUtil.getCellWidth(SheetUtil.java:122)

Fix: Always set value to Cell as shown below, it throws NullPointerException when there is null in Cell, so set the value as:

Cell c = row.createCell(i);
c.setCellValue(text == null ? "" : text );

edited Apr 01 '20 at 13:47

Hakan Dilek

2,178
2
23
35

answered Apr 01 '20 at 11:46

Srini Yedire

1
1

AutosizeColumns on SXSSFWorkbook

5 Answers5

Linked