Testing Office Documents

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

Baseline date: 2026-10-09. This page was written against Apache POI 5.5.1 (the latest release on that date; 6.0 is announced but has not been released), JUnit Jupiter and AssertJ as managed by Spring Boot 4.1.1 (Spring Framework 7), Java 21 and later, and Jakarta EE 11. Every class on this page was compiled and run in a throwaway Maven project; the full test run is shown near the end, and the outputs marked as observed come from it.

An export that "runs without an exception" is not an export that works. The classic failures of Office code are quiet: a date written as a number, a price that loses its format, a formula that points one row too low, a header that is no longer bold, a name with an accent that breaks the download header, an upload endpoint that opens a hostile file. None of them fails a smoke test; all of them fail a round trip: write the file, read it back with the same library the production code uses, and assert on what the next person will see.

This page builds that safety net for the Bookshelf scenario of the section: books-inventory.xlsx and loan-letter.docx (written) and books-import.xlsx (uploaded). It covers six techniques, in the order in which they pay off:

  1. Round-trip tests of a workbook: values, displayed text, styles, formulas and layout.

  2. Round-trip tests of a Word document: paragraphs, styles, runs and tables.

  3. A golden-file comparison with a small ExcelComparator, for reports with many cells.

  4. MockMvc tests of the REST layer: download headers and the bytes of the body, multipart uploads.

  5. Malformed and malicious fixture files, generated by code.

  6. Habits that keep these tests fast, deterministic and independent of the machine.

The APIs under test are explained in Spreadsheet Usermodel Basics, Cell Styles, Fonts & Formats, Formulas & Evaluation and Word XWPF Basics. The threats behind the hostile fixtures are in Security & Untrusted Files. For the general testing toolbox of a Spring Boot application (test slices, Mockito, Testcontainers) see Unit & Integration Testing, and for the controllers themselves REST APIs.

What to test, and at which level

The cheapest test that can detect a given defect is the right one. Most Office defects live in the document-building code and need no Spring context at all.

Level What it proves Tools Cost

Round trip (unit)

Cells, formats, styles, formulas, paragraphs and tables are what the code meant to write.

JUnit 5, AssertJ, POI, @TempDir

Milliseconds

Golden file (unit)

A large report did not change in any cell or style that matters.

ExcelComparator, a checked-in .xlsx

Milliseconds

Web slice

Routing, Content-Type, Content-Disposition, status codes, multipart binding, error mapping, and that the body is a valid file.

@WebMvcTest, MockMvc, POI to read the body

Hundreds of milliseconds

Hostile input

The upload path rejects broken and dangerous files with a 4xx, never a 500, and never hangs.

Generated fixtures, parameterized tests

Milliseconds (a zip bomb test is the slowest, about a second)

Full application

Real multipart limits (spring.servlet.multipart.*), proxies, security filters, a real container.

@SpringBootTest with a random port, curl in a pipeline

Seconds

Two limits of this page: it does not render documents (POI cannot render an .xlsx or .docx to an image or PDF, so a test can never assert on how a page looks, only on the model that produces it), and it does not test Excel or Word themselves. A file that POI reads back correctly can still open with a repair prompt in Office; the usual defence is a manual check of one representative output per release, plus the golden file below.

Project setup

The tests need JUnit Jupiter and AssertJ (both come with Spring Boot’s test starter), MockMvc for the web layer, and the same POI artifact as production. In Spring Boot 4 the web test support moved into its own module, so the starter is spring-boot-starter-webmvc-test; its packages are org.springframework.boot.webmvc.test.autoconfigure (for @WebMvcTest and @AutoConfigureMockMvc), no longer org.springframework.boot.test.autoconfigure.web.servlet.

<parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-parent</artifactId>
  <version>4.1.1</version>
</parent>

<dependencies>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-webmvc</artifactId>
  </dependency>
  <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.5.1</version>
  </dependency>

  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-webmvc-test</artifactId>
    <scope>test</scope>
  </dependency>
</dependencies>
dependencies {
    implementation("org.springframework.boot:spring-boot-starter-webmvc")
    implementation("org.apache.poi:poi-ooxml:5.5.1")

    testImplementation("org.springframework.boot:spring-boot-starter-webmvc-test")
}

Source: Spring Boot, Testing Spring Boot Applications, JUnit User Guide and AssertJ documentation.

Test code reads files with the same POI jar that production uses (poi-ooxml already brings poi and the OOXML schemas), so a test passes only if POI itself can parse what the code wrote. Add poi-scratchpad only when the code under test handles legacy .doc files.

The code under test

The examples test these classes. BookExporter writes the inventory (header style, number and date formats, a formula per row, a total, a freeze pane, an auto-filter and column widths); text that starts with =, +, - or @ is written as a string cell with a quote-prefixed style, the rule explained in the formula-injection section of Security & Untrusted Files.

package com.example.bookshelf;

import java.io.IOException;
import java.io.OutputStream;
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.CreationHelper;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
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.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

/** Writes the Bookshelf inventory as books-inventory.xlsx. */
@Component
public class BookExporter {

    public static final String SHEET = "Books";
    public static final String[] HEADERS = {"ISBN", "Title", "Author", "Copies", "Price", "Acquired", "Stock value"};

    public void write(List<Book> books, OutputStream out) throws IOException {
        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            CreationHelper helper = workbook.getCreationHelper();

            Font bold = workbook.createFont();
            bold.setBold(true);
            CellStyle header = workbook.createCellStyle();
            header.setFont(bold);
            header.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            header.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            header.setBorderBottom(BorderStyle.THIN);

            CellStyle money = workbook.createCellStyle();
            money.setDataFormat(helper.createDataFormat().getFormat("#,##0.00"));

            CellStyle date = workbook.createCellStyle();
            date.setDataFormat(helper.createDataFormat().getFormat("yyyy-mm-dd"));

            CellStyle text = workbook.createCellStyle();
            text.setQuotePrefixed(true);   // a cell that starts with = + - @ stays text

            Sheet sheet = workbook.createSheet(SHEET);
            Row head = sheet.createRow(0);
            for (int c = 0; c < HEADERS.length; c++) {
                head.createCell(c).setCellValue(HEADERS[c]);
                head.getCell(c).setCellStyle(header);
            }

            int r = 1;
            for (Book b : books) {
                Row row = sheet.createRow(r);
                row.createCell(0).setCellValue(b.isbn());
                writeText(row.createCell(1), b.title(), text);
                writeText(row.createCell(2), b.author(), text);
                row.createCell(3).setCellValue(b.copies());
                row.createCell(4).setCellValue(b.price());
                row.getCell(4).setCellStyle(money);
                row.createCell(5).setCellValue(b.acquired());
                row.getCell(5).setCellStyle(date);
                row.createCell(6).setCellFormula("D" + (r + 1) + "*E" + (r + 1));
                row.getCell(6).setCellStyle(money);
                r++;
            }
            Row total = sheet.createRow(r);
            total.createCell(5).setCellValue("Total");
            total.createCell(6).setCellFormula("SUM(G2:G" + r + ")");
            total.getCell(6).setCellStyle(money);

            sheet.createFreezePane(0, 1);
            sheet.setAutoFilter(new org.apache.poi.ss.util.CellRangeAddress(0, 0, 0, HEADERS.length - 1));
            for (int c = 0; c < HEADERS.length; c++) {
                sheet.setColumnWidth(c, c == 1 || c == 2 ? 30 * 256 : 14 * 256);
            }
            workbook.write(out);
        }
    }

    private static void writeText(Cell cell, String value, CellStyle quotePrefixed) {
        cell.setCellValue(value);
        if (!value.isEmpty() && "=+-@\t\r".indexOf(value.charAt(0)) >= 0) {
            cell.setCellStyle(quotePrefixed);
        }
    }
}

