Excel导出动态一级二级表头

导出Excel动态显示一级或二级表头
1、引人po相关i依赖包

 <dependency>
      <groupId>org.apache.poi</groupId>
         <artifactId>poi</artifactId>
         <version>4.1.0</version>
 </dependency>
 <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>4.1.0</version>
  </dependency>
 <dependency>
     <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml-schemas</artifactId>
      <version>4.1.0</version>
</dependency>

2、封装导出工具类，具体编写导出工具类操作，导出数据、导出头部数据可以动态传入。

public String generateExcelWithMember() throws Exception {
        // XSSFWorkbook workbook = null; //数据量小可以使用，大数据量会导致内存溢出
        SXSSFWorkbook workbook = new SXSSFWorkbook(3000);
        FileOutputStream output = null;
        List<List<String>> dataList = new ArrayList<>();
        try {
             // 获取平台设置的密码,excel不可编辑密码
            String passwordValue = "123456";// 可动态获取

            // 获取平台水印文案描述
            String watermarkValue = "EXCEL水印文案";//可动态获取

            String fileName = "excel_watermark_export20201219160601.xlsx";// 文件名
            String filePath = "导出路径";//导出路径
            // 检查目录是否存在
            File dir = new File(filePath);
            FileUtils.forceMkdir(dir);
            File file = new File(dir, fileName);

            // 标题
            Map<String, List<String>> titleMap = new HashMap<>();// 需要动态获取标题

            // 内容
            dataList = new ArrayList<>;//需要动态获取内容

            // 权限数据
            Map<Integer, Object> authSet =  new HashMap<>();// 可通过数据权限控制标题是否显示

            Map<Integer, Integer> sheetLinePage = new HashMap<>();
            sheetLinePage.put(1, dataList.size() + 1);

 List<ExcelHeader> excelHeaderList = this.getExcelHeader(apiVersion, titleMap, authSet);
            CellStyle[][] cellStyleArrays = this.getCellStyle(workbook, dataList, excelHeaderList);

            workbook = ExcelHelper.writeDataToExcel(2, excelHeaderList, dataList,
                    sheetLinePage, null, cellStyleArrays, passwordValue, watermarkValue);

            output = new FileOutputStream(file);
            workbook.write(output);
            return filePath + File.separatorChar + fileName;
        } catch (Exception ex) {
            log.error(this.getClass().getName(), ex, "EXPORT_ERROR_导出异常");
            throw new Exception(ex);
        } finally {
            CollectionUtils.recycle(dataList);// 可自行封装
            try {
                if (output != null) {
                    output.close();
                }
            } catch (IOException e) {
                throw new Exception(e);
            }
        }
    }

    /**
     * 获取标题某一列的索引
     *
     * @param headerList 标题
     * @param columnName 列名
     * @return 索引
     */
    private int getHeaderIndex(List<ExcelHeader> headerList, String columnName) {
        int index = NumberUtils.INTEGER_ZERO;
        for (ExcelHeader excelHeader : headerList) {
            List<ExcelHeader> subExcelHeaderList = excelHeader.getSubExcelHeader();
            if (CollectionUtils.isNotEmpty(subExcelHeaderList)) {
                for (ExcelHeader subExcelHeader : subExcelHeaderList) {
                    if (subExcelHeader.getName().equals(columnName)) {
                        return index;
                    }
                    ++index;
                }
            } else if (excelHeader.getName().equals(columnName)) {
                return index;
            } else {
                ++index;
            }
        }
        return NumberUtils.INTEGER_MINUS_ONE;
    }

 /**
  * 组装单元格样式
  *
  * @param workbook   工作簿
  * @param dataList   数据
  * @param headerList 已经组装好的标题
  * @return 单元格样式
  */
