Excel2007格式分析和XML解析

 

在物料清单采购中，用到excel上传文件解析功能，不过使用poi来解析，发现如果某个单元格为空，则使用poi的官网示例则会被忽略，导致某些非必填的单元格为空，而解析出来则认为不符合格式。找了半天，也没发现poi正确解析的示例和一些资料，只能自己查查excel的格式，然后再解析了。官网地址示例：http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api

那么我们就看看excel2007的格式了。

 

1.    excel2007是使用xml格式来存储的，把一个excel文件后缀改为.zip,打开之后就直接可以看到一个excel文件对应的xml格式的文件了。

这里面有几部分

 

 

 

<!--[if !supportLists]-->1.       1 <!--[endif]-->对于docProps目录下  这里core是文件的创建时间和修改时间，标题，主题和作者，app是文档的其他属性，文档类型，版本，是否只读，是否共享，安全属性等文档属性信息。

 

 

 

 

 
Core.xml
       <dc:creator></dc:creator>
       <cp:lastModifiedBy></cp:lastModifiedBy>
       <dcterms:created xsi:type="dcterms:W3CDTF">2006-09-13T11:21:51Z</dcterms:created>
       <dcterms:modified xsi:type="dcterms:W3CDTF">2013-06-05T09:28:23Z</dcterms:modified>
 
App.xml
 
       <Application>Microsoft Excel</Application>
       <DocSecurity>0</DocSecurity>
       <ScaleCrop>false</ScaleCrop>
       <Company></Company>
       <LinksUpToDate>false</LinksUpToDate>
       <SharedDoc>false</SharedDoc>
       <HyperlinksChanged>false</HyperlinksChanged>
       <AppVersion>12.0000</AppVersion>
     ……

 

 2.在xl目录下是文档的具体内容信息

 

 

 

先看workbook.xml

<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
       xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
       <fileVersion appName="xl" lastEdited="4" lowestEdited="4"
              rupBuild="4507" />
       <workbookPr filterPrivacy="1" defaultThemeVersion="124226" />
       <bookViews>
              <workbookView xWindow="0" yWindow="90" windowWidth="19200"
                     windowHeight="11640" />
       </bookViews>
       <sheets>
              <sheet name="Sheet1" sheetId="1" r:id="rId1" />
              <sheet name="Sheet2" sheetId="2" r:id="rId2" />
              <sheet name="Sheet3" sheetId="3" r:id="rId3" />
       </sheets>
       <calcPr calcId="125725" />
</workbook>

 workbook.xml文件包含一对<sheets>标签，其中的每个<sheet>元素都代表Excel 2007文件中的一个，工作表的名称就是其name属性的值，这里有三个sheet。

 

xl/_rels/workbook.xml.rels定义每个sheetid对应的sheet内容文件sheet1.xml，共享的单元格内容文件sharedstring.xml,样式文件style.xml是当前单元格的样式字体，颜色等样式的xml配置。

Theme存放的是当前的设置导航栏的默认样式。这两个看看大概也就能明白。

 

关键我们看看下面每个sheet的内容格式，

 

 

打开一个sheet1.xml看看

       <sheetData>
              <row r="1" spans="1:7" ht="33.75" customHeight="1"> row标签是表示每一行的数据，r表示第几行，其他几个都是这几行的样式
                     <c r="A1" s="9" t="s">c标签表示每个单元格的内容，这里A1 第一行的第一列，r表示位置，s表示这个单元格的样式，
                                                          s=9对应style.xml的的index为9的样式即为这个单元格的样式，t=s表示这个单元格有值，里面的v标签即为值的id，id对应到sharedstring.xm里的id对应的值
                            <v>2</v>
                     </c>
                     <c r="B1" s="10" />  没有t属性，表示这个单元格没有值设置
                     <c r="C1" s="10" />
                     <c r="D1" s="10" />
                     <c r="E1" s="10" />
                     <c r="F1" s="10" />
              </row>
              <row r="2" spans="1:7" ht="27.75" customHeight="1">  第二行
                     <c r="A2" s="3" t="s">  第二行第二列
                            <v>1</v>
                     </c>
                     <c r="B2" s="4" t="s">
                            <v>5</v>
                     </c>
                     <c r="C2" s="3" t="s">
                            <v>0</v>

 

 我们找到对应的第一行第一列的值索引为2对应到sharedStrings.xml里面的index的值，这里si从0开始，第三个即为index为2的值，刚好跟我们的excel的A1值符合

 

 

<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
    count="71" uniqueCount="13">
    <si>
           <t>物料编号</t>
           <phoneticPr fontId="1" type="noConversion" />
    </si>
    <si>
           <t>序号</t>
           <phoneticPr fontId="1" type="noConversion" />
    </si>
    <si>
           <t>注意：请不要修改表中蓝色区域文字；带有*号字段是必填项；每张物料清单最多只能导入20条物料信息</t>
           <phoneticPr fontId="1" type="noConversion" />
    </si>

 而A1的s=9对应的样式style.xml我们也看看，找到cellXfs里面的第9个，不过这里又引用fontid字体样式，borderid样式，numfmtId格式等

 

  <cellXfs count="11">
       ......
              <xf numFmtId="0" fontId="0" fillId="3" borderId="1" xfId="0"
                     applyFill="1" applyBorder="1">
                     <alignment vertical="center" />
              </xf>
              <xf numFmtId="0" fontId="2" fillId="0" borderId="2" xfId="0"
                     applyFont="1" applyBorder="1" applyAlignment="1">
                     <alignment horizontal="left" vertical="center" />
              </xf>
              <xf numFmtId="0" fontId="0" fillId="0" borderId="3" xfId="0"
                     applyBorder="1" applyAlignment="1">
                     <alignment horizontal="left" vertical="center" />
              </xf>
    </cellXfs>

 最重要的是对于单元格的空值是没有v标签的，那么使用xml解析的时候就需要特别处理下，下面这个是官方示例程序，我做了修改，黄色部分是添加解析空单元格情况下座位默认空值添加到rowlist上，这样rowlist就完整了，不会因为一行空单元格就不往rowlist添加，造成无法判断是哪列为空，也无法验证某些列非必填下的判断。（使用的是POI）

 

