excel工具

撰寫常用utils，整合共用元件，減少開發速度

提供工具

• 讀取

  • 客製化轉換(資料結構複雜)

  • Map資料轉換(資料結構單純，請教像是清單)

  • 表頭與表身轉換(有客製化表頭，搭配清單系列)

• 寫檔案

  • 客製化產製

 <groupId>io.github.h8000572003</groupId>
 <artifactId>excel-commons</artifactId>
 <version>1.0.0</version>

讀取-客製化轉換

try (InputStream resourceAsStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("general.xlsx")) {
    final ExcelBook excelBook = ExcelBook.reader(resourceAsStream);
    GeneralDTO reader = excelBook.reader(this::reader);
    assertThat( reader.getITems().size()).isEqualTo(17);
}
private GeneralDTO reader(IExcelBook workbook) {
final IExcelSheet sheet = workbook.getSheet("work1");
List<GeneralITemDTO> generalITemDTOS = sheet.mapList((row, index) -> {
    final GeneralITemDTO iTemDTO = new GeneralITemDTO(
            new String[]{
                    row.getCell(0).getString(),
                    row.getCell(1).getString(),
                    row.getCell(2).getString(),
                    row.getCell(3).getString(),
                    row.getCell(4).getString(),
                    row.getCell(5).getString(),
                    row.getCell(6).getString(),
            }
    );
    return iTemDTO;

});
GeneralDTO generalDTO = new GeneralDTO();
generalDTO.setITems(generalITemDTOS);
return generalDTO;
}

Map資料轉換(資料結構單純，請教像是清單)

try (InputStream resourceAsStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("general2.xls")) {
           final ExcelMapReaders excelMapReaders = new ExcelMapReaders("work1", "A", "B", "C", "D", "E", "F", "G");
           final ExcelBook excelBook = ExcelBook.reader(resourceAsStream);
           List<Map<String, Object>> reader = excelBook.reader(excelMapReaders);
           assertThat( reader.size()).isEqualTo(17);
}

表頭與表身轉換

try (InputStream resourceAsStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("customer.xls")) {

 final ExcelPointReaders excelPointReaders = new ExcelPointReaders("work1");
 excelPointReaders.addSpace(0, 1,"yyy");
 excelPointReaders.addSpace(0, 3,"mon");


 excelPointReaders.addExtract(0, "Data1");
 excelPointReaders.addExtract(1, "Data2");
 excelPointReaders.addExtract(2, "Data3");
 excelPointReaders.addExtract(4, "Data4");
 excelPointReaders.addExtract(5, "Data5");

 final ExcelBook excelBook = ExcelBook.reader(resourceAsStream);
 final List<ExcelPointValues> reader = excelBook.reader(excelPointReaders);

 assertThat( reader.size()).isEqualTo(4);

}