LoanLetterWriter writes loan-letter.docx: a Heading1 paragraph, a greeting paragraph and a three-column table with a bold header row and one row per book. Book is a record (isbn, title, author, copies, price, acquired).

The web layer is one controller and one import service. The download returns a StreamingResponseBody; the upload takes a MultipartFile and rejects empty files, other extensions, macro parts and anything POI cannot open. Both turn an InvalidUploadException into a 400 with a JSON message.

package com.example.bookshelf;

import java.nio.charset.StandardCharsets;
import java.time.LocalDate;
import java.util.List;
import java.util.Map;
import org.springframework.http.ContentDisposition;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestPart;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.mvc.method.annotation.StreamingResponseBody;

@RestController
@RequestMapping("/api/books")
public class BookController {

    public static final MediaType XLSX = MediaType.parseMediaType(
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

    private final BookExporter exporter;
    private final BookImportService importer;

    public BookController(BookExporter exporter, BookImportService importer) {
        this.exporter = exporter;
        this.importer = importer;
    }

    @GetMapping("/export")
    public ResponseEntity<StreamingResponseBody> export() {
        List<Book> books = List.of(
                new Book("9780134685991", "Effective Java", "Joshua Bloch", 3, 45.5, LocalDate.of(2024, 3, 1)),
                new Book("9781617294945", "Spring in Action", "Craig Walls", 2, 39.9, LocalDate.of(2025, 1, 15)));
        ContentDisposition disposition = ContentDisposition.attachment()
                .filename("books-inventory.xlsx", StandardCharsets.UTF_8)
                .build();
        return ResponseEntity.ok()
                .contentType(XLSX)
                .header(HttpHeaders.CONTENT_DISPOSITION, disposition.toString())
                .header(HttpHeaders.CACHE_CONTROL, "no-store")
                .body(out -> exporter.write(books, out));
    }

    @PostMapping("/import")
    public Map<String, Object> importBooks(@RequestPart("file") MultipartFile file) {
        List<String> isbns = importer.importIsbns(file);
        return Map.of("imported", isbns.size(), "isbns", isbns);
    }

    @ExceptionHandler(InvalidUploadException.class)
    public ResponseEntity<Map<String, String>> invalid(InvalidUploadException e) {
        return ResponseEntity.badRequest().body(Map.of("error", e.getMessage()));
    }
}
package com.example.bookshelf;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
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;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

/** Reads books-import.xlsx uploads: ISBN, Title, Author, Copies. */
@Service
public class BookImportService {

    public List<String> importIsbns(MultipartFile file) {
        String name = file.getOriginalFilename() == null ? "" : file.getOriginalFilename().toLowerCase(Locale.ROOT);
        if (file.isEmpty()) {
            throw new InvalidUploadException("The file is empty");
        }
        if (!name.endsWith(".xlsx")) {
            throw new InvalidUploadException("Only .xlsx files are accepted");
        }
        try {
            byte[] bytes = file.getBytes();
            rejectMacros(bytes);
            return readIsbns(bytes);
        } catch (IOException e) {
            throw new InvalidUploadException("The file cannot be read", e);
        }
    }

    private static void rejectMacros(byte[] bytes) throws IOException {
        try (ZipInputStream zip = new ZipInputStream(new java.io.ByteArrayInputStream(bytes))) {
            for (ZipEntry e = zip.getNextEntry(); e != null; e = zip.getNextEntry()) {
                if (e.getName().equals("xl/vbaProject.bin")) {
                    throw new InvalidUploadException("Macro-enabled workbooks are not accepted");
                }
            }
        }
    }

    private static List<String> readIsbns(byte[] bytes) {
        try (InputStream in = new java.io.ByteArrayInputStream(bytes);
             Workbook workbook = WorkbookFactory.create(in)) {
            Sheet sheet = workbook.getSheetAt(0);
            DataFormatter formatter = new DataFormatter();
            List<String> isbns = new ArrayList<>();
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                Cell cell = row == null ? null : row.getCell(0);
                if (cell != null) {
                    isbns.add(formatter.formatCellValue(cell));
                }
            }
            return isbns;
        } catch (Exception | StackOverflowError e) {
            throw new InvalidUploadException("Not a readable workbook", e);
        }
    }
}

Source: POI Spreadsheet Quick Guide, Spring MVC, Multipart and the page Spreadsheet Usermodel Basics.

BookImportService is deliberately minimal: it is the code under test of this page, not a model importer. It reads the whole upload into the heap with getBytes(), opens it with WorkbookFactory.create(InputStream), which buffers the package in memory again instead of opening a file, and reads ISBNs with DataFormatter, whose display text depends on the cell format (a numeric ISBN can come back as 9.78E+12). A production importer stores the part in a private temporary file, opens it read-only with WorkbookFactory.create(file, null, true) and coerces each cell by its type, as Importing Data from Excel shows.

Round-trip tests for a workbook

A round-trip test has three steps: write with the production code to a real file, read it back with WorkbookFactory, and assert on the model. Write to a @TempDir so the test exercises the same File-based open that production code should use (it needs less memory than an InputStream and does not copy the package), and so JUnit deletes the file afterwards. Open the workbook in try-with-resources in every test; an unclosed Workbook leaks a file handle and, on Windows, stops the temp directory from being deleted.

The checks below are grouped by what can go wrong:

What How to assert Typical defect it catches

Values

getStringCellValue, getNumericCellValue, getLocalDateTimeCellValue; getCellType() when the type matters.

A number written as text, a date written as a serial number without a date format, an off-by-one row.

Displayed text

new DataFormatter(Locale.US).formatCellValue(cell).

