parent-spring-tools

spring-tools-simples

Demo and example application for spring-excel-mapper with two recommended integration patterns.

Overview

This module demonstrates how to use spring-excel-mapper in real Spring Boot applications. It includes:

• Pattern 1: Direct API usage — full control for complex scenarios
• Pattern 2: Template method pattern — recommended for most use cases
• Example services, controllers, and MapStruct integration
• Sample Excel templates

For detailed API reference and design documentation, see spring-excel-mapper README.

---

Usage Patterns

Pattern 1: Direct API Usage (Full Control)

Use ExcelMapper API directly when you need maximum control over each step or have complex business logic during import.

Step 1: Define Excel Model and DTO

@Data
@ExcelSheet
@ExcelRow(startRowIndex = 1)
public class StudentExcelModel {
    @ExcelColumn(col = "A", required = true)
    private String name;
    
    @ExcelColumn(col = "B", regex = "^[\\w-\\.]+@([\\w-]+\\.)+[\\w-]{2,4}$")
    private String email;
    
    @ExcelColumn(col = "C", min = 18, max = 100)
    private Integer age;
}

@Data
public class StudentDTO {
    private String name;
    private String email;
    private Integer age;
}

Step 2: Create MapStruct Mapper

@Mapper(componentModel = "spring")
public interface StudentMapper {
    StudentMapper INSTANCE = Mappers.getMapper(StudentMapper.class);
    
    StudentDTO toDTO(StudentExcelModel model);
    List<StudentDTO> toDTOs(List<StudentExcelModel> models);
}

Step 3: Implement Service with Direct API

@Service
@Slf4j
public class StudentExcelServiceManual {
    
    public ImportResult<StudentDTO> importStudents(MultipartFile file) throws Exception {
        try (InputStream is = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(is)) {
            
            // Parse Excel models
            ImportResult<StudentExcelModel> parseResult = 
                ExcelMapper.parseWorkbookWithResult(workbook, StudentExcelModel.class, false);
            
            if (!parseResult.isSuccess()) {
                // Handle errors
                ImportResult<StudentDTO> result = new ImportResult<>();
                result.setErrors(parseResult.getErrors());
                return result;
            }
            
            // Convert to DTOs using MapStruct
            List<StudentDTO> dtoList = StudentMapper.INSTANCE.toDTOs(parseResult.getSuccessList());
            
            // Optional: Add business logic here (e.g., database save)
            
            // Return result
            ImportResult<StudentDTO> result = new ImportResult<>();
            result.setSuccessList(dtoList);
            result.setTotalRows(dtoList.size());
            return result;
        }
    }
}

Step 4: Use in Controller

@RestController
@RequestMapping("/api/students")
public class StudentController {
    
    @Autowired
    private StudentExcelServiceManual excelService;
    
    @PostMapping("/import")
    public ResponseEntity<?> importStudents(@RequestParam("file") MultipartFile file) throws Exception {
        ImportResult<StudentDTO> result = excelService.importStudents(file);
        
        if (result.isSuccess()) {
            return ResponseEntity.ok(Map.of(
                "success", true,
                "data", result.getSuccessList(),
                "count", result.getTotalRows()
            ));
        } else {
            return ResponseEntity.badRequest().body(Map.of(
                "success", false,
                "errors", result.getErrors()
            ));
        }
    }
}

Pros:

• ✅ Full control over each step
• ✅ Easy to debug and customize
• ✅ Flexible for complex business logic

Cons:

• ❌ More boilerplate code
• ❌ Repetitive error handling
• ❌ Need to manage workbook lifecycle

---

Pattern 2: Template Method (Recommended)

Use ExcelImportService<ExcelModel, DTO> abstract base class for cleaner, simpler code with unified workflow. Recommended for most use cases.

Step 1: Define Excel Model and DTO (same as Pattern 1)

@Data
@ExcelSheet
@ExcelRow(startRowIndex = 1)
public class StudentExcelModel {
    @ExcelColumn(col = "A", required = true)
    private String name;
    
    @ExcelColumn(col = "B", regex = "^[\\w-\\.]+@([\\w-]+\\.)+[\\w-]{2,4}$")
    private String email;
    
