EasyExcel文档链接与使用示例

文档链接

注解
https://blog.csdn.net/estelle_belle/article/details/134508223

官方文档地址
https://github.com/alibaba/easyexcel/tree/master?tab=readme-ov-file

使用示例

依赖版本

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.3.1</version></dependency>

业务层

   /*** 导出费用信息列表** @param dto* @param response*/@Overridepublic void exportCostinfo(ExportCostinfoDTO dto, HttpServletResponse response) {List<Long> ids = dto.getIds();// 导出文件try {String fileName = new String("费用信息记录.xls".getBytes("GBK"), StandardCharsets.ISO_8859_1);response.setContentType("application/octet-stream");response.setHeader("Content-disposition", "attachment;filename=" + fileName);OutputStream outputStream = response.getOutputStream();try (ExcelWriter excelWriter = EasyExcel.write(outputStream, CostinfoListVO.class).registerWriteHandler(new AutoIncrementNumberRowWriteHandler()).build()) {WriteSheet writeSheet = EasyExcel.writerSheet("记录").build();if (ids != null && ids.size() > 0) {//获取数据List<CostinfoListVO> exportList = convertResult(dto, ids);//导出 大数据量时可循环写入excelWriter.write(exportList, writeSheet);} else {dto.setPageNum(1L);dto.setPageSize(1L);PageParam<CostinfoDO, ListCostinfoDTO> page = new PageParam<>(dto);List<CostinfoListVO> list = list(page, dto);PageResult<CostinfoListVO> pageResult = new PageResult(list, page);Long total = pageResult.getTotal();long loop = total / 1000 + (total % 1000 != 0 ? 1 : 0);for (long i = 0; i < loop; i++) {dto.setPageNum(i + 1);dto.setPageSize(Long.valueOf(1000));List<CostinfoListVO> exportList = convertResult(dto, ids);excelWriter.write(exportList, writeSheet);}}//添加序号,添加空行数据excelWriter.write(Arrays.asList(new CostinfoListVO()), writeSheet);excelWriter.finish();}response.flushBuffer();// 写完数据关闭流outputStream.close();} catch (IOException e) {log.error("exportCostinfo 导出error! ", e);}}/*** 处理数据为展示值** @param dto 数据来源* @param ids 选择的数据* @return*/private List<CostinfoListVO> convertResult(ExportCostinfoDTO dto, List<Long> ids) {List<CostinfoListVO> exportList = new ArrayList<>();if (ids != null && ids.size() > 0) {//导出选择数据List<CostinfoListVO> idsList = costinfoMapper.exportIds(ids);for (int i = 0; i < idsList.size(); i++) {CostinfoListVO vo = idsList.get(i);if (ids.contains(Long.valueOf(vo.getId()))) {try {vo.setPayMethod(CosttypeEnumInterface.PayMethod.getMethod(StringUtils.defaultString(vo.getPayMethod())));vo.setInvoiceType(CosttypeEnumInterface.InvoiceType.getInvoiceType(StringUtils.defaultString(vo.getInvoiceType())));vo.setStatus(CosttypeEnumInterface.CosttypePayerAndReceiveEnum.getStatusName(Short.parseShort(vo.getCosttype()), Integer.parseInt(vo.getStatus())));} catch (Exception e) {log.error("枚举转换异常", e);}exportList.add(vo);}}} else {//导出全部数据PageParam<CostinfoDO, ListCostinfoDTO> page = new PageParam<>(dto);List<CostinfoListVO> list = list(page, dto);for (int i = 0; i < list.size(); i++) {CostinfoListVO vo = list.get(i);try {vo.setPayMethod(CosttypeEnumInterface.PayMethod.getMethod(StringUtils.defaultString(vo.getPayMethod())));vo.setInvoiceType(CosttypeEnumInterface.InvoiceType.getInvoiceType(StringUtils.defaultString(vo.getInvoiceType())));vo.setStatus(CosttypeEnumInterface.CosttypePayerAndReceiveEnum.getStatusName(Short.parseShort(vo.getCosttype()), Integer.parseInt(vo.getStatus())));} catch (Exception e) {log.error("枚举转换异常", e);}exportList.add(vo);}}return exportList;}

展示类

package net.cnki.editor.costcenter.pojo.vo.costinfo;import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import groovy.transform.EqualsAndHashCode;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;/*** 费用信息类别VO*/
@Data
@EqualsAndHashCode
@ExcelIgnoreUnannotated //没有注解的列不导出
public class CostinfoListVO {@ApiModelProperty(value = "ID: 费用信息表id")@ExcelProperty(" 序号") //导出列标题private String id;@ApiModelProperty(value = "稿件id")private String paperId;@ApiModelProperty(value = "工作流环节实例id")private String taskInstId;@ApiModelProperty(value = "稿号")@ExcelProperty(" 稿号")private String paperNum;@ApiModelProperty(value = "稿件标题")@ExcelProperty(" 稿件标题")private String chineseTitle;@ApiModelProperty(value = "投稿作者")@ExcelProperty(" 投稿作者")private String authorName;@ApiModelProperty(value = "投稿作者ID")private String authorID;@ApiModelProperty(value = "实际发生金额")@ExcelProperty(" 实际发生金额")private String realAmount;@ApiModelProperty(value = "结算方式: 结算方式 0 第三方转账 1 邮局汇款 2银行汇款 3现金支付 4内部转账 5其他")@ExcelProperty(" 结算方式")private String payMethod;@ApiModelProperty(value = "发票类型 0 普票 1 专票")@ExcelProperty(" 发票类型")private String invoiceType;@ApiModelProperty(value = "支付日期")@ExcelProperty(" 支付日期")private String payTime;@ApiModelProperty(value = "费用信息状态（不同费用类型状态不同）")@ExcelProperty(" 费用信息状态")private String status;@ApiModelProperty(value = "费用性质（ 1 审稿费; 2 版面费; 3 作者稿费; 4 专家审稿费; 5 编辑稿费;）")private String costtype;}

拦截器

package net.cnki.editor.costcenter.util.easyexcel;import com.alibaba.excel.write.handler.context.RowWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.handler.RowWriteHandler;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;import java.util.Map;/*** easyexcel 导出自增序号处理*/
public class AutoIncrementNumberRowWriteHandler implements RowWriteHandler {/** 注: 导出模板类中需要设置序号列;* 注: 此版本easyexcel 在导出拦截时会丢失最后一行数据,所有需要在导出list中手动添加一行空数据;* */private int number = 1; // 序号计数器@Overridepublic void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {try {int rowNum = row.getRowNum();if (rowNum > 1) {Sheet sheet = row.getSheet();Row row1 = sheet.getRow(number++);Cell cell = row1.getCell(0);cell.setCellValue(number - 1);}} catch (Exception e) {}}}