那么xml怎么解析如下这个空单元格呢

<c r="C1" s="10" />

 

下面private boolean cellNull; 这个就是添加来判断是否为空单元格的

public class Excel2007Reader extends DefaultHandler {

    // 共享字符串表
    private SharedStringsTable sst;
    // 上一次的内容
    private String             lastContents;
    private boolean            nextIsString;
    
    private boolean            cellNull;

    private int                sheetIndex = -1;
    private List<String>       rowlist    = new ArrayList<String>();
    // 当前行
    private int                curRow     = 0;
    // 当前列
    private int                curCol     = 0;

    private IRowReader         rowReader;

    public void setRowReader(IRowReader rowReader) {
        this.rowReader = rowReader;
    }

    /**
     * 只遍历一个电子表格，其中sheetId为要遍历的sheet索引，从1开始，1-3
     * 
     * @param filename
     * @param sheetId
     * @throws Exception
     */
    public void processOneSheet(String filename, int sheetId) throws Exception {
        OPCPackage pkg = OPCPackage.open(filename);
        XSSFReader r = new XSSFReader(pkg);
        SharedStringsTable sst = r.getSharedStringsTable();
        XMLReader parser = fetchSheetParser(sst);

        // 根据 rId# 或 rSheet# 查找sheet
        InputStream sheet2 = r.getSheet("rId" + sheetId);
        sheetIndex++;
        InputSource sheetSource = new InputSource(sheet2);
        parser.parse(sheetSource);
        sheet2.close();
    }

    public void process(InputStream inputStream) throws Exception {
        OPCPackage pkg = OPCPackage.open(inputStream);
        proccessintern(pkg);
    }

    /**
     * 遍历工作簿中所有的电子表格

     * 
     * @param filename
     * @throws Exception
     */
    public void process(String filename) throws Exception {
        OPCPackage pkg = OPCPackage.open(filename);
        proccessintern(pkg);
    }

    private void proccessintern(OPCPackage pkg) throws IOException, OpenXML4JException, InvalidFormatException,
                                               SAXException {
        XSSFReader r = new XSSFReader(pkg);
        SharedStringsTable sst = r.getSharedStringsTable();
        XMLReader parser = fetchSheetParser(sst);
        Iterator<InputStream> sheets = r.getSheetsData();
        while (sheets.hasNext()) {
            curRow = 0;
            sheetIndex++;
            InputStream sheet = sheets.next();
            InputSource sheetSource = new InputSource(sheet);
            parser.parse(sheetSource);
            sheet.close();
        }
    }

    public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
        XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
        this.sst = sst;
        parser.setContentHandler(this);
        return parser;
    }

    public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {

        // c => 单元格
        if ("c".equals(name)) {
            // 如果下一个元素是 SST 的索引，则将nextIsString标记为true
            String cellType = attributes.getValue("t");
            if ("s".equals(cellType)) {
                nextIsString = true;
                cellNull = false;
            } 
            else {
                nextIsString = false;
                cellNull = true;
            }
        }

        // 置空
        lastContents = "";
    }

    public void endElement(String uri, String localName, String name) throws SAXException {

        // 根据SST的索引值的到单元格的真正要存储的字符串
        // 这时characters()方法可能会被调用多次
        if (nextIsString) {
            try {
                int idx = Integer.parseInt(lastContents);
                lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
            } catch (Exception e) {

            }
        }
     
        
        if ("v".equals(name) || "t".equals(name)) {
            String value = lastContents.trim();
            value = value.equals("") ? " " : value;
            rowlist.add(curCol, value);
            curCol++;
            cellNull = false;
        }else if("c".equals(name) && cellNull == true){
            rowlist.add(curCol, "");
            curCol++;
            cellNull = false;
        }
        else {
            // 如果标签名称为 row ，这说明已到行尾，调用 optRows() 方法
            if (name.equals("row")) {
                rowReader.getRows(sheetIndex, curRow, rowlist);
                rowlist.clear();
                curRow++;
                curCol = 0;
            }
        }

    }

    public void characters(char[] ch, int start, int length) throws SAXException {
        // 得到单元格内容的值

        lastContents += new String(ch, start, length);
    }
}

  

 

 另外：poi有几种解析excel的方式，如果把整个文档加载进去解析，那么可能会造成内存溢出（之前以前出过问题），所以可以使用这个xml事件驱动方式解析，内存使用率非常小。而使用buffered streaming只能写excel，读不支持。这是官网的几种方式的说明。

 

 

 

评论

1 楼 aki319 2014-04-03  

帮了我大忙了，谢谢哈~