parent-spring-tools

spring-excel-mapper

Lightweight, annotation-driven Excel import/export mapper for Java (JDK 17+).

Overview

spring-excel-mapper provides annotation-driven mapping between Excel Workbook/Sheet and Java POJOs, plus utilities for exporting objects into Excel templates while preserving styles.

Key capabilities:

Quick example:

@ExcelSheet
@ExcelRow(startRowIndex = 1)
public class StudentExcelModel {
    @ExcelColumn(col = "A", required = true)
    public String name;
}

Workbook wb = WorkbookFactory.create(inputStream);
ImportResult<StudentExcelModel> result = 
    ExcelMapper.parseWorkbookWithResult(wb, StudentExcelModel.class, false);

For usage patterns and examples, see spring-tools-simples README.

Architecture & Design

Core Components

1. Annotations (org.springtools.excel.annotation)

Define mapping metadata on your model classes:

@ExcelSheet

@ExcelRow

@ExcelSection

@ExcelColumn

2. Mapping & I/O

ExcelMapper — reads Excel → Java objects

Main methods:

ExcelExporter — writes Java objects → Excel templates

Main methods:

3. Validation & Conversion

ExcelValidator

ExcelTypeConverter

4. Utilities

ExcelUtils — low-level helpers

Technical Details

Column Mapping

Column mapping uses Excel column letters (A, B, C, … Z, AA, AB, …). ExcelUtils.colLetterToIndex converts letters to 0-based indices for POI API usage.

Row Indexing

Annotations use 1-based “natural” numbering (row 1 = first row in Excel UI). Code typically does -1 when calling POI APIs.

Field Ordering & Binding Cache

ExcelUtils.prepareBindings(Class) reads clazz.getDeclaredFields() and caches bindings. Field declaration order currently matters. Consider adding order() to @ExcelColumn for explicit ordering in future versions.

Section Parsing

@ExcelSection uses sectionKeyword + keywordInCol to locate a data block. ExcelMapper.parseSheetSection scans for the keyword, then reads rows below it until stopKeyword or sheet end.

Error Handling

API Reference

ExcelMapper

// Parse all sheets, throw on error
List<T> parseWorkbook(Workbook workbook, Class<T> clazz)

// Parse with error collection
ImportResult<T> parseWorkbookWithResult(Workbook workbook, Class<T> clazz, boolean failFast)

// Parse by sheet name
Map<String, List<T>> parseWorkbookBySheet(Workbook workbook, Class<T> clazz, boolean includeEmpty)

// Parse section-based models
List<T> parseSheetSection(Sheet sheet, Class<T> clazz)

ExcelExporter

// Fill template rows
void fillTableRows(Sheet sheet, int startRow, List<T> dataList, Class<T> clazz)

// Fill section in template
void fillSection(Sheet sheet, String sectionKeyword, String keywordInCol, 
                 List<T> dataList, Class<T> clazz)

// Clone sheet with styles
Sheet copySheetWithStyles(Sheet sourceSheet, Workbook targetWorkbook, String newSheetName)

ExcelValidator

// Validate single object
ValidationResult validate(Object obj, int rowNum, Class<?> clazz)

// Validate list (checks unique constraints)
List<ValidationResult> validateList(List<T> list, Class<T> clazz)

ExcelTypeConverter

// Convert cell value to target type
<T> T convertValue(Object value, Class<T> targetType, String dateFormat)

Notes & Recommendations

Current Limitations

  1. Field ordering: Binding relies on getDeclaredFields() order. To ensure stability across compilers and refactoring:
    • Add order() property to @ExcelColumn
    • Or document field declaration order requirement clearly
    • Add tests to verify deterministic mapping
  2. Error types: Currently uses generic RuntimeException. Consider adding:
    • ExcelParseException with sheet/row/col context
    • ValidationException with collected errors
  3. Performance: Reflection happens per parse. prepareBindings now uses cache, but consider:
    • Defensive copies if bindings are mutated
    • Benchmark for large datasets
  1. Validation:
    • Add batch validation mode (collect all errors before failing)
    • Support custom validator hooks
    • Add regex pattern validation for strings
  2. Export:
    • Add formula preservation option
    • Support conditional formatting copy
    • Add row height/column width preservation
  3. Testing:
    • Add integration tests for formulas, merged cells
    • Test edge cases: blank rows, skipRowByBlank, unique constraints
    • Test enum conversion, numeric ranges, date formats
  4. Documentation:
    • Add JavaDoc for all public methods
    • Add more inline examples in README

Usage Examples

See spring-tools-simples README for:

This site is open source. Improve this page.