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?

Florafloral

Answer 1

currentRow.setHeight((short)-1)

Works for XSSFCell and Excel 2013

Steadman

Shockingly, this also worked in NPOI (the .NET implementation) and opens correctly in Excel 2013. Syntax is slightly different: row.Height = -1; – Teakettle

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 – Saimon

Exactly the good answer! – Ancell

Answer 2

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.

Kauai

Eee...no this does not work. At least not on Ubuntu Linux with Libre Office – Narrows

@ed22, doesn't work for me as well. Same environment. – Violaviolable

This is a Libre Office related issue. Spreadsheet opens correctly in MS Excel – Narrows

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. – Dodecasyllable

style.setWrapText(true) was enough in my case! There are merged cells present. – Damascus

doesn't work with MS Office 365 – Laundromat

Hi wraps the text for cells do not have new line as well. How to wrap the text only for cell contain new line? – Gibrian

Answer 3

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.

Bonanza

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 – Biblicist

Answer 4

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.

Siouan

The last line must use either: row.getHeightInPoints() or currSht.getDefaultRowHeightInPoints() instead of "getHeight()" – Narrows

Answer 5

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());

Revue

pointless since you get this even without new lines – Woolson

Answer 6

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

Worked for HSSFCell in apache poi 3.9 or above

Primm

Answer 7

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);
    }

Sesquicentennial

sooo no auto height – Hobbs

Answer 8

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);

Huihuie

This is not automatic sizing (auto-size), it is manual sizing. – Lookthrough

Answer 9

Row aitosize work for me:

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

Here 0 for calculate autoheight.

Ochs

This doesn't appear to work. When I open in Excel (2010), the row is simply zero height. – Siouan

@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 for fix it). – Ochs

Sadly, this will set to zero heigh a.k.a invisible – Laundromat

value -1 actually worked for me – Laundrywoman

Answer 10

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);
   }

Hedi

Answer 11

//we can use column width for sheet

Ex: sheet.setColumnWidth(0, 2000);

Carmancarmarthen

The OP is asking about adjusting row height not column width. – Siouan