A missing #,##0.00 or yyyy-mm-dd format; the cell holds the right number but the user sees 45.5.

Style

cell.getCellStyle(), then getFont(), getFillPattern(), getBorderBottom(), getQuotePrefixed(); getDataFormatString().

A style created per cell instead of shared, a header that lost its bold font.

Formulas

getCellFormula() for the text; FormulaEvaluator.evaluate(cell) for the result.

A range that stops one row early, a reference to the wrong column.

Layout

getPaneInformation(), getColumnWidth(i), the auto-filter, merged regions, print setup.

A forgotten freeze pane or auto-size.

 
package com.example.bookshelf;

import static org.assertj.core.api.Assertions.assertThat;
import static org.assertj.core.api.Assertions.within;

import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.time.LocalDate;
import java.util.List;
import java.util.Locale;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
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.XSSFCellStyle;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.io.TempDir;

class BookExporterRoundTripTest {

    private final BookExporter exporter = new BookExporter();
    private Path file;

    @BeforeEach
    void export(@TempDir Path dir) throws IOException {
        file = dir.resolve("books-inventory.xlsx");
        try (OutputStream out = Files.newOutputStream(file)) {
            exporter.write(TestBooks.TWO, out);
        }
    }

    @Test
    void writesHeaderAndValues() throws IOException {
        try (Workbook wb = WorkbookFactory.create(file.toFile())) {
            Sheet sheet = wb.getSheet(BookExporter.SHEET);
            assertThat(sheet).isNotNull();
            assertThat(sheet.getLastRowNum()).isEqualTo(3);   // header + 2 books + total

            List<String> headers = new java.util.ArrayList<>();
            sheet.getRow(0).forEach(c -> headers.add(c.getStringCellValue()));
            assertThat(headers).containsExactly(BookExporter.HEADERS);

            assertThat(sheet.getRow(1).getCell(0).getStringCellValue()).isEqualTo("9780134685991");
            assertThat(sheet.getRow(1).getCell(3).getNumericCellValue()).isEqualTo(3.0);
            assertThat(sheet.getRow(1).getCell(4).getNumericCellValue()).isCloseTo(45.5, within(0.001));
            assertThat(sheet.getRow(1).getCell(5).getLocalDateTimeCellValue().toLocalDate())
                    .isEqualTo(LocalDate.of(2024, 3, 1));
        }
    }

    @Test
    void formatsCellsTheWayTheUserWillSeeThem() throws IOException {
        DataFormatter formatter = new DataFormatter(Locale.US);
        try (Workbook wb = WorkbookFactory.create(file.toFile())) {
            Sheet sheet = wb.getSheetAt(0);
            assertThat(formatter.formatCellValue(sheet.getRow(1).getCell(4))).isEqualTo("45.50");
            assertThat(formatter.formatCellValue(sheet.getRow(1).getCell(5))).isEqualTo("2024-03-01");
        }
    }

    @Test
    void headerStyleIsBoldWithFill() throws IOException {
        try (Workbook wb = WorkbookFactory.create(file.toFile())) {
            Cell isbn = wb.getSheetAt(0).getRow(0).getCell(0);
            XSSFCellStyle style = (XSSFCellStyle) isbn.getCellStyle();
            assertThat(style.getFont().getBold()).isTrue();
            assertThat(style.getFillPattern().name()).isEqualTo("SOLID_FOREGROUND");
            assertThat(style.getBorderBottom().name()).isEqualTo("THIN");
        }
    }

    @Test
    void writesFormulasAndTheyEvaluate() throws IOException {
        try (Workbook wb = WorkbookFactory.create(file.toFile())) {
            Sheet sheet = wb.getSheetAt(0);
            Cell stock = sheet.getRow(1).getCell(6);
            Cell total = sheet.getRow(3).getCell(6);

            assertThat(stock.getCellType()).isEqualTo(CellType.FORMULA);
            assertThat(stock.getCellFormula()).isEqualTo("D2*E2");
            assertThat(total.getCellFormula()).isEqualTo("SUM(G2:G3)");

            // POI wrote no cached results, so evaluate to check the numbers
            FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
            assertThat(evaluator.evaluate(stock).getNumberValue()).isCloseTo(136.5, within(0.001));
            assertThat(evaluator.evaluate(total).getNumberValue()).isCloseTo(136.5 + 79.8, within(0.001));
        }
    }

    @Test
    void setsLayout() throws IOException {
        try (Workbook wb = WorkbookFactory.create(file.toFile())) {
            Sheet sheet = wb.getSheetAt(0);
            assertThat(sheet.getPaneInformation().isFreezePane()).isTrue();
            assertThat(sheet.getPaneInformation().getHorizontalSplitPosition()).isEqualTo((short) 1);
            assertThat(sheet.getColumnWidth(1)).isEqualTo(30 * 256);
            assertThat(((org.apache.poi.xssf.usermodel.XSSFSheet) sheet).getCTWorksheet().getAutoFilter()).isNotNull();
        }
    }

    @Test
    void usersTextIsNeverAFormula() throws IOException {
        Path hostile = file.resolveSibling("hostile.xlsx");
        List<Book> books = List.of(new Book("1", "=HYPERLINK(\"https://example.test\",\"x\")", "@SUM(1)", 1, 1.0,
                LocalDate.of(2026, 1, 1)));
        try (OutputStream out = Files.newOutputStream(hostile)) {
            exporter.write(books, out);
        }
        try (Workbook wb = WorkbookFactory.create(hostile.toFile())) {
            Cell title = wb.getSheetAt(0).getRow(1).getCell(1);
            assertThat(title.getCellType()).isEqualTo(CellType.STRING);
            assertThat(title.getStringCellValue()).startsWith("=");
            assertThat(title.getCellStyle().getQuotePrefixed()).isTrue();
        }
    }
}

Source: POI Spreadsheet Quick Guide, the Javadoc of WorkbookFactory and DataFormatter, and the JUnit @TempDir documentation.

Four points in that class are worth stating, because each one cost a failed test to find:

Always pass a Locale to DataFormatter. The first version of formatsCellsTheWayTheUserWillSeeThem used new DataFormatter() and failed on a developer machine configured for Spanish, with expected: "45.50" but was: "45,50" (observed). The default constructor uses the JVM’s default locale, so the same test passes on the CI server and fails on a laptop. Pin the locale in the test (Locale.US), and if the production code formats for a specific audience, test that audience’s locale explicitly. See Dates, Numbers & DataFormatter.

