POI实现Excel多行复杂表头导出

POI实现Excel多行复杂表头导出

1. pom文件添加POI相关依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.10-FINAL</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.10-FINAL</version>
</dependency>

2. 代码实现

1. 定义表头标题

/**
 * home.php?mod=space&uid=686208 vvirster@163.com
 * home.php?mod=space&uid=686237 2022-11-24 15:28
 * @description 复杂表格表头单元格
 **/
public class CellModel {
    /**
     * 表头列名称
     */
    private String cellName;
    /**
     * 起始行
     */
    private Integer startRow;
    /**
     * 结束行
     */
    private Integer endRow;
    /**
     * 起始列
     */
    private Integer startColumn;

    /**
     * 结束列
     */
    private Integer endColumn;
    /**
     * 设置单元格宽度
     */
    private Integer width;

    // setter getter省略。。
 }

2. 编写导出/生成Excel工具类

/**
 * @author vvirster@163.com
 * @date 2022-11-24 15:29
 * @description POI导出Excel工具类
 **/
public class ExportExcelUtil {
        private static final Logger logger = LoggerFactory.getLogger(ExportExcelUtil.class);

      /**
     * 生成表格（用于生成复杂表头）
     *
     * home.php?mod=space&uid=952169 sheetName   sheet名称
     * @param wb          表对象
     * @param cellListMap 表头数据 {key=cellRowNum}
     * @param cellRowNum  表头总占用行数
     * @param exportData  行数据
     * home.php?mod=space&uid=155549 HSSFWorkbook 数据表对象
     */
    @SuppressWarnings({"rawtypes", "unchecked"})
    public static Workbook createCSVUtil(String sheetName, Workbook wb,
                                         Map<String, List<CellModel>> cellListMap,
                                         Integer cellRowNum, List<LinkedHashMap> exportData) throws Exception {
        //设置表格名称
        Sheet sheet = wb.createSheet(sheetName);
        // 设置打印参数
        PrintSetup printSetup = sheet.getPrintSetup();
        // 纸张大小
        printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
        sheet.setDisplayGridlines(false);
        sheet.setPrintGridlines(false);
        // 上边距
        sheet.setMargin(Sheet.TopMargin, 0.5);
        // 下边距
        sheet.setMargin(Sheet.BottomMargin, 0.5);
        // 左边距
        sheet.setMargin(Sheet.LeftMargin, 0.5);
        // 右边距
        sheet.setMargin(Sheet.RightMargin, 0.5);
        sheet.setHorizontallyCenter(true);

        sheet.autoSizeColumn(1, true);
        // 定义title列cell样式
        CellStyle cellStyle = wb.createCellStyle();
        //设置单元格内容水平对齐
        // 文字居中
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        //设置单元格内容垂直对齐
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        //设置自动换行
        cellStyle.setWrapText(true);
        cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//背景色
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        // 定义title列cell字体
        Font font = wb.createFont();
        // font.setColor(HSSFColor.VIOLET.index);//字体颜色
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        cellStyle.setFont(font);

        // 定义数据内容单元格样式
        CellStyle cellDataStyle = wb.createCellStyle();
        //设置单元格内容水平对齐
        // 文字居中
        cellDataStyle.setAlignment(CellStyle.ALIGN_CENTER);
        //设置单元格内容垂直对齐
        cellDataStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        //设置自动换行
        cellDataStyle.setWrapText(true);
        cellDataStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        cellDataStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellDataStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellDataStyle.setBorderRight(CellStyle.BORDER_THIN);
        cellDataStyle.setBorderTop(CellStyle.BORDER_THIN);

        for (int t = 0; t < cellRowNum; t++) {
            Row row = sheet.createRow(t);
            List<CellModel> cellNameList = cellListMap.get(String.valueOf(t));

            for (CellModel cellModel : cellNameList) {
                // 遍历插入表头
                if (cellModel.getStartColumn() != null) {
                    Cell cell = row.createCell(cellModel.getStartColumn());
                    cell.setCellValue(cellModel.getCellName());
                    cell.setCellStyle(cellStyle);
                }
            }
            // 解决合并单元格后出现部分单元格没有边框的问题
            if (t != 0) {
                int lastCellNum = sheet.getRow(0).getLastCellNum();
                for (int cellIndex = 0; cellIndex < lastCellNum; cellIndex++) {
                    Cell cell = sheet.getRow(t).getCell(cellIndex);
                    if (cell == null) {
                        cell = sheet.getRow(t).createCell(cellIndex);
                        cell.setCellValue("");
                    }
                    cell.setCellStyle(cellStyle);
                }
            }
            // 合并单元格
            for (CellModel cellModel : cellNameList) {
                logger.info("cellModelInfo,{}", JSON.toJSONString(cellModel));
                if (cellModel.getStartRow() != null) {
                    //合并单元格
                    CellRangeAddress region = new CellRangeAddress(cellModel.getStartRow(),
                            cellModel.getEndRow(), cellModel.getStartColumn(), cellModel.getEndColumn());
                    //给定要合并的单元格范围
                    sheet.addMergedRegion(region);
                }
                // 根据标题设置单元格宽度
                if (cellModel.getWidth() != null) {
                    sheet.setColumnWidth(cellModel.getStartColumn(), cellModel.getWidth() * 256);
                } else {
                    sheet.setColumnWidth(cellModel.getStartColumn(), cellModel.getCellName().getBytes().length * 256);
                }
            }

        }
        // 导出具体的数据
        for (LinkedHashMap hashMap : exportData) {
            Row rowValue = sheet.createRow(cellRowNum);
            for (Map.Entry entryRow : (Iterable<Map.Entry>) hashMap.entrySet()) {
                int key = Integer.parseInt(entryRow.getKey().toString());
                String value = "";
                if (entryRow.getValue() != null) {
                    value = entryRow.getValue().toString();
                } else {
                    value = "";
                }
                Cell cellValue = rowValue.createCell(key);
                cellValue.setCellValue(value);
                cellValue.setCellStyle(cellDataStyle);
            }
            cellRowNum++;
        }
        return wb;
    }

