使用easyExcel生成动态表头表格

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

直接上菜

一、业务层：

@Overridepublic 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> 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 head1 = new ArrayList<>();head1.add(mainHead);head1.add("学号");head1.add("学号");List head2 = new ArrayList<>();head2.add(mainHead);head2.add("姓名");head2.add("姓名");List> resHead = new ArrayList<>();resHead.add(head1);resHead.add(head2);boolean time = true;for (int i = 0; i < (betweenDay + 1) * 2; i++) {List 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 head3 = new ArrayList<>();head3.add(mainHead);head3.add("出勤汇总");head3.add("出勤汇总");resHead.add(head3);List head4 = new ArrayList<>();head4.add(mainHead);head4.add("备注");head4.add("备注");resHead.add(head4);return resHead;}

三、设置数据

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

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

四、表格自定义格式

public class CustomizeColumnWidth extends AbstractColumnWidthStyleStrategy {/*** 表格动态设置列宽**/@Overrideprotected void setColumnWidth(WriteSheetHolder writeSheetHolder, List 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);}
}

五、效果图

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！