说明:适合数据库导出为xml时转成Excel

 

本工具将上传至GitHub:https://github.com/xiaostudy/xiaostudyAPI3

doc4j的maven依赖

1 <!--xml解析的dom4j-->
2         <!-- https://mvnrepository.com/artifact/dom4j/dom4j -->
3         <dependency>
4             <groupId>dom4j</groupId>
5             <artifactId>dom4j</artifactId>
6             <version>1.6.1</version>
7         </dependency>

 

主要代码

package com.xiaostudy.util;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

/**
 * XML工具类
 * @author xiaostudy
 * @date 2019.4.26
 * @version 1.0.0
 */
public class XmlUtil {

    public static void main(String[] args) {
        Boolean aBoolean = Xml2ExcelFile("C:\\Users\\Administrator\\Desktop\\test.xml", "C:\\Users\\Administrator\\Desktop\\test2.xlsx");
        System.out.println(aBoolean);
    }

    /**
     * Xml转Excel文件
     * @param strXmlFilePath Xml文件路径
     * @param strExcleFilePath Excel文件存放路径
     * @return
     */
    public static Boolean Xml2ExcelFile(String strXmlFilePath, String strExcleFilePath) {
        if(StringUtil.isTrimNull(strXmlFilePath) || StringUtil.isTrimNull(strExcleFilePath)) {
            return false;
        }

        List<List<String>> listList = readXml(strXmlFilePath);
        if(null == listList || listList.isEmpty()) {
            return false;
        }

        return ExcelUtil.createExcelFile(strExcleFilePath, listList);
    }

    /**
     * 读取Xml文件,以List<List<String>>形式返回
     * @param strFile
     * @return
     */
    public static List<List<String>> readXml(String strFile) {
        if(StringUtil.isTrimNull(strFile)) {
            return null;
        }

        SAXReader reader = new SAXReader();
        File file = new File(strFile);
        if(!file.exists() || !file.isFile()) {
            return null;
        }

        Document document = null;
        try {
            document = reader.read(file);
        } catch (DocumentException e) {
            e.printStackTrace();
        }
        List<List<String>> listList = new ArrayList<>();

        if (null != document) {
            Element root = document.getRootElement();
            List<Element> childElements = root.elements();
            List<String> titleList = new ArrayList<>();
            listList.add(titleList);
            boolean flag = true;
            for (Element child : childElements) {
                List<String> list = new ArrayList<>();

                List<Element> elementList = child.elements();
                for (Element ele : elementList) {
                    if(flag) {
                        titleList.add(ele.getName());
                    }
                    list.add(ele.getText());
                }
                flag = false;

                listList.add(list);

                System.out.println();
            }
        }

        return listList;
    }
}

 

 

依赖的工具类GitHub上有https://github.com/xiaostudy/xiaostudyAPI3

 

StringUtil.isTrimNull

1 public static boolean isTrimNull(Object obj) {
2         if (null != obj) {
3             return isNull(obj.toString().trim());
4         } else {
5             return true;
6         }
7     }

 

Excel.createExcelFile

 1 public static <T> Boolean createExcelFile(String fileName, List<List<T>> list) {
 2         logger.debug(">>>>>" + CLASSNAME + ".createExcelFile()...");
 3         logger.debug("fileName: " + fileName);
 4         logger.debug("list: " + list);
 5 
 6         FileOutputStream fileOut = null;
 7         try {
 8             if(StringUtil.isTrimNull(fileName)) {
 9                 return false;
10             }
11 
12             File file = new File(fileName);
13             if(file.exists()) {
14                 System.out.println("文件已存在!");
15                 logger.debug("文件已存在!");
16                 return false;
17             }
18 
19             Workbook workbook = null;
20             String excelType = null;
21             if(FileUtil.isFileNameEndsWith(fileName, ".xls")) {
22                 excelType = "xls";
23                 workbook = new HSSFWorkbook();
24             } else if(FileUtil.isFileNameEndsWith(fileName, ".xlsx")) {
25                 excelType = "xlsx";
26                 workbook = new XSSFWorkbook();
27             } else {
28                 return false;
29             }
30 
31             fileOut = new FileOutputStream(fileName);
32             workbook.write(fileOut);
33             fileOut.close();
34 
35             FileInputStream is = new FileInputStream(file);
36             if("xls".equals(excelType)) {
37                 POIFSFileSystem fs = new POIFSFileSystem(is);
38                 workbook = new HSSFWorkbook(fs);
39             } else if("xlsx".equals(excelType)) {
40                 workbook = new XSSFWorkbook(is);
41             }
42 
43             Sheet sheet = workbook.createSheet();
44             sheet = setSheetValue(sheet , list);
45 
46             fileOut = new FileOutputStream(fileName);
47             workbook.write(fileOut);
48         } catch (Exception e) {
49             e.printStackTrace();
50         } finally {
51             if(fileOut != null) {
52                 try {
53                     fileOut.close();
54                 } catch (IOException e) {
55                     e.printStackTrace();
56                 }
57             }
58             logger.debug("<<<<<" + CLASSNAME + ".createExcelFile().");
59             return true;
60         }
61     }