Templates & Modifying Workbooks

    /** Resolves a workbook-scoped name that points at one cell and returns that cell. */
    private static Cell setNamedCell(XSSFWorkbook wb, String name) {
        List<? extends Name> names = wb.getNames(name);
        if (names.isEmpty()) {
            throw new IllegalStateException("The template defines no name '" + name + "'");
        }
        CellReference ref = new CellReference(names.get(0).getRefersToFormula());
        Sheet sheet = wb.getSheet(ref.getSheetName());
        Row row = sheet.getRow(ref.getRow());
        if (row == null) {
            row = sheet.createRow(ref.getRow());
        }
        return row.getCell(ref.getCol(), Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
    }

package com.bookshelf.office;

public record Book(String title, String author, int year, double price, int stock) {
}

package com.bookshelf.office;

import java.io.IOException;
import java.io.OutputStream;
import java.time.LocalDate;
import java.util.List;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFTable;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public final class InventoryFiller {

    private final InventoryTemplate template;

    public InventoryFiller(InventoryTemplate template) {
        this.template = template;
    }

    public void write(String title, List<Book> books, OutputStream out) throws IOException {
        try (XSSFWorkbook wb = template.open()) {
            setNamedCell(wb, "ReportTitle").setCellValue(title);
            setNamedCell(wb, "GeneratedOn").setCellValue(LocalDate.now());

            XSSFTable table = wb.getTable("Books");
            XSSFSheet sheet = table.getXSSFSheet();
            int firstData = table.getStartRowIndex() + 1;
            int lastTemplateData = table.getEndRowIndex();
            int footerStart = lastTemplateData + 2;            // the template leaves one blank row
            int extraRows = Math.max(books.size(), 1) - (lastTemplateData - firstData + 1);

            // 1. make room: move the footer (and everything below it) down
            if (extraRows > 0) {
                sheet.shiftRows(footerStart, sheet.getLastRowNum(), extraRows);
            }

            // 2. write the data, reusing the styles of the prototype row
            Row prototype = sheet.getRow(firstData);
            int rowIndex = firstData;
            for (Book b : books) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    row = sheet.createRow(rowIndex);
                }
                put(row, prototype, 0).setCellValue(b.title());
                put(row, prototype, 1).setCellValue(b.author());
                put(row, prototype, 2).setCellValue(b.year());
                put(row, prototype, 3).setCellValue(b.price());
                put(row, prototype, 4).setCellValue(b.stock());
                rowIndex++;
            }
            int lastData = Math.max(rowIndex - 1, firstData);

            // 3. resize the table: shiftRows and setCellValue never touch it
            table.setCellReferences(new AreaReference(
                    new CellReference(table.getStartRowIndex(), table.getStartColIndex()),
                    new CellReference(lastData, table.getEndColIndex()),
                    SpreadsheetVersion.EXCEL2007));

            // 4. rewrite the footer formula: its range still points at the template rows
            Cell total = sheet.getRow(lastData + 2).getCell(4);
            total.setCellFormula("SUM(E" + (firstData + 1) + ":E" + (lastData + 1) + ")");

            wb.setForceFormulaRecalculation(true);   // Excel recalculates when the file is opened
            wb.write(out);
        }
    }

    /** Resolves a workbook-scoped name that points at one cell and returns that cell. */
    private static Cell setNamedCell(XSSFWorkbook wb, String name) {
        List<? extends Name> names = wb.getNames(name);
        if (names.isEmpty()) {
            throw new IllegalStateException("The template defines no name '" + name + "'");
        }
        CellReference ref = new CellReference(names.get(0).getRefersToFormula());
        Sheet sheet = wb.getSheet(ref.getSheetName());
        Row row = sheet.getRow(ref.getRow());
        if (row == null) {
            row = sheet.createRow(ref.getRow());
        }
        return row.getCell(ref.getCol(), Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
    }

    private static Cell put(Row row, Row prototype, int column) {
        Cell cell = row.getCell(column, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
        cell.setCellStyle(prototype.getCell(column).getCellStyle());
        return cell;
    }
}

InventoryFiller filler = new InventoryFiller(new InventoryTemplate());
try (OutputStream out = Files.newOutputStream(Path.of("books-inventory.xlsx"))) {
    filler.write("Bookshelf inventory", books, out);
}

$ unzip -p books-inventory.xlsx xl/tables/table1.xml | grep -o 'ref="[^"]*"'
ref="A4:E8"

package com.bookshelf.office;

import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

/** Replaces ${key} markers inside text cells. Formula cells and non-text cells are left alone. */
public final class PlaceholderReplacer {

    private static final Pattern MARKER = Pattern.compile("\\$\\{([A-Za-z0-9_.]+)}");

    private PlaceholderReplacer() {
    }

    public static int replace(Workbook wb, Map<String, String> values) {
        int replaced = 0;
        for (Sheet sheet : wb) {
            for (Row row : sheet) {
                for (Cell cell : row) {
                    if (cell.getCellType() != CellType.STRING) {
                        continue;                       // formulas, numbers, blanks
                    }
                    String text = cell.getStringCellValue();
                    if (!text.contains("${")) {
                        continue;
                    }
                    Matcher m = MARKER.matcher(text);
                    StringBuilder out = new StringBuilder();
                    while (m.find()) {
                        String value = values.get(m.group(1));
                        // unknown keys stay visible instead of silently disappearing
                        m.appendReplacement(out, Matcher.quoteReplacement(value != null ? value : m.group()));
                        replaced++;
                    }
                    m.appendTail(out);
                    cell.setCellValue(out.toString());  // plain string: rich-text runs are lost
                }
            }
        }
        return replaced;
    }
}

package com.bookshelf.office;

import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.SheetVisibility;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public final class BranchSheets {

    private BranchSheets() {
    }

    /** One sheet per branch, cloned from the template sheet "Inventory", which is then removed. */
    public static void write(InventoryTemplate template, Map<String, List<Book>> booksByBranch,
                             OutputStream out) throws IOException {
        try (XSSFWorkbook wb = template.open()) {
            int templateIndex = wb.getSheetIndex("Inventory");

            for (Map.Entry<String, List<Book>> branch : booksByBranch.entrySet()) {
                // sheet names: at most 31 characters, none of  \ / ? * [ ] :
                String name = WorkbookUtil.createSafeSheetName(branch.getKey());
                Sheet copy = wb.cloneSheet(templateIndex);
                wb.setSheetName(wb.getSheetIndex(copy), name);
                // ... fill the copy here (cells, table rows) ...
            }

            // Names are NOT re-pointed or invalidated when a sheet disappears: delete the ones
            // that still refer to the template sheet before removing it.
            List<Name> stale = new ArrayList<>();
            for (Name n : wb.getAllNames()) {
                if ("Inventory".equals(n.getSheetName())
                        || n.getRefersToFormula().startsWith("Inventory!")) {
                    stale.add(n);
                }
            }
            stale.forEach(wb::removeName);

            wb.removeSheetAt(templateIndex);       // or: wb.setSheetVisibility(i, SheetVisibility.VERY_HIDDEN)
            wb.setActiveSheet(0);                   // the active tab may now point past the end
            wb.write(out);
        }
    }
}

package com.bookshelf.office;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.Map;
import org.apache.poi.poifs.macros.VBAMacroReader;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public final class MacroTemplate {

    public static void main(String[] args) throws IOException {
        byte[] template = Files.readAllBytes(Path.of("books-inventory-macros.xlsm"));

        // 1. Reading: list the VBA modules (POI only reads them, it cannot create or edit them)
        try (VBAMacroReader reader = new VBAMacroReader(new ByteArrayInputStream(template))) {
            Map<String, String> macros = reader.readMacros();
            macros.forEach((module, code) ->
                    System.out.println(module + ": " + code.lines().count() + " lines of VBA"));
        }

        // 2. Filling: the vbaProject.bin part travels through the open/write cycle untouched.
        //    Opened from bytes, not from the File, so close() cannot write into the template.
        try (InputStream in = new ByteArrayInputStream(template);
             XSSFWorkbook wb = new XSSFWorkbook(in);
             OutputStream out = Files.newOutputStream(Path.of("books-inventory-filled.xlsm"))) {
            if (!wb.isMacroEnabled()) {
                throw new IllegalStateException("Not a macro-enabled workbook");
            }
            wb.getSheetAt(0).getRow(0).createCell(1).setCellValue("Filled by POI");
            wb.write(out);                          // the target file name MUST end in .xlsm
        }
    }
}

package com.bookshelf.office;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.time.LocalDate;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public final class LoansReportFromTemplate {

    public record Loan(long id, String member, String title, LocalDate loaned, LocalDate due) {
    }

    /**
     * The template holds the title, the header row (index 2) and a prototype body row (index 3)
     * that only carries styles. All of it is written as is; the loans start after it.
     */
    public static void write(byte[] template, Iterator<Loan> loans, OutputStream out) throws IOException {
        try (XSSFWorkbook xssf = new XSSFWorkbook(new ByteArrayInputStream(template))) {
            XSSFSheet templateSheet = xssf.getSheetAt(0);
            int firstFreeRow = templateSheet.getLastRowNum() + 1;
            // Read the styles NOW: once wrapped, SXSSF cannot give the template rows back
            CellStyle text = templateSheet.getRow(3).getCell(1).getCellStyle();
            CellStyle date = templateSheet.getRow(3).getCell(3).getCellStyle();

            // window of 200 rows; close() also deletes the temp files (POI 5.3+)
            try (SXSSFWorkbook wb = new SXSSFWorkbook(xssf, 200)) {
                Sheet sheet = wb.getSheetAt(0);
                // sheet.getRow(0) == null here, and createRow(i) with i < firstFreeRow throws
                int r = firstFreeRow;
                while (loans.hasNext()) {
                    Loan loan = loans.next();
                    Row row = sheet.createRow(r++);
                    row.createCell(0).setCellValue(loan.id());
                    row.createCell(1).setCellValue(loan.member());
                    row.createCell(2).setCellValue(loan.title());
                    row.createCell(3).setCellValue(loan.loaned());
                    row.createCell(4).setCellValue(loan.due());
                    row.getCell(1).setCellStyle(text);
                    row.getCell(3).setCellStyle(date);
                    row.getCell(4).setCellStyle(date);
                }
                wb.write(out);
            }
        }
    }
}