Cell Styles, Fonts & Formats

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.time.LocalDate;
import java.util.List;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class BookshelfHeaderStyles {

    record Book(String title, String author, int year, double price, LocalDate acquired, int copies) { }

    public static void main(String[] args) throws IOException {
        List<Book> books = List.of(
                new Book("Dune", "Frank Herbert", 1965, 12.5, LocalDate.of(2024, 3, 14), 4),
                new Book("The Left Hand of Darkness: a novel of Gethen", "Ursula K. Le Guin", 1969, 9.99,
                        LocalDate.of(2024, 5, 2), 2),
                new Book("Neuromancer", "William Gibson", 1984, 1234.5, LocalDate.of(2025, 1, 20), 11));

        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Books");

            // 1. Fonts first: a style points to a font, so create the font once.
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerFont.setFontName("Calibri");
            headerFont.setFontHeightInPoints((short) 11);
            headerFont.setColor(IndexedColors.WHITE.getIndex());

            // 2. One style per look, never one per cell.
            CellStyle header = workbook.createCellStyle();
            header.setFont(headerFont);
            header.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
            header.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            header.setAlignment(HorizontalAlignment.CENTER);
            header.setVerticalAlignment(VerticalAlignment.CENTER);
            header.setWrapText(true);
            header.setBorderBottom(BorderStyle.MEDIUM);
            header.setBottomBorderColor(IndexedColors.BLACK.getIndex());

            DataFormat formats = workbook.createDataFormat();

            CellStyle text = workbook.createCellStyle();
            text.setWrapText(true);
            text.setVerticalAlignment(VerticalAlignment.TOP);

            CellStyle money = workbook.createCellStyle();
            money.setDataFormat(formats.getFormat("#,##0.00\\ \"EUR\""));
            money.setVerticalAlignment(VerticalAlignment.TOP);

            CellStyle date = workbook.createCellStyle();
            date.setDataFormat(formats.getFormat("yyyy-mm-dd"));
            date.setAlignment(HorizontalAlignment.CENTER);
            date.setVerticalAlignment(VerticalAlignment.TOP);

            CellStyle count = workbook.createCellStyle();
            count.setDataFormat(formats.getFormat("#,##0"));
            count.setVerticalAlignment(VerticalAlignment.TOP);

            String[] titles = {"Title", "Author", "Year", "Price", "Acquired", "Copies"};
            Row headerRow = sheet.createRow(0);
            headerRow.setHeightInPoints(24);
            for (int c = 0; c < titles.length; c++) {
                Cell cell = headerRow.createCell(c);
                cell.setCellValue(titles[c]);
                cell.setCellStyle(header);
            }

            int r = 1;
            for (Book book : books) {
                Row row = sheet.createRow(r++);
                Cell title = row.createCell(0);
                title.setCellValue(book.title());
                title.setCellStyle(text);
                Cell author = row.createCell(1);
                author.setCellValue(book.author());
                author.setCellStyle(text);
                Cell year = row.createCell(2);
                year.setCellValue(book.year());
                Cell price = row.createCell(3);
                price.setCellValue(book.price());
                price.setCellStyle(money);
                Cell acquired = row.createCell(4);
                acquired.setCellValue(book.acquired());   // LocalDate overload since POI 4.1
                acquired.setCellStyle(date);
                Cell copies = row.createCell(5);
                copies.setCellValue(book.copies());
                copies.setCellStyle(count);
            }
            sheet.setColumnWidth(0, 30 * 256);
            sheet.setColumnWidth(1, 22 * 256);
            sheet.setColumnWidth(3, 14 * 256);
            sheet.setColumnWidth(4, 12 * 256);

            System.out.println("Cell styles in the workbook: " + workbook.getNumCellStyles());
            Path out = Files.createTempFile("books-inventory", ".xlsx");
            try (var os = Files.newOutputStream(out)) {
                workbook.write(os);
            }
            System.out.println("Written " + out);
        }
    }
}