private CellStyle[][] getCellStyle(SXSSFWorkbook workbook, List<List<String>> dataList, List<ExcelHeader> headerList) {
        int headerSize = this.getExcelHeaderColumnSize(headerList);

        // 单元格背景色
        CellStyle foregroundColorCellStyle = workbook.createCellStyle();
        foregroundColorCellStyle.setFillForegroundColor(IndexedColors.GOLD.getIndex());
        foregroundColorCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 字体颜色
        CellStyle fontColorCellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setColor(IndexedColors.RED.getIndex());
        font.setFontHeightInPoints((short) 12);
        fontColorCellStyle.setFont(font);

        // 有加背景色的字段1
        int buyingPriceColumnIndex = this.getHeaderIndex(headerList, ResearchPricingAuthEnum.FIRST_BUYING_PRICE.getDesc());
        // 有加背景色的字段12
        int synthesizeCostColumnIndex = this.getHeaderIndex(headerList, ResearchPricingAuthEnum.FIRST_SYNTHESIZE_COST.getDesc());
        // 有加背景色的字段13
        int synthesizeWarehouseCostColumnIndex = this.getHeaderIndex(headerList, ResearchPricingAuthEnum.SECOND_SYNTHESIZE_WAREHOUSE_COST.getDesc());
        // 有加背景色的字段4
        int synthesizeSaleCostColumnIndex = this.getHeaderIndex(headerList, ResearchPricingAuthEnum.COST_THREE_SYNTHESIZE_SALE_COST.getDesc());

        CellStyle[][] cellStyleArrays = new CellStyle[dataList.size()][headerSize];
        if (CollectionUtils.isNotEmpty(dataList)) {
            for (int rowIndex = 0; rowIndex < dataList.size(); rowIndex++) {
                List<String> rowData = dataList.get(rowIndex);
                // 标红，1为标红，其他为正常颜色
                if (!NumberUtils.INTEGER_MINUS_ONE.equals(buyingPriceColumnIndex)) {
                    String buyingPriceFlag = rowData.get(rowData.size() - Constants.ONE);
                    if (NumberUtils.INTEGER_ONE.toString().equals(buyingPriceFlag)) {
                        cellStyleArrays[rowIndex][buyingPriceColumnIndex] = fontColorCellStyle;
                    }
                }
                if (!NumberUtils.INTEGER_MINUS_ONE.equals(synthesizeCostColumnIndex)) {
                    cellStyleArrays[rowIndex][synthesizeCostColumnIndex] = foregroundColorCellStyle;
                }
                if (!NumberUtils.INTEGER_MINUS_ONE.equals(synthesizeWarehouseCostColumnIndex)) {
                    cellStyleArrays[rowIndex][synthesizeWarehouseCostColumnIndex] = foregroundColorCellStyle;
                }
                if (!NumberUtils.INTEGER_MINUS_ONE.equals(synthesizeSaleCostColumnIndex)) {
                    cellStyleArrays[rowIndex][synthesizeSaleCostColumnIndex] = foregroundColorCellStyle;
                }

                // 去掉多余的标红列，仅做字体颜色，不需要导出到excel文件
                rowData.remove(rowData.size() - Constants.ONE);
            }
        }
        return cellStyleArrays;
    }

/**
     * 获取标题列数
     *
     * @param headerList 标题
     * @return 标题列数
     */
    private int getExcelHeaderColumnSize(List<ExcelHeader> headerList) {
        int headerSize = 0;
        for (ExcelHeader excelHeader : headerList) {
            headerSize += CollectionUtils.isNotEmpty(excelHeader.getSubExcelHeader()) ? excelHeader.getSubExcelHeader().size() : Constants.ONE;
        }
        return headerSize;
    }

    /**
     * 获取Excel标题
     *
     * @return 标题
     */
    protected List<ExcelHeader> getExcelHeader( Map<String, List<String>> titleMap, Map<Integer, Object> authSet) {
        // 写标题
        List<ExcelHeader> titleList = new ArrayList<>();
        // 名称
        ExcelHeader excelHeader = new ExcelHeader();
        excelHeader.setName("名称");
        titleList.add(excelHeader);

        for (Map.Entry<String, List<String>> entry : titleMap.entrySet()) {
            ExcelHeader topHeader = new ExcelHeader();
            topHeader.setName(entry.getKey());
            if (null != entry.getValue()) {
                List<ExcelHeader> subList = new ArrayList<>();
                for (String name : entry.getValue()) {
                    ExcelHeader subHeader = new ExcelHeader();
                    subHeader.setName(name);
                    subList.add(subHeader);
                }
                topHeader.setSubExcelHeader(subList);
            }
            titleList.add(topHeader);
        }

        Set<String> authFieldName = new HashSet<>();
        List<String> others =  Lists.newArrayList( );
        // "*" 代码超级管理
        Set<String> all = (Set<String>) authSet.get(NumberUtils.INTEGER_ZERO);
        // 可通过权限控制列标题显示 略..
        others.add(BizTypePricingEnum.GUIDE_RETAIL_PRICE.getName());
        List<String> othersOperations = Lists.newArrayList( "备注", "创建人", "创建时间", "修改人", "修改时间");
        others.addAll(othersOperations);

        for (String name : others) {
            excelHeader = new ExcelHeader();
            excelHeader.setName(name);
            titleList.add(excelHeader);
        }
        return titleList;
    }

