Training of CBX (Training_07)

ExcelToXml.java

package com.cn.poi;
import java.io.File;
import java.io.FileWriter;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;

public class ExcelToXml {
    public static void main(final String[] args) throws Exception {
        generateXml("f:\\system.xlsx", "f:\\system.xml");
    }
    /**
     * excel转换成下来xml的方法
     * @param excelPath 要转换excel的路径
     * @param xmlPath   生成xml文档的路径
     * @throws Exception
     */
    public static void generateXml(final String excelPath, final String xmlPath) throws Exception {
        final OutputFormat format = OutputFormat.createPrettyPrint();// 输出格式化
        format.setEncoding("UTF-8");// 指定XML编码
        final XMLWriter output = new XMLWriter(new FileWriter(xmlPath), format);
        final Document document = DocumentHelper.createDocument();//使用DocumentHelper.createDocument方法建立一个文档实例
        Element RootElement = document.getRootElement();
        final File tempFile = new File(excelPath.trim());
        final String fileName = tempFile.getName();
        final String prefix = fileName.substring(fileName.lastIndexOf("."));
        final int num = prefix.length();
        final String fileOtherName = fileName.substring(0, fileName.length() - num);
        if (RootElement == null) {
            RootElement = document.addElement(fileOtherName);// 创建根节点
            RootElement.addAttribute("position", fileName);
        }
        final Workbook wb = WorkbookFactory.create(new File(excelPath));
        final int sheetNum = wb.getNumberOfSheets();
        System.out.println("sheet页的数量:" + sheetNum);
        for (int i = 0; i < sheetNum; i++) {
            final Sheet sheet = wb.getSheetAt(i);
            boolean isFieldIdLabel = false;
            boolean isFieldValue = false;
            int coloumNum = 0;
            final List<String> fields = new ArrayList<String>();
            final String sheetName = sheet.getSheetName();
            System.out.println("一级节点：" + sheetName);
            final String prePosition = new String(fileName + "," + sheetName);
            final Element firstElm = RootElement.addElement("sheet");
            firstElm.addAttribute("id", sheetName);
            firstElm.addAttribute("position", prePosition.toString());
            Element secondElm = null;
            Element thirdElm = null;
            for (final Row row : sheet) {
                coloumNum = row.getPhysicalNumberOfCells();
                System.out.println("列的数量：" + coloumNum);
                final String rowNum = String.valueOf(row.getRowNum() + 1);
                Element fourthElm = null;
                boolean isNextRow = true;
                for (final Cell cell : row) {
                    final String cellStr = cellValueToString(cell);
                    final int cellIndex = cell.getColumnIndex();
                    if (cellStr.startsWith("##")) {
                        System.out.println("第一种情况##");
                        final String cellElm = cellStr.substring(2);
                        System.out.println("二级节点：" + cellElm);
                        secondElm = firstElm.addElement(cellElm);
                        secondElm.addAttribute("position", prePosition + "," + rowNum);
                    } else if (cellStr.startsWith("#begin")) {
                        System.out.println("第二种情况#begin_elem");
                        thirdElm = secondElm.addElement("elements");
                        final String[] arrayStr = cellStr.split(":");
                        if (arrayStr.length == 1) {
                            thirdElm.addAttribute("id", "default");
                        } else {
                            thirdElm.addAttribute("id", arrayStr[1]);
                        }
                        isFieldIdLabel = true;
                    } else if (isFieldIdLabel) {
                        System.out.println("第三种情况字段名称:" + cellStr + ",索引：" + cellIndex);
                        if (!cellStr.isEmpty()) {
                            if (coloumNum != 0) {
                                fields.add(cellStr);
                                coloumNum -= 1;
                            }
                        } else {
                            if (coloumNum != 0) {
                                coloumNum -= 1;
                            }
                        }
                        if (coloumNum == 0) {
                            System.out.println("fields集合长度：" + fields.size());
                            printList(fields);
                            isFieldIdLabel = false;
                            isFieldValue = true;
                        }
                    } else if (cellStr.startsWith("#end")) {
                        System.out.println("这是结尾#end");
                        isFieldValue = false;
                        fields.clear();
                    } else if (isFieldValue) {
                        if (isNextRow) {
                            fourthElm = thirdElm.addElement("element");
                            fourthElm.addAttribute("position", prePosition + "," + rowNum);
                            final Element fifthElm = fourthElm.addElement(fields.get(cellIndex));
                            fifthElm.setText(cellStr);
                            isNextRow = false;
                        } else {
                            if (cellIndex < fields.size()) {
                                final Element fifthElm = fourthElm.addElement(fields.get(cellIndex));
                                fifthElm.setText(cellStr);
                            }
                        }
                    } else {
                        System.out.println("这是其他的情况，行数是：" + String.valueOf(row.getRowNum() + 1) + ",列数是："
                                + String.valueOf(cellIndex + 1));
                    }
                }
            }
        }
        System.out.println("文件转换成功！");
        output.write(document);
        output.flush();
        output.close();
    }
    /**
     * 将单元格的内容全部转换成字符串
     */
    private static String cellValueToString(final Cell cell) {
        String str = "";
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            str = cell.getRichStringCellValue().getString();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                str = cell.getDateCellValue().toString();
            } else {
                str = String.valueOf(cell.getNumericCellValue());
            }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            str = String.valueOf(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            str = cell.getCellFormula();
            break;
        default:
            str = cell.getRichStringCellValue().getString();
            break;
        }
        return str;
    }
    public static void printList(final List<String> list) {
        for (int k = 0; k < list.size(); k++) {
            System.out.println(list.get(k));
        }
    }

}