快捷導(dǎo)航

Java樹形結(jié)構(gòu)數(shù)據(jù)生成導(dǎo)出excel文件方法記錄

更新時間：2021年10月21日 09:02:27 作者：早晨陽光一般暖

最近好像得罪了poi,遇到的都是導(dǎo)出word、Excel、pdf的問題,下面這篇文章主要給大家介紹了關(guān)于Java樹形結(jié)構(gòu)數(shù)據(jù)生成導(dǎo)出excel文件的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),需要的朋友可以參考下

什么是樹形結(jié)構(gòu)數(shù)據(jù)

效果

用法

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");

源碼

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;
 
/**
 * 樹形結(jié)構(gòu)數(shù)據(jù)導(dǎo)出excel工具
 * <p>
 * Created by lzy on 2021/2/24 14:09
 */
@SuppressWarnings("ALL")
public class Tree2ExcelUtil {
 
    /**
     * 樹形結(jié)構(gòu)數(shù)據(jù)生成excel文件
     *
     * @param tree     樹形數(shù)據(jù)
     * @param filePath 文件路徑
     * @return
     */
    public static boolean tree2Excel(Map tree, String filePath) {
        return tree2Excel(tree, filePath, null, null);
    }
 
    /**
     * 樹形結(jié)構(gòu)數(shù)據(jù)生成excel文件
     *
     * @param tree         樹形數(shù)據(jù)
     * @param filePath     文件路徑
     * @param lableName    標(biāo)簽Key名稱
     * @param childrenName 子節(jié)點(diǎn)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;
    }
 
    /**
     * 樹形結(jié)構(gòu)數(shù)據(jù)生成Workbook對象
     *
     * @param tree    樹形數(shù)據(jù)
     * @param fileSuf 文件后綴，xls/xlsx
     * @return
     */
    public static Workbook tree2Worbook(Map tree, String fileSuf) {
        return tree2Worbook(tree, fileSuf, null, null);
    }
 
    /**
     * 樹形結(jié)構(gòu)數(shù)據(jù)生成Workbook對象
     *
     * @param tree         樹形數(shù)據(jù)
     * @param fileSuf      文件后綴，xls/xlsx
     * @param lableName    標(biāo)簽Key名稱
     * @param childrenName 子節(jié)點(diǎn)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;
    }
 
 
    //具體實(shí)現(xiàn)
 
    /**
     * 標(biāo)識最大列
     */
    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);
    }
 
    /**
     * 主要算法，計算列的坐標(biāo)，計算每個節(jié)點(diǎn)所占行
     *
     * @param tree  數(shù)據(jù)
     * @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);
                }
            }
        }
    }
 
    /**
     * 主要算法，計算行的坐標(biāo)
     *
     * @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);
                }
            }
        }
    }
 
    /**
     * 創(chuàng)建excel文件
     *
     * @param filePath 文件路徑，具體路徑到文件名
     * @param tree     數(shù)據(jù)
     * @throws IOException
     */
    private static void createExcel(String filePath, Map tree) throws IOException {
        File file = new File(filePath);
        boolean bfile = file.createNewFile();
        // 復(fù)制模板到新文件
        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數(shù)據(jù)
     *
     * @param tree 數(shù)據(jù)
     * @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;
        }
 
        //創(chuàng)建一個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;
    }
 
    /**
     * 根據(jù)計算好的坐標(biāo)填充每一個單元格
     *
     * @param sheet  #
     * @param tree   數(shù)據(jù)
     * @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;
    }
 
    /**
     * 關(guān)閉流
     *
     * @param closeables 不定長數(shù)組 流對象
     */
    public static void closeStream(Closeable... closeables) {
        for (Closeable closeable : closeables) {
            if (closeable != null) {
                try {
                    closeable.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
 
}

總結(jié)

到此這篇關(guān)于Java樹形結(jié)構(gòu)數(shù)據(jù)生成導(dǎo)出excel文件的文章就介紹到這了,更多相關(guān)Java樹形結(jié)構(gòu)數(shù)據(jù)生成導(dǎo)出excel內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: