Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

apache poi excel big auto column width

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

like image 776
Ram Sharan Mittal Avatar asked Nov 25 '13 10:11

Ram Sharan Mittal


People also ask

How do I automatically adjust column width in Excel using Apache POI?

sheetName. autoSizeColumn(columnIndex);

How do you automate column width in Excel?

Change the column width to automatically fit the contents (auto fit) Select the column or columns that you want to change. On the Home tab, in the Cells group, click Format. Under Cell Size, click AutoFit Column Width.

How do you adjust the column width based on longest entry?

You can quickly adjust the column width to accommodate the longest entry. Select any column from the worksheet. From the Cells group on the Home tab, choose Format. Select AutoFit Column Width to set that column to auto-fit the width depending on the contents.


1 Answers

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.

like image 72
Sankumarsingh Avatar answered Sep 27 '22 16:09

Sankumarsingh