Alibaba easyexcel导出使用和工具封装

• 引入easyexcel依赖

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

导出对象封装两种方式：

• 实体封装（推荐）

@ContentRowHeight(25)
@HeadRowHeight(30)
@ColumnWidth(10)
public class TrafficExcelResponse {

    /**
     * 通行时间
     */
    @ExcelProperty(value = "公司名称",index = 0)
    @ColumnWidth(20)
    private String qyname;

    @ExcelProperty(value = "姓名",index = 1)
    @ColumnWidth(10)
    private String qyjname;

    public String getQyname() {
        return qyname;
    }

    public void setQyname(String qyname) {
        this.qyname = qyname;
    }

    public String getQyjname() {
        return qyjname;
    }

    public void setQyjname(String qyjname) {
        this.qyjname = qyjname;
    }
}

// 导出
// List<TrafficExcelResponse> collect = new ArrayList<>();
// EasyExcelUtil.writeExcel(response,collect,fileName,"sheet1",TrafficExcelResponse.class);

可以通过@ColumnWidth注解指定某一列的宽度。
也可以在实体类上使用@ColumnWidth，这样表示将表中的所有列都设置成指定的宽度，实体类上还可以使用@ContentRowHeight指定行高，@HeadRowHeight指定表头高度

• 自定义list导出

List<LoginExtendsVO> list = oracleNamedJdbcTemplate.query(sql, params, new RowMapperService<>(LoginExtendsVO.class));
 // 动态添加 表头 headList --> 所有表头行集合
        List<List<String>> headList = new ArrayList<>();
        // 有几列 表头 就有几个list
        List<String> headTitle0 = new ArrayList<>();
        List<String> headTitle1 = new ArrayList<>();
        List<String> headTitle2 = new ArrayList<>();
        List<String> headTitle3 = new ArrayList<>();

        headTitle0.add(range + "登录情况统计明细表");
        headTitle0.add("镇街");
        headTitle1.add(range + "登录情况统计明细表");
        headTitle1.add("单位");
        headTitle2.add(range + "登录情况统计明细表");
        headTitle2.add("登录次数");
        headTitle3.add(range + "登录情况统计明细表");
        headTitle3.add("备注");

        headList.add(headTitle0);
        headList.add(headTitle1);
        headList.add(headTitle2);
        headList.add(headTitle3);

        String fileName = "网格化系统" + "登录情况统计明细表";
        EasyExcelUtil.writeExcelAndHead(response,headList,list,fileName,range, LoginExtendsVO.class);

工具封装

/**
 *@author lxy
 *@description 表格导出工具类
 *@since 2021/11/25
 */
public class EasyExcelUtil {

    /**
     * excel文件流xlsx格式导出 注解表头 @ExcelProperty(value = "主叫姓名",index = 0)
     * @params [response, list, fileName, sheetName, clazz]
     * @return void
    */
    public static void writeExcel(HttpServletResponse response, List<?> list, String fileName, String sheetName, Class<?> clazz)throws Exception  {
        //内容策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(null, contentWriteCellStyle);

        handleResponse(response,fileName);
        //sheet名
        EasyExcel.write(response.getOutputStream(), clazz)
                .registerWriteHandler(horizontalCellStyleStrategy)
                .sheet(sheetName)
                .doWrite(list);
    }

    /**
     * 自定义动态表头生成表格
     * @params [response, header, list, fileName, sheetName, clazz]
     * @return void
    */
    public static void writeExcelAndHead(HttpServletResponse response,List<List<String>> header, List<?> list, String fileName, String sheetName, Class<?> clazz)throws Exception  {
        //内容策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(null, contentWriteCellStyle);

        handleResponse(response,fileName);
        //sheet名
        EasyExcel.write(response.getOutputStream(), clazz)
                .registerWriteHandler(horizontalCellStyleStrategy)
                .head(header).sheet(sheetName).doWrite(list);
    }

    private static void handleResponse(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        response.setCharacterEncoding("utf8");
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        //文件名
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
        response.setHeader("Cache-Control", "no-store");
        response.addHeader("Cache-Control", "max-age=0");
    }
}