A formula written by POI has no cached result. setCellFormula stores the text only; the file has no <v> value until Excel (or a FormulaEvaluator) computes one. getNumericCellValue() on the freshly read formula cell returns 0.0 (observed), not the total, so the test asserts the formula text and evaluates it, as writesFormulasAndTheyEvaluate does. Assert the text to catch a wrong reference, and the evaluated value to catch a wrong formula. Use evaluator.evaluate(cell) (it does not touch the cell) rather than evaluateInCell, which would replace the formula in the model the next assertion reads.

Compare doubles with a tolerance. isCloseTo(45.5, within(0.001)) survives a change of representation; isEqualTo(45.5) is fine for values that are exact in binary (3.0) and a trap for prices computed with *.

Do not assert on bytes or on document properties. The same workbook written twice is not byte-for-byte equal (the core properties carry a creation timestamp and the zip carries entry times). Assert on the model, as the tests above do, or use the comparator below.

Round-trip tests for a Word document

The same three steps apply to XWPFDocument. The model is the body of the document: paragraphs, tables and the order of the elements. Assert on getText() of paragraphs and cells, on the paragraph style id (Heading1), and on run properties such as isBold().

package com.example.bookshelf;

import static org.assertj.core.api.Assertions.assertThat;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
import org.junit.jupiter.api.Test;

class LoanLetterTest {

    private XWPFDocument roundTrip() throws IOException {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        new LoanLetterWriter().write("Ana Pérez", TestBooks.TWO, out);
        return new XWPFDocument(new ByteArrayInputStream(out.toByteArray()));
    }

    @Test
    void headingAndGreeting() throws IOException {
        try (XWPFDocument doc = roundTrip()) {
            List<XWPFParagraph> paragraphs = doc.getParagraphs();
            assertThat(paragraphs).extracting(XWPFParagraph::getText)
                    .containsExactly("Loan letter", "Dear Ana Pérez, you borrowed the following books:");
            assertThat(paragraphs.get(0).getStyle()).isEqualTo("Heading1");
        }
    }

    @Test
    void tableHasOneRowPerBookPlusHeader() throws IOException {
        try (XWPFDocument doc = roundTrip()) {
            assertThat(doc.getTables()).hasSize(1);
            XWPFTable table = doc.getTables().get(0);
            assertThat(table.getRows()).hasSize(3);

            List<List<String>> text = table.getRows().stream()
                    .map(row -> row.getTableCells().stream().map(c -> c.getText()).toList())
                    .toList();
            assertThat(text).containsExactly(
                    List.of("ISBN", "Title", "Copies"),
                    List.of("9780134685991", "Effective Java", "3"),
                    List.of("9781617294945", "Spring in Action", "2"));
        }
    }

    @Test
    void headerCellsAreBold() throws IOException {
        try (XWPFDocument doc = roundTrip()) {
            XWPFTableRow header = doc.getTables().get(0).getRow(0);
            assertThat(header.getTableCells())
                    .allSatisfy(cell -> assertThat(cell.getParagraphs().get(0).getRuns().get(0).isBold()).isTrue());
            XWPFTableRow body = doc.getTables().get(0).getRow(1);
            assertThat(body.getCell(1).getParagraphs().get(0).getRuns().get(0).isBold()).isFalse();
        }
    }

    @Test
    void bodyOrderIsParagraphThenTable() throws IOException {
        try (XWPFDocument doc = roundTrip()) {
            assertThat(doc.getBodyElements()).extracting(e -> e.getElementType().name())
                    .containsExactly("PARAGRAPH", "PARAGRAPH", "TABLE");
        }
    }
}

Source: POI XWPF Quick Guide and the Javadoc of XWPFDocument. The table and run APIs are explained in Word Tables, Lists & Images.

Things to know when you assert on Word content:

  • XWPFParagraph.getText() joins the runs of one paragraph, so a test does not need to know how Word split the text into runs. That is why the tests assert on getText() and use runs only for formatting. The reverse is a pitfall for the production code (placeholders split across runs, see Word Templates & Placeholder Replacement), and a good reason to test a template filler with a template whose placeholder is deliberately split.

  • A table cell always contains at least one paragraph. XWPFTableCell.getText() joins its paragraphs with newlines; use getParagraphs() when the structure matters.

  • getBodyElements() is the only API that keeps the order of paragraphs and tables. getParagraphs() and getTables() are two separate lists; asserting only on them cannot detect a table written above its caption.

  • Headers, footers, footnotes and text boxes are not part of the body. Assert on getHeaderList() and getFooterList() for them.

  • Compare styles by id, not by name: Heading1 is the id, "Heading 1" is the name shown in Word.

Golden-file comparison

When a report has hundreds of cells, asserting each one by hand is both tedious and fragile. A golden file is a checked-in copy of an output that someone has reviewed in Excel and approved. The test generates the report again and compares it with the golden file. Two things make this work in practice:

  1. Compare the model, not the bytes. The comparator below walks both workbooks and reports one line per difference: sheet names, row counts, merged regions, per cell the type, the displayed value, or the formula text for a formula cell, and a chosen subset of the style (number format, bold, fill pattern, quote prefix), plus column widths. It ignores everything that legitimately varies (timestamps, internal style indexes, the order of the shared string table).

  2. Return a list of differences, not a boolean. assertThat(diffs).isEmpty() then prints every difference at once, with the cell address, which is the information a developer needs to decide whether the golden file or the code is wrong.

package com.example.bookshelf;

import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

/**
 * Compares two workbooks by what they mean, not by their bytes: sheet names, cell types, values, formulas,
 * number formats, bold, fill, quote prefix, column widths and merged regions. Returns one line per difference.
 */
final class ExcelComparator {

    private final DataFormatter formatter = new DataFormatter(Locale.US);

    List<String> compare(Workbook expected, Workbook actual) {
        List<String> diffs = new ArrayList<>();
        if (expected.getNumberOfSheets() != actual.getNumberOfSheets()) {
            diffs.add("sheet count: " + expected.getNumberOfSheets() + " != " + actual.getNumberOfSheets());
            return diffs;
        }
        for (int s = 0; s < expected.getNumberOfSheets(); s++) {
            Sheet e = expected.getSheetAt(s);
            Sheet a = actual.getSheetAt(s);
            String where = "sheet '" + e.getSheetName() + "'";
            if (!e.getSheetName().equals(a.getSheetName())) {
                diffs.add(where + ": name is '" + a.getSheetName() + "'");
            }
            if (e.getLastRowNum() != a.getLastRowNum()) {
                diffs.add(where + ": last row " + e.getLastRowNum() + " != " + a.getLastRowNum());
            }
            if (e.getNumMergedRegions() != a.getNumMergedRegions()) {
                diffs.add(where + ": merged regions " + e.getNumMergedRegions() + " != " + a.getNumMergedRegions());
            }
            for (int r = 0; r <= Math.max(e.getLastRowNum(), a.getLastRowNum()); r++) {
                compareRow(where, e.getRow(r), a.getRow(r), r, diffs);
            }
            int columns = e.getRow(0) == null ? 0 : e.getRow(0).getLastCellNum();
            for (int c = 0; c < columns; c++) {
                if (e.getColumnWidth(c) != a.getColumnWidth(c)) {
                    diffs.add(where + ": width of column " + c + " " + e.getColumnWidth(c) + " != "
                            + a.getColumnWidth(c));
                }
            }
        }
        return diffs;
    }

