Java 树形结构数据生成导出excel文件(转载)

　　转载自https://blog.csdn.net/LZY_1993/article/details/114083456

  1. 效果

     

  2. 用法

    1 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\"}]}]}]}";
2 Map tree = JSONObject.parseObject(jsonStr, Map.class);
3 tree2Excel(tree, "E:\\" + System.currentTimeMillis() + ".xls", "name", "children");

  3. 源码

     

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

     

     

posted @ 2021-02-25 19:29  早晨阳光一般暖  阅读(3014)  评论(0)    收藏  举报