Cell styles in the workbook: 6

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.IndexedColorMap;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ColorsAndRichText {

    public static void main(String[] args) throws IOException {
        // .xlsx: any 24-bit colour, given as RGB bytes (or a theme colour with a tint).
        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            IndexedColorMap indexedColors = workbook.getStylesSource().getIndexedColors();
            XSSFColor bookshelfBlue = new XSSFColor(new byte[] {(byte) 0x1F, (byte) 0x4E, (byte) 0x79}, indexedColors);

            XSSFFont font = workbook.createFont();
            font.setBold(true);
            font.setColor(new XSSFColor(new byte[] {(byte) 0xFF, (byte) 0xFF, (byte) 0xFF}, indexedColors));

            XSSFCellStyle style = workbook.createCellStyle();
            style.setFillForegroundColor(bookshelfBlue);
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setFont(font);

            Sheet sheet = workbook.createSheet("Colours");
            Cell cell = sheet.createRow(0).createCell(0);
            cell.setCellValue("Title");
            cell.setCellStyle(style);

            // Rich text: several fonts inside one cell. The cell style's font is the base font.
            CreationHelper helper = workbook.getCreationHelper();
            RichTextString rich = helper.createRichTextString("Dune (1965), out of print");
            XSSFFont italic = workbook.createFont();
            italic.setItalic(true);
            XSSFFont red = workbook.createFont();
            red.setColor(new XSSFColor(new byte[] {(byte) 0xC0, 0x00, 0x00}, indexedColors));
            rich.applyFont(0, 4, italic);            // "Dune"
            rich.applyFont(13, 25, red);             // "out of print"
            Cell richCell = sheet.getRow(0).createCell(1);
            richCell.setCellValue(rich);

            // Theme colour with a tint: follows the workbook theme when the user changes it in Excel.
            XSSFColor themed = new XSSFColor(indexedColors);
            themed.setTheme(4);                      // accent 1
            themed.setTint(0.6);                     // 60% lighter
            XSSFCellStyle light = workbook.createCellStyle();
            light.setFillForegroundColor(themed);
            light.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            sheet.getRow(0).createCell(2).setCellStyle(light);

            Path out = Files.createTempFile("colours", ".xlsx");
            try (var os = Files.newOutputStream(out)) {
                workbook.write(os);
            }
        }

        // .xls: only a 56-entry palette. Overwrite a slot you do not use, then refer to it by index.
        try (HSSFWorkbook workbook = new HSSFWorkbook()) {
            HSSFPalette palette = workbook.getCustomPalette();
            short slot = HSSFColorPredefined.LAVENDER.getIndex();
            palette.setColorAtIndex(slot, (byte) 0x1F, (byte) 0x4E, (byte) 0x79);
            HSSFColor custom = palette.getColor(slot);
            System.out.println("Slot " + slot + " now holds " + java.util.Arrays.toString(custom.getTriplet()));

            HSSFCellStyle style = workbook.createCellStyle();
            style.setFillForegroundColor(slot);
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }
    }
}

Slot 46 now holds [31, 78, 121]

import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.Map;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellPropertyType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.ss.util.PropertyTemplate;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderExtent;

public class PropertyTemplateAndMerges {

    public static void main(String[] args) throws IOException {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Summary");

            // A merged title across A1:F1. Only the top-left cell holds the value and the style.
            Row titleRow = sheet.createRow(0);
            Cell title = titleRow.createCell(0);
            title.setCellValue("Bookshelf inventory, October 2026");
            CellRangeAddress titleRegion = new CellRangeAddress(0, 0, 0, 5);
            sheet.addMergedRegion(titleRegion);          // throws IllegalStateException on overlap
            CellUtil.setAlignment(title, HorizontalAlignment.CENTER);

            // Borders on a block of cells without creating the cells' styles by hand.
            for (int r = 2; r <= 6; r++) {
                Row row = sheet.createRow(r);
                for (int c = 0; c <= 3; c++) {
                    row.createCell(c).setCellValue(r * 10 + c);
                }
            }
            PropertyTemplate template = new PropertyTemplate();
            CellRangeAddress table = new CellRangeAddress(2, 6, 0, 3);
            template.drawBorders(table, BorderStyle.THIN, BorderExtent.ALL);
            template.drawBorders(table, BorderStyle.MEDIUM, IndexedColors.DARK_BLUE.getIndex(),
                    BorderExtent.OUTSIDE);
            template.applyBorders(sheet);                // one pass; POI merges with the existing styles

            // Borders around the merged title: RegionUtil styles every cell of the region.
            RegionUtil.setBorderBottom(BorderStyle.MEDIUM, titleRegion, sheet);
            RegionUtil.setBottomBorderColor(IndexedColors.DARK_BLUE.getIndex(), titleRegion, sheet);

            // Several properties at once on one cell.
            Cell note = sheet.getRow(2).createCell(5);
            note.setCellValue("Check stock");
            CellUtil.setCellStylePropertiesEnum(note, Map.of(
                    CellPropertyType.ALIGNMENT, HorizontalAlignment.RIGHT,
                    CellPropertyType.WRAP_TEXT, true,
                    CellPropertyType.BORDER_BOTTOM, BorderStyle.DOUBLE));

            // The same call on a second cell: does it reuse the style it created for the first one?
            Cell note2 = sheet.getRow(3).createCell(5);
            note2.setCellValue("Reorder");
            int before = workbook.getNumCellStyles();
            CellUtil.setCellStylePropertiesEnum(note2, Map.of(
                    CellPropertyType.ALIGNMENT, HorizontalAlignment.RIGHT,
                    CellPropertyType.WRAP_TEXT, true,
                    CellPropertyType.BORDER_BOTTOM, BorderStyle.DOUBLE));
            System.out.println("New styles created by the second call: " + (workbook.getNumCellStyles() - before));

            System.out.println("Cell styles: " + workbook.getNumCellStyles());
            Path out = Files.createTempFile("summary", ".xlsx");
            try (OutputStream os = Files.newOutputStream(out)) {
                workbook.write(os);
            }
        }
    }
}

