基于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 -- \trianglesharegit rainingCBX_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;
      }
  }
  

        结果图：