Excel树状数据绘制导出

        //存放数据的二维集合，twoDimensional 中每个List是树状结构的一个分支的所有数据
        List<List<JSONObject>> twoDimensional = new ArrayList<>();
        
        
        //创建对象
        XSSFWorkbook xwb = new XSSFWorkbook();
        //创建工作表
        Sheet sheet = xwb.createSheet("统计");
        //动态创建首行表头
        Row firstRow = sheet.createRow(0);
        JSONObject cm = new JSONObject(columnMap);
        for (int i = 0; i < tier+keys.size(); i++) {
            Cell cell = firstRow.createCell(i);
            if (i<tier){
                String s="一";
                switch (i){
                    case 1 : s="二";break;
                    case 2 : s="三";break;
                }
                cell.setCellValue(s+"级分类");
            }else {
                cell.setCellValue(cm.getString(keys.get(i - tier)));
            }
        }

绘制完表头如下：

 

        //动态绘制数据，tier是层级数，根据业务最多三级
        for (int i = 0; i < twoDimensional.size(); i++) {
            Row row = sheet.createRow(i+1);
            List<JSONObject> list = twoDimensional.get(i);

            for (int j = 0; j < keys.size()+tier; j++) {
                Cell cell = row.createCell(j);
                if (j<tier){
                    //绘制层级标签
                    if (j==0){
                        JSONObject jsonObject = list.get(tier - 1);
                        cell.setCellValue(jsonObject.getString("dictLabel"));
                        continue;
                    }
                    if (j==1){
                        Map map = list.get(tier - 2);
                        cell.setCellValue((String)map.get("dictLabel"));
                        continue;
                    }
                    if (j==2){
                        Map map = list.get(tier - 3);
                        cell.setCellValue((String)map.get("dictLabel"));
                        continue;
                    }
                }else {
                    //绘制层级的数据
                    JSONObject bean = new JSONObject(list.get(0));
                    cell.setCellValue(bean.getString(keys.get(j - tier)));
                }
            }
        }
        
        //添加总合计行，并合并单元格
        Row totalRow = sheet.createRow(twoDimensional.size()+1);
        for (int j = 0; j < keys.size()+tier; j++) {
            Cell cell = totalRow.createCell(j);
            if (j<tier){
                cell.setCellValue("合计");
            }else {
                cell.setCellValue(topBean.getString(keys.get(j - tier)));
            }
        }
        if (tier != 1){
            sheet.addMergedRegion(new CellRangeAddress(twoDimensional.size()+1,twoDimensional.size()+1,0,tier-1));
        }

添加数值后如下：

 

        //合并相同的单元格
        int lastRowNum = sheet.getLastRowNum();
        int index = 0;
        //根据业务只有标签这几列需要合并
        for (int i = 0; i < tier-1; i++) {
            //比较相邻cell的值是否相同，并记录
            for (int j = 0; j < lastRowNum; j++) {
                Row row = sheet.getRow(j);
                Cell cell = row.getCell(i);
                String stringCellValue = cell.getStringCellValue();
                if ("合计".equals(stringCellValue)){
                    sheet.addMergedRegion(new CellRangeAddress(j,j,i,i+1));
                    continue;
                }

                Row nextRow = sheet.getRow(j+1);
                Cell nextcell = nextRow.getCell(i);
                String nextStringCellValue = nextcell.getStringCellValue();
                if (stringCellValue.equals(nextStringCellValue)){
                    if (j+1 == lastRowNum){
                        if (index != 0){
                            sheet.addMergedRegion(new CellRangeAddress(j-index,j+1,i,i));
                            index = 0;
                        }
                    }else {
                        index++;
                    }
                }else {
                    if (index != 0){
                        sheet.addMergedRegion(new CellRangeAddress(j-index,j,i,i));
                        index = 0;
                    }
                }
            }
        }
        return xwb;

合并后最终如下：