什么是树形结构数据
效果
用法
|
1 2 3 |
String jsonStr = "{\"name\":\"aaa\",\"children\":[{\"name\":\"bbb\",\"children\":[{\"name\":\"eee\"},{\"name\":\"fff\",\"children\":[{\"name\":\"iii\"},{\"name\":\"jjj\",\"children\":[{\"name\":\"qqq\"},{\"name\":\"ttt\"}]}]},{\"name\":\"www\"}]},{\"name\":\"ccc\",\"children\":[{\"name\":\"ggg\"},{\"name\":\"hhh\",\"children\":[{\"name\":\"kkk\",\"children\":[{\"name\":\"ttt\"},{\"name\":\"mmm\"}]},{\"name\":\"uuu\"}]},{\"name\":\"ooo\"}]},{\"name\":\"ddd\",\"children\":[{\"name\":\"ggg\"},{\"name\":\"hhh\",\"children\":[{\"name\":\"kkk\"},{\"name\":\"uuu\"}]}]}]}" ; Map tree = JSONObject.parseObject(jsonStr, Map. class ); tree2Excel(tree, "E:\\" + System.currentTimeMillis() + ".xls" , "name" , "children" ); |
源码
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 |
package pers.xxx.demo.tree2excel;
import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.Closeable; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.util.List; import java.util.Map;
/** * 树形结构数据导出excel工具 * <p> * Created by lzy on 2021/2/24 14:09 */ @SuppressWarnings ( "ALL" ) public class Tree2ExcelUtil {
/** * 树形结构数据生成excel文件 * * @param tree 树形数据 * @param filePath 文件路径 * @return */ public static boolean tree2Excel(Map tree, String filePath) { return tree2Excel(tree, filePath, null , null ); }
/** * 树形结构数据生成excel文件 * * @param tree 树形数据 * @param filePath 文件路径 * @param lableName 标签Key名称 * @param childrenName 子节点Key名称 * @return */ public static boolean tree2Excel(Map tree, String filePath, String lableName, String childrenName) { if (isBlank(filePath)) { System.err.println( "文件名称不能为空" ); return false ; } try { doSame(tree, lableName, childrenName); createExcel(filePath, tree); return true ; } catch (IOException e) { e.printStackTrace(); } return false ; }
/** * 树形结构数据生成Workbook对象 * * @param tree 树形数据 * @param fileSuf 文件后缀，xls/xlsx * @return */ public static Workbook tree2Worbook(Map tree, String fileSuf) { return tree2Worbook(tree, fileSuf, null , null ); }
/** * 树形结构数据生成Workbook对象 * * @param tree 树形数据 * @param fileSuf 文件后缀，xls/xlsx * @param lableName 标签Key名称 * @param childrenName 子节点Key名称 * @return */ public static Workbook tree2Worbook(Map tree, String fileSuf, String lableName, String childrenName) { if (isBlank(fileSuf)) { System.err.println( "必须指定文件后缀" ); return null ; } try { doSame(tree, lableName, childrenName); return procesData(tree, fileSuf); } catch (Exception e) { e.printStackTrace(); } return null ; }
//具体实现
/** * 标识最大列 */ private static int maxCol = 0 ; private static String lableName = "lable" ; private static String childrenName = "children" ; private static final String COL = "col" ; private static final String ROW = "row" ; private static final String ROW_OFT = "rowOft" ; private static final String ROW_SIZE = "rowSize" ;
private static void doSame(Map tree, String lableName, String childrenName) { if (!isBlank(lableName)) { Tree2ExcelUtil.lableName = lableName; } if (!isBlank(childrenName)) { Tree2ExcelUtil.childrenName = childrenName; } coreAlgoCol(tree, 1 ); coreAlgoRow(tree); }
/** * 主要算法，计算列的坐标，计算每个节点所占行 * * @param tree 数据 * @param col 递增的列 * @param trees 把高级别向下传递计算递增的行高 */ private static void coreAlgoCol(Map tree, int col, Map... trees) { tree.put(COL, col); Object childrenObj = tree.get(childrenName); if (childrenObj != null ) { List<Map> children = (List<Map>) childrenObj; if (children.size() > 0 ) { int size = children.size() * 2 - 1 ; tree.put(ROW_SIZE, size); int len = trees != null ? trees.length + 1 : 1 ; Map[] arrData = new Map[len];
if (trees != null && trees.length > 0 ) { for ( int i = 0 ; i < trees.length; i++) { Map tree1 = trees[i]; tree1.put(ROW_SIZE, toInt(tree1.get(ROW_SIZE), 1 ) + size - 1 ); arrData[i] = tree1; } } arrData[len - 1 ] = tree; for (Map tree1 : children) { int newCol = col + 1 ; if (newCol > maxCol) { maxCol = newCol; } coreAlgoCol(tree1, newCol, arrData); } } } }
/** * 主要算法，计算行的坐标 * * @param tree */ private static void coreAlgoRow(Map tree) { if (toInt(tree.get(ROW)) == 0 ) { tree.put(ROW, Math.round(toInt(tree.get(ROW_SIZE), 1 ) / 2 .0f)); } Object childrenObj = tree.get(childrenName); if (childrenObj != null ) { List<Map> children = (List<Map>) childrenObj; if (children.size() > 0 ) { int tempOft = toInt(tree.get(ROW_OFT)); for (Map tree1 : children) { int rowSize = toInt(tree1.get(ROW_SIZE), 1 ); tree1.put(ROW_OFT, tempOft); tree1.put(ROW, tempOft + Math.round(rowSize / 2 .0f)); tempOft += rowSize + 1 ; coreAlgoRow(tree1); } } } }
/** * 创建excel文件 * * @param filePath 文件路径，具体路径到文件名 * @param tree 数据 * @throws IOException */ private static void createExcel(String filePath, Map tree) throws IOException { File file = new File(filePath); boolean bfile = file.createNewFile(); // 复制模板到新文件 if (bfile) { Workbook wk = procesData(tree, filePath); if (wk != null ) { FileOutputStream fos = null ; try { fos = new FileOutputStream(file); wk.write(fos);
fos.flush(); } finally { closeStream(fos); wk.close(); } } } }
/** * 处理excel数据 * * @param tree 数据 * @return 工作表对象 */ private static Workbook procesData(Map tree, String fileName) {
Workbook wk = null ; if (fileName.endsWith( "xls" )) { wk = new HSSFWorkbook(); } if (fileName.endsWith( "xlsx" )) { wk = new XSSFWorkbook(); } if (wk == null ) { System.err.println( "文件名称不正确" ); return null ; }
//创建一个sheet页 Sheet sheet = wk.createSheet( "Sheet1" );
int colSize = maxCol * 2 + 2 ; int rowSize = toInt(tree.get(ROW_SIZE), 1 ); for ( int i = 0 ; i <= rowSize; i++) { Row row = sheet.createRow(i); for ( int j = 0 ; j <= colSize; j++) { row.createCell(j); } } //配置单元格背景色 CellStyle style1 = wk.createCellStyle(); style1.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); style1.setFillPattern(FillPatternType.SOLID_FOREGROUND); CellStyle style2 = wk.createCellStyle(); style2.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex()); style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
dealCell(sheet, tree, style1, style2);
return wk; }
/** * 根据计算好的坐标填充每一个单元格 * * @param sheet # * @param tree 数据 * @param style1 单元格格式 * @param style2 单元格格式 */ private static void dealCell(Sheet sheet, Map tree, CellStyle style1, CellStyle style2) { Row row = sheet.getRow(toInt(tree.get(ROW))); int oftCol = (toInt(tree.get(COL)) - 1 ) * 2 + 1 ; Cell cell = row.getCell(oftCol); cell.setCellStyle(style1); cell.setCellValue(String.valueOf(tree.get(lableName)));
sheet.setColumnWidth(oftCol, 256 * 20 );
Object childrenObj = tree.get(childrenName); if (childrenObj != null ) { List<Map> children = (List<Map>) childrenObj; if (children.size() > 0 ) { int size = children.size();
int startRow = toInt(children.get( 0 ).get(ROW)); int endRow = toInt(children.get(size - 1 ).get(ROW)); int col = oftCol + 1 ; sheet.setColumnWidth(col, 256 ); for (; startRow <= endRow; startRow++) { sheet.getRow(startRow).getCell(col).setCellStyle(style2); }
for (Map child : children) { dealCell(sheet, child, style1, style2); } } } }
private static int toInt(Object val) { return toInt(val, 0 ); }
private static int toInt(Object val, Integer defVal) { try { return Integer.parseInt(String.valueOf(val)); } catch (NumberFormatException ignored) { } return defVal; }
private static boolean isBlank(String str) { return str == null || str.trim().length() == 0 ; }
/** * 关闭流 * * @param closeables 不定长数组 流对象 */ public static void closeStream(Closeable... closeables) { for (Closeable closeable : closeables) { if (closeable != null ) { try { closeable.close(); } catch (IOException e) { e.printStackTrace(); } } } }
} |
总结
到此这篇关于Java树形结构数据生成导出excel文件的文章就介绍到这了,更多相关Java树形结构数据生成导出excel内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！
原文链接：https://blog.csdn.net/LZY_1993/article/details/114083456
查看更多关于Java树形结构数据生成导出excel文件方法记录的详细内容...