Auto size height for rows in Apache POI

Question

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?

score 31 · Answer 1 · edited Mar 18 '15 at 20:00

31

currentRow.setHeight((short)-1)

Works for XSSFCell and Excel 2013

edited Mar 18 '15 at 20:00

Opal

81,889
28
189
210

answered Mar 18 '15 at 19:41

Franz Frühwirth

327
3
2

1

Shockingly, this also worked in NPOI (the .NET implementation) and opens correctly in Excel 2013. Syntax is slightly different: `row.Height = -1;` – Tyler Forsythe Jun 07 '17 at 01:27
12

This solution does not work for Ubuntu's LibreOffice version 5.1.6.2 and POI 3.17, but it does work if document is opened with MSExcel – Oscar Pérez May 21 '18 at 07:58
Exactly the good answer! – sadati boina Sep 13 '22 at 07:14

score 22 · Answer 2 · edited Jun 25 '18 at 07:07

22

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.

edited Jun 25 '18 at 07:07

Paolo Forgia

6,572
8
46
58

answered Feb 09 '15 at 10:37

Vinil Vijayan

245
2
2

8

`style.setWrapText(true)` was enough. Thanks! – Rubens Mariuzzo Mar 25 '15 at 14:59
10

Eee...no this does not work. At least not on Ubuntu Linux with Libre Office – ed22 Nov 14 '16 at 23:46
2

@ed22, doesn't work for me as well. Same environment. – mr.nothing Dec 02 '16 at 07:17
1

This is a Libre Office related issue. Spreadsheet opens correctly in MS Excel – ed22 Dec 03 '16 at 13:02
1

I also have to use Libre Office and this doesn't solve the row height problem. Is there any way to make Libre Office display better row heights? I have a cell with a slightly bigger font in it. Libre Office doesn't increase the row height automatically, whereas MS Office and Open Office increase the height in a nice way. – Sky Mar 28 '17 at 15:02
style.setWrapText(true) was enough in my case! There are merged cells present. – Ekaterina Ivanova iceja.net Feb 12 '21 at 12:54
doesn't work with MS Office 365 – iroel Jan 28 '22 at 12:04
Hi wraps the text for cells do not have new line as well. How to wrap the text only for cell contain new line? – Prem Jun 12 '23 at 06:26

score 5 · Answer 3 · answered Oct 03 '13 at 17:08

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.

Note: This only works with cells that have `\n` on their values. You have to determine where to place the `\n` on your own. If there is none, the row will have one line height — Gideon, Feb 26 '19 at 07:51

GreenGiant · Answer 4 · 2014-01-20T18:53:46.380

See all this link, which provides some code to manually calculate the correct height for a row, based on the column width and cell content. I've not personally tested it. Also pasted below for convenience:

// Create Font object with Font attribute (e.g. Font family, Font size, etc) for calculation
java.awt.Font currFont = new java.awt.Font(fontName, 0, fontSize);
AttributedString attrStr = new AttributedString(cellValue);
attrStr.addAttribute(TextAttribute.FONT, currFont);

// Use LineBreakMeasurer to count number of lines needed for the text
FontRenderContext frc = new FontRenderContext(null, true, true);
LineBreakMeasurer measurer = new LineBreakMeasurer(attrStr.getIterator(), frc);
int nextPos = 0;
int lineCnt = 0;
while (measurer.getPosition() < cellValue.length())
{
    nextPos = measurer.nextOffset(mergedCellWidth); // mergedCellWidth is the max width of each line
    lineCnt++;
    measurer.setPosition(nextPos);
}

Row currRow = currSht.getRow(rowNum);
currRow.setHeight((short)(currRow.getHeight() * lineCnt));

// The above solution doesn't handle the newline character, i.e. "\n", and only
// tested under horizontal merged cells.

The last line must use either: row.getHeightInPoints() or currSht.getDefaultRowHeightInPoints() instead of "getHeight()" — ed22, Nov 15 '16 at 18:42

score 3 · Answer 5 · answered Jun 30 '21 at 10:55

3

In my case a robust solution was to calculate the number of lines and set the row height to a multiple of the default row height:

int numberOfLines = cell.getStringCellValue().split("\n").length;
row.setHeightInPoints(numberOfLines*sheet.getDefaultRowHeightInPoints());