/**
 * @ClassName: ExcelHelper 
 * @Description: 导出excel文件
 * @Author: Aaron-x
 * @Date: 2020-12-19 16:14:00
 */
public final class ExcelHelper {
    private OnionExcelHelper() {
    }

    /**
     * 导出excel，推荐使用这个，性能较优
     *
     * @param beginLine         第几行开始
     * @param excelHeaderList   标题
     * @param dataList          数据
     * @param sheetLinePage     sheet
     * @param sheetMergedRegion 合并区域
     * @param cellStyleList     单元格style
     * @return 工作簿
     * @throws Exception 异常
     */
    public static SXSSFWorkbook writeDataToExcel(int beginLine, List<ExcelHeader> excelHeaderList, List<List<String>> dataList, Map<Integer, Integer> sheetLinePage,
                                                 Map<Integer, List<CellRangeAddress>> sheetMergedRegion, List<CellStyle> cellStyleList) throws Exception {
        if (MapUtils.isEmpty(sheetLinePage)) {
            throw new Exception("导出出错，sheet设置错误");
        } else if (CollectionUtils.isEmpty(excelHeaderList)) {
            throw new Exception("导出出错，标题为空");
        } else if (null == dataList) {
            throw new Exception("导出出错，数据为空");
        } else if (CollectionUtils.isNotEmpty(cellStyleList) && cellStyleList.size() != excelHeaderList.size()) {
            throw new Exception("导出出错，列样式设置错误");
        } else {
            SXSSFWorkbook wb = new SXSSFWorkbook(5000);
            int hasRead = 0;

            for (Map.Entry<Integer, Integer> entry : sheetLinePage.entrySet()) {
                int sheetNum = entry.getKey();
                int lineNumTotal = entry.getValue();
                SXSSFSheet sxssfSheet = wb.createSheet("Sheet" + sheetNum);

                createHead(excelHeaderList, sxssfSheet, wb);

                int dataBeginLineNum = beginLine;
                for (int d = hasRead; d < dataList.size(); ++d) {
                    SXSSFRow dataRow = sxssfSheet.createRow(dataBeginLineNum);
                    List<String> columnList = dataList.get(d);
                    for (int c = 0; c < columnList.size(); ++c) {
                        Cell cell = dataRow.createCell(c);
                        if (CollectionUtils.isNotEmpty(cellStyleList)) {
                            cell.setCellStyle(cellStyleList.get(c));
                        }

                        cell.setCellValue(columnList.get(c));
                    }

                    ++hasRead;
                    if (dataBeginLineNum >= lineNumTotal) {
                        break;
                    }

                    ++dataBeginLineNum;
                }

                if (MapUtils.isNotEmpty(sheetMergedRegion) && sheetMergedRegion.size() >= sheetNum) {
                    List<CellRangeAddress> cellRangeAddressList = sheetMergedRegion.get(sheetNum);
                    for (CellRangeAddress cellRangeAddress : cellRangeAddressList) {
                        sxssfSheet.addMergedRegion(cellRangeAddress);
                    }
                }

                sxssfSheet.trackAllColumnsForAutoSizing();

                for (short t = 0; t < excelHeaderList.size(); ++t) {
                    sxssfSheet.autoSizeColumn(t, true);
                }
            }

            return wb;
        }
    }

