java笔记_16_EasyExcel表头设计

导出对象：

import com.alibaba.excel.annotation.ExcelProperty;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

@Data
public class ProdEvaluatResultExcelVO {

    // ExcelProperty设置默认表头（无自定义表头时，直接生成）
    @ExcelProperty("表头1")
    private String data1;

    @ExcelProperty("表头2")
    private String data2;

    @ExcelProperty("表头3")
    private String data3;

    @ExcelProperty("表头4")
    private Double data4;

    // 时间格式设置
    @ExcelProperty("表头5")
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    private Date data5;
}

依赖：

import cn.hutool.core.collection.CollUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;

（默认表头）主要代码：

     @ApiOperation("导出")
    @PostMapping("/export")
    public void export(@RequestBody @Validated ExportReq req, HttpServletResponse response) throws IOException {
        // 获取导出数据
        List<XxxExcelVO> xxxExcelVOList = xxxService.getList(req);
        // 没数据时导出表头
        if (xxxExcelVOList == null || xxxExcelVOList.size() == 0) {
            xxxExcelVOList = new ArrayList<>();
            xxxExcelVOList.add(new XxxExcelVO());
        }
        if (CollUtil.isNotEmpty(xxxExcelVOList)) {
            ResponseUtil.handleExcelResponse(response, "导出文件");
            long start = System.currentTimeMillis() / 1000;
            ExcelWriter build = EasyExcel.write(response.getOutputStream()).head(XxxExcelVO.class).excelType(ExcelTypeEnum.XLSX).build();
            WriteSheet writeSheet = EasyExcel.writerSheet("sheet").build();
            build.write(xxxVOList, writeSheet);
            build.finish();
            long end = System.currentTimeMillis() / 1000;
            log.info("导出耗时：" + (end - start) + " 秒");
        }
    }

 （多行表头）主要代码：

    @ApiOperation("导出")
    @PostMapping("/export")
    public void export(@RequestBody @Validated ExportReq req, HttpServletResponse response) throws IOException {
        // 获取导出数据
        List<XxxExcelVO> xxxExcelVOList = xxxService.getList(req);
        // 没数据时导出表头
        if (xxxExcelVOList == null || xxxExcelVOList.size() == 0) {
            xxxExcelVOList = new ArrayList<>();
            xxxExcelVOList.add(new XxxExcelVO());
        }
        if (CollUtil.isNotEmpty(xxxExcelVOList)) {
            ResponseUtil.handleExcelResponse(response, "导出文件");
            // 计时
            long start = System.currentTimeMillis() / 1000;

            // 设置字体样式 设置粗体、字号
            WriteFont writeFont = new WriteFont();
            writeFont.setBold(false); 
            writeFont.setFontHeightInPoints(Short.valueOf((short)11));
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            contentWriteCellStyle.setWriteFont(writeFont);
            // 字体样式添加
              ExcelWriter build = EasyExcel.write(response.getOutputStream())
                    .registerWriteHandler(new HorizontalCellStyleStrategy(contentWriteCellStyle, contentWriteCellStyle))
                    .excelType(ExcelTypeEnum.XLSX).build();

            // 动态添加表头
            WriteSheet writeSheet = EasyExcel.writerSheet("sheet").build();
            // 创建表格，Sheet 中使用
            WriteTable table = new WriteTable();
            table.setTableNo(1);
            // 设置表头
            List<List<String>> headTitles = ListUtils.newArrayList();
            //表头数据(按列添加)(横向相邻且数据相同的表格，自动合并)
            //第1、2列
            headTitles.add(
                    ListUtils.newArrayList(
                            "aaaa",
                            "aaa",
                            "aa",
                            "a")
            );
            headTitles.add(
                    ListUtils.newArrayList(
                            "aaaa",
                            "aaa",
                            "bb",
                            "b")
            );
            //第3、4、5列 可以设置空表头" "
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            headTitles.add(
                    ListUtils.newArrayList(
                            "aaaa",
                            "bbb",
                            "bb",
                            "c")
            );
            headTitles.add(
                    ListUtils.newArrayList(
                            "aaaa",
                            "bbb",
                            " ",
                            "d")
            );
            headTitles.add(
                    ListUtils.newArrayList(
                            "aaaa",
                            "bbb",
                            " ",
                            "e")
            );
            table.setHead(headTitles);
            // 根据返回的列表写数据
            build.write(xxxExcelVOList, writeSheet, table);

            build.finish();
            long end = System.currentTimeMillis() / 1000;
            log.info("导出耗时：" + (end - start) + " 秒");
        }
    }