    @ExcelColumn(col = "C", min = 18, max = 100)
    private Integer age;
}

@Data
public class StudentDTO {
    private String name;
    private String email;
    private Integer age;
}

Step 2: Create MapStruct Mapper (same as Pattern 1)

@Mapper(componentModel = "spring")
public interface StudentMapper {
    StudentMapper INSTANCE = Mappers.getMapper(StudentMapper.class);
    
    StudentDTO toDTO(StudentExcelModel model);
    List<StudentDTO> toDTOs(List<StudentExcelModel> models);
}

Step 3: Extend ExcelImportService (minimal implementation)

@Service
@Slf4j
public class StudentExcelImportService extends ExcelImportService<StudentExcelModel, StudentDTO> {
    
    @Override
    protected Class<StudentExcelModel> getExcelModelClass() {
        return StudentExcelModel.class;
    }
    
    @Override
    protected StudentDTO convertToDTO(StudentExcelModel model) {
        return StudentMapper.INSTANCE.toDTO(model);
    }
    
    @Override
    protected List<StudentDTO> convertToDTOList(List<StudentExcelModel> models) {
        return StudentMapper.INSTANCE.toDTOs(models);
    }
    
    // Optional lifecycle hooks:
    
    @Override
    protected void beforeImport(Workbook workbook) {
        log.info("Starting student import, sheets: {}", workbook.getNumberOfSheets());
    }
    
    @Override
    protected void afterImport(List<StudentDTO> dtoList) {
        log.info("Import completed, {} records imported", dtoList.size());
        // Example: save to database, send notifications, etc.
    }
    
    @Override
    protected void onImportError(Exception e) {
        log.error("Import failed", e);
        // Example: send alert, rollback transaction, etc.
    }
}

Step 4: Use in Controller (simplified)

@RestController
@RequestMapping("/api/students")
public class StudentController {
    
    @Autowired
    private StudentExcelImportService importService;
    
    @PostMapping("/import")
    public ResponseEntity<?> importStudents(@RequestParam("file") MultipartFile file) throws Exception {
        ImportResult<StudentDTO> result = importService.importExcel(file.getInputStream(), false);
        
        if (result.isSuccess()) {
            return ResponseEntity.ok(Map.of(
                "success", true,
                "data", result.getSuccessList(),
                "count", result.getTotalRows()
            ));
        } else {
            return ResponseEntity.badRequest().body(Map.of(
                "success", false,
                "errors", result.getErrors()
            ));
        }
    }
}

Pros:

• ✅ Minimal boilerplate (only 3 required methods)
• ✅ Unified workflow and error handling
• ✅ Optional lifecycle hooks for extension
• ✅ Clean, type-safe code
• ✅ Automatic workbook management
• ✅ Recommended for new projects

Cons:

• ❌ Less flexible for very complex scenarios (use Pattern 1 in those cases)

---

Common Usage Snippets

Parse Workbook into List

Workbook wb = WorkbookFactory.create(inputStream);
List<MyModel> list = ExcelMapper.parseWorkbook(wb, MyModel.class);

Parse with Error Collection

ImportResult<MyModel> result = 
    ExcelMapper.parseWorkbookWithResult(workbook, MyModel.class, false);
    
if (result.isSuccess()) {
    List<MyModel> data = result.getSuccessList();
} else {
    List<ValidationResult> errors = result.getErrors();
}

Parse by Sheet Name

Map<String, List<MyModel>> perSheet = 
    ExcelMapper.parseWorkbookBySheet(wb, MyModel.class, true);

Export Data to Template

Sheet sheet = workbook.getSheet("Items");
ExcelExporter.fillTableRows(sheet, startRow, dataList, MyModel.class);

Section-based Parsing

@ExcelSheet
@ExcelSection(
    sectionKeyword = "Student List",
    keywordInCol = "A",
    startRowIndex = 1,
    stopKeyword = "End"
)
public class StudentSection {
    @ExcelColumn(col = "A")
    private String name;
}