    /**
     * 导出excel，包含excel不可编辑、水印
     *
     * @param beginLine         第几行开始
     * @param excelHeaderList   标题
     * @param dataList          数据
     * @param sheetLinePage     sheet
     * @param sheetMergedRegion 合并区域
     * @param cellStyleList     单元格style
     * @param sheetPassword     sheet密码，不可编辑
     * @param waterMarkContent  水印文本
     * @return 工作簿
     * @throws Exception 异常
     */
    public static XSSFWorkbook writeDataToExcel(int beginLine, List<ExcelHeader> excelHeaderList, List<List<String>> dataList, Map<Integer, Integer> sheetLinePage, Map<Integer,
            List<CellRangeAddress>> sheetMergedRegion, List<CellStyle> cellStyleList, String sheetPassword, String waterMarkContent) throwsException {
        if (apUtils.isEmpty(sheetLinePage)) {
            throw new Exception("导出出错，sheet设置错误");
        } else if (CollectionUtils.isEmpty(excelHeaderList)) {
            throw new Exception("导出出错，标题为空");
        } else if (null == dataList) {
            throw new Exception("导出出错，数据为空");
        } else if (CollectionUtils.isNotEmpty(cellStyleList) && cellStyleList.size() != excelHeaderList.size()) {
            throw new Exception("导出出错，列样式设置错误");
        } else {
            XSSFWorkbook wb = new XSSFWorkbook();
            int hasRead = 0;
            ByteArrayOutputStream byteArrayOutputStream = null;
            try {
                for (Map.Entry<Integer, Integer> entry : sheetLinePage.entrySet()) {
                    int sheetNum = entry.getKey();
                    int lineNumTotal = entry.getValue();
                    XSSFSheet sheet = wb.createSheet("Sheet" + sheetNum);

                    // 设置密码，不可编辑
                    if (StringUtils.isNotEmpty(sheetPassword)) {
                        sheet.protectSheet(sheetPassword);
                    }

                    // 设置水印
                    if (StringUtils.isNotEmpty(waterMarkContent)) {
                        byteArrayOutputStream = createWaterMark(waterMarkContent);
                        int pictureIdx = wb.addPicture(byteArrayOutputStream.toByteArray(), Workbook.PICTURE_TYPE_PNG);
                        String relationId = sheet.addRelation(null, XSSFRelation.IMAGES, wb.getAllPictures().get(pictureIdx)).getRelationship().getId();
                        sheet.getCTWorksheet().addNewPicture().setId(relationId);
                    }

                    createHead(excelHeaderList, sheet, wb);

                    int dataBeginLineNum = beginLine;
                    for (int d = hasRead; d < dataList.size(); ++d) {
                        Row dataRow = sheet.createRow(dataBeginLineNum);
                        List<String> columnList = dataList.get(d);

                        for (int c = 0; c < columnList.size(); ++c) {
                            Cell cell = dataRow.createCell(c);
                            if (CollectionUtils.isNotEmpty(cellStyleList)) {
                                cell.setCellStyle(cellStyleList.get(c));
                            }

                            cell.setCellValue(columnList.get(c));
                        }

                        ++hasRead;
                        if (dataBeginLineNum >= lineNumTotal) {
                            break;
                        }

                        ++dataBeginLineNum;
                    }

                    if (MapUtils.isNotEmpty(sheetMergedRegion) && sheetMergedRegion.size() >= sheetNum) {
                        List<CellRangeAddress> cellRangeAddressList = sheetMergedRegion.get(sheetNum);
                        for (CellRangeAddress cellRangeAddress : cellRangeAddressList) {
                            sheet.addMergedRegion(cellRangeAddress);
                        }
                    }

                    for (short t = 0; t < excelHeaderList.size(); ++t) {
                        sheet.autoSizeColumn(t, true);
                    }
                }
            } catch (Exception e) {
                throw new Exception(e);
            } finally {
                IOUtils.closeQuietly(byteArrayOutputStream);
            }

            return wb;
        }
    }



/**
     * 导出excel，包含excel不可编辑、水印
     *
     * @param wb                工作簿
     * @param beginLine         第几行开始
     * @param excelHeaderList   标题
     * @param dataList          数据
     * @param sheetLinePage     sheet
     * @param sheetMergedRegion 合并区域
     * @param cellStyleArray    单元格style
     * @param sheetPassword     sheet密码，不可编辑
     * @param waterMarkContent  水印文本
     * @return 工作簿
     * @throws Exception 异常
     */
    public static SXSSFWorkbook writeDataToExcel(SXSSFWorkbook wb, int beginLine, List<ExcelHeader> excelHeaderList, List<List<String>> dataList, Map<Integer, Integer> sheetLinePage, Map<Integer,
            List<CellRangeAddress>> sheetMergedRegion, CellStyle[][] cellStyleArray, String sheetPassword, String waterMarkContent) throws Exception {
        if (MapUtils.isEmpty(sheetLinePage)) {
            throw new Exception("导出出错，sheet设置错误");
        } else if (CollectionUtils.isEmpty(excelHeaderList)) {
            throw new Exception("导出出错，标题为空");
        } else if (null == dataList) {
            throw new Exception("导出出错，数据为空");
        } else if (ArrayUtils.isNotEmpty(cellStyleArray) && cellStyleArray.length != dataList.size()) {
            throw new Exception("导出出错，列样式设置错误");
        } else {
            ByteArrayOutputStream byteArrayOutputStream = null;
            try {
                for (Map.Entry<Integer, Integer> entry : sheetLinePage.entrySet()) {
                    int sheetNum = entry.getKey();
                    int lineNumTotal = entry.getValue();
                    SXSSFSheet sheet = wb.createSheet("Sheet" + sheetNum);

                    // 设置密码，不可编辑
                    if (StringUtils.isNotEmpty(sheetPassword)) {
                        sheet.protectSheet(sheetPassword);
                    }

                    // 设置水印
                    if (StringUtils.isNotEmpty(waterMarkContent)) {
//                        byteArrayOutputStream = createWaterMark(waterMarkContent);
//                        int pictureIdx = wb.addPicture(byteArrayOutputStream.toByteArray(), Workbook.PICTURE_TYPE_PNG);
//                        String relationId = sheet.addRelation(null, XSSFRelation.IMAGES, wb.getAllPictures().get(pictureIdx)).getRelationship().getId();
//                        sheet.getCTWorksheet().addNewPicture().setId(relationId);

                        int xCount = Math.max(dataList.size() / 10, 10);
                        int yCount = Math.max(dataList.get(0).size() / 30, 30);

                        // 设置水印
                        putWaterRemarkToExcel(wb, sheet, waterMarkContent, 0, 0, 5, 5, xCount,
                                yCount, 10, 30);
                    }

                    createHead(excelHeaderList, sheet, wb);

                    int dataBeginLineNum = beginLine;
                    for (int rowIndex = 0; rowIndex < dataList.size(); ++rowIndex) {
                        Row dataRow = sheet.createRow(dataBeginLineNum);
                        List<String> columnList = dataList.get(rowIndex);

                        for (int columnIndex = 0; columnIndex < columnList.size(); ++columnIndex) {
                            Cell cell = dataRow.createCell(columnIndex);
                            if (ArrayUtils.isNotEmpty(cellStyleArray)) {
                                CellStyle cellStyle = cellStyleArray[rowIndex][columnIndex];
                                if (null != cellStyle) {
                                    cell.setCellStyle(cellStyle);
                                }
                            }

                            cell.setCellValue(columnList.get(columnIndex));
                        }

                        if (dataBeginLineNum >= lineNumTotal) {
                            break;
                        }

                        ++dataBeginLineNum;
                    }

                    if (MapUtils.isNotEmpty(sheetMergedRegion) && sheetMergedRegion.size() >= sheetNum) {
                        List<CellRangeAddress> cellRangeAddressList = sheetMergedRegion.get(sheetNum);
                        for (CellRangeAddress cellRangeAddress : cellRangeAddressList) {
                            sheet.addMergedRegion(cellRangeAddress);
                        }
                    }

                    sheet.trackAllColumnsForAutoSizing();
                    for (short t = 0; t < excelHeaderList.size(); ++t) {
                        sheet.autoSizeColumn(t, true);
                    }
                }
            } catch (Exception e) {
                throw new Exception(e);
            } finally {
                IOUtils.closeQuietly(byteArrayOutputStream);
            }

            return wb;
        }
    }



