封装的POI导出EXCEL(SXSSFWorkbook),支持单元格合并,多sheet导出,自适应列宽,特殊标题定制,数字类型数据自定义格式导出,大数据量导出,大数据分批导出成一个excel

封装的POI导出EXCEL(SXSSFWorkbook),支持单元格合并,多sheet导出,自适应列宽,特殊标题定制,数字类型数据自定义格式导出,大数据量导出,大数据分批导出成一个excel

一.导出对象

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;

/**
 * @Author : tangwenchao
 * @Description excel导出对象
 * @Date 2022/3/17 13:51
 */
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ExportDto implements Serializable {

    private static final long serialVersionUID = -2194048487324978603L;
    /**
     * 显示的导出表的sheet名字
     */
    private String sheetName;

    /**
     * 显示的导出表的标题
     */
    private String title;

    /**
     * 标题的方法名称
     * 方法必须定义在IExclUtil或IDownloadExclUtil,看使用的哪个导出工具类
     */
    private String titleStyleMethod;

    /**
     * 是否是特殊格式标题
     * 其中false为常规标题 常规标题占两行
     * true为特殊格式标题
     * 默认为常规标题
     */
    @Builder.Default
    private Boolean specialTitle = false;

    /**
     * 需要导出的数据集合
     */
    private List<HashMap<String, Object>> dataList;

    /**
     * 需要导出的数据列名与字段名映射(excel列名)
     */
    private LinkedHashMap<String, String> keyNameMap;

    /**
     * 从第几行开始创建列名(从0开始,数据是rowRowNum+1)  目的是为了方便留出标题,可自定义定义标题
     * 注意,有标题的话,rowRowNum必须>=1
     * 此处rowRowNum表示的是列头开始的位置,即keyNameMap的位置,数据位置后面都处理好了
     */
    private int rowRowNum;

    /**
     * 数据是否需要合并单元格
     * 注意: 合并单元格时,只有前后两条数据一致,才会合并,请提前对要合并的数据进行排序
     * 默认不需要合并
     */
    @Builder.Default
    private Boolean needMergedData = false;

    /**
     * 合并基准列(以某一列或某几列为基准)
     */
    private Integer[] mergeBasis;

    /**
     * 要合并的列(从0开始)
     */
    private Integer[] mergeCells;

    /**
     * 特殊格式集合,一个格式一个ExcelStyleDto,当有特殊格式时,必须要涉及所有的数据列
     * 没有特殊格式可不传
     */
    private List<ExcelStyleDto> styleList;

二.excel导出特殊格式对象

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.poi.ss.usermodel.CellStyle;

/**
 * @Author : tangwenchao
 * @Description excel导出特殊格式对象
 * 一个格式一个ExcelStyleDto,当有特殊格式时,必须要涉及所有的数据列
 * @Date 2022/7/18 11:18
 */
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ExcelStyleDto {
    /**
     * 特殊格式里,数据是否为文本类型
     * 默认为是文本类型
     */
    @Builder.Default
    private Boolean isString = true;

    /**
     * 列(从0开始),如果使用了该特殊样式对象,colum需要包含导出excel所有的列
     */
    private Integer[] colum;
    /**
     * 数字类型导出excel的形式(四舍五入)
     * 整数 0
     * 一位小数 0.0
     * 两位小数 0.00
     * 三位小数 0.000
     * 两位百分数 0.00%  (会将原始数据乘以100)
     */
    private String format;

    /**
     * 设置水平对齐的样式
     * 就是poi的样式
     * 数据在单元格中的位置,默认在中间
     */
    @Builder.Default
    private short alignment = CellStyle.ALIGN_CENTER;

    /**
     * 当数字类型导出强制格式的小数点位数太多时,使用此参数微调列宽
     * 否则excel显示有问题
     * 默认为0,
     */
    @Builder.Default
    private Integer length = 0;

}

三.导出工具类

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.shiro.util.CollectionUtils;
import org.springframework.util.ReflectionUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;

/*
 *
 * DownloadExcelUtil
 * 新版导出Excl工具类
 * @author tangwenchao
 * @date 2022/7/19
 *
 */
public class DownloadExcelUtil implements IDownloadExclUtil {
    //设置-1表示不限制大小,否则后面shee.getRow时一直为null,因为默认窗口100,就拿不到前面的了
    //否则合并单元格会出错
    SXSSFWorkbook workbook = new SXSSFWorkbook(-1);

    @Override
    public List handlerExcel(Workbook wb) {
        return null;
    }


    //导出1个sheet页
    public void exportOneSheetSXSS(String fileName, ExportDto exportDto, HttpServletRequest request, HttpServletResponse response) {
        try {
            oneSheet(exportDto);
            exportSXSS(fileName, workbook, response, request);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                //使用完毕后将产生的临时文件删除 防止将磁盘搞满
                workbook.dispose();
            }
        }
    }

    //导出多个sheet页
    public void exportNSheetSXSS(String fileName, List<ExportDto> exportDto, HttpServletRequest request, HttpServletResponse response) {
        try {
            for (ExportDto dto : exportDto) {
                oneSheet(dto);
            }
            exportSXSS(fileName, workbook, response, request);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                //使用完毕后将产生的临时文件删除 防止将磁盘搞满
                workbook.dispose();
            }
        }
    }

    public void oneSheet(ExportDto exportDto) throws IllegalAccessException, InvocationTargetException {
        //设置标题和单元格样式
        CellStyle columnTopStyle = IDownloadExclUtil.getColumnTopStyle(workbook);  //获取列头样式对象
        CellStyle style = IDownloadExclUtil.getStyleXSS(workbook);
        SXSSFSheet sheet = workbook.createSheet(exportDto.getSheetName());                     // 创建工作表
        //设置报表title
        String title = exportDto.getTitle();
        LinkedHashMap<String, String> keyNameMap = exportDto.getKeyNameMap();//列名映射关系
        List<HashMap<String, Object>> dataList = exportDto.getDataList();//数据
        int columnNum = keyNameMap.size();  // 定义所需列数
        int rowRowNum = exportDto.getRowRowNum();
        if (StringUtils.isNotBlank(title)) {
            Boolean specialTitle = exportDto.getSpecialTitle();
            //是特殊标题
            if (specialTitle) {
                //样式都放在IDownloadExclUtil里,故写死
                Method method = ReflectionUtils.findMethod(IDownloadExclUtil.class, exportDto.getTitleStyleMethod(), null);
                CellStyle styleTital = (CellStyle) method.invoke(new SXSSFWorkbook(), workbook);
                setSpeciaTitleXSS(sheet, styleTital, columnNum, rowRowNum, title);
            } else {
                setTitleSXSS(sheet, columnTopStyle, columnNum, title);
            }
        }
        //设置标题信息
        Map<Integer, Integer> sizeMap = setHeadColumSXSS(sheet, columnTopStyle, rowRowNum, keyNameMap);
        //将查询出的数据设置到sheet对应的单元格中,并自适应长度
        if (!CollectionUtils.isEmpty(dataList)) {
            initDataSXSSAndAutoExpandColumWidth(workbook, sheet, style, rowRowNum, dataList, keyNameMap, sizeMap, exportDto.getStyleList());
            if (exportDto.getNeedMergedData()) {
                //合并单元格的方法
                merge(sheet, style, rowRowNum, exportDto.getMergeBasis(), exportDto.getMergeCells(), workbook);
            }
        } else {
            //当没有数据的时候,仅需调整标题的列宽,至于标题,没考虑
            setColumWidth(sheet, sizeMap);
        }
    }


    /**
     * 调用方式:
     * 1.创建SXSSFWorkbook对象
     * 2.调用iniTitletAndHeadColum创建excel与表头标题
     * 3.循环获取数据,并依次调用initData方法设置数据,注意rowRowNum
     * 4.调用autoExpandColumWidth设置单元格列宽
     * 5.调用exportBigSXSS导出
     * 6.finally释放SXSSFWorkbook对象
     */
    //大数据量下,机器内存不够,需要分批次查询,此方法仅创建excel与表头标题,不涉及数据
    public Map<String, Object> iniTitletAndHeadColum(String sheetName, String title, LinkedHashMap<String, String> keyNameMap, int rowRowNum, SXSSFWorkbook workbook) {
        // 工作簿对象在调用处创建,最后要finally释放 //workbook = new SXSSFWorkbook();
        // 打开压缩功能 防止占用过多磁盘
        workbook.setCompressTempFiles(true);
        //设置标题和单元格样式
        CellStyle columnTopStyle = IDownloadExclUtil.getColumnTopStyle(workbook);  //获取列头样式对象
        CellStyle style = IDownloadExclUtil.getStyleXSS(workbook);                    //单元格样式对象
        int columnNum = keyNameMap.size();  // 定义所需列数
        SXSSFSheet sheet = workbook.createSheet(sheetName);// 创建工作表
        //设置报表title
        setTitleSXSS(sheet, columnTopStyle, columnNum, title);
        //设置标题信息
        Map<Integer, Integer> sizeMap = setHeadColumSXSS(sheet, columnTopStyle, rowRowNum, keyNameMap);
        HashMap<String, Object> map = new HashMap<>();
        map.put("sheet", sheet);
        map.put("style", style);
        map.put("sizeMap", sizeMap);
        map.put("workbook", workbook);
        return map;
    }

    // 大数据量下,机器内存不够,需要分批次查询,此方法仅为填充数据
    //需要多次调用,返回长度map
    public Map<Integer, Integer> initData(SXSSFSheet sheet, CellStyle style, int rowRowNum, List<HashMap<String, Object>> dataList, LinkedHashMap<String, String> keyNameMap, Map<Integer, Integer> sizeMap) {
        if (!CollectionUtils.isEmpty(dataList)) {
            sizeMap = setOnlyStringData(sheet, style, rowRowNum, dataList, keyNameMap, sizeMap);
        }
        return sizeMap;
    }

    // 大数据量下,机器内存不够,需要分批次查询,此方法仅为设置列宽,填充完所有数据后调用
    public void autoExpandColumWidth(SXSSFSheet sheet, Map<Integer, Integer> sizeMap) {
        setColumWidth(sheet, sizeMap);
    }

    //大数据量下,机器内存不够,需要分批次查询,仅为导出最后的excel,不涉及workbook释放
    public void exportBigSXSS(String fileName, SXSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) {
        //导出数据 // 工作簿对象在调用处finally释放
        exportSXSS(fileName, workbook, response, request);
    }
}

