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

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

POI Exercise

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)

Reference

· POI official site -- http://poi.apache.org/ 

system.xlxs: http://www.cnblogs.com/jrsmith/admin/Files.aspx

package com.core.pio;

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;

/**
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<resource>
<_system>
<entity id="ProjectInfo">
<elements id="begin_attr">
<element>
<field id="domain">$root</field>
<field id="release_no">5.01</field>
</element>
</elements>
</entity>
</_system>
</resource>
*/

public class ExcelToXmlUtil2 {

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

toXml("E:/core training/system.xlsx", "E:/core training/corePio.xml");
}

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

// Element rootElm = getRootElm(targetPath);
//输出格式化
OutputFormat format = OutputFormat.createPrettyPrint();
format.setEncoding("UTF-8"); // 指定XML编码
XMLWriter output = new XMLWriter(new FileWriter(targetPath), format);
// 使用DocumentHelper.createDocument方法建立一个文档实例
Document document = DocumentHelper.createDocument();
Element rootElm = document.getRootElement();
if( rootElm == null) {
rootElm = document.addElement("resource");//创建根节点
}
final Workbook wb = WorkbookFactory.create(new File(sourcePath));
final int sheetNum = wb.getNumberOfSheets();


for(int i = 0; i<sheetNum; i++) {
Sheet sheet = wb.getSheetAt(i);
// 标记是否接下来的是否为fieldIdLabel
boolean isFieldIdLabel = false;
boolean isFieldValue = false;
int coloumNum = 0;
List<String> fields = new ArrayList<String>();
String sheetName = sheet.getSheetName();
// 1#添加一级节点
Element firstElm = rootElm.addElement(sheetName);
Element secondElm = null;
Element thirdElm = null;
for (final Row row : sheet) {
coloumNum = row.getPhysicalNumberOfCells();
// Element thirdElm = null;
Element fourthElm = null;
boolean isNextRow = true;
for (final Cell cell : row) {
String cellStr = cellValueToString(cell);
// 2#添加二级节点
if (cellStr.startsWith("##")) {
String cellElm = cellStr.substring(2);
// secondElm = firstElm.addElement("elements");
secondElm = firstElm.addElement("entity");
secondElm.addAttribute("id", cellElm);
// 3#添加三级节点
} else if (cellStr.startsWith("#begin")) {
// thirdElm = secondElm.addElement("element");
thirdElm = secondElm.addElement("elements");
String []arrayStr = cellStr.split(":");
if(arrayStr.length == 1) {
thirdElm.addAttribute("id", arrayStr[0].substring(1));
isFieldIdLabel = true;
} else {
thirdElm.addAttribute("id", arrayStr[1]);
isFieldIdLabel = true;
}
// 4#收集fieldIdLabel
} else if (isFieldIdLabel && !cellStr.isEmpty()){
if (coloumNum != 0) {
fields.add(cellStr);
coloumNum--;
}
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");
// thirdElm.addAttribute("name", arg1)
Element fifthElm = fourthElm.addElement("field");
int celIndex = cell.getColumnIndex();
fifthElm.addAttribute("id", fields.get(celIndex));
fifthElm.setText(cellStr);
isNextRow = false;
} else {
Element fifthElm = fourthElm.addElement("field");
int celIndex = cell.getColumnIndex();
if (celIndex < fields.size()) {
fifthElm.addAttribute("id", 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;
}
}