刚进公司的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;
}
}
结果图：