mjava-awinic/mjava/src/main/java/com/malk/utils/UtilExcel.java

package com.malk.utils;

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.google.common.base.Strings;
import com.malk.server.common.McException;
import com.malk.server.common.McREnum;
import lombok.Builder;
import lombok.Data;
import lombok.SneakyThrows;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.xssf.usermodel.*;

import javax.annotation.Nullable;
import javax.servlet.http.HttpServletResponse;
import javax.validation.constraints.NotNull;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * excel 导出工具 [poi & easy excel]
 */
@Builder
@Data
public class UtilExcel {

    /**
     * 设置响应流
     */
    @SneakyThrows
    public static void setResponseHeader(HttpServletResponse response, @NotNull String fileName, String extension) {
        // 后缀: poi, xlsx下预览无详情; EasyExcel 需要使用xlsx, 否则不能预览
        if (StringUtils.isBlank(extension)) {
            extension = ".xls";
        }
        String date = new SimpleDateFormat("yyyy-MM-dd HH_mm_ss").format(new Date());
        fileName = fileName + "_" + date + extension;
        // 设置导出流信息
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setCharacterEncoding("utf-8");
        // 文件名兼容中文
        fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-Disposition", "attachment;filename*=utf-8" + fileName);
        // 想要让客户端CellStyleModel可以访问到其他的首部信息，服务器不仅要在header里加入该首部，还要将它们在 Access-Control-Expose-Headers 里面列出来
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
    }

    /////////////////// Poi ///////////////////

    // 各个列的表头
    private String[] heardList;
    // 各个列的元素key值
    private String[] heardKey;
    // 需要填充的数据信息
    private List<Map> data;
    // 导出文件名称
    private String fileName;

    // 字体大小
    @Builder.Default
    private int fontSize = 12;
    @Builder.Default
    private String fontName = "微软雅黑";
    // 行高
    @Builder.Default
    private int rowHeight = 30;
    // 列宽
    @Builder.Default
    private int columnWidth = 20;
    // 工作表
    @Builder.Default
    private String sheetName = "sheet1";

    /**
     * 回调单元格样式
     * -
     * cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex()); // 是设置前景色不是背景色
     * cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
     */
    @FunctionalInterface
    public interface UpdateCellStyle {
        void invoke(String value, XSSFCellStyle cellStyle);
    }

    public void exportExcelByPoi(HttpServletResponse response) {
        exportExcelByPoi(response, null);
    }

    /**
     * Poi 导出功能
     * -
     * - java中的强制类型转换只是针对单个对象的，想要偷懒将整个数组转换成另外一种类型的数组是不行的. toArray 运行中已经转为 Object, 需要指定初始化类型
     * - 4.x打包引用会异常: HSSFCellStyle.BORDER_THIN 指向的是 CellStyle.BORDER_THIN. 另若引入 easyExcel, 无需再引入 poi [其依赖是低版本 poi]
     * - 数量限制: HSSF改为XSSF后，导出1000000条数据. HSSF最大容量为65535 [最大65536(2的16次方)行;即横向256个单元格，竖向65536个单元格]
     */
    public void exportExcelByPoi(HttpServletResponse response, UpdateCellStyle lambda) {
        // 检查参数配置信息
        checkConfig();
        // 创建工作簿
        XSSFWorkbook wb = new XSSFWorkbook();
        // 创建工作表
        XSSFSheet wbSheet = wb.createSheet(sheetName);
        // 设置默认行宽
        wbSheet.setDefaultColumnWidth(columnWidth);

        // 设置表格样式
        XSSFCellStyle styleHeader = createCellStyle(wb);
        // 设置表头字体
        XSSFFont fontHeader = wb.createFont();
        fontHeader.setFontHeightInPoints((short) fontSize);
        fontHeader.setFontName(fontName);
        fontHeader.setBold(true);
        styleHeader.setFont(fontHeader);

        //设置列头元素
        XSSFRow row = wbSheet.createRow(0);
        XSSFCell cellHead = null;
        for (int i = 0; i < heardList.length; i++) {
            cellHead = row.createCell(i);
            cellHead.setCellValue(heardList[i]);
            cellHead.setCellStyle(styleHeader);
        }

        // 设置表格样式
        XSSFCellStyle bodyStyle = createCellStyle(wb);
        // 设置表格字体
        XSSFFont fontBody = wb.createFont();
        fontBody.setFontHeightInPoints((short) fontSize);
        fontBody.setFontName(fontName);
        fontBody.setBold(false);
        bodyStyle.setFont(fontBody);

        //开始写入实体数据信息
        int a = 1;
        for (int i = 0; i < data.size(); i++) {
            XSSFRow roww = wbSheet.createRow(a);
            Map map = data.get(i);
            XSSFCell cell = null;
            for (int j = 0; j < heardKey.length; j++) {
                cell = roww.createCell(j);
                cell.setCellStyle(bodyStyle);
                Object valueObject = map.get(heardKey[j]);
                String value = null;
                if (valueObject == null) {
                    valueObject = "";
                }
                if (valueObject instanceof String) {
                    //取出的数据是字符串直接赋值
                    value = (String) map.get(heardKey[j]);
                } else if (valueObject instanceof Integer) {
                    //取出的数据是Integer
                    value = String.valueOf(((Integer) (valueObject)).floatValue());
                } else if (valueObject instanceof BigDecimal) {
                    //取出的数据是BigDecimal
                    value = String.valueOf(((BigDecimal) (valueObject)).floatValue());
                } else {
                    value = valueObject.toString();
                }
                cell.setCellValue(Strings.isNullOrEmpty(value) ? "" : value);
                // 独立设置单元格样式
                if (ObjectUtil.isNotNull(lambda)) {
                    XSSFCellStyle newBodyStyle = createCellStyle(wb);
                    newBodyStyle.setFont(fontBody);
                    lambda.invoke(value, newBodyStyle);
                    cell.setCellStyle(newBodyStyle);
                }
            }
            a++;
        }
        // 导出文件
        setResponseHeader(response, fileName, ".xls");
        try {
            // 取得输出流
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException ex) {
            throw new McException(McREnum.METHOD_EXECUTE);
        }
    }

