解析xml文件并写入Excel表

解析xml文件并写入Excel表

类似于如下格式的xml数据，解析之后将数据写入Excel中

<?xml version="1.0" encoding="UTF-8"?>
<DATASETS>
    <DATASET>
        <education>undergraduate</education>
        <occupation>programmer</occupation>
        <name>张三</name>
        <weight>150</weight>
        <age>23</age>
        <height>173</height>
        <hobby>basketball</hobby>
    </DATASET>
    <DATASET>
        <education>undergraduate</education>
        <occupation>programmer</occupation>
        <name>王一</name>
        <weight>120</weight>
        <age>22</age>
        <height>168</height>
        <hobby>game</hobby>
    </DATASET>
    <DATASET>
        <education>undergraduate</education>
        <occupation>programmer</occupation>
        <name>魏某</name>
        <weight>125</weight>
        <age>22</age>
        <height>170</height>
        <hobby>novel</hobby>
    </DATASET>
</DATASETS>

一. 构建springboot项目，导入依赖

 <!--JDom解析xml -->
 <dependency>
      <groupId>org.jdom</groupId>
      <artifactId>jdom</artifactId>
      <version>1.1.3</version>
  </dependency>
 <!--   读写Excel文件依赖     -->
  <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>4.1.0</version>
  </dependency>
  <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>4.1.0</version>
  </dependency>

二. 直接上方法

    /**
     * xml文件转为Excel文件
     * 返回：字符串
     * @param xmlPosition (xml文件的位置)
     * @param excelName （默认生成扩展名为xls的文件，默认位置生成在桌面）
     * @return
     */
    public static void xmlToExcel(String xmlPosition,String excelName){
        //1.创建SAXBuilder对象
        SAXBuilder saxBuilder = new SAXBuilder();
        //2.创建输入流
        InputStream is = null;
        try {
            is = new FileInputStream(new File(xmlPosition));
        }catch (Exception e){
            e.printStackTrace();
            System.out.println("文件位置未找到！");
            return;
        }
 
        //3.将输入流加载到build中
        Document document = null;
        try {
            document = saxBuilder.build(is);
        }catch (Exception e){
            e.printStackTrace();
            System.out.println("文件内容构建失败！");
            System.out.println("请检查文件内容是否符合xml格式");
        }
        //4.获取根节点
        Element rootElement = document.getRootElement();
        //5.获取子节点
        List<Element> children = rootElement.getChildren();
        ArrayList<HashMap<String, Object>> list = new ArrayList<>();
        for (Element child : children) {
            // System.out.println("通过rollno获取属性值:"+child.getAttribute("属性名"));
            // List<Attribute> attributes = child.getAttributes();
            //打印属性
            // for (Attribute attr : attributes) {
            // System.out.println(attr.getName()+":"+attr.getValue());
            // }
            // 标签的属性，可以没有
 
            List<Element> childrenList = child.getChildren();
            HashMap<String, Object> map = new HashMap<>();
            for (Element o : childrenList) {
                // System.out.println("节点名:"+o.getName()+"---"+"节点值:"+o.getValue());
                map.put(o.getName(), o.getValue());
            }
            list.add(map);
        }
         // System.out.println(list);
        if (list.size() > 0){
            // 新建excel文件（如果存在就提示换个名字）
            File file = new File("C:\\Users\\Asus\\Desktop\\" + excelName + ".xls");
            if (file.exists()){
                System.out.println("该位置存在相同名字的文件，请重新命名！");
                return;
            }else {
                try {
                    boolean newFile = file.createNewFile();
                    if (newFile){
                        System.out.println("文件创建成功！");
                        System.out.println("开始导入数据！");
                        HSSFWorkbook wb = new HSSFWorkbook();
                        HSSFSheet sheet = wb.createSheet("sheet1");
                        for (int i = 0; i <= list.size(); i++) {
                            HSSFRow row = sheet.createRow(i);
                            if (i == 0){
                                Set<Map.Entry<String, Object>> entries = list.get(i).entrySet();
                                int j = 0;
                                for (Map.Entry<String, Object> entry : entries) {
                                    HSSFCell cell = row.createCell(j);
                                    cell.setCellValue(entry.getKey());
                                    j++;
                                }
                            }else {
                                Set<Map.Entry<String, Object>> entries = list.get(i-1).entrySet();
                                int j = 0;
                                for (Map.Entry<String, Object> entry : entries) {
                                    HSSFCell cell = row.createCell(j);
                                    cell.setCellValue(entry.getValue().toString());
                                    j++;
                                }
                            }
                        }
                        ByteArrayOutputStream os = new ByteArrayOutputStream();
                        try {
                            wb.write(os);
                        }catch (IOException e){
                            System.out.println("导入失败！");
                            e.printStackTrace();
                        }
                        byte[] content = os.toByteArray();
                        OutputStream fos = null;
                        try {
                            fos = new FileOutputStream(file);
                            fos.write(content);
                            os.close();
                            fos.close();
                            System.out.println("数据导入成功！");
                        }catch (IOException e){
                            System.out.println("导入失败！");
                            e.printStackTrace();
                        }
                    }else {
                        System.out.println("文件创建失败！");
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                    System.out.println("excel文件创建失败！");
                }
            }
        }else {
            System.out.println("源文件没有数据！");
            return;
        }

三. 最后excel文件

四. 扩展： 上面提到了写入excel，下面来试试读

public void pushData() {
        // 读取本地Excel表格数据
        File file = new File("excel文件的地址");
        Workbook wb = null;
        try {
            wb = readExcel(new FileInputStream(file), file.getName());
            Row row = null;
            if(wb != null){
                //获取第一个sheet
                Sheet sheet = wb.getSheetAt(0);
 
                // 读取出来放在list里
                ArrayList<HashMap<String, Object>> list = new ArrayList<>();
                // 最大行数
                int rows = sheet.getPhysicalNumberOfRows();
                // 获取表格最后一行的行数
				int lastRowNum = sheet.getLastRowNum();
                // 获取第一行
                Row firstRow = sheet.getRow(0);
                // 获取第一行最后一列号
                short lastCellNum = firstRow.getLastCellNum();
                // 第一行第一列
                Cell cell = firstRow.getCell(0);
                // 第一行第一列的值
                String value = cell.getStringCellValue();
				
				// 根据实际情况可以把数据放在list中，通过行和列循环
            }
        } catch (FileNotFoundException e) {
            System.out.println("读取文件失败！");
            e.printStackTrace();
        }
    }
 
    public Workbook readExcel(InputStream is, String extString){
        Workbook wb = null;
        extString = extString.substring(extString.lastIndexOf("."));
        try {
            if(".xls".equals(extString)){
                return new HSSFWorkbook(is);
            }else if(".xlsx".equals(extString)){
                return new XSSFWorkbook(is);
            }else {
                return null;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }