Building Excel Workflows with Spring Boot and Apache POI

Excel remains a practical interchange format for Australian finance, operations and warehouse teams. Businesses in Sydney, Melbourne and Brisbane routinely receive order sheets, timesheets and invoice registers from administrators, franchisees or suppliers who prefer a familiar grid to a web form.

Integrating Spring Boot with Apache POI for Excel file processing lets those organisations expose controlled spreadsheet imports and exports without exposing database details. The Java service can read workbook cells, apply validation, write formatted sheets and stream large files while retaining Spring Security, dependency injection and REST conventions.

Why Apache POI belongs in a Spring Boot service

Apache POI provides a mature API for creating and reading Microsoft Excel formats, including the widely used .xlsx workbook. It supports cell styles, formulas, dates, validation rules, multiple worksheets and streaming operations for larger files.

It also fits naturally into a layered Spring Boot application. Controllers can handle uploads and downloads, services can coordinate business rules, and repositories can remain unaware of the spreadsheet format. This separation keeps spreadsheet handling testable and prevents import logic from becoming tangled with persistence code.

Dependencies and project configuration

Add the poi-ooxml dependency and pin a compatible release in the project’s Maven properties:

<properties>
    <poi.version>5.4.1</poi.version>
</properties>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>${poi.version}</version>
</dependency>

Configure Spring Boot’s multipart limits so oversized uploads fail early. The application should also set sensible worksheet, row and cell limits because a small compressed workbook can expand into a large in-memory object graph. Workbook instances must not be shared between requests because Apache POI objects are not generally thread-safe.

Creating reliable Excel exports

Use SXSSFWorkbook when an export may contain thousands of rows. It writes cells to temporary storage and keeps only a limited window in memory, making it more suitable for reporting than the standard XSSFWorkbook.

var workbook = new SXSSFWorkbook(200);
try {
    var sheet = workbook.createSheet("Orders");
    sheet.createRow(0).createCell(0).setCellValue("Order");

    // Populate headers and order rows
    workbook.write(outputStream);
} finally {
    workbook.dispose();
    workbook.close();
}

Create styles once per workbook rather than for every cell. Apply explicit formats for currency, percentages and dates, and set useful column widths. The download response should include a sanitised filename and a suitable content type. For very large reports, writing directly to the response stream avoids creating an unnecessary copy on disk.

Reading uploaded workbooks safely

Begin by validating the upload’s extension, content type and size, then open it with WorkbookFactory. Iterate through worksheets and rows using a numeric index so blank rows do not unexpectedly terminate processing. DataFormatter can convert cells into display-friendly text while respecting their workbook styles.

Excel dates are numeric values, so parse them into Java time types when business logic requires dates rather than presentation strings. Use DateTimeFormatter with a clear pattern such as dd/MM/yyyy for Australian reports. Formula evaluation should be limited to required cells because unsupported or external formulas can slow processing. Never trust formulas, hyperlinks or filenames supplied by the uploader.

Designing import and download APIs

Separate upload and download endpoints so each has a clear responsibility. A typical import endpoint accepts a MultipartFile, parses it into validated domain records and returns a structured summary containing accepted, rejected and duplicate row counts. Download endpoints should return a generated workbook with Content-Disposition set appropriately.

Persist records only after the complete workbook passes validation. Otherwise, a malformed row near the end could leave a partially imported order batch. For large imports, store the uploaded file temporarily, return a job identifier and process it asynchronously with status updates rather than holding an HTTP request open.

Export fields and validation checks

Agree on business meaning before defining worksheet columns. Useful Australian order exports commonly contain:

Validation should protect both the workbook and the records it creates:

Production practices for Australian deployments

Australian teams frequently operate across multiple locations while expecting consistent data handling. Keep sensitive employee, payroll or customer records in Australian-hosted infrastructure when contractual or regulatory requirements demand it. AWS workloads around Sydney and Melbourne commonly use ap-southeast-2, while access controls and audit logging should reflect the organisations involved.

For Australian small and medium-sized businesses, spreadsheet data often originates in systems such as MYOB or Xero. A common Australian office custom is to circulate a workbook for approval before finance reconciles it. Exports should therefore preserve source identifiers and timestamps, allowing reviewers to trace each figure back to the originating transaction.

Monitor generation time, memory consumption, upload failures and rejected rows in production. Apply timeouts to imports, clean up temporary files and avoid logging spreadsheet contents because they may contain commercially sensitive information. Once these controls are in place, Spring Boot and Apache POI provide a dependable bridge between familiar Excel workflows and modern Java services.