正文

Java树形结构数据生成导出excel文件方法记录

︶ㄣSDDLL学院 メ
SDDLL学院V管理员 / 阅读
0710
最近好像得罪了poi,遇到的都是导出word、Excel、pdf的问题,下面这篇文章主要给大家介绍了关于Java树形结构数据生成导出excel文件的相关资料,文中通过示例代码介绍的非常详细,需要的朋友

最近好像得罪了poi,遇到的都是导出word、Excel、pdf的问题,下面这篇文章主要给大家介绍了关于Java树形结构数据生成导出excel文件的相关资料,文中通过示例代码介绍的非常详细,需要的朋友可以参考下

什么是树形结构数据

Java树形结构数据生成导出excel文件方法记录

效果

Java树形结构数据生成导出excel文件方法记录

用法

?
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