CODEWITHSUNDEEP
javaexcelapache-poi
user1007895
user1007895

Reputation: 3965

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?

Upvotes: 46

Views: 128636

Answers (11)

ekraus
ekraus

Reputation: 124

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

Upvotes: 3

Joter
Joter

Reputation: 316

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

Upvotes: -1

Valery Tarasenko
Valery Tarasenko

Reputation: 19

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

Upvotes: 1

Miller Cy Chan
Miller Cy Chan

Reputation: 947

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

Worked for HSSFCell in apache poi 3.9 or above

Upvotes: 2

Vinil Vijayan
Vinil Vijayan

Reputation: 255

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.

Upvotes: 23

Franz Fr&#252;hwirth
Franz Fr&#252;hwirth

Reputation: 347

currentRow.setHeight((short)-1)

Works for XSSFCell and Excel 2013

Upvotes: 33

GreenGiant
GreenGiant

Reputation: 5236

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.

Upvotes: 4

Michael Kazarian
Michael Kazarian

Reputation: 4462

Row aitosize work for me:

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

Here 0 for calculate autoheight.

Upvotes: -1

Arthur
Arthur

Reputation: 151

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

Upvotes: 0

JMB
JMB

Reputation: 1959

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.

Upvotes: 6

swamy
swamy

Reputation: 1210

//we can use column width for sheet

Ex: sheet.setColumnWidth(0, 2000);

Upvotes: -11

Related Questions