526互联

easyExcel导出单个文件多个sheet页-注解方式

发布时间 2023-04-21 10:41:25作者: 我想养只狗

easyExcel注解方式导出，为什么要用注解方式？

注解方式更简单、更方便，相比非注解的方式减少了大量代码。

但是，注解方式不够灵活，精确度也不高，比如列宽，注解方式仅支持int类型，最大255个字符，而非注解方式就要精确的多，可以随意设置。

所以，对于要求比较高的、复杂的Excel，还是推荐使用非注解的方式实现。

重点一：注解方式实现复杂表头

表头样式（网上找的，非原创）

在这里插入图片描述

表实现类（网上找的，该代码非原创）

@Data
public class WmsOperationQueryExcleVO {

    //时间段
    @ColumnWidth(14)
    @ExcelProperty(value = {"仓库运营报表(按天查询)","时间段/项目","时间段/项目"},index = 0)
    private String timeSlot;

    //包裹签收/个
    @ColumnWidth(14)
    @ExcelProperty(value = {"仓库运营报表(按天查询)","前端","包裹签收/个"},index = 1)
    private String packageSigning;

    //收货完成/件
    @ColumnWidth(14)
    @ExcelProperty(value = {"仓库运营报表(按天查询)","前端","收货完成/件"},index = 2)
    private String receivingCompleted;

    //上架完成/件
    @ColumnWidth(14)
    @ExcelProperty(value = {"仓库运营报表(按天查询)","前端","上架完成/件"},index = 3)
    private String completedShelf;

    //调拔入库/件
    @ColumnWidth(14)
    @ExcelProperty(value = {"仓库运营报表(按天查询)","前端","调拔入库/件"},index = 4)
    private String transferToStorage;

    //退货入库/件
    @ColumnWidth(14)
    @ExcelProperty(value = {"仓库运营报表(按天查询)","前端","退货入库/件"},index = 5)
    private String returnWarehousing;

    //其他入库/件
    @ColumnWidth(14)
    @ExcelProperty(value = {"仓库运营报表(按天查询)","前端","其他入库/件"},index = 6)
    private String otherWarehousing;

    //订单拣货完成/件
    @ColumnWidth(14)
    @ExcelProperty(value = {"仓库运营报表(按天查询)","中端","订单拣货完成/件"},index = 7)
    private String orderPickingCompleted;

    //海外仓拣货完成/件
    @ColumnWidth(14)
    @ExcelProperty(value = {"仓库运营报表(按天查询)","中端","海外仓拣货完成/件"},index = 8)
    private String overseasWarehousePicking;

    //FBA拣货完成/件
    @ColumnWidth(14)
    @ExcelProperty(value = {"仓库运营报表(按天查询)","中端","FBA拣货完成/件"},index = 9)
    private String pickingFba;

    //其他出库/件
    @ColumnWidth(14)
    @ExcelProperty(value = {"仓库运营报表(按天查询)","中端","其他出库/件"},index = 10)
    private String otherStockOut;

    //订单分拣完成/件
    @ColumnWidth(14)
    @ExcelProperty(value = {"仓库运营报表(按天查询)","后端","订单分拣完成/件"},index = 11)
    private String orderSortingCompleted;

    //订单包装完成/单
    @ColumnWidth(14)
    @ExcelProperty(value = {"仓库运营报表(按天查询)","后端","订单包装完成/单"},index = 12)
    private String orderPackaging;

    //海外仓包装完成/单
    @ColumnWidth(14)
    @ExcelProperty(value = {"仓库运营报表(按天查询)","后端","海外仓包装完成/单"},index = 13)
    private String overseasWarehousePackaging;

    //FBA包装完成/单
    @ColumnWidth(14)
    @ExcelProperty(value = {"仓库运营报表(按天查询)","后端","FBA包装完成/单"},index = 14)
    private String packagingFba;
}

重点二：多个sheet页的Excel文件导出（直接上代码）

pom.xlm引入maven

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

ExcelUtils.java导出工具类，具体导出实现

/**
 * excel工具类
 */
public class ExcelUtils {

    /**
     * 通用 Excel导出
     *
     * @param response      response
     * @param fileName      文件名
     * @param sheetName     sheetName
     * @param list          数据List
     * @param pojoClass     对象Class
     */
    public static void exportExcel(HttpServletResponse response, String fileName, String sheetName, List<?> list,
                                     Class<?> pojoClass) throws IOException {
        if(StringUtils.isBlank(fileName)){
            //当前日期
            fileName = DateUtils.format(new Date());
        }

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Access-Control-Expose-Headers","Content-Disposition");//不设置该参数前端（vue）接收不到文件名
        response.setHeader("Content-disposition", "attachment;filename=" +  fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), pojoClass).sheet(sheetName).doWrite(list);
    }

    /**
     * Excel导出，先sourceList转换成List<targetClass>，再导出
     * 单个sheet
     *
     * @param response      response
     * @param fileName      文件名
     * @param sheetName     sheetName
     * @param sourceList    原数据List
     * @param targetClass   目标对象Class
     */
    public static void exportExcelToTarget(HttpServletResponse response, String fileName, String sheetName, List<?> sourceList,
                                     Class<?> targetClass) throws Exception {
        List targetList = new ArrayList<>(sourceList.size());
        for(Object source : sourceList){
            Object target = targetClass.newInstance();
            BeanUtils.copyProperties(source, target);
            targetList.add(target);
        }

        exportExcel(response, fileName, sheetName, targetList, targetClass);
    }

    /**
     * Excel导出，先sourceList转换成List<targetClass>，再导出
     * 多个sheet
     *
     * @param response      response
     * @param fileName      文件名
     * @param sheetNames     sheetName列表
     * @param sourceMap    原数据Map,key为sheetName,value为list
     * @param targetClass   目标对象Class
     */
    public static void exportExcel(HttpServletResponse response, String fileName, List<String> sheetNames, Map<String, Object> sourceMap,
                                           Class<?> targetClass) throws Exception {
        if(StringUtils.isBlank(fileName)){
            //当前日期
            fileName = DateUtils.format(new Date());
        }
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Access-Control-Expose-Headers","Content-Disposition");//不设置该参数前端（vue）接收不到文件名
        response.setHeader("Content-disposition", "attachment;filename=" +  fileName + ".xlsx");

        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), targetClass).build();
        for (int i = 0; i < sheetNames.size(); i++) {
            List list = (List) sourceMap.get(sheetNames.get(i));
            List targetList = new ArrayList<>(list.size());
            for(Object source : list){
                Object target = targetClass.newInstance();
                BeanUtils.copyProperties(source, target);
                targetList.add(target);
            }
            WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetNames.get(i)).build();
            excelWriter.write(targetList, writeSheet);
        }
        excelWriter.finish();
    }

}