    private void compareRow(String where, Row e, Row a, int r, List<String> diffs) {
        if (e == null || a == null) {
            if (e != a) {
                diffs.add(where + ": row " + (r + 1) + (e == null ? " is unexpected" : " is missing"));
            }
            return;
        }
        int last = Math.max(e.getLastCellNum(), a.getLastCellNum());
        for (int c = 0; c < last; c++) {
            Cell ec = e.getCell(c);
            Cell ac = a.getCell(c);
            String ref = where + " " + new org.apache.poi.ss.util.CellReference(r, c).formatAsString();
            if (ec == null || ac == null) {
                if (ec != ac) {
                    diffs.add(ref + ": " + (ec == null ? "unexpected cell" : "missing cell"));
                }
                continue;
            }
            if (ec.getCellType() != ac.getCellType()) {
                diffs.add(ref + ": type " + ec.getCellType() + " != " + ac.getCellType());
                continue;
            }
            if (ec.getCellType() == CellType.FORMULA) {
                if (!ec.getCellFormula().equals(ac.getCellFormula())) {
                    diffs.add(ref + ": formula " + ec.getCellFormula() + " != " + ac.getCellFormula());
                }
            } else if (!formatter.formatCellValue(ec).equals(formatter.formatCellValue(ac))) {
                diffs.add(ref + ": value '" + formatter.formatCellValue(ec) + "' != '"
                        + formatter.formatCellValue(ac) + "'");
            }
            compareStyle(ref, ec.getCellStyle(), ac.getCellStyle(), ec.getSheet().getWorkbook(),
                    ac.getSheet().getWorkbook(), diffs);
        }
    }

    private void compareStyle(String ref, CellStyle e, CellStyle a, Workbook ew, Workbook aw, List<String> diffs) {
        if (!e.getDataFormatString().equals(a.getDataFormatString())) {
            diffs.add(ref + ": number format " + e.getDataFormatString() + " != " + a.getDataFormatString());
        }
        if (ew.getFontAt(e.getFontIndex()).getBold() != aw.getFontAt(a.getFontIndex()).getBold()) {
            diffs.add(ref + ": bold differs");
        }
        if (e.getFillPattern() != a.getFillPattern()) {
            diffs.add(ref + ": fill " + e.getFillPattern() + " != " + a.getFillPattern());
        }
        if (e.getQuotePrefixed() != a.getQuotePrefixed()) {
            diffs.add(ref + ": quote prefix differs");
        }
    }
}
package com.example.bookshelf;

import static org.assertj.core.api.Assertions.assertThat;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
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.Cell;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.junit.jupiter.api.Test;

class GoldenFileTest {

    private static final Path GOLDEN = Path.of("src/test/resources/golden/books-inventory.xlsx");

    private final ExcelComparator comparator = new ExcelComparator();

    @Test
    void inventoryMatchesTheGoldenFile() throws IOException {
        byte[] actual = export(TestBooks.TWO);
        if (Boolean.getBoolean("golden.update")) {            // mvn test -Dgolden.update=true
            Files.write(GOLDEN, actual);
        }
        try (Workbook expected = WorkbookFactory.create(GOLDEN.toFile(), null, true);
             Workbook produced = WorkbookFactory.create(new ByteArrayInputStream(actual))) {
            assertThat(comparator.compare(expected, produced))
                    .as("differences from %s", GOLDEN)
                    .isEmpty();
        }
    }

    @Test
    void comparatorReportsEveryDifference() throws IOException {
        List<Book> changed = List.of(
                new Book("9780134685991", "Effective Java, 3rd ed.", "Joshua Bloch", 4, 45.5, LocalDate.of(2024, 3, 1)),
                new Book("9781617294945", "Spring in Action", "Craig Walls", 2, 39.9, LocalDate.of(2025, 1, 15)));
        try (Workbook expected = WorkbookFactory.create(GOLDEN.toFile(), null, true);
             Workbook produced = WorkbookFactory.create(new ByteArrayInputStream(export(changed)))) {
            assertThat(comparator.compare(expected, produced)).containsExactly(
                    "sheet 'Books' B2: value 'Effective Java' != 'Effective Java, 3rd ed.'",
                    "sheet 'Books' D2: value '3' != '4'");
        }
    }

    @Test
    void comparatorSeesAStyleChange() throws IOException {
        try (Workbook expected = WorkbookFactory.create(GOLDEN.toFile(), null, true);
             Workbook produced = WorkbookFactory.create(new ByteArrayInputStream(export(TestBooks.TWO)))) {
            Cell price = produced.getSheetAt(0).getRow(1).getCell(4);
            price.setCellStyle(produced.getSheetAt(0).getRow(1).getCell(3).getCellStyle());   // lose "#,##0.00"
            assertThat(comparator.compare(expected, produced))
                    .containsExactly(
                            "sheet 'Books' E2: value '45.50' != '45.5'",
                            "sheet 'Books' E2: number format #,##0.00 != General");
        }
    }

    private static byte[] export(List<Book> books) throws IOException {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        new BookExporter().write(books, out);
        return out.toByteArray();
    }
}

Source: POI Spreadsheet Quick Guide (cell types, styles and fonts) and the org.apache.poi.ss.usermodel Javadoc. ExcelComparator is application code on this page, not a POI class: POI publishes no assertion library for tests.

The three tests have different jobs. inventoryMatchesTheGoldenFile is the regression test. The other two test the comparator itself, which is code too: without them, a comparator that silently compares nothing would make the regression test pass forever. Their observed expectations show what a report looks like when a title and a copy count change:

sheet 'Books' B2: value 'Effective Java' != 'Effective Java, 3rd ed.'
sheet 'Books' D2: value '3' != '4'

and when a price cell loses its number format (the displayed value changes too, because the format is what produced 45.50):

sheet 'Books' E2: value '45.50' != '45.5'
sheet 'Books' E2: number format #,##0.00 != General

Updating the golden file is a deliberate act. The test regenerates it only when the JVM property golden.update is true:

mvn test -Dgolden.update=true -Dtest=GoldenFileTest
git diff --stat src/test/resources/golden    # then open the file in Excel and review it before committing