New styles created by the second call: 0
Cell styles: 16

import java.util.HashMap;
import java.util.Map;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Workbook;

/**
 * Creates each distinct look once per workbook. Not thread safe: a Workbook is not either,
 * so create one cache per workbook and use both from the same thread.
 */
public final class StyleCache {

    /** Everything that makes two cells look different. A record gives equals and hashCode for free. */
    public record Look(String format, boolean bold, HorizontalAlignment alignment, BorderStyle border,
                       Short fillColor, boolean wrap) {

        public static Look of(String format) {
            return new Look(format, false, HorizontalAlignment.GENERAL, BorderStyle.NONE, null, false);
        }

        public Look withBold() {
            return new Look(format, true, alignment, border, fillColor, wrap);
        }

        public Look withAlignment(HorizontalAlignment a) {
            return new Look(format, bold, a, border, fillColor, wrap);
        }

        public Look withBorder(BorderStyle b) {
            return new Look(format, bold, alignment, b, fillColor, wrap);
        }

        public Look withFill(short indexedColor) {
            return new Look(format, bold, alignment, border, indexedColor, wrap);
        }

        public Look withWrap() {
            return new Look(format, bold, alignment, border, fillColor, true);
        }
    }

    private final Workbook workbook;
    private final Map<Look, CellStyle> styles = new HashMap<>();
    private Font boldFont;                       // created lazily, then shared by every bold style

    public StyleCache(Workbook workbook) {
        this.workbook = workbook;
    }

    public CellStyle get(Look look) {
        return styles.computeIfAbsent(look, this::create);
    }

    private CellStyle create(Look look) {
        CellStyle style = workbook.createCellStyle();
        if (look.format() != null) {
            style.setDataFormat(workbook.createDataFormat().getFormat(look.format()));
        }
        if (look.bold()) {
            if (boldFont == null) {
                boldFont = workbook.createFont();
                boldFont.setBold(true);
            }
            style.setFont(boldFont);
        }
        style.setAlignment(look.alignment());
        style.setWrapText(look.wrap());
        if (look.border() != BorderStyle.NONE) {
            style.setBorderTop(look.border());
            style.setBorderBottom(look.border());
            style.setBorderLeft(look.border());
            style.setBorderRight(look.border());
        }
        if (look.fillColor() != null) {
            style.setFillForegroundColor(look.fillColor());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }
        return style;
    }

    public int size() {
        return styles.size();
    }
}

import java.io.IOException;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class StyleCacheDemo {

    public static void main(String[] args) throws IOException {
        System.out.println("Limit .xlsx: " + SpreadsheetVersion.EXCEL2007.getMaxCellStyles()
                + ", limit .xls: " + SpreadsheetVersion.EXCEL97.getMaxCellStyles());

        // WRONG: one style per cell. The workbook runs out of styles long before it runs out of rows.
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Wrong");
            try {
                for (int r = 0; r < 100_000; r++) {
                    Cell cell = sheet.createRow(r).createCell(0);
                    cell.setCellValue(r * 1.5);
                    CellStyle style = workbook.createCellStyle();        // a new <xf> for every cell
                    style.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00"));
                    cell.setCellStyle(style);
                }
            } catch (IllegalStateException e) {
                System.out.println("Failed with " + workbook.getNumCellStyles() + " styles: " + e.getMessage());
            }
        }

        // RIGHT: a handful of looks, shared by every cell that needs them.
        try (Workbook workbook = new XSSFWorkbook()) {
            StyleCache cache = new StyleCache(workbook);
            CellStyle amount = cache.get(StyleCache.Look.of("#,##0.00").withAlignment(HorizontalAlignment.RIGHT));
            CellStyle total = cache.get(StyleCache.Look.of("#,##0.00").withBold().withBorder(BorderStyle.THIN)
                    .withFill(IndexedColors.GREY_25_PERCENT.getIndex()));
            Sheet sheet = workbook.createSheet("Right");
            double sum = 0;
            for (int r = 0; r < 100_000; r++) {
                Cell cell = sheet.createRow(r).createCell(0);
                cell.setCellValue(r * 1.5);
                sum += r * 1.5;
                cell.setCellStyle(cache.get(StyleCache.Look.of("#,##0.00").withAlignment(HorizontalAlignment.RIGHT)));
            }
            Cell last = sheet.createRow(100_000).createCell(0);
            last.setCellValue(sum);
            last.setCellStyle(total);
            System.out.println("Styles in the workbook: " + workbook.getNumCellStyles()
                    + " (cache entries: " + cache.size() + ")");
            System.out.println(amount == cache.get(StyleCache.Look.of("#,##0.00").withAlignment(HorizontalAlignment.RIGHT)));
        }
    }
}

