java使用poi导出自定义动态复杂表头

前言：对于普通的导出可以用easyexcel或者poi框架，例如若依框架的导出就是封装好的poi，但是有些场景或者需求比较复杂我们需要自己写。主要功能点就是通过java代码在工作簿上画表头。然后填充数据，demo示例仅提供思路，毕竟都自定义了肯定业务要求很高~~~

	@Override
    public void jobLearningExport(HttpServletResponse response, JobLearningExcelVo jobLearningExcelVo) {
		
		//这里是设置导出格式的
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");

		//这里是每一条数据的主键,复杂点用这个主键一个一个查，简单的就可以一次性查出来了
        Integer[] userIds = xxx;

        //生成表头
        List<HeaderNode> headerNodeList = new ArrayList<>();
        HashMap<Integer, Integer> map = new HashMap<>();
        headerNodeList.add(new HeaderNode("xxx", 0, 0));
        headerNodeList.add(new HeaderNode("xxx", 0, 1));
        headerNodeList.add(new HeaderNode("xxx", 0, 2));
        headerNodeList.add(new HeaderNode("xxx", 0, 3));
        //从第四列开始
        int columnXiLie = 3;
        int columnLearn = 3;
        //查询动态表头(以下是业务逻辑，各位可以自己改动，毕竟需求不一样这里只提供思路)
        for (Integer xiLieId : jobLearningExcelVo.getXiLieIds()) {
            columnXiLie++;
            //根据系列id查询系列名称
            String xiLieName = excelCommonMapper.getXiLieName(xiLieId);
            //根据系列id查询所有的课程
            List<UserLearn> userLearnList = excelCommonMapper.getLearnNameList(xiLieId);
            if (userLearnList != null && userLearnList.size() > 0) {
                //根据系列id查询目录名称
                String directoryName = excelCommonMapper.getDirectoryName(xiLieId);
                headerNodeList.add(new HeaderNode(directoryName, 0, userLearnList.size(), false, columnXiLie));
                //生成系列表头
                headerNodeList.add(new HeaderNode(xiLieName, 1, userLearnList.size(), false, columnXiLie));
                columnXiLie--;
                columnXiLie = columnXiLie + userLearnList.size();
                for (UserLearn userLearn : userLearnList) {
                    columnLearn++;
                    headerNodeList.add(new HeaderNode(userLearn.getLearnName(), 2, columnLearn));
                    map.put(userLearn.getLearnId(), columnLearn);
                }
            } else {
                columnXiLie--;
            }
        }

        SXSSFWorkbook book = new SXSSFWorkbook();
        SXSSFSheet sxssfSheet = book.createSheet("xxx");
        CellStyle headStyle = book.createCellStyle();
        defaultHeadStyle(headStyle);
        //表头层级
        int deep = headerNodeList.stream().map(HeaderNode::getRow).reduce(Integer::max).orElse(1);
        for (int i = 0; i < deep; i++) {
            sxssfSheet.createRow(i);
        }

        //创建单元格
        for (HeaderNode headerNode : headerNodeList) {
            int row = headerNode.getRow();
            int col = headerNode.getColumn();
            SXSSFCell sxssfCell = sxssfSheet.getRow(row).createCell(col);
            sxssfSheet.setColumnWidth(col, headerNode.getWidth() * 256);
            sxssfCell.setCellStyle(headStyle);
            sxssfCell.setCellValue(headerNode.getHeaderName());

            CellRangeAddress region;
            //是否跨列
            if (headerNode.isOverNode()) {
                region = new CellRangeAddress(row, deep, col, col);
            } else {
                region = new CellRangeAddress(row, row, col, (col + headerNode.getOverNodeCount() - 1));
            }
            if (region.getNumberOfCells() > 1) {
                sxssfSheet.addMergedRegionUnsafe(region);
                //合并后设置下边框
                RegionUtil.setBorderTop(BorderStyle.THIN, region, sxssfSheet);
                RegionUtil.setBorderLeft(BorderStyle.THIN, region, sxssfSheet);
                RegionUtil.setBorderBottom(BorderStyle.THIN, region, sxssfSheet);
                RegionUtil.setBorderRight(BorderStyle.THIN, region, sxssfSheet);
            }
        }

        //获取用户数据(其实就是每一行的数据)
        if (userIds.length > 0) {
            List<UserBasicVo> userBasicVoList = excelCommonMapper.getUserBasicVoList(userIds);
            int rowUser = 2;
            for (UserBasicVo userBasicVo : userBasicVoList) {
                rowUser++;
                SXSSFRow row = sxssfSheet.createRow(rowUser);
                row.createCell(0).setCellValue(userBasicVo.getDealerName());
                row.createCell(1).setCellValue(userBasicVo.getRoleName());
                row.createCell(2).setCellValue(userBasicVo.getUserName());
                row.createCell(3).setCellValue(userBasicVo.getPhoneNumber());
                //查询是否通过
                List<UserLearn> userLearnList = excelCommonMapper.getStudyStatus(userBasicVo.getUserId(), jobLearningExcelVo.getXiLieIds());
                for (UserLearn userLearn : userLearnList) {
                    row.createCell(map.get(userLearn.getLearnId())).setCellValue(userLearn.getStudyStatus());
                }
            }
        }

        try {
            book.write(response.getOutputStream());
            System.out.println("----Excle文件已生成------");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(book);
        }
    }

    private static void defaultHeadStyle(CellStyle headStyle) {
        headStyle.setBorderTop(BorderStyle.THIN);
        headStyle.setBorderLeft(BorderStyle.THIN);
        headStyle.setBorderBottom(BorderStyle.THIN);
        headStyle.setBorderRight(BorderStyle.THIN);

        headStyle.setAlignment(HorizontalAlignment.CENTER);
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
    }

实体类HeaderNode

@Data
public class HeaderNode implements Serializable {

    private static final long serialVersionUID = 3731735396808031009L;

    /**
     * 标题头
     */
    private String headerName;

    /**
     * 层级
     */
    private int row;

    /**
     * 非叶子节点列跨度
     */
    private int overNodeCount;

    /**
     * 当前列没有子节点
     */
    private boolean overNode = true;

    /**
     * 列
     */
    private int column;

    /**
     * 宽度
     */
    private int width = 15;

    public HeaderNode() {
    }

    public HeaderNode(String headerName, int row, int column) {
        this.headerName = headerName;
        this.row = row;
        this.column = column;
    }

    public HeaderNode(String headerName, int row, int overNodeCount, boolean overNode, int column) {
        this.headerName = headerName;
        this.row = row;
        this.overNodeCount = overNodeCount;
        this.overNode = overNode;
        this.column = column;
    }
}