Apache POI get Font Metrics

Question

I want to auto-size columns in excel, but without spending too much performance. The built-in auto-size of Apache POI is very slow (didn't finish after couple hours with 1 million rows). To save performance I just want to approximate the cell width, but for that I need the Font Metrics.

Apache POI has a class called FontDetails, but it does not work on its own. The class StaticFontMetrics seems to be the one actually loading the metrics, but it is not public. But even with copying the protected code to my workspace and making it accessible it fails to load the Font Metrics.

How can I get those Metrics? Will java.awt.FontMetrics always return an accurate result?

EDIT: The stacktrace I get when trying to get the Metrics of a Font:

Caused by: java.lang.IllegalArgumentException: The supplied FontMetrics doesn't know about the font 'Calibri', so we can't use it. Please add it to your font metrics file (see StaticFontMetrics.getFontDetails
at ourpackagestructure.apachepoi.FontDetails.create(FontDetails.java:106)
at ourpackagestructure.apachepoi.StaticFontMetrics.getFontDetails(StaticFontMetrics.java:94)

Answer 1

Apache poi uses AttributedString and TextLayout to get the bounds out of a text in a special font.

So, as long as the whole column is in same font, best approach would be first get the longest string which shall be stored in that column. Then get the width of that string in that font using java.awt.font.TextLayout. Then set this as the column width.

Note, the column width in Excel is set in in units of 1/256th of a default character width. So in addition to the width of the string in that font in pixels, you need the width of a default character to calculate the Excel column width.

Example:

import java.awt.font.FontRenderContext;
import java.awt.font.TextAttribute;
import java.awt.font.TextLayout;
import java.text.AttributedString;
import java.awt.geom.Rectangle2D;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.SheetUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ApachePoiGetStringWidth {

 public static void main(String args[]) throws Exception {

  String testString = "Lorem ipsum semit dolor";
  String fontName = "Calibri";
  short fontSize = 24;
  boolean italic = true;
  boolean bold = false;

  Workbook workbook = new XSSFWorkbook();
  Font font = workbook.createFont();
  font.setFontHeightInPoints(fontSize);
  font.setFontName(fontName);
  font.setItalic(italic);
  font.setBold(bold);
  CellStyle style = workbook.createCellStyle();
  style.setFont(font);

  AttributedString attributedString = new AttributedString(testString);
  attributedString.addAttribute(TextAttribute.FAMILY, font.getFontName(), 0, testString.length());
  attributedString.addAttribute(TextAttribute.SIZE, (float)font.getFontHeightInPoints());
  if (font.getBold()) attributedString.addAttribute(TextAttribute.WEIGHT, TextAttribute.WEIGHT_BOLD,  0, testString.length());
  if (font.getItalic()) attributedString.addAttribute(TextAttribute.POSTURE, TextAttribute.POSTURE_OBLIQUE,  0, testString.length());

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

  TextLayout layout = new TextLayout(attributedString.getIterator(), fontRenderContext);
  Rectangle2D bounds = layout.getBounds();
  double frameWidth = bounds.getX() + bounds.getWidth();

System.out.println(frameWidth);

  Sheet sheet = workbook.createSheet();
  Row row = sheet.createRow(2);
  Cell cell = row.createCell(2);
  cell.setCellValue(testString);
  cell.setCellStyle(style);

  int defaultCharWidth = SheetUtil.getDefaultCharWidth(workbook);
  sheet.setColumnWidth(2, (int)Math.round(frameWidth / defaultCharWidth * 256));

  try (java.io.FileOutputStream out = new java.io.FileOutputStream("Excel.xlsx")) {
   workbook.write(out);
  }

  workbook.close();

 }
}