使用easyExcel生成动态表头表格

easyExcel在线文档https://alibaba-easyexcel.github.io/docs/current/

直接上菜

一、业务层：

@Override
    public void downloadSummaryTable(String classId, HttpServletResponse response) throws UnsupportedEncodingException {
        if (StrKit.isBlank(classId)) {
            return;
        }
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("导出", "utf-8") + ".xlsx";
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
        try {
            EasyExcel.write(response.getOutputStream()).head(getHeader(classId)).sheet("模板")
                    .registerWriteHandler(new CustomizeColumnWidth())
                    .registerWriteHandler(new CustomizeColumnWidth().setStyleStrategy())
                    .doWrite(getAttendanceData(classId));
        } catch (Exception e) {
            throw new RuntimeException("导出模板异常");
        }
    }

二、设置动态表头

private List<List<String>> getHeader(String classId) {
        Record classInfo = Db.findFirst(Db.getSqlPara(SQL_KEY + "findClassTime", Kv.create().set("classId", classId)));
        String className = classInfo.getStr("name");
        Date sdate = classInfo.getDate("sdate");
        Date edate = classInfo.getDate("edate");
        long betweenDay = DateUtil.between(sdate, edate, DateUnit.DAY);
        final String mainHead = className + "出勤汇总表";
        List<String> head1 = new ArrayList<>();
        head1.add(mainHead);
        head1.add("学号");
        head1.add("学号");
        List<String> head2 = new ArrayList<>();
        head2.add(mainHead);
        head2.add("姓名");
        head2.add("姓名");
        List<List<String>> resHead = new ArrayList<>();
        resHead.add(head1);
        resHead.add(head2);
        boolean time = true;
        for (int i = 0; i < (betweenDay + 1) * 2; i++) {
            List<String> head = new ArrayList<>();
            head.add(mainHead);
            double day = Math.floor(i / 2);
            DateTime dateTime = DateUtil.offsetDay(sdate, (int) day);
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
            String dayTime = format.format(dateTime);
            head.add(dayTime);
            if (time) {
                head.add("上午");
                time = false;
            } else {
                head.add("下午");
                time = true;
            }
            resHead.add(head);
        }
        List<String> head3 = new ArrayList<>();
        head3.add(mainHead);
        head3.add("出勤汇总");
        head3.add("出勤汇总");
        resHead.add(head3);
        List<String> head4 = new ArrayList<>();
        head4.add(mainHead);
        head4.add("备注");
        head4.add("备注");
        resHead.add(head4);

        return resHead;
    }

三、设置数据

此处简化 反正表格内容数据格式也为List<List<String>>

List<List<String>> total = new ArrayList<>();
for(Reocrd record:list){
     List<String> list = new ArrayList<>();
     list.add(record.getStr("name");
}
    return total;

四、表格自定义格式

public class CustomizeColumnWidth extends AbstractColumnWidthStyleStrategy {

    /**
     * 表格动态设置列宽
     *
     */
    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        //判断是否为表头
        if (isHead && cell.getRowIndex() == 1) {
            int columnWidth;
            int cellIndex = cell.getColumnIndex();
            int summarySerial = writeSheetHolder.getHead().size() - 2;
            if (cellIndex == 0) {  //学号
                columnWidth = 13;
            } else if (cellIndex == 1) { //姓名
                columnWidth = 12;
            } else if (cellIndex == summarySerial) { //出勤汇总
                columnWidth = 26;
            } else {
                columnWidth = 9;
            }
            writeSheetHolder.getSheet().setColumnWidth(cellIndex, columnWidth * 256);
            //20倍short值为表格中的磅值
            writeSheetHolder.getSheet().setDefaultRowHeight((short) 512);
        }
    }

    /**
     * 自定义样式（表头、内容的字体、背景、样式）
     * @return HorizontalCellStyleStrategy
     */
    public HorizontalCellStyleStrategy setStyleStrategy(){
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 设置居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 背景色, 设置为浅灰
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        // 字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 14);
        headWriteCellStyle.setWriteFont(headWriteFont);

        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        // contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);

        // 背景绿色
        //contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        // 设置 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 设置边框样式
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);

        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }
}

五、效果图