excel-plus Java Library

excel-plus uses annotations to map Java class fields to Excel columns. This allows you to define how data is read from or written to an Excel file directly within your domain models.

Key annotations:

• @ExcelColumn: Used to define the mapping.
  • title: The header name in the Excel file.
  • index: The zero-based column index.
  • datePattern: A custom date format string (e.g., "yyyy-MM-dd") for date/time fields.

@ExcelColumn(title = "发卡日期", index = 14, datePattern = "M/d/yyyy HH:mm")
private Date sendCardTime;

The following classes form the core API of the library:

• ExcelPlus: The primary entry point for reading and exporting Excel documents.
• Converter: The interface used to implement custom data type transformations.
• ReaderResult: A container for the results of an Excel read operation, which includes the list of objects and any validation error messages.
• Exporter: A configuration object used to define export settings such as styles, templates, and column configurations.

To read or write data using a Java model, use the @ExcelColumn annotation to map class fields to specific Excel columns. You can specify the column index and date patterns for date fields.

Key attributes:

• index: The zero-based column index.
• datePattern: The pattern used for date formatting.

public class Sample {

    @ExcelColumn(index = 0, datePattern = "M/d/yy")
    private LocalDate date;

    @ExcelColumn(index = 1)
    private String location;

    @ExcelColumn(index = 4)
    private int proportion;

    @ExcelColumn(index = 5)
    private double ss;

    @ExcelColumn(index = 6)
    private BigDecimal amount;
    
    // getter setter omitted
}

You can filter the rows being read by converting the reader output to a Stream using .asStream(). This allows you to use standard Java Stream API filtering before collecting the results.

List<Sample> samples = Reader.create(Sample.class)
                .from(new File(classPath() + "/SampleData.xlsx"))
                .sheet("SalesOrders")
                .startRow(1)
                .asStream()
                .filter(sample -> sample.getAmount().intValue() > 1000)
                .collect(toList());

Use ExcelPlus.export() with a list of annotated model objects, then call writeAsFile() to save the result.

ExcelPlus excelPlus = new ExcelPlus();
List<CardSecret> cardSecrets = new ArrayList<>();
// ... add data to list

excelPlus.export(cardSecrets).writeAsFile(new File("卡密列表.xlsx"));

Use Reader.create() to specify the file, and excelPlus.read() to map the rows to your model class. Use .asList() to retrieve the final list.

Reader reader = Reader.create().excelFile(new File("卡密列表.xlsx"));
List<CardSecret> cardList = excelPlus.read(CardSecret.class, reader).asList();

To trigger a file download directly in a web browser using a Servlet-based application, use ResponseWrapper.createXLSX (or other formats) within the .to() method of the Writer.

Writer.create()
      ... 
      .to(ResponseWrapper.createXLSX(servletResponse, "xxx表格.xlsx"))

You can chain a .filter() method after the read() call to exclude specific rows based on a predicate.

excelPlus.read(CardSecret.class, reader)
         .filter(cardSecret -> !cardSecret.getSecret().isEmpty())
         .asList();

To customize styles (font, color, alignment), use Exporter.create() and provide a headerStyle lambda that accepts an Apache POI Workbook. This allows you to manipulate the CellStyle directly.

List<CardSecret> cardSecrets = this.buildCardSecrets();

Exporter<CardSecret> exporter = Exporter.create(cardSecrets);
exporter.headerStyle(workbook -> {
    CellStyle headerStyle = workbook.createCellStyle();
    headerStyle.setAlignment(HorizontalAlignment.LEFT);
    headerStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
    headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    Font headerFont = workbook.createFont();
    headerFont.setFontHeightInPoints((short) 12);
    headerFont.setBold(true);
    headerStyle.setFont(headerFont);
    return headerStyle;
});

excelPlus.export(exporter).writeAsFile(new File("卡密列表.xlsx"));

Use the .valid() method to perform row-level validation. If a row fails validation, it is excluded from the successful list, and error messages are captured in a ReaderResult object.

ReaderResult<CardSecret> result = excelPlus.read(new File("卡密列表.xls"), CardSecret.class)
         .valid((index, cardSecret) -> {
            if(!cardSecret.getUsed()){
                return ValidRow.ok();
            }
            return ValidRow.fail("已经被使用");
         });

if (!result.isValid()) {
    result.errors().forEach(System.out::println);
} else {
    System.out.println(result.asList().size());
}