Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Auto size height for rows in Apache POI

I am inputting values into a spreadsheet using Apache POI. These values have newlines, and I was able to use this code successfully:

CellStyle style = cell.getCellStyle()
style.setWrapText(true)
cell.setCellStyle(style)

Unfortunately, while the text is wrapping correctly, the rows are not always growing in height enough to show the content. How do I ensure that my rows are always the correct height?

like image 778
user1007895 Avatar asked Oct 02 '13 19:10

user1007895


People also ask

How do I change the row height in Apache POI?

row. setHeightInPoints((2 * sheet. getDefaultRowHeightInPoints())); to set it (for example) to 2 characters high.

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

sheetName. autoSizeColumn(columnIndex);

How do you make Excel cells expand to fit text automatically in Java?

AutoFit Columns and Rows Using the Ribbon In Excel, using the Ribbon option you can autofit the text by following the given steps: Choose one or all the columns given on the sheet to AutoFit column width. For this, you need to go to the Home tab > Cells group > Format > AutoFit Column Width.


3 Answers

currentRow.setHeight((short)-1)

Works for XSSFCell and Excel 2013

like image 145
Franz Frühwirth Avatar answered Oct 13 '22 21:10

Franz Frühwirth


HSSFWorkbook workbook=new HSSFWorkbook();
HSSFSheet sheet =  workbook.createSheet("FirstSheet");  
HSSFRow rowhead=   sheet.createRow((short)0);
HSSFCellStyle style = workbook.createCellStyle();
style.setWrapText(true);
row.setRowStyle(style);
row.getCell(0).setCellStyle(style);

The above code will generate dynamic height of rows.

like image 40
Vinil Vijayan Avatar answered Oct 13 '22 21:10

Vinil Vijayan


The only way I got this to work was write my own implementation to calculate the row height. The code is now released as the Taro project, so you could use that. It has numerous convenience methods to let you write an Excel file in far fewer lines of code.

If you prefer to put the implementation in your own code, you can find it in the SpreadsheetTab class. There is an autoSizeRow(int rowIndex) method half way down. It basically iterates down the row and for each cell finds the number of lines of text, then uses the font size to calculate the optimal cell height. It then sets the row height to the height of the tallest cell.

like image 5
JMB Avatar answered Oct 13 '22 22:10

JMB