When working with business systems, requirements like “I want to download the list as an Excel file” or “I want to import an uploaded Excel file” come up almost without fail. If you’re doing this in Java, the go-to library is Apache POI.

In this article, we’ll go through it code-first—from generating an xlsx with Apache POI and serving it for download from a Spring Boot REST API, to SXSSFWorkbook for keeping memory from overflowing on large row counts, and finally to parsing an uploaded xlsx and mapping it into DTOs.

Let me say the key point up front: for output, the axis is choosing between XSSFWorkbook for the normal case, and SXSSFWorkbook for large data. Once you’ve got this down, the whole picture becomes much clearer.

Adding the Apache POI Dependency and Setting Up

First, the dependency. Since we’re dealing with xlsx (the new format), we add both poi and poi-ooxml.

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

For Gradle, it’s as follows.

implementation 'org.apache.poi:poi-ooxml:5.3.0'

Adding poi-ooxml pulls in poi itself transitively. Note that .xls (the old format) uses HSSFWorkbook and .xlsx (the new format) uses XSSFWorkbook, but what we’re building here is basically fine as xlsx, so this article proceeds on the assumption of xlsx.

Apache POI’s data structure is a straightforward hierarchy: Workbook → Sheet → Row → Cell. If you keep the image of assembling this nesting in code, you won’t get lost.

The Basics of Generating Excel with XSSFWorkbook

Let’s create a list with a header. We’ll make only the header bold and also auto-size the column widths.

public Workbook createUserSheet(List<User> users) {
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("ユーザー一覧");

    // ヘッダー用スタイル
    CellStyle headerStyle = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setBold(true);
    headerStyle.setFont(font);

    Row header = sheet.createRow(0);
    String[] titles = {"ID", "名前", "登録日"};
    for (int i = 0; i < titles.length; i++) {
        Cell cell = header.createCell(i);
        cell.setCellValue(titles[i]);
        cell.setCellStyle(headerStyle);
    }

    // 日付セル用スタイル
    CellStyle dateStyle = workbook.createCellStyle();
    CreationHelper helper = workbook.getCreationHelper();
    dateStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy/mm/dd"));

    int rowNum = 1;
    for (User u : users) {
        Row row = sheet.createRow(rowNum++);
        row.createCell(0).setCellValue(u.getId());        // 数値
        row.createCell(1).setCellValue(u.getName());      // 文字列
        Cell dateCell = row.createCell(2);
        dateCell.setCellValue(u.getCreatedAt());          // LocalDateTime
        dateCell.setCellStyle(dateStyle);
    }

    for (int i = 0; i < titles.length; i++) {
        sheet.autoSizeColumn(i);
    }
    return workbook;
}

setCellValue behaves differently depending on the type of its argument. A String is written as-is as a string, a double or int as a number, and a LocalDateTime / Date as a date serial value. For dates, be careful: unless you specify not just the value but also the display format (DataFormat), it will appear as a plain number.

autoSizeColumn is convenient, but it scans every row, so it gets heavy with large data. It generally can’t be used with SXSSF (described later), so in that case the realistic approach is to fix widths with setColumnWidth.

Serving Excel for Download from a REST API

We return the Workbook we created as an HTTP response. Writing it to a ByteArrayOutputStream and returning it as ResponseEntity<byte[]> is easy to handle.

