autoSizeColumn POI Java is not working properly

Question

I am using POI 3.9 & jdk1.6.0_14.

I am using the below code to autoSizeColumn, but the issue is when the excel is generated, its not completely autosized to column, when i double click on between columns, at that time i can see that column in autosized correctly.

for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            HSSFSheet thisSheet = workbook.getSheetAt(i);
            log.info("Last row : "+thisSheet.getLastRowNum());
            HSSFRow rowexcel = thisSheet.getRow(thisSheet.getLastRowNum());
            // Auto sizing columns
            for (short j = 0; j < rowexcel.getLastCellNum(); j++) {
                workbook.getSheetAt(i).autoSizeColumn(j);
            }
            // Freezing the top row
            workbook.getSheetAt(i).createFreezePane(0, 1);
        }

Instead of

HSSFRow rowexcel = thisSheet.getRow(thisSheet.getLastRowNum());

I have also tried with top row

HSSFRow rowexcel = thisSheet.getRow(0);

But still no resolution.

Do you have all the fonts you use present + available to Java on the system you're generating the file on? — Gagravarr, May 07 '13 at 15:22
Yes its using Arial font, which is present in Windows/Font. The generated file has Arial font too. — Soheb, May 07 '13 at 15:37
Does Java see it though? The column sizing is very font dependent, and if Java doesn't have access to the right font it can't properly calculate the width... — Gagravarr, May 07 '13 at 16:01
U can also use setColumnWidth for each header cells.So it will apply size for all the cells. — swamy, May 08 '13 at 05:39

score 1 · Answer 1 · edited May 23 '17 at 11:51

I experienced the exact issue you described and was able to find some success by explicitly setting the font via the Cell style as suggested in some of the comments above (also here).

However, one thing I noticed is that autoSizeColumn was still not taking into consideration the width of all cells. Particularly, I have a row of cells that are basically the column headers describing each column's data. These cells had the custom Font applied successfully, but were still not being considered with respect to the column's width when autoSizeColumn ran. There are differences, but I would have assumed they were irrelevant. For example, the headers had a different cell type from the rest of the data in the column... and the cell headers have different colors applied to make them stand out.

Having said that, try creating a sheet with just a very basic set of cell styles applied, and then try tweaking from there:

// Let's test with Arial, 10pt
Font testFont = workbook.createFont();
testFont.setFontName("Arial");
testFont.setFontHeightInPoints((short)10);

// We'll apply a very bare-bones style to our cells that just applies the Font
CellStyle testCellStyle = workbook.createCellStyle();
testCellStyle.setFont(testFont);

// Your real data cell creation would go here instead of my dummy code:
CreationHelper creationHelper = workbook.getCreationHelper();
Row testRow = thisSheet.createRow(0);
int currentColumn = 0;

Cell testCell = testRow.createCell(currentColumn++);
testCell.setCellStyle(testCellStyle);
testCell.setCellType(Cell.CELL_TYPE_STRING);
testCell.setCellValue(creationHelper.createRichTextString("Cell Data Goes Here");

testCell = testRow.createCell(currentColumn++);
testCell.setCellStyle(testCellStyle);
testCell.setCellType(Cell.CELL_TYPE_STRING);
testCell.setCellValue(creationHelper.createRichTextString("Your Real Code Won't Be This Redundant :)");

One final thought, if autoSizeColumn is still doing unfortunate or inconsistent things to column widths, you could add a safety net to ensure that columns don't get smaller than the default:

int origColWidth = thisSheet.getColumnWidth(currentColumn);
thisSheet.autoSizeColumn(currentColumn);

// Reset to original width if resized width is smaller than default/original
if (origColWidth > thisSheet.getColumnWidth(currentColumn))
  thisSheet.setColumnWidth(currentColumn, origColWidth);

autoSizeColumn POI Java is not working properly

1 Answers1