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));
}
}
}