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
     */
    @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) {

        }

    }


}