How to set fixed column width in Apache POI

Question

How to set fixed column width in Apache POI. I want to make my first column to fixed width.

I have tried with sheet.setColumnWidth(0, 1000); cellStyle.setWrapText(true); //Set wordwrap it is not reflecting

    public XSSFWorkbook generateReport(List<Dto> result, boolean isRes, boolean isRes1) {
    XSSFWorkbook workbook = null;
    XSSFSheet sheet = null;
    XSSFRow row = null;
    XSSFCell cell = null;
    String[] headers = null;
    int rowNum = 0;
    int colNum = 0;
    CellStyle cellStyle = null;
    CellStyle headerStyle = null;
    XSSFFont font = null;
    CellStyle datecellStyle = null;
    /* set the weight of the font */



    try {
        workbook = new XSSFWorkbook();

        headers = new String[] { ...values goes here...};
        row = sheet.createRow(rowNum);
        font = workbook.createFont();
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);

        headerStyle = workbook.createCellStyle();
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        headerStyle.setFillForegroundColor((short) 200);
        headerStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        headerStyle.setFont(font);

        cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);



        datecellStyle = workbook.createCellStyle();
        datecellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("dd-MMM-yyyy"));
        datecellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        datecellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        datecellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        datecellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);



        /**
         * Writing Headers
         */
        for (String header : headers) {
            cell = row.createCell(colNum);
            cell.setCellValue(header);
            cell.setCellStyle(headerStyle);
            ++colNum;
        }

        /**
         * Writing Other Rows
         */
        SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
        for (Dto detail : result) {
            ++rowNum;
            colNum = 0;
            row = sheet.createRow(rowNum);
            cell = row.createCell(colNum);
            //sheet.setColumnWidth(0, 4000);
            cell.
            if(null != detail.getGid()){
                cell.setCellValue(detail.getGid());
            }else{
                cell.setCellValue("-");
            }
            cell.setCellStyle(cellStyle);

            ++colNum;
            cell = row.createCell(colNum);
            if(null != detail.getName()){
                cell.setCellValue(detail.getName());
            }else{
                cell.setCellValue("-");
            }
            cell.setCellStyle(cellStyle);

            ++colNum;
            cell = row.createCell(colNum);
            if(null != detail.getNGid()){
                cell.setCellValue(detail.getNGid());
            }else{
                cell.setCellValue("-");
            }
            cell.setCellStyle(cellStyle);

            ++colNum;
            cell = row.createCell(colNum);
            if(null != detail.getName()){
                cell.setCellValue(detail.getName());
            }else{
                cell.setCellValue("-");
            }
            cell.setCellStyle(cellStyle);




        }

        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }
        sheet.createFreezePane(1, 1);

    } catch (Exception e) {
        e.printStackTrace();
    }
    return workbook;
}

So you don't want the width of column 0 to be formatted autosize? So simple exclude `i==0` from `sheet.autoSizeColumn(i);`. — Axel Richter, Feb 07 '17 at 10:32

score 43 · Answer 1 · answered Jan 13 '20 at 14:40

43

You can set the column width using setColumnWidth method of XSSFWorkbook. The 1st parameter is the column number (starts from zero) and the 2nd parameter is the width. We need to be little tricky here to set the width. To set the width as 25 we need to pass the parameter as 25 * 256.

XSSFSheet sheet = workbook.createSheet("MySheet");
sheet.setColumnWidth(3, 25 * 256);

answered Jan 13 '20 at 14:40

Sarath Subramanian

20,027
11
82
86

For setting columns for all columns, you have to first get the number of columns into a variable and then loop the code inside the loop `sheet.setColumnWidth(3, 25 * 256);` where `3` will be the counter in your loop – Sarath Subramanian Aug 09 '20 at 11:45
4

To set the column width in one go, we can use `sheet.setDefaultColumnWidth();` – mantri Aug 11 '20 at 04:35
check this answer, it gives a more accurate x256 calculation. https://stackoverflow.com/a/59818288/557968 – sarath Feb 13 '22 at 06:00

score 25 · Accepted Answer · answered Feb 07 '17 at 10:34

25

setColumnWidth(int, int) should work ... is it because you reset the sizes to auto in your loop?

for (int i = 0; i < headers.length; i++) {
    sheet.autoSizeColumn(i);
}

Start your loop from 1 to headers.length instead.

answered Feb 07 '17 at 10:34

Nikolas

2,066
1
19
20

How to set fixed column width in Apache POI

2 Answers2

Linked