☕🍃 APACHE POI in SPRING BOOT: IMPORT, EXPORT & TRANSFOR...

Return to site

☕🍃 APACHE POI in SPRING BOOT: IMPORT, EXPORT & TRANSFORM EXCEL

Excel files are still everywhere. Java can automate them cleanly.

· spring

🔸 TL;DR

Apache POI ( Poor Obfuscation Implementation ) is the Apache Java library for manipulating Microsoft Office files.

For Java backends, one of its most useful jobs is turning Excel into application data; and application data back into Excel.

Think:

XLS/XLSX → Java → process → XLSX/CSV/XML

Important nuance: POI itself is primarily for Office formats. For CSV or arbitrary XML, combine it with libraries such as Apache Commons CSV, Jackson XML or JAXB.

🔸 WHAT DOES APACHE POI DO?

For spreadsheets, POI gives you:

▪️ HSSF → legacy .xls

▪️ XSSF → modern .xlsx

▪️ SXSSF → streaming .xlsx generation for large exports

▪️ Read sheets, rows and cells

▪️ Create and modify workbooks

▪️ Import Excel data into Java objects

▪️ Export business data to Excel

▪️ Process and transform values

▪️ Handle formulas, dates and cell types

▪️ Add styles, fonts, borders and merged cells

▪️ Build reports dynamically

A tiny export already looks like this:

try (Workbook wb = new XSSFWorkbook()) {
    Sheet sheet = wb.createSheet("Users");

    Row row = sheet.createRow(0);
    row.createCell(0).setCellValue("Java");
    row.createCell(1).setCellValue(27);

    try (OutputStream out =
             Files.newOutputStream(Path.of("users.xlsx"))) {
        wb.write(out);
    }
}

🔸 SET IT UP WITH SPRING BOOT

Add POI OOXML:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.5.1</version>
</dependency>

Then importing an uploaded workbook can be as simple as:

public void importFile(MultipartFile file) throws IOException {
    try (Workbook wb =
             WorkbookFactory.create(file.getInputStream())) {

        Sheet sheet = wb.getSheetAt(0);

        for (Row row : sheet) {
            String name = row.getCell(0).getStringCellValue();

            // validate
            // map to DTO/domain
            // transform
            // persist
        }
    }
}

And your typical Spring architecture becomes:

Controller → ExcelService → Apache POI → Domain/DB

For export:

Domain/DB → ExcelService → Apache POI → HTTP response

@GetMapping("/export")
public void export(HttpServletResponse response)
        throws IOException {

    response.setContentType(
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

    try (Workbook wb = reportService.createWorkbook()) {
        wb.write(response.getOutputStream());
    }
}

🔸 XLSX, CSV AND XML TOGETHER?

This is where POI becomes part of a broader data-processing pipeline.

For example:

Excel → Apache POI → Java DTOs → validation/business rules → Commons CSV / Jackson XML / Apache POI → CSV, XML or XLSX

POI handles the Excel complexity while your Java model becomes the neutral transformation layer.

That is especially useful for imports, reporting, migrations, batch processing and integration with legacy enterprise systems.

🔸 TAKEAWAYS

▪️ Apache POI is the Java standard toolbox for Office file manipulation.

▪️ For Excel, think HSSF / XSSF / SXSSF.

▪️ It can both IMPORT and EXPORT spreadsheet data.

▪️ Put Java DTOs/domain objects between input and output formats.

▪️ POI is not a generic CSV/XML library: combine specialized libraries when needed.

▪️ In Spring Boot, keep POI inside a dedicated service instead of spreading spreadsheet logic across controllers.

Sometimes the enterprise integration API is still just an Excel file. 😄

#Java #SpringBoot #ApachePOI #Excel #Backend #SoftwareDevelopment #JavaDeveloper #DataProcessing #Spring #Programming

Go further with Java certification:

Java👇

https://bit.ly/javaOCP

Spring👇

https://bit.ly/2v7222

SpringBook👇

https://bit.ly/springtify

JavaFullstackBook👇

https://bit.ly/jfstack27