How to auto adjust the column in excel in apache POI

Question

I am creating an excel file with apache poi the excel is generated but i can not adjust the column with according to the cell values i am posting the code what i have done so far

This is how i have created the headers in excel

HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        sheet.protectSheet("password");
        sheet.autoSizeColumn(15);
        HSSFFont hSSFFont = wb.createFont();
        hSSFFont.setFontName(HSSFFont.FONT_ARIAL);
        hSSFFont.setFontHeightInPoints((short) 8);

        CellStyle style = wb.createCellStyle();
        /* cell style for locking */
        CellStyle lockedCellStyle = wb.createCellStyle();
        lockedCellStyle.setLocked(true);

        HSSFRow row = null;

        HSSFCell cell = null;

        row = sheet.createRow(0);
        int headercolumnNo = 0;



        //1st Column Header for Indicator
        cell = row.createCell(headercolumnNo);
        cell.setCellValue(new HSSFRichTextString(listOfActiveCarrierUserHeader.get(0)));
        style.setWrapText(true);
        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        style.setFont(hSSFFont);
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        cell.setCellStyle(style);
        headercolumnNo = 1;

        cell = row.createCell(headercolumnNo);  //2nd Column Header for Firstname
        cell.setCellValue(new HSSFRichTextString(listOfActiveCarrierUserHeader.get(1)));
        style.setWrapText(true);
        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        style.setFont(hSSFFont);
        cell.setCellStyle(style);
        headercolumnNo = headercolumnNo + 1;

        cell = row.createCell(headercolumnNo);  //2nd Column Header for Firstname
        cell.setCellValue(new HSSFRichTextString(listOfActiveCarrierUserHeader.get(2)));
        style.setWrapText(true);
        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        style.setFont(hSSFFont);
        cell.setCellStyle(style);
        headercolumnNo = headercolumnNo + 1;

and this is how i have  populated the values in that excel file 

for(CarrierActiveUser carrierActiveUser : listOfCarrierUser){

            int columnNo = 0;
            row = sheet.createRow(j + 1);

            cell = row.createCell(columnNo);
            if(null != carrierActiveUser.getFistName()){

                cell.setCellValue(new HSSFRichTextString(carrierActiveUser.getFistName()));
                 lockedCellStyle.setFont(hSSFFont);
                 cell.setCellStyle(lockedCellStyle);

            }else{
                cell.setCellValue(new HSSFRichTextString(" "));
                cell.setCellStyle(lockedCellStyle);
            }

            columnNo = columnNo + 1;
            cell = row.createCell(columnNo);
            if(null != carrierActiveUser.getLastName()){

                cell.setCellValue(new HSSFRichTextString(carrierActiveUser.getLastName()));
                   lockedCellStyle.setFont(hSSFFont);
                cell.setCellStyle(lockedCellStyle);

            }else{
                cell.setCellValue(new HSSFRichTextString(" "));
                cell.setCellStyle(lockedCellStyle);
            }

            columnNo = columnNo + 1;
            cell = row.createCell(columnNo);
            if(null != carrierActiveUser.getLastName()){

                cell.setCellValue(new HSSFRichTextString(carrierActiveUser.getEmailId()));
                lockedCellStyle.setFont(hSSFFont);
                cell.setCellStyle(lockedCellStyle);

            }else{
                cell.setCellValue(new HSSFRichTextString(" "));
                cell.setCellStyle(lockedCellStyle);
            }

Please someone help me to adjust the columns , i am new to apache poi

Current output

@Ravikumar i want to adjust the column according to the header , for example if the firstname is 10 character the header will be adjuste as well — storyteller, Oct 19 '16 at 12:09
Not clear enough, edit your question and add something like _current output is this_ and _the expected output should be like this_. — Ravikumar, Oct 19 '16 at 12:19
@Ravikumar see i have attached a screen what is output is coming and i want to adjust the header length with the cell values — storyteller, Oct 19 '16 at 12:28
Do you want the header names to come in single line? Or Do you want the columns to be aligned perfectly without overlapping on other columns? — Ravikumar, Oct 19 '16 at 12:37

Ravikumar · Accepted Answer · 2016-10-19T12:59:09.353

11

You can use HSSFSheet.autoSizeColumn(columnNumber) method to align the columns perfectly.

This method adjusts the column width to fit the contents, read the doc.

After setting all cell values for all columns you can use this method, in your current code call this method after for loop.

Sample code

sheet.autoSizeColumn(1);
sheet.autoSizeColumn(2);

Note - You have to do this separately for all columns which you want to be aligned and the call to sheet.autoSizeColumn(columnNumber) should be made after populating the data into the excel. Calling before populating data will not have any effect.

edited Oct 19 '16 at 12:59

answered Oct 19 '16 at 12:52

Ravikumar

891
12
22

Be aware that autoSizeColumn is slow, especially on sheets with merged regions. – IceArdor Oct 20 '16 at 02:31
Hi I know I'm late but when i use sheet.autoSizeColumn(0); it seems to work correctly but still be slightly smaller than needed. If i autofit the column width's in excel itself it ticks up a few pixels, has anyone else experienced this or does anyone know how to fix this? – Brendan Robert Apr 03 '17 at 23:02
@BrendanRobert Not sure but may be font issue. Check out these links [link1](http://stackoverflow.com/questions/16943493/apache-poi-autosizecolumn-resizes-incorrectly) and [link2](http://stackoverflow.com/questions/11573993/setting-column-width-in-apache-poi/11574647). – Ravikumar Apr 04 '17 at 10:29
giving Error: java.lang.NoClassDefFoundError: Failed resolution of: Ljava/awt/font/FontRenderContext; at org.apache.poi.ss.util.SheetUtil.(SheetUtil.java:111) at org.apache.poi.ss.util.SheetUtil.getColumnWidth(SheetUtil.java:235) at org.apache.poi.hssf.usermodel.HSSFSheet.autoSizeColumn(HSSFSheet.java:2165) at org.apache.poi.hssf.usermodel.HSSFSheet.autoSizeColumn(HSSFSheet.java:2147) – Ramesh Bhati May 23 '22 at 12:34

How to auto adjust the column in excel in apache POI

1 Answers1