List<StudentSection> sections = ExcelMapper.parseSheetSection(sheet, StudentSection.class);

---

Project Structure

spring-tools-simples/
├── src/main/java/org/springtools/excel/
│   ├── controller/          # REST controllers
│   ├── service/             # Example services
│   │   ├── ExcelImportService.java              # Base template class
│   │   ├── ExcelServiceLegacy.java              # Pattern 1 examples
│   │   └── impl/
│   │       ├── ClassImportService.java          # Pattern 2 example
│   │       ├── DepartmentImportService.java     # Pattern 2 example
│   │       ├── StudentImportService.java        # Pattern 2 example
│   │       └── ExcelMapperServiceImpl.java      # Combined import/export
│   ├── assembler/           # MapStruct mappers
│   ├── dto/                 # DTOs
│   └── excelmodel/          # Excel model classes
└── src/main/resources/
    └── templates/           # Sample Excel templates

---

Running Examples

Build & Run

# Build the project
mvn clean package

# Run the Spring Boot application
mvn -pl spring-tools-simples spring-boot:run

# Or run the JAR
java -jar spring-tools-simples/target/spring-tools-simples-1.0.0-SNAPSHOT.jar

Test Endpoints

After starting the application, you can test the endpoints:

# Import students (Pattern 2)
curl -X POST http://localhost:8080/api/students/import \
  -F "file=@students.xlsx"

# Import departments
curl -X POST http://localhost:8080/api/departments/import \
  -F "file=@departments.xlsx"

# Export example
curl -X GET http://localhost:8080/api/export/template \
  -o output.xlsx

---

Advanced Examples

Multi-section Import

Import multiple sections from a single Excel file:

@Service
public class ExcelMapperServiceImpl {
    
    public CombinedResult importAll(MultipartFile file) throws Exception {
        try (InputStream is = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(is)) {
            
            Sheet sheet = workbook.getSheetAt(0);
            
            // Parse different sections
            List<DepartmentExcelModel> departments = 
                ExcelMapper.parseSheetSection(sheet, DepartmentExcelModel.class);
            
            List<ClassExcelModel> classes = 
                ExcelMapper.parseSheetSection(sheet, ClassExcelModel.class);
            
            List<StudentExcelModel> students = 
                ExcelMapper.parseSheetSection(sheet, StudentExcelModel.class);
            
            // Convert and combine
            CombinedResult result = new CombinedResult();
            result.setDepartments(convertDepartments(departments));
            result.setClasses(convertClasses(classes));
            result.setStudents(convertStudents(students));
            
            return result;
        }
    }
}

Template-based Export

Export data using Excel templates (preserves formatting):

@Service
public class ExportService {
    
    public Workbook exportToTemplate(List<StudentDTO> students) throws Exception {
        // Load template
        try (InputStream template = getClass().getResourceAsStream("/templates/student_template.xlsx")) {
            Workbook workbook = WorkbookFactory.create(template);
            Sheet sheet = workbook.getSheet("Students");
            
            // Convert DTOs to Excel models
            List<StudentExcelModel> models = StudentMapper.INSTANCE.toExcelModels(students);
            
            // Fill template (starts at row 1, preserves header at row 0)
            ExcelExporter.fillTableRows(sheet, 1, models, StudentExcelModel.class);
            
            return workbook;
        }
    }
}

---

Related Documentation

• API & Design: spring-excel-mapper README
• 中文文档: spring-excel-mapper README-zh
• Parent Project: Parent README

---

Tips & Best Practices

1. Choose Pattern 2 unless you have complex business logic that requires Pattern 1’s flexibility
2. Use MapStruct for model conversion — it’s type-safe and generates efficient code
3. Handle errors gracefully — use parseWorkbookWithResult(..., failFast=false) to collect all errors
4. Validate business rules in afterImport() hook, not during parsing
5. Use templates for export to preserve Excel formatting and formulas
6. Test edge cases — blank rows, merged cells, formulas, large datasets
7. Add logging in lifecycle hooks for debugging and monitoring
8. Use expression validation — @ExcelColumn supports regex, min, max and other validation options

This site is open source. Improve this page.