     /**
     * 通过HTTP请求下载Excel
     * @param cellListMap    表格标题
     * @param exportData     需要导出的数据
     * @param sheetName      表格名称
     * @param exportFileName 导出文件名称
     * @param response       servletResponse
     */
    public static void downloadExcel(Map<String, List<CellModel>> cellListMap, List<LinkedHashMap> exportData,
                                     String sheetName, String exportFileName, HttpServletResponse response) throws Exception {
        OutputStream out = null;
         String fileType = ".xlsx";
        try {
            Workbook wb = createCSVUtil(sheetName, new HSSFWorkbook(), cellListMap, cellListMap.size(), exportData);

            String s_attachment = "attachment; filename=" + exportFileName + fileType;

            // 设置字符编码的格式
            s_attachment = new String(s_attachment.getBytes("gb2312"), "ISO8859-1");
            // 设定输出文件头
            response.setHeader("Content-disposition", s_attachment);
            // 定义输出类型
            response.setContentType("application/vnd.ms-excel");
            response.setContentType("text/plain;charset=UTF-8");
            out = response.getOutputStream();
            wb.write(out);
            out.flush();
        } catch (Exception e) {
            throw new RuntimeException("导出Excel失败！");
        } finally {
            if (out != null) {
                out.close();
            }
        }

    }

}

3. 测试

