文档链接
注解
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
*/
@Override
public 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; // 序号计数器
@Override
public 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) {
}
}
}