Apache POI is a Java library for reading and writing Microsoft Office binary and OOXML file formats. This article covers the necessary Maven dependencies, a summary of its components, and practical code examples for creating Excel workbooks.

Maven Dependencies

Include the following dependencies for Excel (XLS and XLSX), Word, PowerPoint, Visio, Publisher, and Outlook support:

<!-- Core POI for OLE2 and HSSF (XLS) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>

<!-- OOXML support (XSSF, XWPF, etc.) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

<!-- OOXML schemas (only one is needed) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.2</version>
</dependency>

<!-- Scratchpad for older formats like HWPF, HSLF, etc. -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>4.1.2</version>
</dependency>

POI Component Overview

Note: For Excel, HSSFWorkbook handles .xls and XSSFWorkbook handles .xlsx. Both implement the Workbook interface. Sheet names are limited to 31 characters and cannot contain *, /, or \.

Common POI Code Patterns

// Font configuration
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 12);
font.setColor(IndexedColors.RED.getIndex());
font.setBold(true);

// Cell style with alignment and borders
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.MIDDLE);
cellStyle.setBorderTop(BorderStyle.MEDIUM);
cellStyle.setBorderBottom(BorderStyle.MEDIUM);
cellStyle.setBorderLeft(BorderStyle.MEDIUM);
cellStyle.setBorderRight(BorderStyle.MEDIUM);
cellStyle.setFont(font);

// Apply style to a cell
cell.setCellStyle(cellStyle);

Practical Example: Exporting Excel Data

Below is a controller endpoint that triggers an Excel export with dynamic sheets.

@GetMapping("/exportScores")
@ApiOperation("Export score data to Excel")
public void exportScores(HttpServletRequest request, HttpServletResponse response) throws Exception {
    String fileName = new String("成绩导出".getBytes("gb2312"), "ISO8859-1")
        + DatePattern.PURE_DATETIME_MS_FORMAT.format(new Date()) + ".xls";
    response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
    response.setContentType(request.getServletContext().getMimeType(fileName));
    try (OutputStream out = response.getOutputStream()) {
        scoreService.exportToExcel(out);
    }
}

// Service implementation
public void exportToExcel(OutputStream out) {
    // Simulated dataset
    Map<String, List<String>> subjectData = new HashMap<>();
    subjectData.put("English", Arrays.asList("Name,Score", "Alice,88", "Bob,92"));
    subjectData.put("Math", Arrays.asList("Name,Score", "Alice,95", "Bob,78"));
    
    try (HSSFWorkbook workbook = new HSSFWorkbook()) {  // For .xls; use XSSFWorkbook for .xlsx
        for (String subject : subjectData.keySet()) {
            HSSFSheet sheet = workbook.createSheet(subject);
            HSSFCellStyle style = workbook.createCellStyle();
            style.setAlignment(HorizontalAlignment.LEFT);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            
            List<String> rows = subjectData.get(subject);
            for (int i = 0; i < rows.size(); i++) {
                HSSFRow row = sheet.createRow(i);
                String[] cells = rows.get(i).split(",");
                for (int j = 0; j < cells.length; j++) {
                    HSSFCell cell = row.createCell(j);
                    cell.setCellValue(cells[j]);
                    cell.setCellStyle(style);
                }
            }
        }
        workbook.write(out);
    } catch (IOException e) {
        e.printStackTrace();
    }
}

Using Hutool for Simplified Excel Export

Hutool’s ExcelWriter wrapper reduces boilerplate. The following example exports department data with header styling.

public void exportDepartments(OutputStream outputStream) {
    List<Department> departments = departmentService.list();
    if (departments == null || departments.isEmpty()) {
        return;
    }
    
    // Header row
    List<String> headers = Arrays.asList("Name", "Short Name", "Filled In", "Code", "Region Code");
    ExcelWriter writer = new ExcelWriter();
    writer.writeRow(headers);
    
    // Apply style to header cells (positions 0-4, row 0)
    for (int col = 0; col < headers.size(); col++) {
        setHeaderStyle(writer, 0, col);
    }
    
    // Data rows
    for (Department dept : departments) {
        List<Object> values = new ArrayList<>();
        values.add(dept.getName());
        values.add(dept.getShortName());
        values.add(dept.getFillInName());
        values.add(dept.getCode());
        values.add(dept.getRegionCode());
        writer.writeRow(values);
    }
    
    // Set column widths (approx 40 characters)
    for (int col = 0; col < headers.size(); col++) {
        writer.setColumnWidth(col, 20 * 2);
    }
    
    writer.flush(outputStream);
}

private void setHeaderStyle(ExcelWriter writer, int row, int col) {
    CellStyle style = writer.createCellStyle(row, col);
    // Top border
    style.setBorderTop(BorderStyle.THIN);
    style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
    // Right border
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
    // Bottom border
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
    // Left border
    style.setBorderLeft(BorderStyle.THIN);
    style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
    // Grey background
    style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}