public class ExcelUtilTest {
        /**
        * 生成Excel表头
        **/
        public static Map<String, List<CellModel>> genExcelTitleCel() {
            //表头数据
            Map<String, List<CellModel>> cellTitleMap = new HashMap<String, List<CellModel>>();
            // 第一行表头数据
            List<CellModel> firstRow = new ArrayList<CellModel>();
            CellModel first_cellModel1 = new CellModel();
            first_cellModel1.setCellName("项目名称");
            first_cellModel1.setStartRow(0);
            first_cellModel1.setWidth(20);
            first_cellModel1.setEndRow(1);
            first_cellModel1.setStartColumn(0);
            first_cellModel1.setEndColumn(0);

            CellModel first_cellModel2 = new CellModel();
            first_cellModel2.setCellName("计划类别");
            first_cellModel2.setStartRow(0);
            first_cellModel2.setEndRow(1);
            first_cellModel2.setStartColumn(1);
            first_cellModel2.setEndColumn(1);

            CellModel first_cellModel3 = new CellModel();
            first_cellModel3.setCellName("项目负责人");
            first_cellModel3.setStartRow(0);
            first_cellModel3.setEndRow(1);
            first_cellModel3.setStartColumn(2);
            first_cellModel3.setEndColumn(2);

            CellModel first_cellModel4 = new CellModel();
            first_cellModel4.setCellName("项目编号");
            first_cellModel4.setStartRow(0);
            first_cellModel4.setEndRow(1);
            first_cellModel4.setStartColumn(3);
            first_cellModel4.setEndColumn(3);

            CellModel first_cellModel5 = new CellModel();
            first_cellModel5.setCellName("2022年度发表受本项目资助的论文（篇）");
            first_cellModel5.setStartRow(0);
            first_cellModel5.setEndRow(0);
            first_cellModel5.setStartColumn(4);
            first_cellModel5.setEndColumn(10);

            CellModel first_cellModel6 = new CellModel();
            first_cellModel6.setCellName("2022年专著出版（册）");
            first_cellModel6.setWidth(100);
            first_cellModel6.setStartRow(0);
            first_cellModel6.setEndRow(0);
            first_cellModel6.setStartColumn(11);
            first_cellModel6.setEndColumn(12);

            CellModel first_cellModel7 = new CellModel();
            first_cellModel7.setCellName("2022年申请专利（项）");
            first_cellModel7.setStartRow(0);
            first_cellModel7.setEndRow(0);
            first_cellModel7.setStartColumn(13);
            first_cellModel7.setEndColumn(14);

            CellModel first_cellModel8 = new CellModel();
            first_cellModel8.setCellName("2022年授权专利（项）");
            first_cellModel8.setStartRow(0);
            first_cellModel8.setEndRow(0);
            first_cellModel8.setStartColumn(15);
            first_cellModel8.setEndColumn(16);

            CellModel first_cellModel9 = new CellModel();
            first_cellModel9.setCellName("2022年学术交流（次）");
            first_cellModel9.setStartRow(0);
            first_cellModel9.setEndRow(0);
            first_cellModel9.setStartColumn(17);
            first_cellModel9.setEndColumn(18);

            CellModel first_cellModel10 = new CellModel();
            first_cellModel10.setCellName("2022年人才培养（人）");
            first_cellModel10.setStartRow(0);
            first_cellModel10.setEndRow(0);
            first_cellModel10.setStartColumn(19);
            first_cellModel10.setEndColumn(20);

            CellModel first_cellModel11 = new CellModel();
            first_cellModel11.setCellName("2022年培育新立项纵向项目（项）");
            first_cellModel11.setStartRow(0);
            first_cellModel11.setEndRow(1);
            first_cellModel11.setStartColumn(21);
            first_cellModel11.setEndColumn(21);

            CellModel first_cellModel12 = new CellModel();
            first_cellModel12.setCellName("2022年获得省部级及以上科技奖励（项）");
            first_cellModel12.setStartRow(0);
            first_cellModel12.setEndRow(1);
            first_cellModel12.setStartColumn(22);
            first_cellModel12.setEndColumn(22);

            firstRow.add(first_cellModel1);
            firstRow.add(first_cellModel2);
            firstRow.add(first_cellModel3);
            firstRow.add(first_cellModel4);
            firstRow.add(first_cellModel5);
            firstRow.add(first_cellModel6);
            firstRow.add(first_cellModel7);
            firstRow.add(first_cellModel8);
            firstRow.add(first_cellModel9);
            firstRow.add(first_cellModel10);
            firstRow.add(first_cellModel11);
            firstRow.add(first_cellModel12);
            //第二行表头数据
            List<CellModel> secondRow = new ArrayList<CellModel>();
            CellModel second_cellModel1 = new CellModel();
            second_cellModel1.setCellName("发表学术论文总计");
            second_cellModel1.setStartRow(1);
            second_cellModel1.setEndRow(1);
            second_cellModel1.setStartColumn(4);
            second_cellModel1.setEndColumn(4);

            CellModel second_cellModel2 = new CellModel();
            second_cellModel2.setCellName("英文论文");
            second_cellModel2.setStartRow(1);
            second_cellModel2.setEndRow(1);
            second_cellModel2.setStartColumn(5);
            second_cellModel2.setEndColumn(5);

            CellModel second_cellModel3 = new CellModel();
            second_cellModel3.setCellName("SCI");
            second_cellModel3.setStartRow(1);
            second_cellModel3.setWidth(10);
            second_cellModel3.setEndRow(1);
            second_cellModel3.setStartColumn(6);
            second_cellModel3.setEndColumn(6);

            CellModel second_cellModel4 = new CellModel();
            second_cellModel4.setCellName("EI");
            second_cellModel4.setStartRow(1);
            second_cellModel4.setEndRow(1);
            second_cellModel4.setWidth(10);
            second_cellModel4.setStartColumn(7);
            second_cellModel4.setEndColumn(7);

            CellModel second_cellModel5 = new CellModel();
            second_cellModel5.setCellName("国内核心");
            second_cellModel5.setStartRow(1);
            second_cellModel5.setEndRow(1);
            second_cellModel5.setStartColumn(8);
            second_cellModel5.setEndColumn(8);

            CellModel second_cellModel6 = new CellModel();
            second_cellModel6.setCellName("国外学术期刊");
            second_cellModel6.setStartRow(1);
            second_cellModel6.setEndRow(1);
            second_cellModel6.setStartColumn(9);
            second_cellModel6.setEndColumn(9);

            CellModel second_cellModel7 = new CellModel();
            second_cellModel7.setCellName("其他");
            second_cellModel7.setStartRow(1);
            second_cellModel7.setEndRow(1);
            second_cellModel7.setStartColumn(10);
            second_cellModel7.setEndColumn(10);

            CellModel second_cellModel8 = new CellModel();
            second_cellModel8.setCellName("主编");
            second_cellModel8.setStartRow(1);
            second_cellModel8.setEndRow(1);
            second_cellModel8.setStartColumn(11);
            second_cellModel8.setEndColumn(11);

            CellModel second_cellModel9 = new CellModel();
            second_cellModel9.setCellName("参编");
            second_cellModel9.setStartRow(1);
            second_cellModel9.setEndRow(1);
            second_cellModel9.setStartColumn(12);
            second_cellModel9.setEndColumn(12);

            CellModel second_cellModel10 = new CellModel();
            second_cellModel10.setCellName("发明专利");
            second_cellModel10.setStartRow(1);
            second_cellModel10.setEndRow(1);
            second_cellModel10.setStartColumn(13);
            second_cellModel10.setEndColumn(13);

            CellModel second_cellModel11 = new CellModel();
            second_cellModel11.setCellName("其他专利");
            second_cellModel11.setStartRow(1);
            second_cellModel11.setEndRow(1);
            second_cellModel11.setStartColumn(14);
            second_cellModel11.setEndColumn(14);

            CellModel second_cellModel12 = new CellModel();
            second_cellModel12.setCellName("发明专利");
            second_cellModel12.setStartRow(1);
            second_cellModel12.setEndRow(1);
            second_cellModel12.setStartColumn(15);
            second_cellModel12.setEndColumn(15);

            CellModel second_cellModel13 = new CellModel();
            second_cellModel13.setCellName("其他专利");
            second_cellModel13.setStartRow(1);
            second_cellModel13.setEndRow(1);
            second_cellModel13.setStartColumn(16);
            second_cellModel13.setEndColumn(16);

            CellModel second_cellModel14 = new CellModel();
            second_cellModel14.setCellName("举办学术会议");
            second_cellModel14.setStartRow(1);
            second_cellModel14.setEndRow(1);
            second_cellModel14.setStartColumn(17);
            second_cellModel14.setEndColumn(17);

            CellModel second_cellModel15 = new CellModel();
            second_cellModel15.setCellName("参加学术会议");
            second_cellModel15.setStartRow(1);
            second_cellModel15.setEndRow(1);
            second_cellModel15.setStartColumn(18);
            second_cellModel15.setEndColumn(18);

            CellModel second_cellModel16 = new CellModel();
            second_cellModel16.setCellName("博士");
            second_cellModel16.setStartRow(1);
            second_cellModel16.setEndRow(1);
            second_cellModel16.setStartColumn(19);
            second_cellModel16.setEndColumn(19);

            CellModel second_cellModel17 = new CellModel();
            second_cellModel17.setCellName("硕士");
            second_cellModel17.setStartRow(1);
            second_cellModel17.setEndRow(1);
            second_cellModel17.setStartColumn(20);
            second_cellModel17.setEndColumn(20);

            secondRow.add(second_cellModel1);
            secondRow.add(second_cellModel2);
            secondRow.add(second_cellModel3);
            secondRow.add(second_cellModel4);
            secondRow.add(second_cellModel5);
            secondRow.add(second_cellModel6);
            secondRow.add(second_cellModel7);
            secondRow.add(second_cellModel8);
            secondRow.add(second_cellModel9);
            secondRow.add(second_cellModel10);
            secondRow.add(second_cellModel11);
            secondRow.add(second_cellModel12);
            secondRow.add(second_cellModel13);
            secondRow.add(second_cellModel14);
            secondRow.add(second_cellModel15);
            secondRow.add(second_cellModel16);
            secondRow.add(second_cellModel17);

            // 组装第一行表头标题
            cellTitleMap.put("0", firstRow);
            // 组装第二行表头标题
            cellTitleMap.put("1", secondRow);
            return cellTitleMap;
        }

