树形数据以POI导出Excel的方法_java - poi递归导出树结构excel-CSDN博客

本文链接：https://blog.csdn.net/weixin_40076255/article/details/88633193

一、简介

这里介绍了使用java poi导出树形结构数据到excel表，这是一种方法。

二、数据源（树形结构数据）

DcdbInfoAO infoAO（根数据）

TaskMainAO（一级目录数据）

TaskSubAO（二级目录数据）

三、代码示例

/**
     * POI : 导出数据，存放于Excel中
     * 
     * @param os
     *            输出流 (action: OutputStream os = response.getOutputStream();)
     * @param infoAO
     *            要导出的数据记录集合(根数据)
     * @param mainAO
     *            重点任务数据(重大工作项:一级目录)
     * @param mainAO
     *            分解任务数据(任务分解项:二级目录)
     */
public void exportPoiExcel(OutputStream os, DcdbInfoAO infoAO) {

	//取出数据源;
	String headTitle = infoAO.getTitle();//标题;
	List<DcdbTaskMainAO> mainList = infoAO.getMainTaskList();
	try {
		// 创建Excel工作薄
		Workbook book = null;
		try {
			book = new XSSFWorkbook();//excell2007版
		} catch (Exception ex) {
			book = new HSSFWorkbook();//excell2003版
		}
		// 在Excel工作薄中建一张sheet工作表;
		sheetTitle = infoAO.getTitle();
		Sheet sheet = book.createSheet(sheetTitle);	
		//设置标题和表头：第一行为标题，第二行为表头
		createTitle(book, sheet, headTitle);
		createHead(book, sheet);
		//表内容默认从工作表第三行开始;
		int start = 2;
		for (int i = 0; i < datas.size();i++) {
			DcdbTaskSummaryAO sumAO=datas.get(i);
			List<DcdbTaskSubAO> subAOList = sumAO.getTaskSubList();	
			for(int j = 0; j < subAOList.size();j++){
				DcdbTaskSubAO subAO=subAOList.get(j);
				//在sheet中创建行;
				Row row = sheet.createRow(start + j);
				//在row创建8列;
				Cell cell0 = row.createCell(0);
				Cell cell1 = row.createCell(1);
				Cell cell2 = row.createCell(2);
				Cell cell3 = row.createCell(3);
				Cell cell4 = row.createCell(4);
				Cell cell5 = row.createCell(5);
				Cell cell6 = row.createCell(6);
				Cell cell7 = row.createCell(7);
				//写入前4列数据（一级目录）
				cell0.setCellValue(sumAO.getBusinessArea());
				cell1.setCellValue(sumAO.getBusinessArea());
				cell2.setCellValue(sumAO.getBusinessArea());
				cell3.setCellValue(sumAO.getBusinessArea());
				//写入后5列数据（二级目录）
				cell4.setCellValue(subAO.getBusinessArea());
				cell5.setCellValue(subAO.getBusinessArea());
				cell6.setCellValue(subAO.getBusinessArea());
				cell7.setCellValue(subAO.getBusinessArea());
				cell8.setCellValue(subAO.getBusinessArea());
			}
			//合并一级目录单元格，4个参数分别为：合并起始行，结束行，起始列，结束列;
			//合并单元格之前，每一行每一列都有数据,合并后只取起始行起始列的数据;
			sheet.addMergedRegion(new CellRangeAddress(start,start + subAOList.size()-1, 0, 0));
			sheet.addMergedRegion(new CellRangeAddress(start,start + subAOList.size()-1, 1, 1));
			sheet.addMergedRegion(new CellRangeAddress(start,start + subAOList.size()-1, 2, 2));
			sheet.addMergedRegion(new CellRangeAddress(start,start + subAOList.size()-1, 3, 3));
			start += subAOList.size();
		}
	}
	
	/**
     * 给工作表设置标题
     * 
     * @param sheet
     */
    public void createTitle(Workbook book, Sheet sheet, String headTitle) {
        CellStyle style = createTitleStyle(book);
        Row row = sheet.createRow(0);// 创建第一行,设置表的标题;
        row.setHeightInPoints(36);//设置行的高度是34个点
        Cell cell = row.createCell(0);//创建第一列
		cell.setCellValue(headTitle);//设值
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));//第一行跨表所有列;
    }

    /**
     * 设置导出表的表头
     * 
     * @param book
     * @param sheet
     */
    private void createHead(Workbook book, Sheet sheet) {
        // 设置单元格格式(文本)
        // 第二行为表头行
        String title = "";
        Row row = sheet.createRow(1);// 创建第一行
        row.setHeightInPoints(22);//设置行的高度是20个点
        for (int j = 0; j < 8; j++) {
			Cell cell = row.createCell(j);//创建9列
            if (j == 0) {
                title = "业务领域";
            }
            if (j == 1) {
                title = "编号";
            }
            if (j == 2) {
                title = "重点工作";
            }
            if (j == 3) {
                title = "预期目标或成效";
            }
            if (j == 4) {
                title = "牵头部门";
            }
            if (j == 5) {
                title = "任务及目标分解";
            }
            if (j == 6) {
                title = "责任部门";
            }
            if (j == 7) {
                title = "办结期限";
            }
            if (j == 8) {
                title = "备注";
            }
			cell.setCellValue(title);//设值
        }
    }
}