A golden file is binary, so Git cannot show what changed; the review is the safeguard. Keep the golden files small (two or three rows of each kind are enough), one per report, and name the data in the test, not only in the file.

Tune the comparator to the report, not to the whole format. Comparing every style attribute makes the test fail when a POI upgrade renames an internal default; comparing too little hides regressions. Compare the attributes that users notice (formats, bold, fills, widths, merged cells, formulas) and add an attribute the first time a bug slips through.

Testing the REST layer with MockMvc

A web-slice test checks what no unit test of BookExporter can: the URL, the media type, the download headers, the status of an error, and that the body is a file POI can open. @WebMvcTest starts only the web layer; @Import adds the two services the controller needs.

Download headers and body

StreamingResponseBody runs on another thread, so MockMvc needs two steps: perform returns with request().asyncStarted(), and asyncDispatch(result) then completes the request and exposes the response. A test that skips the second step sees an empty body and passes assertions on nothing. Read the bytes with getContentAsByteArray() (not getContentAsString(), which would decode a binary file as text) and open them with POI.

The assertions to make on a download are the headers a browser acts on:

Header Assert Why

Content-Type

application/vnd.openxmlformats-officedocument.spreadsheetml.sheet (.xlsx) or application/vnd.openxmlformats-officedocument.wordprocessingml.document (.docx).

The browser and the OS choose the application from it.

Content-Disposition

attachment and the file name, in both the quoted filename and the RFC 6266 filename*=UTF-8''... form that ContentDisposition.filename(name, UTF_8) writes.

Without attachment a browser may try to display the file; a non-ASCII name needs filename*.

Cache-Control

no-store for reports that contain personal data.

A shared proxy must not keep the file.

Body

Starts with PK (a zip) and opens with WorkbookFactory.

A controller that fails after the headers went out still answers 200; the bytes tell the truth.

Multipart uploads

MockMvcRequestBuilders.multipart(url).file(new MockMultipartFile(name, originalFilename, contentType, bytes)) builds the request. The first argument of MockMultipartFile is the part name and must equal the name in @RequestPart("file"); the second is the client’s file name, which the import service uses to choose the expected type. Test the missing part (400), a valid workbook (200 and the parsed result) and a table of rejected files (below).

package com.example.bookshelf;

import static org.assertj.core.api.Assertions.assertThat;
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.asyncDispatch;
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.get;
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.multipart;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.content;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.header;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.jsonPath;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.request;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.status;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.util.stream.Stream;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.params.ParameterizedTest;
import org.junit.jupiter.params.provider.Arguments;
import org.junit.jupiter.params.provider.MethodSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.webmvc.test.autoconfigure.WebMvcTest;
import org.springframework.context.annotation.Import;
import org.springframework.http.HttpHeaders;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.test.web.servlet.MockMvc;
import org.springframework.test.web.servlet.MvcResult;

@WebMvcTest(BookController.class)
@Import({BookExporter.class, BookImportService.class})
class BookControllerTest {

    private static final String XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    @Autowired
    private MockMvc mockMvc;

    @Test
    void downloadSendsTheRightHeadersAndAWorkbook() throws Exception {
        MvcResult started = mockMvc.perform(get("/api/books/export"))
                .andExpect(request().asyncStarted())          // StreamingResponseBody runs asynchronously
                .andReturn();

        MvcResult result = mockMvc.perform(asyncDispatch(started))
                .andExpect(status().isOk())
                .andExpect(content().contentType(XLSX))
                .andExpect(header().string(HttpHeaders.CONTENT_DISPOSITION,
                        "attachment; filename=\"books-inventory.xlsx\"; filename*=UTF-8''books-inventory.xlsx"))
                .andExpect(header().string(HttpHeaders.CACHE_CONTROL, "no-store"))
                .andReturn();

        byte[] body = result.getResponse().getContentAsByteArray();
        assertThat(body).startsWith('P', 'K');                  // a zip, not an error page
        try (Workbook wb = WorkbookFactory.create(new ByteArrayInputStream(body))) {
            assertThat(wb.getSheetAt(0).getRow(1).getCell(1).getStringCellValue()).isEqualTo("Effective Java");
        }
    }

    @Test
    void uploadAcceptsAWorkbook() throws Exception {
        MockMultipartFile file = new MockMultipartFile("file", "books-import.xlsx", XLSX, Fixtures.valid());

        mockMvc.perform(multipart("/api/books/import").file(file))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$.imported").value(2))
                .andExpect(jsonPath("$.isbns[0]").value("9780134685991"));
    }

    @Test
    void uploadWithoutThePartIsABadRequest() throws Exception {
        mockMvc.perform(multipart("/api/books/import"))
                .andExpect(status().isBadRequest());
    }

    @ParameterizedTest(name = "{0}")
    @MethodSource("rejected")
    void uploadRejectsBadFiles(String name, String filename, byte[] bytes, String message) throws Exception {
        MockMultipartFile file = new MockMultipartFile("file", filename, XLSX, bytes);

        mockMvc.perform(multipart("/api/books/import").file(file))
                .andExpect(status().isBadRequest())
                .andExpect(jsonPath("$.error").value(message));
    }

    static Stream<Arguments> rejected() throws IOException {
        return Stream.of(
                Arguments.of("empty file", "books-import.xlsx", Fixtures.empty(), "The file is empty"),
                Arguments.of("wrong extension", "books-import.exe", Fixtures.valid(), "Only .xlsx files are accepted"),
                Arguments.of("text renamed .xlsx", "books-import.xlsx", Fixtures.plainTextNamedXlsx(), "Not a readable workbook"),
                Arguments.of("truncated zip", "books-import.xlsx", Fixtures.truncated(), "The file cannot be read"),
                Arguments.of("zip without workbook part", "books-import.xlsx", Fixtures.zipWithoutWorkbook(), "Not a readable workbook"),
                Arguments.of("macro part", "books-import.xlsx", Fixtures.withMacro(), "Macro-enabled workbooks are not accepted"),
                Arguments.of("DOCTYPE in the workbook part", "books-import.xlsx", Fixtures.withDoctype(), "Not a readable workbook"),
                Arguments.of("zip bomb", "books-import.xlsx", Fixtures.zipBomb(), "Not a readable workbook"));
    }
}

Source: Spring Framework, MockMvc, MockMvc, async requests, the Javadoc of MockMultipartFile and the JUnit documentation of parameterized tests.

What MockMvc does not test is the multipart parser of the container. MockMultipartFile is handed straight to the argument resolver, so spring.servlet.multipart.max-file-size, max-request-size and the container’s own limits are never applied. A test that proves "a 20 MB file gets a 413" needs @SpringBootTest(webEnvironment = RANDOM_PORT) and a real HTTP client, or a check in a pipeline with curl. Keep such a test to one or two cases; the size limits are configuration, and the application-level checks (extension, content, macros) are what the cheap MockMvc tests cover. The download side has the same blind spot: MockMvc does not model chunked transfer or Content-Length negotiation.