@GetMapping("/users/excel")
public ResponseEntity<byte[]> download() throws IOException {
    byte[] bytes;
    try (Workbook workbook = createUserSheet(userService.findAll());
         ByteArrayOutputStream out = new ByteArrayOutputStream()) {
        workbook.write(out);
        bytes = out.toByteArray();
    }

    String filename = URLEncoder.encode("ユーザー一覧.xlsx", StandardCharsets.UTF_8)
            .replace("+", "%20");

    HttpHeaders headers = new HttpHeaders();
    headers.setContentType(MediaType.parseMediaType(
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
    headers.set(HttpHeaders.CONTENT_DISPOSITION,
            "attachment; filename*=UTF-8''" + filename);

    return new ResponseEntity<>(bytes, headers, HttpStatus.OK);
}

There are three key points. For Content-Type, specify the long MIME type dedicated to xlsx. Since non-ASCII filenames are prone to garbling, pass a URL-encoded name in the filename*=UTF-8'' format. And use try-with-resources to always close the Workbook. Forgetting to close it leads to memory leaks and leftover temporary files.

The general conventions for file downloads themselves are also covered in Implementing File Upload and Download with MultipartFile in Spring Boot, so reading them together will deepen your understanding.

Handling Large Data: Preventing Memory Exhaustion with SXSSFWorkbook

XSSFWorkbook holds all rows in memory. A few hundred rows are no problem, but once you get into the tens of thousands to hundreds of thousands of rows, the risk of OutOfMemoryError rises sharply.

That’s where SXSSFWorkbook comes in. It keeps only the specified number of rows in memory and flushes older rows that fall outside the window to a temporary file, operating in a streaming fashion.

public void writeLargeExcel(OutputStream os, List<Order> orders) throws IOException {
    // 直近100行だけメモリに保持
    try (SXSSFWorkbook workbook = new SXSSFWorkbook(100)) {
        Sheet sheet = workbook.createSheet("注文");
        int rowNum = 0;
        for (Order o : orders) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(o.getId());
            row.createCell(1).setCellValue(o.getAmount());
        }
        workbook.write(os);
        workbook.dispose(); // 一時ファイルを削除
    }
}

The constructor argument rowAccessWindowSize is the number of rows to keep in memory. Just be aware that rows before the window have already been flushed, so you can’t rewrite them later with getRow.

Another important thing is dispose(). Since SXSSF creates temporary files on disk, the temporary files will keep piling up if you don’t call it. Make sure to call it reliably along with close().

The choice is simple. If you keep in mind XSSFWorkbook for small scale with elaborate formatting, and SXSSFWorkbook when there are many rows and you just want to output them as lightly as possible, you won’t go far wrong. If output takes time, offloading it to a separate thread—referring to Implementing Asynchronous Processing in Spring Boot—is also an option.

Reading an Uploaded Excel File and Mapping It to a DTO

This time, the reverse direction: reading an uploaded xlsx. Get the InputStream from the MultipartFile and pass it to WorkbookFactory.create(), which automatically determines the format (xls/xlsx).

public List<User> parse(MultipartFile file) throws IOException {
    List<User> result = new ArrayList<>();
    DataFormatter formatter = new DataFormatter();

    try (Workbook workbook = WorkbookFactory.create(file.getInputStream())) {
        Sheet sheet = workbook.getSheetAt(0);
        for (Row row : sheet) {
            if (row.getRowNum() == 0) continue;       // ヘッダー行をスキップ
            if (isEmptyRow(row)) continue;            // 空行をスキップ

            User user = new User();
            user.setId((long) row.getCell(0).getNumericCellValue());
            user.setName(formatter.formatCellValue(row.getCell(1)));
            result.add(user);
        }
    }
    return result;
}

If you want to retrieve values straightforwardly as strings, DataFormatter is handy. It returns exactly the value as displayed in Excel as a string, which is invaluable when you want to import formatted numbers or dates as-is. The flow is: read the cells of one row, pack them into a DTO, gather them into a List, and return it.

For building the foundation of CRUD, also refer to Implementing a REST API CRUD in Spring Boot.

Cell Type Detection and Handling Common Exceptions

A common pitfall in Excel parsing is types. Calling getStringCellValue() on a numeric cell throws IllegalStateException: Cannot get a STRING value from a NUMERIC cell. Determine the cell type with getCellType().

String readAsString(Cell cell) {
    if (cell == null) return "";
    return switch (cell.getCellType()) {
        case STRING -> cell.getStringCellValue();
        case NUMERIC -> DateUtil.isCellDateFormatted(cell)
                ? cell.getLocalDateTimeCellValue().toString()
                : String.valueOf(cell.getNumericCellValue());
        case BOOLEAN -> String.valueOf(cell.getBooleanCellValue());
        case BLANK -> "";
        default -> "";
    };
}

Since dates are numbers internally in Excel, the trick is: among CellType.NUMERIC cells, those for which DateUtil.isCellDateFormatted() is true are dates.

Another trap is missing cells. Calling getCell on a cell whose value is empty may return null, and touching it directly causes a NullPointerException. You can control the behavior by passing a MissingCellPolicy to getCell.

// 欠損セルを null ではなく空セルとして返す
Cell cell = row.getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

Unexpected formats are inevitable in imports, so returning a message with the row number attached—like “The amount on row 3 is not a number”—makes it easier for users to fix. In practice, it helps a lot to convey at which row and which column the failure occurred, rather than swallowing the exception.

Summary

We’ve gone through Excel input and output with Apache POI. Let me organize the key points.

  • XSSFWorkbook is the default for output; use SXSSFWorkbook for large row counts to address memory. For the latter, don’t forget dispose()
  • For downloads, use the xlsx MIME type for Content-Type, and encode non-ASCII filenames with filename*=UTF-8''
  • For reading, open with WorkbookFactory; type detection with getCellType() and DataFormatter are the crux. Handle missing cells safely with MissingCellPolicy

Excel isn’t scary as long as you handle type detection and exception handling carefully. If you need a PDF for the same kind of report, there’s also Generating PDFs in Spring Boot (OpenPDF / iText), so use whichever fits your requirements.