什么是樹形結構數據
效果
用法
|
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