四.底层接口方法

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.shiro.util.CollectionUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;

/*
 *
 * IExpExclUtil
 * 导出Excl
 * @author tangwenchao
 * @date 2022/7/19
 *
 */

public interface IDownloadExclUtil<T> {

    List<T> handlerExcel(Workbook wb);


    default void setTitleSXSS(SXSSFSheet sheet, CellStyle columnTopStyle, int columnNum, String tileName) {
        // 产生表格标题行
        SXSSFRow rowm = sheet.createRow(0);
        SXSSFCell cellTiltle = rowm.createCell(0);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, columnNum - 1));
        cellTiltle.setCellStyle(columnTopStyle);
        cellTiltle.setCellValue(tileName);
    }


    default void setSpeciaTitleXSS(SXSSFSheet sheet, CellStyle columnTopStyle, int columnNum, int rowRowNum, String tileName) {
        // 产生表格标题行
        SXSSFRow rowm = sheet.createRow(0);
        SXSSFCell cellTiltle = rowm.createCell(0);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, rowRowNum - 1, 0, columnNum - 1));
        cellTiltle.setCellStyle(columnTopStyle);
        cellTiltle.setCellValue(tileName);
    }


    /*
     *
     * 设置列名,返回所有列名的索引及对应的列名长度
     * @param sheet
     * @param columnTopStyle
     * @param rowRowNum
     * @param keyNameMap
     * @return 所有列名的索引及对应的列名长度,key是索引,value是长度(一定要用索引做key)
     */
    default Map<Integer, Integer> setHeadColumSXSS(SXSSFSheet sheet, CellStyle columnTopStyle, int rowRowNum, LinkedHashMap<String, String> keyNameMap) {
        // 将列头设置到sheet的单元格中
        SXSSFRow rowRowName = sheet.createRow(rowRowNum);
        int sizeIndex = 0;
        Map<Integer, Integer> sizeMap = new HashMap<>();
        for (Map.Entry<String, String> entry : keyNameMap.entrySet()) {
            SXSSFCell cellRowName = rowRowName.createCell(sizeIndex);//创建列头对应个数的单元格
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);//设置列头单元格的数据类型
            String value = entry.getValue();
            cellRowName.setCellValue(value);//设置列头单元格的值
            cellRowName.setCellStyle(columnTopStyle);
            sizeMap.put(sizeIndex, Math.max(0, value.getBytes().length));
            ++sizeIndex;
        }
        return sizeMap;
    }

    /*
     *
     * 将查询出的数据设置到sheet对应的单元格中,并自适应长度
     * @param sheet
     * @param style
     * @param rowRowNum
     * @param dataList
     * @param keyNameMap
     * @param sizeMap 长度map
     */
    default void initDataSXSSAndAutoExpandColumWidth(SXSSFWorkbook workbook, SXSSFSheet sheet, CellStyle style, int rowRowNum, List<HashMap<String, Object>> dataList, LinkedHashMap<String, String> keyNameMap, Map<Integer, Integer> sizeMap, List<ExcelStyleDto> styleList) {
        //如果数据不是空,在设置数据
        sizeMap = setData(workbook, sheet, style, rowRowNum, dataList, keyNameMap, sizeMap, styleList);
        //列的索引做长度集合的key
        //调整列宽
        setColumWidth(sheet, sizeMap);
    }

    default void setColumWidth(SXSSFSheet sheet, Map<Integer, Integer> sizeMap) {
        for (Integer cellIndex : sizeMap.keySet()) {
            int width = Math.min(65280, (sizeMap.get(cellIndex) + 2) * 256);
            sheet.setColumnWidth(cellIndex, width);
        }
    }


    /**
     * 导出报表
     *
     * @param fileName
     * @param workbook
     * @param response
     * @param request
     */
    default void exportSXSS(String fileName, SXSSFWorkbook workbook, HttpServletResponse response, HttpServletRequest request) {
        try (OutputStream ouputStream = response.getOutputStream()) {
            response.setCharacterEncoding("UTF-8");
            response.setContentType("multipart/form-data");
            String userAgent = request.getHeader("User-Agent");
            byte[] bytes = userAgent.contains("MSIE") ? fileName.getBytes() : fileName.getBytes("UTF-8"); // fileName.getBytes("UTF-8")处理safari的乱码问题
            String fileNameEncode = new String(bytes, "ISO-8859-1"); // 各浏览器基本都支持ISO编码
            response.setHeader("Content-disposition",
                    String.format("attachment; filename=\"%s\"", fileNameEncode + ".xlsx"));
            workbook.write(ouputStream);
            ouputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    static CellStyle getColumnTopStyle(SXSSFWorkbook workbook) {
        // 设置字体
        Font font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 11);
        //字体加粗
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        CellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
        //设置底边框;
        style.setBorderBottom(CellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(CellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(CellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(CellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
//        style.setFillPattern(CellStyle.FINE_DOTS );
//        style.setFillBackgroundColor(HSSFColor.GREEN.index);// 设置背景颜色
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(CellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        return style;
    }


    static CellStyle getStyleXSS(SXSSFWorkbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
        //设置底边框;
        style.setBorderBottom(CellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(CellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(CellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(CellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
//            style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(CellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        return style;
    }


    default void merge(SXSSFSheet sheet, CellStyle style, int rowRowNum, Integer[] mergeBasis, Integer[] mergeCells, SXSSFWorkbook workbook) {
        List<List<Map<String, Integer>>> arrayList = new ArrayList<>();
        if (mergeBasis != null && mergeBasis.length > 0 && mergeCells != null && mergeCells.length > 0) {
            for (int i = 0; i < mergeCells.length; i++) {
                //1.先找到当前sheet中所有需要合并的范围,不能一列一列合并,否则会影响基准列的判断,导致非基准列无法合并
                List<Map<String, Integer>> list = mergedRegionXSS(sheet, mergeCells[i], rowRowNum + 1, sheet.getLastRowNum(), mergeBasis);
                arrayList.add(list);
            }
        }
        //2.合并当前sheet中的单元格
        if (!arrayList.isEmpty()) {
            for (List<Map<String, Integer>> list : arrayList) {
                mergedCellXSS(sheet, list);
            }
        }
    }


    static List<Map<String, Integer>> mergedRegionXSS(SXSSFSheet sheet, int cellLine, int startRow, int endRow, Integer[] mergeBasis) {
        String s_will = getCellValue(sheet.getRow(startRow).getCell(cellLine));// 获取第一行的数据,以便后面进行比较(不要列头)
        int count = 0;
        Set<Integer> set = new HashSet<Integer>();
        List<Map<String, Integer>> list = new ArrayList<>();
        org.apache.commons.collections.CollectionUtils.addAll(set, mergeBasis);
        for (int i = startRow + 1; i <= endRow; i++) {
            String s_current = getCellValue(sheet.getRow(i).getCell(cellLine));
            Map<String, Integer> map = new HashMap<>();
            if (s_will.equals(s_current)) {
                boolean isMerge = true;
                if (!set.contains(cellLine)) {//如果不是作为基准列的列 需要所有基准列都相同
                    for (int j = 0; j < mergeBasis.length; j++) {
                        if (!getCellValue(sheet.getRow(i).getCell(mergeBasis[j]))
                                .equals(getCellValue(sheet.getRow(i - 1).getCell(mergeBasis[j])))) {
                            isMerge = false;
                        }
                    }
                } else {//如果作为基准列的列 只需要比较列号比本列号小的列相同
                    for (int j = 0; j < mergeBasis.length && mergeBasis[j] < cellLine; j++) {
                        if (!getCellValue(sheet.getRow(i).getCell(mergeBasis[j]))
                                .equals(getCellValue(sheet.getRow(i - 1).getCell(mergeBasis[j])))) {
                            isMerge = false;
                        }
                    }
                }
                if (isMerge) {
                    count++;
                } else {
                    map.put("startRow", startRow);
                    map.put("endRow", startRow + count);
                    map.put("column", cellLine);
                    list.add(map);
                    startRow = i;
                    s_will = s_current;
                    count = 0;
                }
            } else {
                map.put("startRow", startRow);
                map.put("endRow", startRow + count);
                map.put("column", cellLine);
                list.add(map);
                startRow = i;
                s_will = s_current;
                count = 0;
            }
            if (i == endRow && count > 0) {
                map.put("startRow", startRow);
                map.put("endRow", startRow + count);
                map.put("column", cellLine);
                list.add(map);
            }
        }
        return list;
    }


    static void mergedCellXSS(SXSSFSheet sheet, List<Map<String, Integer>> list) {
        if (!list.isEmpty()) {
            for (Map<String, Integer> map : list) {
                Integer startRowMerged = map.get("startRow");
                Integer endRowMerged = map.get("endRow");
                Integer column = map.get("column");
                //第一行减最后一行大于1,则一定需要合并
                if (endRowMerged - startRowMerged >= 1) {
                    //从第二行开始改数据,设置成空,解决假合并excel计算问题
                    // 第一行数据要做合并后的展示数据,因此不能改第一行数据
                    for (int row = startRowMerged + 1; row <= endRowMerged; row++) {
                        SXSSFRow rowcell = sheet.getRow(row);
                        SXSSFCell cell1 = rowcell.getCell(column);
                        //只能设置为空字符,否则会影响其他数据精度格式
                        cell1.setCellValue("");
                    }
                    //合并
                    sheet.addMergedRegion(new CellRangeAddress(startRowMerged, endRowMerged, column, column));
                }
            }
        }
    }


    /*
     * Description: 获取单元格的值
     * @param cell
     * @return 单元格中的值
     */

    public static String getCellValue(Cell cell) {
        String value = "";
        if (cell == null) {
            return null;
        }
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                value = cell.getNumericCellValue() + "";
                break;
            case Cell.CELL_TYPE_STRING:
                value = cell.getRichStringCellValue() + "";
                break;
            case Cell.CELL_TYPE_FORMULA:
                value = cell.getCellFormula();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue() + "";
                break;
            default:
                break;
        }
        return value.trim();
    }


    default Map<Integer, Integer> setData(SXSSFWorkbook workbook, SXSSFSheet sheet, CellStyle style, int rowRowNum, List<HashMap<String, Object>> dataList, LinkedHashMap<String, String> keyNameMap, Map<Integer, Integer> sizeMap, List<ExcelStyleDto> styleList) {
        //如果没有特殊格式要求,就正常纯文本形式输出
        if (CollectionUtils.isEmpty(styleList)) {
            setOnlyStringData(sheet, style, rowRowNum, dataList, keyNameMap, sizeMap);
        } else {
            //如果有特殊格式要求
            //1.先创建sheet行
            for (int i = 0; i < dataList.size(); i++) {
                sheet.createRow(i + rowRowNum + 1);//创建所需的行数
            }
            //2.循环特殊格式list
            for (ExcelStyleDto excelStyle : styleList) {
                Integer[] colum = excelStyle.getColum();
                short alignment = excelStyle.getAlignment();
                boolean isString = excelStyle.getIsString();
                CellStyle styleXSS = getStyleXSS(workbook);
                styleXSS.setAlignment(alignment);
                //3.1每种需要判断是否为字符串,是字符串就按照纯文本输出
                if (isString) {
                    setHasStyleStringData(sheet, style, rowRowNum, dataList, keyNameMap, sizeMap, colum, styleXSS);
                } else {
                    //3.2 不是字符串,也就是数字格式
                    setHasStyleNumberData(workbook, sheet, style, rowRowNum, dataList, keyNameMap, sizeMap, excelStyle, colum, styleXSS);
                }
            }
        }
        return sizeMap;
    }

    //  //设置有特殊格式要求的数字类型导出
    default void setHasStyleNumberData(SXSSFWorkbook workbook, SXSSFSheet sheet, CellStyle style, int rowRowNum, List<HashMap<String, Object>> dataList, LinkedHashMap<String, String> keyNameMap, Map<Integer, Integer> sizeMap, ExcelStyleDto excelStyle, Integer[] colum, CellStyle styleXSS) {
        DataFormat df = workbook.createDataFormat();
        String format = excelStyle.getFormat();
        styleXSS.setDataFormat(df.getFormat(format));//数据格式只显示整数
        for (int a = 0; a < colum.length; a++) {
            Integer dataCum = colum[a];
            for (int i = 0; i < dataList.size(); i++) {
                int cellIndex = 0;
                for (Map.Entry<String, String> entry : keyNameMap.entrySet()) {
                    if (cellIndex == dataCum) {
                        HashMap<String, Object> obj = dataList.get(i);//遍历每个对象
                        SXSSFRow row = sheet.getRow(i + rowRowNum + 1);//获取行
                        SXSSFCell cell = row.createCell(cellIndex);
                        String key = entry.getKey();
                        Object o = obj.get(key);
                        if (o != null) {
                            cell.setCellStyle(styleXSS);
                            try {
                                double value = Double.parseDouble(o.toString());
                                cell.setCellValue(value);
                                //3.2.1 此处获取length是因为如果强制小数点位数太多的话,长度会少算,展示时会有影响.这里是对长度做了微调
                                int length = excelStyle.getLength();
                                int length1 = o.toString().length();
                                sizeMap.put(cellIndex, Math.max(sizeMap.get(cellIndex), o.toString().length() + length));
                                //3.2.2 可能有些列里面掺杂了其他逻辑,返回的并不是纯数字形式
                                //捕获异常处理成文本形式
                            } catch (Exception e) {
                                String s = o.toString();
                                cell.setCellValue(s);
                                sizeMap.put(cellIndex, Math.max(sizeMap.get(cellIndex), s.length()));
                            }
                        } else {
                            cell.setCellStyle(style);
                            cell.setCellValue("");
                            sizeMap.put(cellIndex, sizeMap.get(cellIndex));
                        }
                    }
                    ++cellIndex;
                }
            }
        }
    }

    //设置有特殊格式要求的文本类型导出
    default void setHasStyleStringData(SXSSFSheet sheet, CellStyle style, int rowRowNum, List<HashMap<String, Object>> dataList, LinkedHashMap<String, String> keyNameMap, Map<Integer, Integer> sizeMap, Integer[] colum, CellStyle styleXSS) {
        for (int a = 0; a < colum.length; a++) {
            Integer dataCum = colum[a];
            for (int i = 0; i < dataList.size(); i++) {
                int cellIndex = 0;
                for (Map.Entry<String, String> entry : keyNameMap.entrySet()) {
                    if (cellIndex == dataCum) {
                        HashMap<String, Object> obj = dataList.get(i);//遍历每个对象
                        SXSSFRow row = sheet.getRow(i + rowRowNum + 1);//获取行
                        SXSSFCell cell = row.createCell(cellIndex);
                        String key = entry.getKey();
                        Object o = obj.get(key);
                        if (o != null) {
                            cell.setCellStyle(styleXSS);
                            String value = o.toString();
                            cell.setCellValue(value);
                            int length = value.getBytes().length;
                            sizeMap.put(cellIndex, Math.max(sizeMap.get(cellIndex), value.getBytes().length));
                        } else {
                            cell.setCellStyle(style);
                            cell.setCellValue("");
                            sizeMap.put(cellIndex, sizeMap.get(cellIndex));
                        }
                    }
                    ++cellIndex;
                }
            }
        }
    }

    default Map<Integer, Integer> setOnlyStringData(SXSSFSheet sheet, CellStyle style, int rowRowNum, List<HashMap<String, Object>> dataList, LinkedHashMap<String, String> keyNameMap, Map<Integer, Integer> sizeMap) {
        for (int i = 0; i < dataList.size(); i++) {
            HashMap<String, Object> obj = dataList.get(i);//遍历每个对象
            SXSSFRow row = sheet.createRow(i + rowRowNum + 1);//创建所需的行数
            int cellIndex = 0;
            for (Map.Entry<String, String> entry : keyNameMap.entrySet()) {
                SXSSFCell cell = null;
                cell = row.createCell(cellIndex);
                String key = entry.getKey();
                Object o = obj.get(key);
                if (o != null) {
                    String value = o.toString();
                    cell.setCellValue(value);
                    sizeMap.put(cellIndex, Math.max(sizeMap.get(cellIndex), value.getBytes().length));
                } else {
                    cell.setCellValue("");
                    sizeMap.put(cellIndex, sizeMap.get(cellIndex));
                }
                cell.setCellStyle(style);
                ++cellIndex;
            }
        }
        return sizeMap;
    }

}

五.调用方式

5.1 含有特殊格式的

        ExportDto exportDto1 = ExportDto.builder().sheetName("sheet名").keyNameMap(keymap1()).dataList(data).rowRowNum(2).needMergedData(true).mergeBasis(new Integer[]{0}).mergeCells(new Integer[]{0, 1, 2}).title("标题").specialTitle(false).build();
        ExcelStyleDto excelStyleDto = ExcelStyleDto.builder().colum(new Integer[]{0, 3, 4, 6, 7}).build();
        ExcelStyleDto excelStyleDto2 = ExcelStyleDto.builder().colum(new Integer[]{1, 2, 5}).isString(false).format("0.00").build();
        List<ExcelStyleDto> list = new ArrayList<>();
        list.add(excelStyleDto);
        list.add(excelStyleDto2);
        exportDto1.setStyleList(list);
        DownloadExcelUtil downloadExcel = new DownloadExcelUtil();
        downloadExcel.exportOneSheetSXSS("导出文件名", exportDto1, request, response);

5.2 无特殊格式的

        ExportDto exportDto1 = ExportDto.builder().sheetName("sheet名").keyNameMap(keymap1()).dataList(data).rowRowNum(2).needMergedData(true).mergeBasis(new Integer[]{0}).mergeCells(new Integer[]{0, 1, 2}).title("标题").specialTitle(false).build();
       
        DownloadExcelUtil downloadExcel = new DownloadExcelUtil();
        downloadExcel.exportOneSheetSXSS("导出文件名", exportDto1, request, response);