EasyExcel动态表头导出

1、封装方法

package com.skybird.iot.base.utils;import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.skybird.iot.base.utils.bean.Custemhandler;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;public class EasyExcelUtil {/*** @param response* @param t 导出实体类* @param list 数据集合* @param name 文件名称* @throws IOException* @throws IllegalAccessException* @throws InstantiationException*/public static void download(HttpServletRequest request, HttpServletResponse response, Class t, List list, String name)throws IOException, IllegalAccessException, InstantiationException {/*兼容IE和其他浏览器导出文件名乱码的问题*///    name = downloadCommFileName(name, request);response.setContentType("application/vnd.ms-excel"); // 设置文本内省response.setCharacterEncoding("utf-8"); // 设置字符编码response.setHeader("Content-disposition", "attachment;fileName=name.xlsx"); // 设置响应头try (OutputStream outStream = response.getOutputStream()) {EasyExcel.write(outStream, t).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(new Custemhandler()) // 设置自动列宽设置.registerWriteHandler(getStyleStrategy()) // 设置样式.sheet(name).doWrite(list); // 用io流来写入数据outStream.flush();}}/*** 动态表头导出** @param request* @param response* @param head 表头数据* @param dataList 内容数据* @param name 名称* @throws IOException*/public static void trendsDownload(HttpServletRequest request,HttpServletResponse response,List<List<String>> head,List<List<Object>> dataList,String name)throws IOException {try (OutputStream outStream = response.getOutputStream()) {EasyExcel.write(outStream).head(head).sheet(name).registerWriteHandler(new Custemhandler()) // 设置自动列宽设置.registerWriteHandler(getStyleStrategy()) // 设置样式.doWrite(dataList);outStream.flush();}}/*兼容IE和其他浏览器导出文件名乱码的问题*/public static String downloadCommFileName(String fileOut, HttpServletRequest request)throws IOException {String userAgent = request.getHeader("user-agent").toLowerCase();if (!StrUtil.contains(userAgent, "chrome")&& (userAgent.contains("msie") || userAgent.contains("like gecko"))) {// win10 ie edge 浏览器 和其他系统的iefileOut = URLEncoder.encode(fileOut, "UTF-8");} else {// 其他fileOut = new String(fileOut.getBytes("utf-8"), "iso-8859-1");}return fileOut;}public static HorizontalCellStyleStrategy getStyleStrategy() {// 头的策略WriteCellStyle headWriteCellStyle = new WriteCellStyle();// 背景设置headWriteCellStyle.setFillForegroundColor(IndexedColors.AUTOMATIC.getIndex());WriteFont headWriteFont = new WriteFont();headWriteFont.setFontHeightInPoints((short) 12);// 字体样式headWriteFont.setFontName("宋体");headWriteFont.setBold(false); // 取消加粗// 字体设置成红色//        headWriteFont.setColor(IndexedColors.RED.getIndex());headWriteCellStyle.setWriteFont(headWriteFont);// 自动换行headWriteCellStyle.setWrapped(false);//        headWriteCellStyle.setBorderTop(BorderStyle.SLANTED_DASH_DOT);//右边框//        headWriteCellStyle.setBorderBottom(BorderStyle.SLANTED_DASH_DOT);//左//        headWriteCellStyle.setBorderLeft(BorderStyle.SLANTED_DASH_DOT);//底//        headWriteCellStyle.setBorderRight(BorderStyle.SLANTED_DASH_DOT);// 水平对齐方式headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 垂直对齐方式headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 内容的策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了// FillPatternType所以可以不指定//        contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);// 背景白色contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());WriteFont contentWriteFont = new WriteFont();// 字体大小contentWriteFont.setFontHeightInPoints((short) 12);// 字体样式contentWriteFont.setFontName("Calibri");contentWriteCellStyle.setWriteFont(contentWriteFont);return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);}
}

2、Java请求接口

  @RequestMapping("/excel")public void excel(HttpServletRequest request,HttpServletResponse response,@RequestParam(value = "dateRange", required = false) Integer dateRange)throws Exception {List<Document> list = getList(dateRange);// 表头数据List<List<String>> head = ListUtils.newArrayList();getHead(head);List<Document> dtoList = DocuLib.getList(list.get(0), "causeMalfunctionDetails");for (Document item : dtoList) {List<String> head1 = ListUtils.newArrayList();head1.add(DocuLib.getStr(item, "causeMalfunctionName"));head.add(head1);}// 内容数据List<List<Object>> dataList = ListUtils.newArrayList();for (Document item : list) {List<Object> data = ListUtils.newArrayList();data.add(DocuLib.getStr(item, "yearMonth"));data.add(DocuLib.getStr(item, "totalMonthly"));data.add(DocuLib.getStr(item, "auditMonthly"));data.add(DocuLib.getStr(item, "completedMonthly"));List<Document> rowList = DocuLib.getList(item, "causeMalfunctionDetails");for (Document dto : rowList) {data.add(DocuLib.getStr(dto, "count"));}dataList.add(data);}EasyExcelUtil.trendsDownload(request, response, head, dataList, "销售退货统计");}private void getHead(List<List<String>> head) {List<String> head1 = ListUtils.newArrayList();head1.add("时间");List<String> head2 = ListUtils.newArrayList();head2.add("退货单数");List<String> head3 = ListUtils.newArrayList();head3.add("审核通过");List<String> head4 = ListUtils.newArrayList();head4.add("完成单数");head.add(head1);head.add(head2);head.add(head3);head.add(head4);}

3、html关键代码

<div class='card-body' style='height: 100%;'><div style="display: flex;flex-wrap: wrap; align-items: center;"><cb-date inline="true"style="width:100px;margin-bottom: 10px; display: block;"ng-model="ctrl.filter.dateRange"placeholder="创建时间"picker="year"></cb-date><button class="btn btn-default btn-sm"style="margin-left: 8px; margin-bottom: 10px; display: block;" type="button"ng-click="ctrl.loadData()"><i class="bi bi-search"></i>查询</button><div><button class='btn btn-outline-primary btn-sm' ng-click='ctrl.export()'style="margin-left: 5px;margin-bottom: 10px;"ng-if="''| SecurityFilter: 'saleReturnOrderStatistics':'export'"type='button'><i class='bi'></i>导出</button></div></div><div style="overflow: scroll;overflow-x: auto; width: 100%;height: calc(100% - 80px);"ng-if="check === false"><table class="table table-bordered" style="width: 1640px;table-layout: fixed;"><thead style="position: sticky;top: -1px;z-index: 1;"><tr><th width="100px;">时间</th><th width="150px;">退货单数</th><th width="150px;">审核通过</th><th width="150px;">完成单数</th><th ng-repeat="dailyDetail in entity[0].causeMalfunctionDetails"ng-style="{width: dailyDetail.causeMalfunctionName.length>=5?dailyDetail.causeMalfunctionName.length*17:100}">{{dailyDetail.causeMalfunctionName}}</th></tr></thead><tbody><tr ng-repeat="row in entity"><td>{{row.yearMonth}}</td><td>{{row.totalMonthly}}</td><td>{{row.auditMonthly}}</td><td>{{row.completedMonthly}}</td><td ng-repeat="dailyDetail in row.causeMalfunctionDetails">{{dailyDetail.count}}</td></tr></tbody></table></div><div style="overflow: scroll; width: 100%;height: calc(100% - 80px);" ng-if="check === true"><table class="table table-bordered" style="width: auto;table-layout: fixed;"><thead style="position: sticky;top: -1px;z-index: 1;"><tr><th width="100px;">时间</th><th width="150px;">退货单数</th><th width="150px;">审核通过</th><th width="150px;">完成单数</th><th ng-repeat="dailyDetail in entity[0].causeMalfunctionDetails">{{dailyDetail.causeMalfunctionName}}</th></tr></thead><tbody><tr ng-repeat="row in entity"><td>{{row.yearMonth}}</td><td>{{row.totalMonthly}}</td><td>{{row.auditMonthly}}</td><td>{{row.completedMonthly}}</td><td ng-repeat="dailyDetail in row.causeMalfunctionDetails">{{dailyDetail.count}}</td></tr></tbody></table></div></div>

4、js关键代码

                export: function () {//后端导出http.getFile('saleReturnOrderStatistics/excel', {dateRange: ctrl.filter.dateRange}).then(function (response) {const url = window.URL.createObjectURL(new Blob([response.data]));const link = document.createElement('a');link.href = url;link.setAttribute('download', '销售退货统计.xlsx');document.body.appendChild(link);link.click();});}

5、效果