日常开发中，难免遇到需要导入导出的业务场景，特别是一些一对多的场景，如一个用户对应多个职位。如果直接操作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 OneToManyVO {

    @ExcelField(title = "姓名")
    private String username;

    @ExcelField(title = "手机号码")
    private String tel;

    @ExcelField(title = "年龄")
    private Integer age;

    @ExcelField(title = "出生日期")
    private LocalDate birthDay;

    @ExcelField(title = "分数")
    private BigDecimal score;

    @ExcelField(title = "职务", cellHandler = ListStringStringSplitHandler.class)
    private List<String> positionList;
    
}

需要在 @ExcelField 注解上显示指定对应的 cellHandler。 ListStringStringSplitHandler 是一个组件提供的一对多转化CellHandler.
更复杂的一对多转化，读者参可以此为参照自定义实现。

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

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

    @Resource
    private ZouzhiyExcelFactory zouzhiyExcelFactory;

    @GetMapping("list/export")
    public void exportList(HttpServletResponse response) {
        List<OneToManyVO> voList = this.listVo();

        response.addHeader("Content-Disposition"
                , "attachment; filename*=utf-8''" + URLEncoder.encode("列表信息（一对多）.xlsx", StandardCharsets.UTF_8.name()));
        zouzhiyExcelFactory
                .write(response.getOutputStream())
                .sheet()
                .title("列表信息（一对多）")
                .titleRowStartIndex(0)
                .dataRowStartIndex(2)
                .write(voList, OneToManyVO.class);
    }

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

    private List<OneToManyVO> listVo() {
        List<OneToManyVO> voList = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            OneToManyVO vo = OneToManyVO.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()))
                    .positionList(this.listPosition(i))
                    .build();
            voList.add(vo);
        }
        return voList;
    }

    private List<String> listPosition(int sno) {
        int size = random.nextInt(5);
        List<String> positionList = new ArrayList<>();
        for (int i = 0; i < size; i++) {
            positionList.add("position-" + sno + "-" + i);
        }
        return positionList;
    }
}

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

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

简单导入

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

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

    @Resource
    private ZouzhiyExcelFactory zouzhiyExcelFactory;

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

        return zouzhiyExcelFactory
                .read(file.getInputStream())
                .sheet()
                .dataRowStartIndex(2)
                .read(OneToManyVO.class);
    }

}

通过 Idea Client 测试

###
POST http://localhost:8080/one-to-many/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": "1414943745339029698",
    "age": 10,
    "birthDay": "2022-07-23",
    "score": 0.142308879825304,
    "positionList": []
  },
  {
    "username": "姓名-1",
    "tel": "5632446470106653313",
    "age": 11,
    "birthDay": "2022-07-15",
    "score": 0.84110198658769,
    "positionList": [
      "position-1-0",
      "position-1-1",
      "position-1-2"
    ]
  },
  {
    "username": "姓名-2",
    "tel": "8282406730548756663",
    "age": 12,
    "birthDay": "2022-07-07",
    "score": 0.43338285841665,
    "positionList": []
  },
  {
    "username": "姓名-3",
    "tel": "3561461189310264197",
    "age": 13,
    "birthDay": "2022-07-08",
    "score": 0.165114416502293,
    "positionList": [
      "position-3-0",
      "position-3-1",
      "position-3-2",
      "position-3-3"
    ]
  },
  {
    "username": "姓名-4",
    "tel": "3929156340294117712",
    "age": 14,
    "birthDay": "2022-07-15",
    "score": 0.974617562318761,
    "positionList": [
      "position-4-0",
      "position-4-1",
      "position-4-2"
    ]
  }
]

简单的一对多导入功能也完成了，赶紧提交代码下班吧！

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