Search code examples
javaapache-poiout-of-memorybig-o

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


Solution

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