Getting font size from excel spreadsheet using Java

Question

I'm trying to get the font size of the header on an excel spreadsheet but I haven't been able to get it. I tried using the following to get the size but I haven't been able to get the size. None of the following worked for me because it doesn't return the correct font size. headerFont.getFontHeight (); headerFont.getFontHeightInPoints (); Any suggestion?

Below is the code that I have:

    try {
        FileInputStream file = new FileInputStream(new File(fileName));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(1);

        int numRows = sheet.getLastRowNum() + 1;
        int numCols = sheet.getRow(0).getLastCellNum();

        Iterator<Row> rowIterator = sheet.iterator();

        for (int i = 0; i < 1; i++) {
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            for (int j = 0; j < numCols; j++) {
                Cell cell = cellIterator.next();
                Font headerFont = workbook.createFont();
                headerFontFamily = headerFont.getFontName();
                headerFont.getFontHeight();
                headerFont.getFontHeightInPoints();

            }
        }
        file.close();
    } catch (Exception e) {

    }

Answer 1

You need to get the fonts from the cells. Fonts are part of the cell styles. Cell styles can be got via Cell.getCellStyle. Then the index of the used font can be got as a short via CelStyle.getFontIndex or as int via CelStyle.getFontIndexAsInt or as int via CelStyle.getFontIndex dependig of apache poi version used. The latter works using current 5.0.0 version.

Complete example:

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

import java.io.FileInputStream;

class ReadExcel {

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

  Workbook workbook = WorkbookFactory.create(new FileInputStream("./ExcelExample.xlsx"));
  FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();  
 
  DataFormatter dataFormatter = new DataFormatter();
  
  Sheet sheet = workbook.getSheetAt(0);
  for (Row row : sheet) {
   for (Cell cell : row) {
    String value = dataFormatter.formatCellValue(cell, evaluator);
    System.out.println(value);
    CellStyle style = cell.getCellStyle();
    //short fontIdx = style.getFontIndex(); // depends on apache poi version
    //int fontIdx = style.getFontIndexAsInt(); // depends on apache poi version
    int fontIdx = style.getFontIndex(); // depends on apache poi version
    Font font = workbook.getFontAt(fontIdx);
    System.out.println(font.getFontName() + ", " + font.getFontHeightInPoints());
   }
  }
  workbook.close();
 }
}

Note: This only works if the cell only has one font. If the cell contains rich text strings, then there are fonts for each formatting text run. Then RichTextString needs to be got and traversed. This is much more complex and needs to be done different for HSSF and XSSF.