    // 不同的 CellStyle 需要独立创建
    private XSSFCellStyle createCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle style = wb.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);     //下边框
        style.setBorderLeft(BorderStyle.THIN);       //左边框
        style.setBorderTop(BorderStyle.THIN);        //上边框
        style.setBorderRight(BorderStyle.THIN);      //右边框
        return style;
    }

    // 检查数据配置问题
    protected void checkConfig() {
        if (heardKey == null || heardList.length == 0) {
            McException.exceptionParam("列名数组不能为空或者为NULL");
        }
        if (fontSize < 0 || rowHeight < 0 || columnWidth < 0) {
            McException.exceptionParam("字体、宽度或者高度不能为负值");
        }
        if (Strings.isNullOrEmpty(fileName)) {
            McException.exceptionParam("导出文件名称不能为NULL");
        }
    }

    /////////////////// EasyExcel ///////////////////

    /**
     * 导出功能 EasyExcel [class支持map]
     * -
     * - Mac下, 若报错 Times 字体找不到, 下载安装一下, 不影响功能使用
     * - ClassPathResource, 需要打包/编译后才能访问到. 识别不是架包内内容
     */
    @SneakyThrows
    public static void exportListByTemplate(HttpServletResponse response, List dataList, Class dtoClass, @Nullable String fileName, String templateName) {
        InputStream inputStream = UtilFile.readPackageResource("templates/" + templateName);
        UtilExcel.setResponseHeader(response, fileName, ".xlsx");
        // 模板导出
        EasyExcel.write(response.getOutputStream(), dtoClass).withTemplate(inputStream).sheet().doFill(dataList);
    }

    /**
     * 列表与主表进行填充 [格式: 模板主表 {字段}, 列表 {.字段}]
     */
    @SneakyThrows
    public static void exportMapAndListByTemplate(HttpServletResponse response, Object dataMain, List dataList, Class dtoClass, @Nullable String fileName, String templateName) {
        InputStream inputStream = UtilFile.readPackageResource("templates/" + templateName);
        UtilExcel.setResponseHeader(response, fileName, ".xlsx");
        ExcelWriter workBook = EasyExcel.write(response.getOutputStream(), dtoClass).withTemplate(inputStream).build();
        WriteSheet sheet = EasyExcel.writerSheet("HL2025001").build();
        // 先单组数据填充，再多组数据填充
        workBook.fill(dataMain, sheet);
        workBook.fill(dataList, sheet);
        workBook.finish();
    }

    /**
     * 列表与主表进行填充 [格式: 模板主表 {字段}, 列表 {.字段}]
     */
    @SneakyThrows
    public static void exportMapAndListByTemplate(HttpServletResponse response, List dataList, Class dtoClass, @Nullable String fileName, String templateName) {
        InputStream inputStream = UtilFile.readPackageResource("templates/" + templateName);
        UtilExcel.setResponseHeader(response, fileName, ".xlsx");
        ExcelWriter workBook = EasyExcel.write(response.getOutputStream(), dtoClass).withTemplate(inputStream).build();
        WriteSheet sheet = EasyExcel.writerSheet().build();
        // 先单组数据填充，再多组数据填充
        workBook.fill(dataList, sheet);
        workBook.finish();
    }

}