Limit .xlsx: 64000, limit .xls: 4000
Failed with 64001 styles: The maximum number of Cell Styles was exceeded. You can define up to 64000 style in a .xlsx Workbook
Styles in the workbook: 3 (cache entries: 2)
true

import java.io.File;
import java.io.IOException;
import java.util.Set;
import java.util.TreeSet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/** Prints the look of every distinct style used on the first sheet: useful to see what an upload really contains. */
public class InspectStyles {

    public static void main(String[] args) throws IOException {
        File file = new File(args[0]);
        try (Workbook workbook = WorkbookFactory.create(file, null, true)) {      // read-only
            Sheet sheet = workbook.getSheetAt(0);
            Set<Integer> seen = new TreeSet<>();
            for (Row row : sheet) {
                for (Cell cell : row) {
                    CellStyle style = cell.getCellStyle();
                    if (!seen.add((int) style.getIndex())) {
                        continue;
                    }
                    Font font = workbook.getFontAt(style.getFontIndex());
                    System.out.printf("style %d  first used at %s%n", style.getIndex(), cell.getAddress());
                    System.out.printf("  format  : %s (id %d)%n", style.getDataFormatString(), style.getDataFormat());
                    System.out.printf("  font    : %s %dpt bold=%b italic=%b%n", font.getFontName(),
                            font.getFontHeightInPoints(), font.getBold(), font.getItalic());
                    System.out.printf("  align   : %s / %s wrap=%b%n", style.getAlignment(),
                            style.getVerticalAlignment(), style.getWrapText());
                    System.out.printf("  fill    : %s%n", style.getFillPattern());
                    System.out.printf("  border  : bottom=%s%n", style.getBorderBottom());
                }
            }
            System.out.println("Styles defined in the file: " + workbook.getNumCellStyles()
                    + ", fonts: " + workbook.getNumberOfFonts()
                    + ", styles actually used on sheet 1: " + seen.size());
        }
    }
}

style 1  first used at A1
  format  : General (id 0)
  font    : Calibri 11pt bold=true italic=false
  align   : CENTER / CENTER wrap=true
  fill    : SOLID_FOREGROUND
  border  : bottom=MEDIUM
style 3  first used at D2
  format  : #,##0.00\ "EUR" (id 164)
  font    : Calibri 11pt bold=false italic=false
  align   : GENERAL / TOP wrap=false
  fill    : NO_FILL
  border  : bottom=NONE
Styles defined in the file: 6, fonts: 2, styles actually used on sheet 1: 6

import java.io.File;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CopyStyles {

    public static void main(String[] args) throws IOException {
        try (Workbook source = WorkbookFactory.create(new File(args[0]), null, true);
             Workbook target = new XSSFWorkbook()) {
            CellStyle sourceStyle = source.getSheetAt(0).getRow(0).getCell(0).getCellStyle();

            CellStyle copy = target.createCellStyle();
            copy.cloneStyleFrom(sourceStyle);        // copies format, font, fill, borders, even across workbooks
            Sheet sheet = target.createSheet("Copy");
            Cell cell = sheet.createRow(0).createCell(0);
            cell.setCellValue("Same look as the template header");
            cell.setCellStyle(copy);
            Font font = target.getFontAt(copy.getFontIndex());
            System.out.println("Font copied: " + font.getFontName() + ", fonts now " + target.getNumberOfFonts());
        }

        // The .xls limit is much lower: HSSFWorkbook throws once 4,000 styles exist.
        try (Workbook workbook = new HSSFWorkbook()) {
            try {
                for (int i = 0; i < 2 * SpreadsheetVersion.EXCEL97.getMaxCellStyles(); i++) {
                    workbook.createCellStyle();
                }
            } catch (IllegalStateException e) {
                System.out.println(".xls: " + e.getMessage() + " (styles: " + workbook.getNumCellStyles() + ")");
            }
        }
    }
}

Font copied: Calibri, fonts now 2
.xls: The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook (styles: 4030)