参数类DTO

@Data
public class ExportDataParamDTO {
    /**
     * 文件名
     */
    private String fileName;
    /**
     * 标签页名称集合
     */
    private List<String> sheetNames;
    /**
     * 数据map，key标签页名称，value数据list
     */
    private Map<String, Object> dataMap;
    /**
     * 目标类型
     */
    private Class<?> targetClass;
}

导出实体类，具体样式在该类中注解方式实现

/**
 * 导出excel表实体类
 *
 * @author Mark sunlightcs@gmail.com
 * @since 1.0.0 2022-11-21
 */
@Data
@HeadRowHeight(42)
//@ContentRowHeight(14)
@HeadStyle(fillBackgroundColor= 55, horizontalAlignment = HorizontalAlignment.CENTER, borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN, borderBottom = BorderStyle.THIN)
@HeadFontStyle(fontName = "宋体", fontHeightInPoints = 9, bold = true)
@ContentFontStyle(fontName = "宋体", fontHeightInPoints = 8, bold = false)
public class ExportExcel {

    @ExcelProperty("期数")
    @ColumnWidth(4)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER, borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN, borderBottom = BorderStyle.THIN)
    private Integer qici;

    @ExcelProperty("有效期数")
    @ColumnWidth(4)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER, borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN, borderBottom = BorderStyle.THIN)
    private String yxq; //有效期次

    @ExcelProperty("高度(m)")
    @ColumnWidth(6)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER, borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN, borderBottom = BorderStyle.THIN)
    private Double PRELOADH;

    @ExcelProperty("观测")
    @ColumnWidth(26)
    @ContentStyle(borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN, borderBottom = BorderStyle.THIN)
    private String workinfoname;

    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @ExcelProperty("查看时间")
    @ColumnWidth(15)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER, borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN, borderBottom = BorderStyle.THIN)
    private Date mtimeW;

    @ExcelProperty("成果")
    @ColumnWidth(8)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER, borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN, borderBottom = BorderStyle.THIN)
    private String mavalue;

    @ExcelProperty("修改值")
    @ColumnWidth(7)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER, borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN, borderBottom = BorderStyle.THIN)
    private String cvalue;

    @ExcelProperty("状态")
    @ColumnWidth(8)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER, borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN, borderBottom = BorderStyle.THIN)
    private String pstate;

    @ExcelProperty("天数")
    @ColumnWidth(4)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER, borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN, borderBottom = BorderStyle.THIN)
    private Integer day;

    @ExcelProperty("间隔(天)")
    @ColumnWidth(8)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER, borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN, borderBottom = BorderStyle.THIN)
    private Integer dayJg;

    @ExcelProperty("当前值")
    @ColumnWidth(8)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER, borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN, borderBottom = BorderStyle.THIN)
    private String mavalueBc;

    @ExcelProperty("累计值")
    @ColumnWidth(8)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER, borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN, borderBottom = BorderStyle.THIN)
    private String mavalueLj;

    @ExcelProperty("速率")
    @ColumnWidth(4)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER, borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN, borderBottom = BorderStyle.THIN)
    private String mavalueSl;

    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @ExcelProperty("上传时间")
    @ColumnWidth(15)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER, borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN, borderBottom = BorderStyle.THIN)
    private Date createdate;

    @ExcelProperty("人员名称")
    @ColumnWidth(6)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER, borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN, borderBottom = BorderStyle.THIN)
    private String sname;

    @ExcelProperty("删除状态")
    @ColumnWidth(4)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER, borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN, borderBottom = BorderStyle.THIN)
    private String useflag;

    @ExcelProperty("备注")
    @ColumnWidth(11)
    @ContentStyle(borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN, borderBottom = BorderStyle.THIN)
    private String remark;

}

Controller实现方法

@GetMapping("exportResultList")
    @ApiOperation("导出")
    @LogOperation("导出")
    public void export(String ids, HttpServletResponse response) throws Exception {
        String[] split = ids.split(",");
        //数据
        ExportDataParamDTO resultData = checkpointService.exportResultList(Arrays.asList(split));
        //导出操作
        ExcelUtils.exportExcel(response, resultData.getFileName(), resultData.getSheetNames(), resultData.getDataMap(), resultData.getTargetClass());
    }

Excel

完美！