answered Jun 30 '21 at 10:55

ekraus

124
4

pointless since you get this even without new lines – Enerccio Feb 17 '23 at 10:11

Miller Cy Chan · Answer 6 · 2018-09-14T08:38:51.233

2

cell.getRow().setHeight((short) -1);

Worked for HSSFCell in apache poi 3.9 or above

edited Sep 14 '18 at 08:38

answered Mar 04 '16 at 07:01

Miller Cy Chan

897
9
19

score 1 · Answer 7 · answered Apr 21 '19 at 09:05

It works in Excel 2010. I set the limit of cell length of 50 characters

    Row row = sheet.createRow(0);
    CellStyle style = workbook.createCellStyle();
    style.setWrapText(true);
    if (data.length() > 50) {
        for (int i = 1; i <= Math.abs(data.length() / 50); i++) {
            data = data.substring(0, i * 50) + "\n" + data.substring(i * 50);
        }
        Cell cell = row.createCell(0);
        row.setRowStyle(style);
        cell.setCellStyle(style);
        cell.setCellValue(data);
        sheet.autoSizeColumn(0);
    }

sooo no auto height – Joaquín L. Robles Oct 07 '21 at 15:02 — Joaquín L. Robles, Oct 07 '21 at 15:02

score 0 · Answer 8 · answered Dec 13 '13 at 10:50

0

You can't adjust cell height directly. But you can change the row's height

final HSSFSheet fs = wb.createSheet("sheet1");
final HSSFRow row0 = fs.createRow(0);
final HSSFCell cellA1 = row0.createCell(0);
row0.setHeight((short)700);

answered Dec 13 '13 at 10:50

Arthur

151
2
11

10

This is not automatic sizing (auto-size), it is manual sizing. – lu_ko Oct 29 '15 at 15:39

score -1 · Answer 9 · answered Dec 13 '13 at 10:55

-1

Row aitosize work for me:

cell.getRow().setHeight((short)0);

Here 0 for calculate autoheight.

answered Dec 13 '13 at 10:55

Michael Kazarian

4,376
1
21
25

2

This doesn't appear to work. When I open in Excel (2010), the row is simply zero height. – GreenGiant Jan 20 '14 at 17:47
1

@GreenGiant, I don't use Windows and Excel. This solution work for OpenOffice. Also it problem to set automatically row height for `merged` cells in Excel ([need macro](http://blog.contextures.com/archives/2012/06/07/autofit-merged-cell-row-height/) for fix it). – Michael Kazarian Jan 21 '14 at 06:16
Sadly, this will set to zero heigh a.k.a invisible – iroel Jan 28 '22 at 12:07
value -1 actually worked for me – Sergey Karpushin Jul 07 '23 at 13:57

score -1 · Answer 10 · answered Oct 19 '20 at 13:22

Workaround for “LibreOffice Calc“ and “WPS Spreadsheet” with auto height for merged sells.

I add a column out to the right of a main document (In my case it was 32 column) Set width as all merged cells with same text. Set style WrapText to true Set style to Align Top Copy content which will be displayed in the merged cells Set that column to be hidden Set a row height = -1

A sample of code:

   private void applyRowHightWorkaroundForMergedCells(HSSFCell cell0) {
       HSSFSheet sheet = cell0.getSheet();
       HSSFRow row = cell0.getRow();
    
       String value = cell0.getStringCellValue();
       HSSFCell cell = row.createCell(32);
       sheet.setColumnWidth(32, 32000);
       cell.getCellStyle().setWrapText(true);
       cell.getCellStyle().setVerticalAlignment(VerticalAlignment.TOP);
       cell.setCellValue(value);
       sheet.setColumnHidden(32, true);
       row.setHeight((short) -1);
   }

score -11 · Answer 11 · answered Oct 03 '13 at 05:53

-11

//we can use column width for sheet

Ex: sheet.setColumnWidth(0, 2000);

answered Oct 03 '13 at 05:53

swamy

1,200
10
23

1

The OP is asking about adjusting row height not column width. – GreenGiant Jan 18 '14 at 00:41

Auto size height for rows in Apache POI

11 Answers11

Linked