赞
踩
目录
应对多人同时导出Excel导致的服务器崩溃入口:构建高效排队导出
EasyExcel 是一个基于 Java 的、快速、简洁、解决大文件内存溢出的 Excel 处理工具。它能让你在不用考虑性能、内存的等因素的情况下,快速完成 Excel 的读、写等功能。
EasyExcel文档地址:https://easyexcel.opensource.alibaba.com/
-
- <dependency>
- <groupId>com.alibaba</groupId>
- <artifactId>easyexcel</artifactId>
- <version>3.1.3</version>
- </dependency>
以导出用户信息为例,接下来手把手教大家如何使用EasyExcel实现导出功能!
在EasyExcel中,以面向对象思想来实现导入导出,无论是导入数据还是导出数据都可以想象成具体某个对象的集合,所以为了实现导出用户信息功能,首先创建一个用户对象UserDO实体类,用于封装用户信息:
- /**
- * 用户信息
- *
- * @author yun
- */
- @Data
- public class UserDO {
- @ExcelProperty("用户编号")
- @ColumnWidth(20)
- private Long id;
- @ExcelProperty("用户名")
- @ColumnWidth(20)
- private String username;
- @ExcelIgnore
- private String password;
- @ExcelProperty("昵称")
- @ColumnWidth(20)
- private String nickname;
- @ExcelProperty("生日")
- @ColumnWidth(20)
- @DateTimeFormat("yyyy-MM-dd")
- private Date birthday;
- @ExcelProperty("手机号")
- @ColumnWidth(20)
- private String phone;
- @ExcelProperty("身高(米)")
- @NumberFormat("#.##")
- @ColumnWidth(20)
- private Double height;
- @ExcelProperty(value = "性别", converter = GenderConverter.class)
- @ColumnWidth(10)
- private Integer gender;
- }
上面代码中类属性上使用了EasyExcel核心注解:
@ExcelProperty:核心注解,value属性可用来设置表头名称,converter属性可以用来设置类型转换器;
@ColumnWidth:用于设置表格列的宽度;
@DateTimeFormat:用于设置日期转换格式;
@NumberFormat:用于设置数字转换格式。
在EasyExcel中,如果想实现枚举类型到字符串类型转换(例如gender属性:1 -> 男,2 -> 女),需实现Converter接口来自定义转换器,下面为自定义GenderConverter性别转换器代码实现:
-
- /**
- * Excel 性别转换器
- *
- * @author yun
- */
- public class GenderConverter implements Converter<Integer> {
- @Override
- public Class<?> supportJavaTypeKey() {
- return Integer.class;
- }
- @Override
- public CellDataTypeEnum supportExcelTypeKey() {
- return CellDataTypeEnum.STRING;
- }
- @Override
- public Integer convertToJavaData(ReadConverterContext<?> context) {
- return GenderEnum.convert(context.getReadCellData().getStringValue()).getValue();
- }
- @Override
- public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) {
- return new WriteCellData<>(GenderEnum.convert(context.getValue()).getDescription());
- }
- }
- /**
- * 性别枚举
- *
- * @author yun
- */
- @Getter
- @AllArgsConstructor
- public enum GenderEnum {
- /**
- * 未知
- */
- UNKNOWN(0, "未知"),
- /**
- * 男性
- */
- MALE(1, "男性"),
- /**
- * 女性
- */
- FEMALE(2, "女性");
- private final Integer value;
- @JsonFormat
- private final String description;
- public static GenderEnum convert(Integer value) {
- return Stream.of(values())
- .filter(bean -> bean.value.equals(value))
- .findAny()
- .orElse(UNKNOWN);
- }
- public static GenderEnum convert(String description) {
- return Stream.of(values())
- .filter(bean -> bean.description.equals(description))
- .findAny()
- .orElse(UNKNOWN);
- }
- }
-
- /**
- * EasyExcel导入导出
- *
- * @author yun
- */
- @RestController
- @RequestMapping("/excel")
- public class ExcelController {
- @GetMapping("/export/user")
- public void exportUserExcel(HttpServletResponse response) {
- try {
- this.setExcelResponseProp(response, "用户列表");
- List<UserDO> userList = this.getUserList();
- EasyExcel.write(response.getOutputStream())
- .head(UserDO.class)
- .excelType(ExcelTypeEnum.XLSX)
- .sheet("用户列表")
- .doWrite(userList);
- } catch (IOException e) {
- throw new RuntimeException(e);
- }
- }
- /**
- * 设置响应结果
- *
- * @param response 响应结果对象
- * @param rawFileName 文件名
- * @throws UnsupportedEncodingException 不支持编码异常
- */
- private void setExcelResponseProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
- response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
- response.setCharacterEncoding("utf-8");
- String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\+", "%20");
- response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
- }
-
- /**
- * 读取用户列表数据
- *
- * @return 用户列表数据
- * @throws IOException IO异常
- */
- private List<UserDO> getUserList() throws IOException {
- ObjectMapper objectMapper = new ObjectMapper();
- ClassPathResource classPathResource = new ClassPathResource("mock/users.json");
- InputStream inputStream = classPathResource.getInputStream();
- return objectMapper.readValue(inputStream, new TypeReference<List<UserDO>>() {
- });
- }
- }
运行项目,通过 Postman 或者 Apifox 工具来进行接口测试
注意:在 Apifox 中访问接口后无法直接下载,需要点击返回结果中的下载图标才行,点击之后方可对Excel文件进行保存。
接口地址:http://localhost:8080/excel/export/user
由于 EasyPoi 支持嵌套对象导出,直接使用内置 @ExcelCollection 注解即可实现,遗憾的是 EasyExcel 不支持一对多导出,只能自行实现,通过此issues了解到,项目维护者建议通过自定义合并策略方式来实现一对多导出。
解决思路:只需把订单主键相同的列中需要合并的列给合并了,就可以实现这种一对多嵌套信息的导出
创建一个自定义注解,用于标记哪些属性需要合并单元格,哪个属性是主键:
-
- /**
- * 用于判断是否需要合并以及合并的主键
- *
- * @author yun
- */
- @Target({ElementType.FIELD})
- @Retention(RetentionPolicy.RUNTIME)
- @Documented
- public @interface ExcelMerge {
- /**
- * 是否合并单元格
- *
- * @return true || false
- */
- boolean merge() default true;
- /**
- * 是否为主键(即该字段相同的行合并)
- *
- * @return true || false
- */
- boolean isPrimaryKey() default false;
- }
在需要合并单元格的属性上设置 @ExcelMerge 注解,二级表头通过设置 @ExcelProperty 注解中 value 值为数组形式来实现该效果:
- /**
- * @author
- */
- @Data
- public class OrderBO {
- @ExcelProperty(value = "订单主键")
- @ColumnWidth(16)
- @ExcelMerge(merge = true, isPrimaryKey = true)
- private String id;
- @ExcelProperty(value = "订单编号")
- @ColumnWidth(20)
- @ExcelMerge(merge = true)
- private String orderId;
- @ExcelProperty(value = "收货地址")
- @ExcelMerge(merge = true)
- @ColumnWidth(20)
- private String address;
- @ExcelProperty(value = "创建时间")
- @ColumnWidth(20)
- @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
- @ExcelMerge(merge = true)
- private Date createTime;
- @ExcelProperty(value = {"商品信息", "商品编号"})
- @ColumnWidth(20)
- private String productId;
- @ExcelProperty(value = {"商品信息", "商品名称"})
- @ColumnWidth(20)
- private String name;
- @ExcelProperty(value = {"商品信息", "商品标题"})
- @ColumnWidth(30)
- private String subtitle;
- @ExcelProperty(value = {"商品信息", "品牌名称"})
- @ColumnWidth(20)
- private String brandName;
- @ExcelProperty(value = {"商品信息", "商品价格"})
- @ColumnWidth(20)
- private BigDecimal price;
- @ExcelProperty(value = {"商品信息", "商品数量"})
- @ColumnWidth(20)
- private Integer count;
- }
导出之前,需要对数据进行处理,将订单数据进行平铺,orderList为平铺前格式,exportData为平铺后格式:
当 Excel 中两列主键相同时,合并被标记需要合并的列:
-
- /**
- * 自定义单元格合并策略
- *
- * @author yun
- */
- public class ExcelMergeStrategy implements RowWriteHandler {
- /**
- * 主键下标
- */
- private Integer primaryKeyIndex;
- /**
- * 需要合并的列的下标集合
- */
- private final List<Integer> mergeColumnIndexList = new ArrayList<>();
- /**
- * 数据类型
- */
- private final Class<?> elementType;
- public ExcelMergeStrategy(Class<?> elementType) {
- this.elementType = elementType;
- }
- @Override
- public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
- // 判断是否为标题
- if (isHead) {
- return;
- }
- // 获取当前工作表
- Sheet sheet = writeSheetHolder.getSheet();
- // 初始化主键下标和需要合并字段的下标
- if (primaryKeyIndex == null) {
- this.initPrimaryIndexAndMergeIndex(writeSheetHolder);
- }
- // 判断是否需要和上一行进行合并
- // 不能和标题合并,只能数据行之间合并
- if (row.getRowNum() <= 1) {
- return;
- }
- // 获取上一行数据
- Row lastRow = sheet.getRow(row.getRowNum() - 1);
- // 将本行和上一行是同一类型的数据(通过主键字段进行判断),则需要合并
- if (lastRow.getCell(primaryKeyIndex).getStringCellValue().equalsIgnoreCase(row.getCell(primaryKeyIndex).getStringCellValue())) {
- for (Integer mergeIndex : mergeColumnIndexList) {
- CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(), mergeIndex, mergeIndex);
- sheet.addMergedRegionUnsafe(cellRangeAddress);
- }
- }
- }
- /**
- * 初始化主键下标和需要合并字段的下标
- *
- * @param writeSheetHolder WriteSheetHolder
- */
- private void initPrimaryIndexAndMergeIndex(WriteSheetHolder writeSheetHolder) {
- // 获取当前工作表
- Sheet sheet = writeSheetHolder.getSheet();
- // 获取标题行
- Row titleRow = sheet.getRow(0);
- // 获取所有属性字段
- Field[] fields = this.elementType.getDeclaredFields();
- // 遍历所有字段
- for (Field field : fields) {
- // 获取@ExcelProperty注解,用于获取该字段对应列的下标
- ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
- // 判断是否为空
- if (null == excelProperty) {
- continue;
- }
- // 获取自定义注解,用于合并单元格
- ExcelMerge excelMerge = field.getAnnotation(ExcelMerge.class);
- // 判断是否需要合并
- if (null == excelMerge) {
- continue;
- }
- for (int i = 0; i < fields.length; i++) {
- Cell cell = titleRow.getCell(i);
- if (null == cell) {
- continue;
- }
- // 将字段和表头匹配上
- if (excelProperty.value()[0].equalsIgnoreCase(cell.getStringCellValue())) {
- if (excelMerge.isPrimaryKey()) {
- primaryKeyIndex = i;
- }
- if (excelMerge.merge()) {
- mergeColumnIndexList.add(i);
- }
- }
- }
- }
- // 没有指定主键,则异常
- if (null == this.primaryKeyIndex) {
- throw new IllegalStateException("使用@ExcelMerge注解必须指定主键");
- }
- }
- }
将自定义合并策略 ExcelMergeStrategy 通过 registerWriteHandler 注册上去:
-
- /**
- * EasyExcel导入导出
- *
- * @author yun
- */
- @RestController
- @RequestMapping("/excel")
- public class ExcelController {
- @GetMapping("/export/order")
- public void exportOrderExcel(HttpServletResponse response) {
- try {
- this.setExcelResponseProp(response, "订单列表");
- List<OrderDO> orderList = this.getOrderList();
- List<OrderBO> exportData = this.convert(orderList);
- EasyExcel.write(response.getOutputStream())
- .head(OrderBO.class)
- .registerWriteHandler(new ExcelMergeStrategy(OrderBO.class))
- .excelType(ExcelTypeEnum.XLSX)
- .sheet("订单列表")
- .doWrite(exportData);
- } catch (IOException e) {
- throw new RuntimeException(e);
- }
- }
- /**
- * 设置响应结果
- *
- * @param response 响应结果对象
- * @param rawFileName 文件名
- * @throws UnsupportedEncodingException 不支持编码异常
- */
- private void setExcelResponseProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
- response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
- response.setCharacterEncoding("utf-8");
- String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\+", "%20");
- response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
- }
- }
运行项目,通过 Postman 或者 Apifox 工具来进行接口测试
注意:在 Apifox 中访问接口后无法直接下载,需要点击返回结果中的下载图标才行,点击之后方可对Excel文件进行保存。
接口地址:http://localhost:8080/excel/export/order
- //需要合并的列
- int[] mergeColumeIndex = {0,1,2,3,4,5,8,9,11};
- // 从那一列开始合并
- int mergeRowIndex = 0;
- ExcelWriter excelWriter = EasyExcel.write(outputStream)
- .sheet("SheetName")
- //设置合并单元格策略
- .registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex))
- .doWrite(exportVoList);
- public class ExcelFillCellMergeStrategy implements CellWriteHandler {
-
- private int[] mergeColumnIndex;
- private int mergeRowIndex;
-
- public ExcelFillCellMergeStrategy() {
- }
-
- public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
- this.mergeRowIndex = mergeRowIndex;
- this.mergeColumnIndex = mergeColumnIndex;
- }
-
- @Override
- public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
-
- }
-
- @Override
- public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
-
- }
-
- @Override
- public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
- int curRowIndex = cell.getRowIndex();
- int curColIndex = cell.getColumnIndex();
- if (curRowIndex > mergeRowIndex) {
- for (int i = 0; i < mergeColumnIndex.length; i++) {
- if (curColIndex == mergeColumnIndex[i]) {
- mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
- break;
- }
- }
- }
- }
-
- /**
- * 当前单元格向上合并
- *
- * @param writeSheetHolder
- * @param cell 当前单元格
- * @param curRowIndex 当前行
- * @param curColIndex 当前列
- */
- private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
- Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
- Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
- Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
- // 将当前单元格数据与上一个单元格数据比较
- Boolean dataBool = preData.equals(curData);
- //此处需要注意:因为我是按照序号确定是否需要合并的,所以获取每一行第一列数据和上一行第一列数据进行比较,如果相等合并
- Boolean bool = cell.getRow().getCell(0).getNumericCellValue() == cell.getSheet().getRow(curRowIndex - 1).getCell(0).getNumericCellValue();
- if (dataBool && bool) {
- Sheet sheet = writeSheetHolder.getSheet();
- List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
- boolean isMerged = false;
- for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
- CellRangeAddress cellRangeAddr = mergeRegions.get(i);
- // 若上一个单元格已经被合并,则先移出原有的合并单元,再重新添加合并单元
- if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
- sheet.removeMergedRegion(i);
- cellRangeAddr.setLastRow(curRowIndex);
- sheet.addMergedRegion(cellRangeAddr);
- isMerged = true;
- }
- }
- // 若上一个单元格未被合并,则新增合并单元
- if (!isMerged) {
- CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
- sheet.addMergedRegion(cellRangeAddress);
- }
- }
- }
-
- }
exportVoList 导出的数据。
注意:如果需要合并三个单元格需要产生3条数据 例如:
1,李华,英语,80分
1,李华,语文,70分
1,李华,数学,60分
2,李四,数学,80分
导出效果如下:
以导入用户信息为例,接下来手把手教大家如何使用EasyExcel实现导入功能!
-
- /**
- * EasyExcel导入导出
- *
- * @author yun
- */
- @RestController
- @RequestMapping("/excel")
- @Api(tags = "EasyExcel")
- public class ExcelController {
-
- @PostMapping("/import/user")
- public ResponseVO importUserExcel(@RequestPart(value = "file") MultipartFile file) {
- try {
- List<UserDO> userList = EasyExcel.read(file.getInputStream())
- .head(UserDO.class)
- .sheet()
- .doReadSync();
- return ResponseVO.success(userList);
- } catch (IOException e) {
- return ResponseVO.error();
- }
- }
- }
项目地址:https://github.com/alibaba/easyexcel
官方文档:https://www.yuque.com/easyexcel/doc/easyexcel
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。