Malformed and malicious fixture files

Upload code is tested with files the application should refuse. Three rules make these tests safe and maintainable:

  1. Generate fixtures in code. A byte array built by a helper documents what is wrong with the file, never trips a virus scanner on a developer laptop or in the repository, and is created fresh by every run. Do not commit a real exploit, and do not commit a 64 MB bomb: the bomb below is generated in memory and compresses to about 67 KB (observed).

  2. One defect per fixture. A file that is truncated, has a DOCTYPE and carries a macro tells you nothing when the test fails. Start from a valid workbook and change exactly one thing.

  3. Assert on the outcome, not on the message of a third-party exception. The controller test asserts a 400 and the application’s own message. A separate test, kept next to the fixtures, documents what POI itself does, so that a POI upgrade that changes the behaviour (a different exception type, a limit that no longer triggers) shows up in one place.

None of these fixtures is a working attack. Each one only has the shape that trips a defence; the macro part is the text "not really VBA" with the right name and content type.

package com.example.bookshelf;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.util.Arrays;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;
import java.util.zip.ZipOutputStream;

/** Builds invalid and hostile workbooks in memory. Nothing here is a real exploit; each file only trips a check. */
final class Fixtures {

    private Fixtures() {
    }

    static byte[] valid() throws IOException {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        new BookExporter().write(TestBooks.TWO, out);
        return out.toByteArray();
    }

    static byte[] empty() {
        return new byte[0];
    }

    static byte[] plainTextNamedXlsx() {
        return "isbn;title\n9780134685991;Effective Java\n".getBytes(StandardCharsets.UTF_8);
    }

    static byte[] truncated() throws IOException {
        byte[] valid = valid();
        return Arrays.copyOf(valid, valid.length / 2);
    }

    static byte[] zipWithoutWorkbook() throws IOException {
        return rewrite(valid(), "xl/workbook.xml", null);
    }

    /** A valid workbook that also carries a VBA project part, as an .xlsm renamed to .xlsx would. */
    static byte[] withMacro() throws IOException {
        byte[] zip = valid();
        String types = new String(read(zip, "[Content_Types].xml"), StandardCharsets.UTF_8).replace("<Default ",
                "<Default Extension=\"bin\" ContentType=\"application/vnd.ms-office.vbaProject\"/><Default ");
        zip = rewrite(zip, "[Content_Types].xml", types.getBytes(StandardCharsets.UTF_8));
        return rewrite(zip, "xl/vbaProject.bin", "not really VBA".getBytes(StandardCharsets.UTF_8));
    }

    static byte[] read(byte[] zip, String name) throws IOException {
        try (ZipInputStream in = new ZipInputStream(new ByteArrayInputStream(zip))) {
            for (ZipEntry e = in.getNextEntry(); e != null; e = in.getNextEntry()) {
                if (e.getName().equals(name)) {
                    return in.readAllBytes();
                }
            }
        }
        throw new IOException("No entry " + name);
    }

    /** The workbook part starts with a DOCTYPE that declares an external entity. */
    static byte[] withDoctype() throws IOException {
        String xml = """
                <?xml version="1.0" encoding="UTF-8"?>
                <!DOCTYPE workbook [<!ENTITY xxe SYSTEM "file:///does/not/exist">]>
                <workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">&xxe;</workbook>
                """;
        return rewrite(valid(), "xl/workbook.xml", xml.getBytes(StandardCharsets.UTF_8));
    }

    /** A worksheet part that is a few kilobytes compressed and 64 MB expanded. */
    static byte[] zipBomb() throws IOException {
        byte[] head = """
                <?xml version="1.0" encoding="UTF-8"?><worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData/><!--""".getBytes(StandardCharsets.UTF_8);
        byte[] tail = "--></worksheet>".getBytes(StandardCharsets.UTF_8);
        ByteArrayOutputStream bomb = new ByteArrayOutputStream();
        bomb.writeBytes(head);
        byte[] block = new byte[1024 * 1024];
        Arrays.fill(block, (byte) 'A');
        for (int i = 0; i < 64; i++) {
            bomb.writeBytes(block);
        }
        bomb.writeBytes(tail);
        return rewrite(valid(), "xl/worksheets/sheet1.xml", bomb.toByteArray());
    }

    /** Copies a zip, replacing (or adding) one entry; a null replacement removes it. */
    static byte[] rewrite(byte[] zip, String name, byte[] replacement) throws IOException {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        boolean replaced = false;
        try (ZipInputStream in = new ZipInputStream(new ByteArrayInputStream(zip));
             ZipOutputStream zos = new ZipOutputStream(out)) {
            for (ZipEntry e = in.getNextEntry(); e != null; e = in.getNextEntry()) {
                if (e.getName().equals(name)) {
                    replaced = true;
                    if (replacement != null) {
                        zos.putNextEntry(new ZipEntry(name));
                        zos.write(replacement);
                        zos.closeEntry();
                    }
                } else {
                    zos.putNextEntry(new ZipEntry(e.getName()));
                    in.transferTo(zos);
                    zos.closeEntry();
                }
            }
            if (!replaced && replacement != null) {
                zos.putNextEntry(new ZipEntry(name));
                zos.write(replacement);
                zos.closeEntry();
            }
        }
        return out.toByteArray();
    }
}
package com.example.bookshelf;

import static org.assertj.core.api.Assertions.assertThat;
import static org.assertj.core.api.Assertions.assertThatCode;
import static org.assertj.core.api.Assertions.assertThatThrownBy;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import org.apache.poi.EmptyFileException;
import org.apache.poi.ooxml.POIXMLException;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.junit.jupiter.api.Test;

/** What POI itself does with each fixture, before any application code runs. */
class UntrustedFixturesTest {

    private static void open(byte[] bytes) throws IOException {
        try (var wb = WorkbookFactory.create(new ByteArrayInputStream(bytes))) {
            wb.getNumberOfSheets();
        }
    }

    @Test
    void validFixtureOpens() {
        assertThatCode(() -> open(Fixtures.valid())).doesNotThrowAnyException();
    }

    @Test
    void emptyFile() {
        assertThatThrownBy(() -> open(Fixtures.empty())).isInstanceOf(EmptyFileException.class);
    }

    @Test
    void plainTextIsNotAWorkbook() {
        assertThatThrownBy(() -> open(Fixtures.plainTextNamedXlsx()))
                .isInstanceOf(IOException.class)
                .hasMessageContaining("unsupported file type");
    }

