How to get the needed height of a multi line rich-

Question

I am having a string of X Font(like Arial font) having Y height in define value of the width in such a way that string can comes into multiple lines. I need to calculate the required height so that required string can be fit into it. Auto size of row in Apache POI can't be possible as I require height for the rich text string(any font and height) present in merge cell of the row, in this scenario auto size doesn't work.

Answer 1

Found a solution using a JTextPane to render the text.

Sample code is at first the whole text in one font and fontsize. But since JTextPane provides a StyledDocument, rich text content should also be possible to handle. (TODO).

The code comments why code parts are needed.

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class CreateExcelCellWrapTextRenderedHeight {

 public static void main(String[] args) throws Exception {
  XSSFWorkbook workbook = new XSSFWorkbook();

  Font font = workbook.createFont();
  font.setFontName("Arial");
  font.setFontHeightInPoints((short)19);

  CellStyle cellstyle = workbook.createCellStyle();
  cellstyle.setWrapText(true);
  cellstyle.setFont(font);

  Sheet sheet = workbook.createSheet();

  sheet.setColumnWidth(3, 25*256);

  Row row = sheet.createRow(0);

  String text = "String cell content\nhaving line wrap.\nIt has new line marks and then a long text without new line marks.\nFollowed by short text part.\n\nGreetings from Axel so long";

  Cell cell = row.createCell(2);
  cell.setCellValue(text);
  cell.setCellStyle(cellstyle);

  CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 2, 4);
  sheet.addMergedRegion(cellRangeAddress);

//__________________________calculate text height by rendering the text

  //get the used font 
  Font usedfont = workbook.getFontAt(cell.getCellStyle().getFontIndex());

  //get the used font name
  String fontname = usedfont.getFontName();
System.out.println(fontname);

 //get the used font size
  short fontheight = usedfont.getFontHeightInPoints();
System.out.println(fontheight);

  //get the width of the colunms in pixels
  float colwidth = 0;
  for (int c = cellRangeAddress.getFirstColumn(); c <= cellRangeAddress.getLastColumn(); c++) {
   colwidth += sheet.getColumnWidthInPixels(c);
  }
System.out.println(colwidth);

  //get screen resolution
  int ppi = java.awt.Toolkit.getDefaultToolkit().getScreenResolution();
System.out.println(ppi);

  //create a font - correct the size to be appropriate to the screen resolution
  java.awt.Font awtFont = new java.awt.Font(fontname, java.awt.Font.PLAIN, Math.round(fontheight/(72f/ppi)));

  //create a JTextPane to render the text
  javax.swing.JTextPane textpane = new javax.swing.JTextPane();

  //set the font
  textpane.setFont(awtFont);

  //set dimension of the JTextPane to colwidth and maximum height
  java.awt.Dimension dimension = new java.awt.Dimension(Math.round(colwidth), Integer.MAX_VALUE);
  textpane.setSize(dimension);

  //Excels cells have different line spacing than default JTextPane
  javax.swing.text.MutableAttributeSet attributeset = new javax.swing.text.SimpleAttributeSet(textpane.getParagraphAttributes());
  javax.swing.text.StyleConstants.setLineSpacing(attributeset, 0.1f);

  javax.swing.text.StyledDocument document = textpane.getStyledDocument();
  document.setParagraphAttributes(0, document.getLength(), attributeset, true);

  //insert the text - TODO: handle rich text
  document.insertString(0, text, null);

  //resize dimension to preferred height
  dimension.setSize(Math.round(colwidth), textpane.getPreferredSize().getHeight());
  textpane.setPreferredSize(dimension);

  double textwidthpx = dimension.getWidth();
System.out.println(textwidthpx);
  double textheightpx = dimension.getHeight();
System.out.println(textheightpx);

  //textheightpx is in pixel, but we need points
  float textheight = (float)textheightpx * (72f/ppi);
System.out.println(textheight);

//__________________________

  row.setHeightInPoints(textheight);

  workbook.write(new FileOutputStream("CreateExcelCellWrapTextRenderedHeight.xlsx"));
  workbook.close();

  javax.swing.SwingUtilities.invokeLater(new Runnable() {
   public void run() {
    new TextPaneDemo(textpane).setVisible(true);
   }
  });

 }

 static class TextPaneDemo extends javax.swing.JFrame {

  TextPaneDemo(javax.swing.JTextPane textpane) {
   super("TextPaneDemo");
   this.setDefaultCloseOperation(javax.swing.JFrame.DISPOSE_ON_CLOSE);
   this.getContentPane().add(textpane, java.awt.BorderLayout.CENTER);
   this.pack();
  }

 }

}

