日常开发中，难免遇到需要导入导出的业务场景，特别是一些一对多的场景，如一个用户拥有多个科目，每个科目的成绩不一样。每个人的科目数不相等。  
如果直接操作POI的接口实现，代码繁琐且容易出错。  
今天在这里给大家推荐一款非常好用的 Excel 导入导出工具工具： zouzhiy-excel 。可以实现复杂的一对多导入导出功能，希望能给予到您帮助！

zouzhiy-excel 简介

zouzhiy-excel 是一款 Excel 导入导出的轻量级工具。对 POI 的接口做了一层封装，使导入导出更加简便快捷。

Spring 环境下集成

<dependency>
    <groupId>io.github.zouzhiy</groupId>
    <artifactId>zouzhiy-excel-boot-starter</artifactId>
    <version>1.1.1</version>
</dependency>

开始使用

今天来以用户对应多个课程的成绩为例，介绍一下一对多导入导出的实现。

复杂导出

• 首先创建一对多的数据对象

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@ExcelClass
public class OneToManyCustomVO {

    private String username;

    private String tel;

    private Integer age;

    private LocalDate birthDay;

    private BigDecimal score;

    @ExcelField(colspan = 2, cellHandler = ListOneToManyItemStringSplitHandler.class)
    private List<OneToManyItemVO> itemList;

}

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class OneToManyItemVO {

    private String itemName;

    private BigDecimal itemScore;

}

此处涉及到复杂表头，可选的实现方式是代码自定义实现，或者模板导出。代码自定义实现，我们只需要实现 RowHeadWrite 接口即可。
为了简便，本文示例我们选择模板方式。
模板表头如下：

导出模板

• 下一步我们需要实现自定义的CellHandler

@Component
public class ListOneToManyItemStringSplitHandler extends AbstractListSplitHandler<OneToManyItemVO> {

    @Override
    public ExcelType getExcelType() {
        return ExcelType.STRING;
    }
}

@Component
public class OneToManyItemStringCellHandler implements CellHandler<OneToManyItemVO> {

    @Override
    public Class<OneToManyItemVO> getJavaType() {
        return OneToManyItemVO.class;
    }

    @Override
    public ExcelType getExcelType() {
        return ExcelType.STRING;
    }

    @Override
    public OneToManyItemVO read(SheetContext sheetContext, ExcelFieldConfig excelFieldConfig, CellResultSet cellResultSet) {
        if (cellResultSet == null) {
            return null;
        }

        if (cellResultSet.isNone()) {
            return null;
        }
        List<CellResult> cellResultList = cellResultSet.getCellResultListList().get(0);
        CellResult cellResultFirst = cellResultList.size() > 0 ? cellResultList.get(0) : CellResult.none();
        CellResult cellResultSecond = cellResultList.size() > 1 ? cellResultList.get(1) : CellResult.none();

        CellHandler<String> stringStringHandler = sheetContext.getConfiguration().getCellHandlerRegistry().getCellHandler(StringStringHandler.class);
        String itemName = stringStringHandler.read(sheetContext, excelFieldConfig, CellResultSet.firstCellResult(cellResultFirst));

        CellHandler<BigDecimal> bigDecimalCellHandler = sheetContext.getConfiguration().getCellHandlerRegistry().getCellHandler(BigDecimalNumberHandler.class);
        BigDecimal itemScore = bigDecimalCellHandler.read(sheetContext, excelFieldConfig, CellResultSet.firstCellResult(cellResultSecond));

        return OneToManyItemVO.builder().itemName(itemName).itemScore(itemScore).build();
    }

    @Override
    public void write(RowContext rowContext, Integer columnIndex, ExcelFieldConfig excelFieldConfig, OneToManyItemVO value) {
        if (value == null) {
            return;
        }
        Row row = rowContext.getRowList().get(0);
        Cell cellFirst = row.createCell(columnIndex);
        Cell cellSecond = row.createCell(columnIndex + 1);

        if (value.getItemName() != null) {
            cellFirst.setCellValue(value.getItemName());
        }
        if (value.getItemScore() != null) {
            cellSecond.setCellValue(value.getItemScore().doubleValue());
        }


        SheetContext sheetContext = rowContext.getSheetContext();
        CellStyle cellStyle = sheetContext.getDataCellStyle(excelFieldConfig, this.getDefaultExcelFormat());
        cellFirst.setCellStyle(cellStyle);
        cellSecond.setCellStyle(cellStyle);

        int rowspan = rowContext.getRowspan();
        int rowIndex = row.getRowNum();
        RegionUtils.addMergedRegionIfPresent(sheetContext, cellStyle, rowIndex, rowIndex + rowspan - 1, columnIndex, columnIndex);
        RegionUtils.addMergedRegionIfPresent(sheetContext, cellStyle, rowIndex, rowIndex + rowspan - 1, columnIndex + 1, columnIndex + 1);
    }
}

ListOneToManyItemStringSplitHandler 提供了多行写入的逻辑。
OneToManyItemStringCellHandler 提供了每一行如何写入的逻辑。

• 接下来我们在 Controller 中添加一个接口，用于导出列表Excel，具体代码如下：

@RestController
@RequestMapping("one-to-many-custom")
public class OneToManyCustomExportController {

    @Resource
    private ZouzhiyExcelFactory zouzhiyExcelFactory;

