Apache POI and autofit XSSFRow heigth by content

Question

I have fixed width of column and need autofit row height with apache POI, but I can't find working desicion for it. I tried this decision, but it doesn't work with this text

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua

Column width is 33.00 (236 pixels) and cell in table with wrap text property. I can change row height only if content height more that existing height of row.

So, I would like to ask if there is any method or desicions for autosize the row height by content?

private float calculateRowLinesForText(Font cellFont, float columnWidthInPoints, String value) {
        java.awt.Font currFont = new java.awt.Font(cellFont.getFontName(), 0, cellFont.getFontHeightInPoints());

        FontRenderContext frc = new FontRenderContext(null, true, true);

        int lineCnt = 0;
        for (String partValue : value.split("\n")) {
            AttributedString attrStr = new AttributedString(partValue);
            attrStr.addAttribute(TextAttribute.FONT, currFont);
            LineBreakMeasurer measurer = new LineBreakMeasurer(attrStr.getIterator(), frc);

            while (measurer.getPosition() < partValue.length()) {
                int nextPos = measurer.nextOffset(columnWidthInPoints);
                lineCnt++;
                measurer.setPosition(nextPos);
            }
        }

        return lineCnt;
}

Axel Richter · Accepted Answer · 2021-03-26T05:50:28.130

5

As long as the row is not within a merged region, then it will auto height if the height is not set explicitly. So you need set height to undefined (-1). See Row.setHeight:

Set the row's height or set to ff (-1) for undefined/default-height.

Complete example:

Source Excel.xlsx:

Code:

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellUtil;

public class CreateExcelCellWrapText {

 public static void main(String[] args) throws Exception {
  Workbook workbook = WorkbookFactory.create(new FileInputStream("./Excel.xlsx"));

  Sheet sheet = workbook.getSheetAt(0);
  
  Row row = sheet.getRow(2);
  Cell cell = row.getCell(2); // cell C3
  cell.setCellValue("Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua");
  CellUtil.setCellStyleProperty(cell, CellUtil.WRAP_TEXT, true); // make sure wrap text is set.
  row.setHeight((short)-1); // set row heigth undefined so it is auto heigth

  FileOutputStream out = new FileOutputStream("./ExcelNew.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();
 }
}

Result ExcelNew.xlsx:

If you don't know which rows must be auto height and need to decide that from the required height, then you need calculating the required height dependent of cell width, text and used font. That's a challenging task. See How to get the needed height of a multi line rich-text field (any font, any font size) having defined width using Java? for a possible solution.

edited Mar 26 '21 at 05:50

answered Mar 25 '21 at 16:56

Axel Richter

56,077
6
60
87

Yes, I saw this decision. I need set this only for rows which has content more that current height. If row has 53 height and text "B1" that I not change this. And I need iterate by all rows. – Andrew Sulyz Mar 25 '21 at 20:27
1

So you don't know which rows must be auto height and need to decide that from the required height? So you need calculating the required height? That's a challenging task. But see https://stackoverflow.com/questions/45420801/how-to-get-the-needed-height-of-a-multi-line-rich-text-field-any-font-any-font/45437726#45437726. – Axel Richter Mar 26 '21 at 05:08
Your decision works well. Can you explain, please, how work this magic?) Why we use 72? And how I need do it if I develop server application? `(72f/ppi)` – Andrew Sulyz Mar 29 '21 at 13:42
1

@Andrew Sulyz : The `72/ppi` is needed to convert from `px` to `pt` as we need `pt` to set the height in `Excel`. A `pt` is an inch point and there are 72pt in an inch. The `ppi` is `px` per inch in screen resoluntion. And my solution uses a `Swing` `JTextPane` to render the text. So it needs a `GUI` in background. – Axel Richter Mar 29 '21 at 14:51
Thank you very much! – Andrew Sulyz Mar 29 '21 at 15:48
I add for this answer this answer for wrap long words. https://stackoverflow.com/questions/8666727/wrap-long-words-in-jtextpane-java-7 – Andrew Sulyz Mar 30 '21 at 06:01

Apache POI and autofit XSSFRow heigth by content

1 Answers1