基于POI和DOM4将Excel(2007)文档写进Xml文件

刚进公司的training, 下面是要求：

Requirements

Write a java program to read system.xlsx
Use POI API to parse all contents in the excel
Write all contents to an output file
The file should in XML format(optional)
The program can start with a bat command(optional)

Reference

POI official site -- http://poi.apache.org/ ---下载poi相关的包

CBX-Builder implementation -- \\triangle\share\git\training\CBX_Builder [develop branch]

package polproject;

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 {

    /**
     * @param args
     */
    public static void main(String[] args) throws Exception {

        toXml("D:/excel/system.xlsx", "D:/excel/system.xml");
    }

    /**
     * excel to xml
     */
    public static void toXml(String sourcePath, String targetPath) throws Exception {

        // 输出格式化
        final OutputFormat format = OutputFormat.createPrettyPrint();
        format.setEncoding("UTF-8"); // 指定XML编码
        final XMLWriter output = new XMLWriter(new FileWriter(targetPath), format);

        // 使用DocumentHelper.createDocument方法建立一个文档实例
        final Document document = DocumentHelper.createDocument();
        Element rootElm = document.getRootElement();

        final File file = new File(sourcePath);
        final String fileName = file.getName();

        // 如果想获得不带点的后缀，变为fileName.lastIndexOf(".")+1
        final String prefix = fileName.substring(fileName.lastIndexOf("."));

       // 得到后缀名长度
        final int prefix_num = prefix.length();

        // 得到文件名。去掉了后缀
        final String fileOtherName = fileName.substring(0, fileName.length() - prefix_num);

        if (rootElm == null) {
         // 创建根节点
            rootElm = document.addElement(fileOtherName);
            rootElm.addAttribute("pistion", fileName);
        }
        final Workbook wb = WorkbookFactory.create(new File(sourcePath));
        final int sheetNum = wb.getNumberOfSheets();
        for (int i = 0; i < sheetNum; i++) {
            final Sheet sheet = wb.getSheetAt(i);

            // 标记是否接下来的是否为fieldIdLabel
            boolean isFieldIdLabel = false;
            boolean isFieldValue = false;
            int coloumNum = 0;
            final List<String> fields = new ArrayList<String>();
            final String sheetName = sheet.getSheetName();

            // 1#添加一级节点
            final Element firstElm = rootElm.addElement("sheet");
            firstElm.addAttribute("id",sheetName);
            firstElm.addAttribute("position",fileName+ "," +sheetName);
            Element secondElm = null;
            Element thirdElm = null;
            for (final Row row : sheet) {
                coloumNum = row.getPhysicalNumberOfCells();

                Element fourthElm = null;
                boolean isNextRow = true;
              for (final Cell cell : row) {

                    final String cellStr = cellValueToString(cell);

                    // 2#添加二级节点
                    if (cellStr.startsWith("##")) {
                        final String cellElm = cellStr.substring(2);
                        secondElm = firstElm.addElement(cellElm);
                        secondElm.addAttribute("position", fileName + "," + sheetName +"," +String.valueOf(row.getRowNum()+1));

                        // 3#添加三级节点
                    } else if (cellStr.startsWith("#begin")) {
                        thirdElm = secondElm.addElement("elements");
                        final String[] arrayStr = cellStr.split(":");
                        if (arrayStr.length == 1) {
                            thirdElm.addAttribute("id", "default");
                            isFieldIdLabel = true;
                        } else {
                            thirdElm.addAttribute("pistion", arrayStr[1]);
                            isFieldIdLabel = true;
                        }

                        // 4#收集添加四级节点
                    } else if (isFieldIdLabel) {
                        //如果不为空，则列数-1，并把头部加进fields里
                        if( !cellStr.isEmpty()){
                            if (coloumNum != 0) {
                                fields.add(cellStr);
                                coloumNum=coloumNum-1;
                            }
                            if (coloumNum == 0) {
                                isFieldIdLabel = false;
                                isFieldValue = true;
                            }
                        }else{//如果为空，则列数就只-1
                            if (coloumNum != 0) {
                                coloumNum=coloumNum-1;
                            }
                            if (coloumNum == 0) {
                                isFieldIdLabel = false;
                                isFieldValue = true;
                            }
                        }

                    } else if (cellStr.startsWith("#end")) {
                        isFieldValue = false;
                        fields.clear();
                        // 5#写入filedvalue
                    } else if (isFieldValue) {

                        if (isNextRow) {
                            fourthElm = thirdElm.addElement("element");
                            fourthElm.addAttribute("position", fileName + "," +sheetName +"," +String.valueOf(row.getRowNum()+1));
                            final int celIndex = cell.getColumnIndex();
                           Element fifthElm=null;
                            if(fields.get(celIndex).lastIndexOf("*")>0){
                                fifthElm = fourthElm.addElement(fields.get(celIndex).substring(0,fields.get(celIndex).indexOf("*")));
                           }else{
                                fifthElm = fourthElm.addElement(fields.get(celIndex));
                           }

                            fifthElm.setText(cellStr);
                            isNextRow = false;
                        } else {
                            final int celIndex = cell.getColumnIndex();
                            Element fifthElm=null;
                            if (celIndex < fields.size()) {
                                if(fields.get(celIndex).lastIndexOf("*")>0){
                                     fifthElm = fourthElm.addElement(fields.get(celIndex).substring(0,fields.get(celIndex).indexOf("*")-1));
                                }else{
                                     fifthElm = fourthElm.addElement(fields.get(celIndex));
                                }
                                fifthElm.setText(cellStr);
                            }
                        }
                    } else {
                        // System.out.println(coloumNum + " " + isFieldIdLabel);
                    }
                }
            }
        }
        System.out.println("end---------------------");
        output.write(document);
        output.flush();
        output.close();
    }

    /**
     * 将单元格的内容全部转换成字符串
     */
    private static String cellValueToString(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:
            // System.out.println("can not format cell value :" + cell.getRichStringCellValue());
            str = cell.getRichStringCellValue().getString();
            break;
        }
        return str;
    }
}