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> head,
      List> 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 list = getList(dateRange);
 
    // 表头数据
    List> head = ListUtils.newArrayList();
    getHead(head);
    List dtoList = DocuLib.getList(list.get(0), "causeMalfunctionDetails");
    for (Document item : dtoList) {
      List head1 = ListUtils.newArrayList();
      head1.add(DocuLib.getStr(item, "causeMalfunctionName"));
      head.add(head1);
    }
 
    // 内容数据
    List> dataList = ListUtils.newArrayList();
 
    for (Document item : list) {
      List