apache poi excel big auto column width

Question

I am try to create a big excel 2010 with 30 columns and 1 million records with Apache poi latest. I am creating as describe in this link http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java . but I want column width to be same as column header text size. but when I am doing this after creating excel with following code

for (int x = 0; x < sheet.getRow(0).getPhysicalNumberOfCells(); x++) {
            sheet.setColumnWidth(x, 20*256);
        }

it is taking a huge time and even with 5gb heap size I am getting out of memory.

thanks

ram

Why aren't you using SXSSF? That's the new replacement for the demo BigGrid stuff — Gagravarr, Nov 25 '13 at 11:40
yes, seen SXSSF will use in future project ..... come to know later — Ram Sharan Mittal, Dec 12 '13 at 08:55

score 33 · Accepted Answer · answered Nov 25 '13 at 11:12

33

First Select the first row or header because only Header can give you the max number of cells in a row.

HSSFRow row = wb.getSheetAt(0).getRow(0);

Then use autoSizeColumn on each column of that row

for(int colNum = 0; colNum<row.getLastCellNum();colNum++)   
    wb.getSheetAt(0).autoSizeColumn(colNum);

This will set the column width same as that of its Header width.

answered Nov 25 '13 at 11:12

Sankumarsingh

9,889
11
50
74

Sometimes not works - https://stackoverflow.com/questions/37069820/android-poi-crash-when-using-autosizecolumn – Roman Soviak Mar 26 '19 at 12:22
giving Error: java.lang.NoClassDefFoundError: Failed resolution of: Ljava/awt/font/FontRenderContext; – – Ramesh Bhati May 23 '22 at 12:33
@RameshBhati check https://stackoverflow.com/a/37070120/624003 – Sankumarsingh May 31 '22 at 06:59

score 5 · Answer 2 · answered May 21 '14 at 11:28

5

I would also suggest you look at using sxssf. It uses an xml format to load it into Excel. This is much faster and a better way to create large reports or grids. The autosizing function in xssf takes too long by default.

answered May 21 '14 at 11:28

Adi Kish

79
3
9

score 4 · Answer 3 · edited Jul 31 '23 at 02:31

4

sheet.autoSizeColumn(columnNumber) works. This will resize the column to the largest cell length.

edited Jul 31 '23 at 02:31

Pang

9,564
146
81
122

answered Apr 30 '15 at 18:56

Nilesh

133
3
10

https://stackoverflow.com/questions/37069820/android-poi-crash-when-using-autosizecolumn - sometimes not works – Roman Soviak Mar 26 '19 at 12:21
giving Error: java.lang.NoClassDefFoundError: Failed resolution of: Ljava/awt/font/FontRenderContext; – Ramesh Bhati May 23 '22 at 12:32

apache poi excel big auto column width

3 Answers3

Linked