 /**
     * 为Excel打上水印工具函数
     * 请自行确保参数值，以保证水印图片之间不会覆盖。
     * 在计算水印的位置的时候，并没有考虑到单元格合并的情况，请注意
     *
     * @param wb                Excel Workbook
     * @param sheet             需要打水印的Excel
     * @param content           水印内容
     * @param startXCol         水印起始列
     * @param startYRow         水印起始行
     * @param betweenXCol       水印横向之间间隔多少列
     * @param betweenYRow       水印纵向之间间隔多少行
     * @param xCount            横向共有水印多少个
     * @param yCount            纵向共有水印多少个
     * @param waterRemarkWidth  水印图片宽度为多少列
     * @param waterRemarkHeight 水印图片高度为多少行
     * @throws Exception 异常
     */
    public static void putWaterRemarkToExcel(Workbook wb, Sheet sheet, String content, int startXCol, int startYRow,
                                             int betweenXCol, int betweenYRow, int xCount, int yCount,
                                             int waterRemarkWidth, int waterRemarkHeight) throws Exception {
        ByteArrayOutputStream byteArrayOut = null;
        try {
            byteArrayOut = createWaterMark(content);
            //开始打水印
            Drawing drawing = sheet.createDrawingPatriarch();

            //按照共需打印多少行水印进行循环
            for (int y = 0; y < yCount; y++) {
                //按照每行需要打印多少个水印进行循环
                for (int x = 0; x < xCount; x++) {
                    //创建水印图片位置
                    int xIndexInteger = startXCol + (x * waterRemarkWidth) + (x * betweenXCol);
                    int yIndexInteger = startYRow + (y * waterRemarkHeight) + (y * betweenYRow);

                    /*
                     * 参数定义：
                     * 第一个参数是（x轴的开始节点）；
                     * 第二个参数是（是y轴的开始节点）；
                     * 第三个参数是（是x轴的结束节点）；
                     * 第四个参数是（是y轴的结束节点）；
                     * 第五个参数是（是从Excel的第几列开始插入图片，从0开始计数）；
                     * 第六个参数是（是从excel的第几行开始插入图片，从0开始计数）；
                     * 第七个参数是（图片宽度，共多少列）；
                     * 第8个参数是（图片高度，共多少行）；
                     */
                    ClientAnchor anchor = drawing.createAnchor(0, 0, Short.MAX_VALUE, Integer.MAX_VALUE, xIndexInteger, yIndexInteger, waterRemarkWidth, waterRemarkHeight);
                    Picture pic = drawing.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), Workbook.PICTURE_TYPE_PNG));
                    pic.resize();
                }
            }
        } catch (Exception e) {
            throw new Exception(e);
        } finally {
            IOUtils.closeQuietly(byteArrayOut);
        }
    }


    /**
     * 表头样式
     *
     * @param workbook 表格对象
     */
    private static CellStyle headerStyle(Workbook workbook) {
        CellStyle headStyle = workbook.createCellStyle();
        headStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headStyle.setBorderBottom(BorderStyle.THIN);
        headStyle.setBorderRight(BorderStyle.THIN);
        headStyle.setBorderLeft(BorderStyle.THIN);
        headStyle.setBorderTop(BorderStyle.THIN);
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 生成一个字体
        Font headFont = workbook.createFont();
        headFont.setBold(true);
        // 把字体应用到当前的样式
        headStyle.setFont(headFont);
        return headStyle;
    }

    /**
     * 创建2级表头
     *
     * @param excelHeaderList 表头数据
     * @param sheet           表格
     * @param workbook        工作簿
     */
    private static void createHead(List<ExcelHeader> excelHeaderList, Sheet sheet, Workbook workbook) {
        CellStyle headStyle = headerStyle(workbook);
        Row row = sheet.createRow(Constants.ZERO);
        Row row2 = sheet.createRow(Constants.ONE);
        for (int i = 0, n = 0; i < excelHeaderList.size(); i++) {
            Cell cell1 = row.createCell(n);
            cell1.setCellStyle(headStyle);
            // 二级表头
            List<ExcelHeader> level2ExcelHeader = excelHeaderList.get(i).getSubExcelHeader();

            // 一级表头名字
            RichTextString topLevelName = new XSSFRichTextString(excelHeaderList.get(i).getName());

            // 只有一级表头
            if (ObjectUtils.isEmpty(level2ExcelHeader)) {
                CellRangeAddress cra = new CellRangeAddress(0, 1, n, n);
                // 单标题
                sheet.addMergedRegion(cra);
                // 合并表格添加边框
                RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet);
                RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet);
                RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet);
                RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet);
                cell1.setCellValue(topLevelName);
                n++;
                continue;
            }

            cell1.setCellValue(topLevelName);
            // 子标题列数大于1才需要合并，不然会报异常
            if (level2ExcelHeader.size() > Constants.ONE) {
                CellRangeAddress cra = new CellRangeAddress(0, 0, n, n + level2ExcelHeader.size() - 1);
                // 创建一级表头标题
                sheet.addMergedRegion(cra);
                // 合并表格添加边框
                RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet);
                RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet);
                RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet);
                RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet);
            }

            // 创建二级表头
            for (ExcelHeader excelHeader : level2ExcelHeader) {
                Cell cell2 = row2.createCell(n++);
                cell2.setCellStyle(headStyle);
                cell2.setCellValue(new XSSFRichTextString(excelHeader.getName()));
            }
        }
    }

    /**
     * 创建水印
     *
     * @param content 水印文本
     * @return 文件流
     * @throws Exception 异常
     */
    private static ByteArrayOutputStream createWaterMark(String content) throws Exception {
        try {
            int width = 800;
            int height = 750;
            BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);
            String fontType = "微软雅黑";
            int fontStyle = java.awt.Font.BOLD;
            int fontSize = 100;
            java.awt.Font font = new java.awt.Font(fontType, fontStyle, fontSize);
            // 获取Graphics2d对象
            Graphics2D g2d = image.createGraphics();
            image = g2d.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);
            g2d.dispose();
            g2d = image.createGraphics();
            // 设置字体颜色和透明度，最后一个参数为透明度
            g2d.setColor(new Color(0, 0, 0, 20));
            // 设置字体
            g2d.setStroke(new BasicStroke(1));
            // 设置字体类型 加粗 大小
            g2d.setFont(font);
            // 设置倾斜度
            g2d.rotate(-0.5, (double) image.getWidth() / 2, (double) image.getHeight() / 2);
            FontRenderContext context = g2d.getFontRenderContext();
            Rectangle2D bounds = font.getStringBounds(content, context);
            double x = (width - bounds.getWidth()) / 2;
            double y = (height - bounds.getHeight()) / 2;
            double ascent = -bounds.getY();
            double basey = y + ascent;
            // 写入水印文字原定高度过小，所以累计写水印，增加高度
            g2d.drawString(content, (int) x, (int) basey);
            // 设置透明度
            g2d.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER));
            // 释放对象
            g2d.dispose();
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ImageIO.write(image, "png", os);
            return os;
        } catch (IOException e) {
            throw new Exception(e);
        }
    }
}

导出Excel单列及二级表头+不可以编辑+excel水印操作：效果如下
双击excel单元格会弹出需要输入密码才可编辑及背景水印
!(https://img-blog.csdnimg.cn/2020121916215655.png