关于父子结构的poi合并单元格导出

import cn.hutool.core.util.StrUtil;
import lombok.Data;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

/**
 * excel工具类
 *
 * @Author: qiu_wenzhe
 * @DATE: 2024/6/20 10:41
 * @Description:
 */
public class ExcelMergeUtil {

    /**
     * 多sheet导出
     *
     * @param sheetData
     * @param fileName
     * @param columns
     * @param mergeIndexs
     * @param isTree
     * @param response
     * @return
     */
    public static int exportMultiSheet(List<List<List<Object>>> sheetData, String fileName, List<String> columns, List<Integer> mergeIndexs,
                                       boolean isTree, HttpServletResponse response) {
        int flag = 0;
        XSSFWorkbook wb = new XSSFWorkbook();
        for (List<List<Object>> objData : sheetData) {
            XSSFSheet sheet = wb.createSheet();
            flag = exportToExcelForXlsx(objData, fileName, columns, mergeIndexs, isTree, response, wb, sheet);
        }
        OutputStream os = null;
        try {

            // 创建一个普通输出流
            os = response.getOutputStream();
            if (StrUtil.isEmpty(fileName)) {
                fileName = String.valueOf(System.currentTimeMillis());
            }
            // 请求浏览器打开下载窗口
            response.reset();
            response.setCharacterEncoding("UTF-8");
            // 设置响应信息，让浏览器下载文件
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            // 直接用数组缓冲输出流输出
            wb.write(os);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                wb.close();
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
                flag = 0;
            }
        }
//        //本地下载方式
//        FileOutputStream out = null;
//        try {
//            out = new FileOutputStream("D:\\" + fileName + ".xlsx");
//            wb.write(out);
//        } catch (Exception ex) {
//            try {
//                out.flush();
//                out.close();
//            } catch (IOException e) {
//                flag = 0;
//                e.printStackTrace();
//            }
//        }
        return flag;
    }

    /**
     * 单sheet导出
     *
     * @param objData
     * @param fileName
     * @param columns
     * @param mergeIndexs
     * @param isTree
     * @param response
     * @return
     */
    public static int exportSingleSheet(List<List<Object>> objData, String fileName, List<String> columns, List<Integer> mergeIndexs, boolean isTree, HttpServletResponse response) {
        // 创建工作薄
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet();
        int flag = exportToExcelForXlsx(objData, fileName, columns, mergeIndexs, isTree, response, wb, sheet);
        OutputStream os = null;
        try {

            // 创建一个普通输出流
            os = response.getOutputStream();
            if (StrUtil.isEmpty(fileName)) {
                fileName = String.valueOf(System.currentTimeMillis());
            }
            // 请求浏览器打开下载窗口
            response.reset();
            response.setCharacterEncoding("UTF-8");
            // 设置响应信息，让浏览器下载文件
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            // 直接用数组缓冲输出流输出
            wb.write(os);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                wb.close();
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
                flag = 0;
            }
        }
        return flag;
    }

    /**
     * 导出Excel
     *
     * @param objData
     * @param fileName
     * @param columns
     * @param mergeIndexs
     * @param isTree
     * @param response
     * @param wb
     * @param sheet
     * @return
     */
    public static int exportToExcelForXlsx(List<List<Object>> objData, String fileName,
                                           List<String> columns, List<Integer> mergeIndexs, boolean isTree,
                                           HttpServletResponse response,
                                           XSSFWorkbook wb, XSSFSheet sheet) {
        int flag = 0;
        Collections.sort(mergeIndexs);//将列号排序
        //设置样式
        XSSFCellStyle style = createStyle(wb);

        //标题头
//        sheet.createFreezePane(0, 1);//冻结表头
        XSSFRow sheet1row1 = sheet.createRow((short) 0);
        sheet1row1.setHeight((short) 480);
        XSSFCell title = sheet1row1.createCell(0);
        title.setCellValue(fileName); // 设置第一个单元格的内容
        XSSFCellStyle titleStyle = createStyle(wb);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        XSSFFont cellFont = wb.createFont();
        cellFont.setFontHeightInPoints((short) 18);
        cellFont.setBold(true);
        titleStyle.setFont(cellFont);
        title.setCellStyle(titleStyle);
        // 合并 A1 到 F1 这 6 个单元格
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, columns.size() - 1);
        sheet.addMergedRegion(region);
        //表头
        XSSFRow sheet1row2 = sheet.createRow((short) 1);
        sheet1row1.setHeight((short) 480);
        //写入表头
        if (columns != null && columns.size() > 0) {
            for (int i = 0; i < columns.size(); i++) {
                String column = columns.get(i);
                //列
                XSSFCell cell2 = sheet1row2.createCell(i);
                cell2.setCellValue(column);
                titleStyle = createStyle(wb);
                titleStyle.setAlignment(HorizontalAlignment.CENTER);
                XSSFColor color = new XSSFColor(new java.awt.Color(255, 255, 255));
                titleStyle.setFillForegroundColor(color);
                titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                cell2.setCellStyle(titleStyle);
            }
        }
        int dataSatrtIndex = 2;//数据开始行
        boolean isMerge = false;
        if (mergeIndexs != null && mergeIndexs.size() != 0) {
            isMerge = true;
        }
        //写入数据
        if (objData != null && objData.size() > 0) {
            Map<Integer, MergeModel> poiModels = new HashMap<Integer, MergeModel>();
            //循环写入表中数据
            int i = 0;
            for (; i < objData.size(); i++) {
                //数据行
                XSSFRow row = sheet.createRow((short) (i + dataSatrtIndex));
                //行内循环，既单元格（列）
                List<Object> list = objData.get(i);
                DecimalFormat decimalFormat = new DecimalFormat("0.00");
                int j = 0;
                for (Object o : list) {
                    //数据列
                    String content = "";
                    if (o != null) {
                        if (o.toString().contains(".") && isNumeric(o.toString())) {
                            content = decimalFormat.format(Float.valueOf(o.toString()));
                        } else if (o.toString().contains("-") && o.toString().contains(":")) {
                            content = String.valueOf(o).split("\\.")[0];
                        } else {
                            content = String.valueOf(o);
                        }
                    }
                    if (isMerge && mergeIndexs.contains(j)) {
                        //如果该列需要合并
                        ExcelMergeUtil.MergeModel poiModel = poiModels.get(j);
                        if (poiModel == null) {
                            poiModel = new ExcelMergeUtil().new MergeModel();
                            poiModel.setContent(content);
                            poiModel.setRowIndex(i + dataSatrtIndex);
                            poiModel.setCellIndex(j);
                            poiModels.put(j, poiModel);
                        } else {
                            if (!poiModel.getContent().equals(content)) {
                                //如果不同了，则将前面的数据合并写入
                                if (isTree) {
                                    //此列向后的所有列都进行一次写入合并操作，并清空。
                                    //树结构中存在这种情况，a目录和b目录为同级目录，a目录下最后一个子目录和b目录下的第一个子目录名称相同，防止本来不应该合并的单元格被合并
                                    addMergedRegionValue(wb, sheet, poiModels, mergeIndexs, i + dataSatrtIndex, poiModel.getCellIndex());
                                } else {
                                    XSSFRow lastRow = sheet.getRow(poiModel.getRowIndex());
                                    XSSFCell lastCell = lastRow.createCell(poiModel.getCellIndex());//创建列
                                    lastCell.setCellValue(poiModel.getContent());
                                    lastCell.setCellStyle(style);
                                    //合并单元格
                                    if (poiModel.getRowIndex() != i + dataSatrtIndex - 1) {
                                        sheet.addMergedRegion(new CellRangeAddress(poiModel.getRowIndex(), i + dataSatrtIndex - 1, poiModel.getCellIndex(), poiModel.getCellIndex()));
                                    }
                                }
                                //将新数据存入
                                poiModel.setContent(content);
                                poiModel.setRowIndex(i + dataSatrtIndex);
                                poiModel.setCellIndex(j);
                                poiModels.put(j, poiModel);
                            }
                        }
                        //创建单元格
                        XSSFCell cell = row.createCell(j);
                        cell.setCellStyle(style);
                    } else {//该列不需要合并
                        //数据列
                        XSSFCell cell = row.createCell(j);
                        cell.setCellValue(content);
                        cell.setCellStyle(style);
                    }
                    j++;
                }
            }
            //将最后一份存入
            if (poiModels != null && poiModels.size() != 0) {
                for (Integer key : poiModels.keySet()) {
                    MergeModel poiModel = poiModels.get(key);
                    XSSFRow lastRow = sheet.getRow(poiModel.getRowIndex());
                    XSSFCell lastCell = lastRow.getCell(poiModel.getCellIndex());
                    lastCell.setCellValue(poiModel.getContent());
                    lastCell.setCellStyle(style);
                    //合并单元格
                    if (poiModel.getRowIndex() != i + dataSatrtIndex - 1) {
                        sheet.addMergedRegion(new CellRangeAddress(poiModel.getRowIndex(), i + dataSatrtIndex - 1, poiModel.getCellIndex(), poiModel.getCellIndex()));
                    }
                }
            }
        } else {
            flag = -1;
        }
        //设置固定列宽，poi的列宽设置有点操蛋，大概规律网上有不少版本自行百度
        //这里大概是143像素
        for (int i = 0; i < columns.size(); i++) {
            sheet.setColumnWidth(i, 4550);
        }
        return flag;
    }

    /**
     * 判断是不是数字
     *
     * @param str
     * @return
     */
    private static boolean isNumeric(String str) {
        if (str == null || str.length() == 0) {
            return false;
        }
        Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$");
        return pattern.matcher(str).matches();
    }

    private static void addMergedRegionValue(XSSFWorkbook wb, XSSFSheet sheet, Map<Integer, MergeModel> poiModels, List<Integer> mergeIndex, int nowRowIndex, int nowCellIndex) {
        if (poiModels != null && poiModels.size() != 0 && mergeIndex != null && mergeIndex.size() != 0) {
            for (Integer index : mergeIndex) {
                if (index >= nowCellIndex) {
                    MergeModel poiModel = poiModels.remove(index);//删除并获取value
                    if (poiModel != null) {
                        XSSFRow lastRow = sheet.getRow(poiModel.getRowIndex());
                        XSSFCell lastCell = lastRow.createCell(poiModel.getCellIndex());//创建列
                        lastCell.setCellValue(poiModel.getContent());
                        lastCell.setCellStyle(createStyle(wb));
                        //合并单元格
                        if (poiModel.getRowIndex() != nowRowIndex - 1) {
                            sheet.addMergedRegion(new CellRangeAddress(poiModel.getRowIndex(), nowRowIndex - 1, poiModel.getCellIndex(), poiModel.getCellIndex()));
                        }
                    }
                }
            }
        }
    }

    private static XSSFCellStyle createStyle(XSSFWorkbook wb) {
        XSSFCellStyle style = wb.createCellStyle();
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        return style;
    }

    /**
     * 用于保存需要合并的单元格
     *
     * @Author: qiu_wenzhe
     * @DATE: 2024/6/20 10:43
     * @Description:
     */
    @Data
    public class MergeModel {
        /**
         * 内容
         **/
        private String content;
        /**
         * 记录相同内容的开始行号
         **/
        private int rowIndex;
        /**
         * 列号
         */
        private int cellIndex;
    }
}

调用方法：

public static void main(String[] args) {
        //标头
        List<String> columns = CollUtil.newArrayList("经办机构代码", "经办机构名称", "机构级别", "时间维度", "年度", "时间"
                , "参保人数", "经办人数", "业务涉及金额(元)", "业务人次数(人次)", "指标编码", "指标名称",
                "填报方式", "指标维度", "数据字段", "数据内容");
        //需要合并的列号
        List<Integer> mergeIndex = new ArrayList<Integer>();
        for (int i = 0; i < 14; i++) {
            mergeIndex.add(i);
        }
        // sheet 数据
        List<List<List<Object>>> sheetData = new ArrayList<>();
        //内容数据
        List<List<Object>> exportData = null;
        List<Object> rowData = null;
        for (int i = 0; i < 10; i++) {
            exportData = new ArrayList<>();
            rowData = new ArrayList<>();
            for (int j = 0; j < columns.size(); j++) {
                rowData.add(j);
            }
            exportData.add(rowData);
        }
        sheetData.add(exportData);
        ExcelMergeUtil.exportMultiSheet(sheetData, "fileName", columns, mergeIndex, true, response);
    }

运行结果如下：