    @GetMapping("list/export")
    public void exportList(HttpServletResponse response) {

        List<OneToManyCustomVO> voList = this.listVo();

        String exportTemplateFilePath = "template/export/列表信息（一对多自定义）-template.xlsx";
        InputStream exportTemplateInputStream = this.getClass().getClassLoader().getResourceAsStream(exportTemplateFilePath);


        response.addHeader("Content-Disposition"
                , "attachment; filename*=utf-8''" + URLEncoder.encode("列表信息（一对多自定义模板导出）.xlsx", StandardCharsets.UTF_8.name()));
        zouzhiyExcelFactory
                .write(response.getOutputStream())
                .template(exportTemplateInputStream)
                .sheet()
                .title("列表信息（一对多自定义模板导出）")
                .titleRowStartIndex(0)
                .headRowStartIndex(-1)
                .dataRowStartIndex(3)
                .write(voList, OneToManyCustomVO.class);
    }

    private final Random random = new Random(System.currentTimeMillis());

    private List<OneToManyCustomVO> listVo() {
        List<OneToManyCustomVO> voList = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            OneToManyCustomVO vo = OneToManyCustomVO.builder()
                    .username("姓名-" + i)
                    .tel(Math.abs(random.nextLong()) + "")
                    .age(10 + i)
                    .birthDay(LocalDate.of(2022, 7, random.nextInt(29) + 1))
                    .score(BigDecimal.valueOf(random.nextDouble()))
                    .itemList(this.listItem(i))
                    .build();
            voList.add(vo);
        }
        return voList;
    }

    private List<OneToManyItemVO> listItem(int sno) {
        int size = random.nextInt(5) + 2;
        List<OneToManyItemVO> itemList = new ArrayList<>();
        for (int i = 0; i < size; i++) {
            OneToManyItemVO item = OneToManyItemVO.builder()
                    .itemName("itemName-" + sno + "-" + i)
                    .itemScore(BigDecimal.valueOf(random.nextDouble()))
                    .build();
            itemList.add(item);
        }
        return itemList;
    }
}

• 通过浏览器访问： http://localhost:8080/one-to-many/list/export 。下载附件。导出结果如下：
  
  导出结果

复杂的自定义一对多导出就大功告成了！

复杂导入

以上面的导出结果为导入数据。在 Controller 中添加一个接口，用于导入列表数据，具体代码如下；

@RestController
@RequestMapping("one-to-many-custom")
public class OneToManyCustomImportController {

    @Resource
    private ZouzhiyExcelFactory zouzhiyExcelFactory;

    @PostMapping("list/import")
    public List<OneToManyCustomVO> exportList(@RequestPart MultipartFile file) {

        return zouzhiyExcelFactory
                .read(file.getInputStream())
                .sheet()
                .dataRowStartIndex(3)
                .read(OneToManyCustomVO.class);
    }

}

通过 Idea Client 测试

###
POST http://localhost:8080/one-to-many-custom/list/import
Content-Type: multipart/form-data; boundary=WebAppBoundary

--WebAppBoundary
Content-Disposition: form-data; name="file"; filename="export.xls"
Content-Type: multipart/form-data

< ./excel/列表信息（一对多自定义）.xlsx
--WebAppBoundary--

返回结果

[
  {
    "username": "姓名-0",
    "tel": "5654627499990959267",
    "age": 10,
    "birthDay": "2022-07-10",
    "score": 0.04755402523030161,
    "itemList": [
      {
        "itemName": "itemName-0-0",
        "itemScore": 0.9953786943316137
      },
      {
        "itemName": "itemName-0-1",
        "itemScore": 0.4095088855641811
      }
    ]
  },
  {
    "username": "姓名-1",
    "tel": "8948312601319960853",
    "age": 11,
    "birthDay": "2022-07-27",
    "score": 0.6489738152199859,
    "itemList": [
      {
        "itemName": "itemName-1-0",
        "itemScore": 0.7101557536983378
      },
      {
        "itemName": "itemName-1-1",
        "itemScore": 0.42630670224830025
      },
      {
        "itemName": "itemName-1-2",
        "itemScore": 0.3382717945568745
      },
      {
        "itemName": "itemName-1-3",
        "itemScore": 0.8411572536654753
      },
      {
        "itemName": "itemName-1-4",
        "itemScore": 0.1290493177434614
      }
    ]
  },
  {
    "username": "姓名-2",
    "tel": "4747615337563131754",
    "age": 12,
    "birthDay": "2022-07-29",
    "score": 0.9290571054321214,
    "itemList": [
      {
        "itemName": "itemName-2-0",
        "itemScore": 0.6579683757025062
      },
      {
        "itemName": "itemName-2-1",
        "itemScore": 0.39454620044921995
      },
      {
        "itemName": "itemName-2-2",
        "itemScore": 0.694208890206394
      },
      {
        "itemName": "itemName-2-3",
        "itemScore": 0.41485814919392006
      }
    ]
  },
  {
    "username": "姓名-3",
    "tel": "4435022945680413682",
    "age": 13,
    "birthDay": "2022-07-25",
    "score": 0.4389382752214239,
    "itemList": [
      {
        "itemName": "itemName-3-0",
        "itemScore": 0.005098377743345539
      },
      {
        "itemName": "itemName-3-1",
        "itemScore": 0.8441212049334175
      }
    ]
  },
  {
    "username": "姓名-4",
    "tel": "2817604749347068982",
    "age": 14,
    "birthDay": "2022-07-01",
    "score": 0.1625863305590488,
    "itemList": [
      {
        "itemName": "itemName-4-0",
        "itemScore": 0.10979803675973188
      },
      {
        "itemName": "itemName-4-1",
        "itemScore": 0.5053788620400822
      }
    ]
  }
]

复杂的一对多导入功能也完成了，今天的工作就结束了，剩下的时间用来摸鱼了！

项目源码地址： https://github.com/zouzhiy
国内镜像地址： https://gitee.com/zouzhiy/zouzhiy-excel