    public static void main(String[] args) {
        //向指定的Excel中写入数据
        OutputStream out = null;
        Workbook wb = null;
        try {
            String tabName = "test生成Excel";
            Map<String, List<CellModel>> excelTitleCel = genExcelTitleCel();

            System.out.println(JSONObject.toJSONString(excelTitleCel));
            List<LinkedHashMap> exportData = new ArrayList<LinkedHashMap>();
            //生成10条行记录
            for (int i = 0; i < 10; i++) {
                LinkedHashMap<String, String> rowPut = new LinkedHashMap<String, String>();
                // 根据实际列数生成数据
                for (int j = 0; j <= 22; j++) {
                    rowPut.put(j + "", "数据11===" + i);
                }
                exportData.add(rowPut);
            }
            System.out.println(JSONObject.toJSONString(exportData));
            // 用于写入文件
            wb = ExportExcelUtil.createCSVUtil("生成Excel测试", new XSSFWorkbook(), excelTitleCel, excelTitleCel.size(),
                    exportData);

            FileOutputStream fos = new FileOutputStream("D:\\tmp\\" + tabName+".xlsx");
            wb.write(fos);
            fos.flush();
            fos.close();

             //在servlet/web环境下测试此方法
            // ExportExcelUtil.downloadExcel(excelTitleCel,exportData,"生成Excel测试","生成Excel测试",response);

        } catch (Exception e) {
            e.printStackTrace();
        }

}

• 测试结果