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 浏览器 和其他系统的ie
      fileOut = 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、效果