日常开发中,难免遇到需要导入导出的业务场景,如果直接通过poi提供的接口,代码编写繁琐不说,而且还容易出错。 今天在这里给大家推荐一款非常好用的 Excel 导入导出工具工具:zouzhiy-excel。希望对大家有所帮助。
zouzhiy-excel 简介
zouzhiy-excel 是一款 Excel 导入导出的轻量级工具。对 POI 的接口做了一层封装,使导入导出更加简便快捷。
zouzhiy-excel 优势
zouzhiy-excel 支持一对多导入,简单的自定义即可实现一条数据写入多行,一个属性对象写入多列的功能。
Spring 环境下集成
<dependency>
<groupId>io.github.zouzhiy</groupId>
<artifactId>zouzhiy-excel-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>
开始使用
接下来介绍下 zouzhiy-excel 的使用,以用户信息的导入导出为例,实现简单的导入导出功能
简单导出
- 首先创建一个用户对象
@Data
@Builder
@ExcelClass
@NoArgsConstructor
@AllArgsConstructor
public class UserVO {
@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;
}
-
@ExcelClass 、 @ExcelField 为 zouzhiy-excel 的核心注解,根据配置可生成配置信息,
然后通过配置信息将需要的数据按照需求写入到 Excel 表格当中。 -
接下来我们在 Controller 中添加一个接口,用于导出用户列表Excel,具体代码如下;
@RestController
@RequestMapping("user")
public class UserContrller {
@Resource
private ZouzhiyExcelFactory zouzhiyExcelFactory;
@GetMapping("list/export")
public void exportUserList(HttpServletResponse response) {
List<UserVO> userVOList = this.listUser();
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(userVOList, UserVO.class);
}
private List<UserVO> listUser() {
Random random = new Random(System.currentTimeMillis());
List<UserVO> userVOList = new ArrayList<>();
for (int i = 0; i < 5; i++) {
UserVO userVO = UserVO.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()))
.build();
userVOList.add(userVO);
}
return userVOList;
}
}
- 通过浏览器访问:http://localhost:8080/user/list/export 。下载附件。导出结果如下:
简单导入
接下来我们在 Controller 中添加一个接口,用于导入用户列表,具体代码如下;
@RestController
@RequestMapping("user")
public class UserImportContrller {
@Resource
private ZouzhiyExcelFactory zouzhiyExcelFactory;
@PostMapping("list/import")
public List<UserVO> importUserList(@RequestPart MultipartFile file) throws IOException {
List<UserVO> userVoList = zouzhiyExcelFactory
.read(file.getInputStream())
.sheet()
.dataRowStartIndex(2)
.read(UserVO.class);
return userVoList;
}
}
通过 Idea Client 测试
POST http://localhost:8080/user/list/import
Content-Type: multipart/form-data; boundary=WebAppBoundary
--WebAppBoundary
Content-Disposition: form-data; name="multipartFile"; filename="export.xls"
Content-Type: multipart/form-data
< ./excel/用户信息.xlsx
--WebAppBoundary--
返回结果
[
{
"username": "姓名-0",
"tel": "5186651702952533855",
"age": 10,
"birthDay": "2022-07-24",
"score": 0.2121625571592407
},
{
"username": "姓名-1",
"tel": "8192023157348938686",
"age": 11,
"birthDay": "2022-07-04",
"score": 0.2055268632764633
},
{
"username": "姓名-2",
"tel": "3918436485538028977",
"age": 12,
"birthDay": "2022-07-09",
"score": 0.08979637102439264
},
{
"username": "姓名-3",
"tel": "4451525995612671225",
"age": 13,
"birthDay": "2022-07-17",
"score": 0.7695874250940976
},
{
"username": "姓名-4",
"tel": "3487905218747380965",
"age": 14,
"birthDay": "2022-07-09",
"score": 0.7823811696796359
}
]
以上就是通过 zouzhiy-excel 实现简单的导入导出功能。希望能给到你实际的帮助!
项目源码地址: https://github.com/zouzhiy
国内镜像地址: https://gitee.com/zouzhiy/zouzhiy-excel