    @Test
    void truncatedZip() {
        assertThatThrownBy(() -> open(Fixtures.truncated()))
                .isInstanceOf(IOException.class)
                .hasMessageContaining("Truncated ZIP");
    }

    @Test
    void missingWorkbookPart() {
        assertThatThrownBy(() -> open(Fixtures.zipWithoutWorkbook()))
                .isInstanceOf(POIXMLException.class)
                .hasMessageContaining("/xl/workbook.xml");
    }

    @Test
    void doctypeIsRefused() {
        assertThatThrownBy(() -> open(Fixtures.withDoctype()))
                .isInstanceOf(POIXMLException.class)
                .hasMessageContaining("DOCTYPE is disallowed");
    }

    @Test
    void zipBombIsStoppedByTheInflateRatio() {
        assertThatThrownBy(() -> open(Fixtures.zipBomb()))
                .isInstanceOf(IOException.class)
                .hasMessageContaining("Zip bomb detected");
    }

    @Test
    void macroPartIsInTheZipButPoiNeverRunsIt() throws IOException {
        byte[] macro = Fixtures.withMacro();
        assertThat(macro).isNotEqualTo(Fixtures.valid());
        assertThatCode(() -> open(macro)).doesNotThrowAnyException();   // POI opens it; the application must reject it
    }
}

Source: POI Security, ZipSecureFile and the OWASP File Upload Cheat Sheet.

What POI does with each fixture (observed, POI 5.5.1):

Fixture Exception from WorkbookFactory.create Message (start)

Empty file

EmptyFileException

The supplied file was empty (zero bytes long)

Text renamed .xlsx

IOException

Can't open workbook - unsupported file type: UNKNOWN

Truncated zip

IOException

Truncated ZIP file

Zip without xl/workbook.xml

POIXMLException

OOXML file structure broken/invalid - core document '/xl/workbook.xml' not found.

DOCTYPE with an external entity

POIXMLException (wrapping XmlException)

DOCTYPE is disallowed when the feature "http://apache.org/xml/features/disallow-doctype-decl" set to true.

64 MB part that compresses to about 67 KB

IOException

Zip bomb detected! The file would exceed the max. ratio of compressed file size to the size of the expanded data.

Macro part (xl/vbaProject.bin)

none, POI opens it

The application must reject it: POI never runs VBA, but the next person who opens the stored file might.

The table shows why the import service catches broadly. The failures arrive as four different exception types, two of them unchecked, and the truncated file can fail even earlier, in the zip scan that looks for macros (the controller test shows it answering "The file cannot be read" rather than "Not a readable workbook"). The service catches Exception and StackOverflowError at the boundary of the parse and answers 400 for all of them, which is what the POI security guidance asks for. The macro case is the reminder that POI opening a file successfully proves nothing about it: the decision to reject macro content is the application’s, and it needs its own test, which is the macro part row of the parameterized test.

Add the same style of test for each defence the application depends on: an oversized IOUtils byte array, a part that exceeds ZipSecureFile.setMaxTextSize, a duplicate zip entry name, an .xls renamed .xlsx (the FileMagic check in Security & Untrusted Files). Limits that the application changes at startup, such as ZipSecureFile.setMinInflateRatio, are JVM-wide: a test that changes one must restore it in an @AfterEach, or it affects every test that runs later in the same JVM.

Keeping the tests fast and deterministic

  • Pin locale and time zone wherever a displayed string or a date is asserted: new DataFormatter(Locale.US), a LocalDate and not "today", and for Date cells a fixed zone. The machine running the tests must not matter.

  • Use small data. Two books are enough to test formats, formulas and the total row. A test of "10,000 rows through SXSSF" is a performance or memory test and belongs in a separate, tagged class (@Tag("slow")) that CI runs on its own; see Streaming Writes with SXSSF for what to measure.

  • Test streaming writers by reading back, never by inspecting the temp files; SXSSF removes them when the workbook is closed or disposed, and a test that depends on them depends on an implementation detail.

  • Share nothing mutable. A Workbook is not thread-safe and holds its styles; create a new one per test, as the @BeforeEach method does. Keep the fixtures (Fixtures.valid()) as methods that return fresh bytes, not as static arrays that a test can corrupt for the next.

  • Name the failing scenario. @ParameterizedTest(name = "{0}") makes the report read "zip bomb" instead of "[8]". Put the scenario first in the Arguments, as rejected() does.

  • Test the error path of a download too. A controller that throws after streaming started cannot change the status any more. Assert that the service fails before the response is committed (build the workbook first, stream second) when the data is small enough to do so, and test the larger streaming case with the full application.

  • Do not mock POI. Mocking Workbook or Sheet tests the mock. The real classes are fast and in-memory; the value of the test is in letting them read what the production code wrote.

The complete run

The whole project (5 test classes, 32 tests) was run with mvn test against POI 5.5.1 and Spring Boot 4.1.1. Observed:

[INFO] Tests run: 11, Failures: 0, Errors: 0, Skipped: 0 -- in com.example.bookshelf.BookControllerTest
[INFO] Tests run: 6, Failures: 0, Errors: 0, Skipped: 0 -- in com.example.bookshelf.BookExporterRoundTripTest
[INFO] Tests run: 8, Failures: 0, Errors: 0, Skipped: 0 -- in com.example.bookshelf.UntrustedFixturesTest
[INFO] Tests run: 4, Failures: 0, Errors: 0, Skipped: 0 -- in com.example.bookshelf.LoanLetterTest
[INFO] Tests run: 3, Failures: 0, Errors: 0, Skipped: 0 -- in com.example.bookshelf.GoldenFileTest
[INFO] Tests run: 32, Failures: 0, Errors: 0, Skipped: 0
[INFO] BUILD SUCCESS

The 11 tests of BookControllerTest are the download test, the upload test, the missing part and the eight rows of the parameterized rejection test. BookControllerTest is the slowest class because it starts the web slice and generates the 64 MB zip bomb.

What changes with the POI version

  • 5.5.1 baseline: everything above was run on 5.5.1. The exact messages in the hostile-fixture table are not API and may differ between releases. That is the reason to keep UntrustedFixturesTest separate from the controller test.

  • 6.0 (announced, not released): the project has announced that DataFormatter will return cached values for formula cells by default and that deprecated APIs are removed. A round-trip test that formats formula cells will need to state which behaviour it expects, and tests that still call deprecated methods will stop compiling; the tests on this page use neither. Re-run the suite on the release candidate before adopting 6.0.

  • Spring Boot 3 to 4: only the imports change. @WebMvcTest and @AutoConfigureMockMvc now live in org.springframework.boot.webmvc.test.autoconfigure, and the starter is spring-boot-starter-webmvc-test. @MockitoBean replaces @MockBean.

References