The JTextPane content is nearly exact as the content in the Excel cell. So the height will be mostly accurate.

The result of a test using multiple fonts and font sizes is also not so poor.

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class CreateExcelCellWrapTextRenderedHeightTest {

 static float getPreferredHeight(Cell cell, String text, CellRangeAddress cellrangeaddress) throws Exception {
  //get the used font 
  Font usedfont = cell.getSheet().getWorkbook().getFontAt(cell.getCellStyle().getFontIndex());

  //get the used font name
  String fontname = usedfont.getFontName();

  //get the used font size
  short fontheight = usedfont.getFontHeightInPoints();

  //get the width of the colunms in pixels
  float colwidth = 0;
  for (int c = cellrangeaddress.getFirstColumn(); c <= cellrangeaddress.getLastColumn(); c++) {
   colwidth += cell.getSheet().getColumnWidthInPixels(c);
  }

  //get screen resolution
  int ppi = java.awt.Toolkit.getDefaultToolkit().getScreenResolution();

  //create a font - correct the size to be appropriate to the screen resolution
  java.awt.Font awtFont = new java.awt.Font(fontname, java.awt.Font.PLAIN, Math.round(fontheight/(72f/ppi)));

  //create a JTextPane to render the text
  javax.swing.JTextPane textpane = new javax.swing.JTextPane();

  //set the font
  textpane.setFont(awtFont);

  //set dimension of the JTextPane to colwidth and maximum height
  java.awt.Dimension dimension = new java.awt.Dimension(Math.round(colwidth), Integer.MAX_VALUE);
  textpane.setSize(dimension);

  //Excels cells have different line spacing than default JTextPane
  javax.swing.text.MutableAttributeSet attributeset = new javax.swing.text.SimpleAttributeSet(textpane.getParagraphAttributes());
  javax.swing.text.StyleConstants.setLineSpacing(attributeset, 0.1f);

  javax.swing.text.StyledDocument document = textpane.getStyledDocument();
  document.setParagraphAttributes(0, document.getLength(), attributeset, true);

  //insert the text
  document.insertString(0, text, null);

  //resize dimension to preferred height
  dimension.setSize(Math.round(colwidth), textpane.getPreferredSize().getHeight());
  textpane.setPreferredSize(dimension);

  double textheightpx = dimension.getHeight();

  //textheightpx is in pixel, but we need points
  float textheight = (float)textheightpx * (72f/ppi);

  return textheight;
 }

 public static void main(String[] args) throws Exception {
  Workbook workbook = new XSSFWorkbook();

  Sheet sheet = workbook.createSheet();
  sheet.setColumnWidth(3, 30*256);

  String text = "String cell content\nhaving line wrap.\nIt has new line marks and then a long text without new line marks.\nFollowed by short text part.\n\nGreetings from Axel so long";

  String[] fontnames = new String[]{"Arial", "Times New Roman", "Courier New", "Arial Black"};

  for (int r = 0; r < 16; r++) {

   Font font = workbook.createFont();
   font.setFontName(fontnames[(r % 4)]);

   font.setFontHeightInPoints((short)(r+6));

   CellStyle cellstyle = workbook.createCellStyle();
   cellstyle.setWrapText(true);
   cellstyle.setFont(font);

   Row row = sheet.createRow(r);

   Cell cell = row.createCell(2);
   cell.setCellValue(text);
   cell.setCellStyle(cellstyle);

   CellRangeAddress cellrangeaddress = new CellRangeAddress(r, r, 2, 4);
   sheet.addMergedRegion(cellrangeaddress);

   float textheight = getPreferredHeight(cell, text, cellrangeaddress);
   row.setHeightInPoints(textheight);

  }

  workbook.write(new FileOutputStream("CreateExcelCellWrapTextRenderedHeightTest.xlsx"));
  workbook.close();

 }

}