使用Apache POI的SAX-Parser获取Excel的单元格值

I read an excel-sheet with the EventUserModel of Apache POI which operates with a SAX-Parser.

我使用Apache POI的EventUserModel读取了一个excel-sheet,它使用SAX-Parser进行操作。

The problem is I have some normal Cells with content like "hello" and Cells with content like this: =IF(SUM(P254;R254;N254)=0;V254;VLOOKUP(Z254;Cirteria!$Y$2:$Z$4;2;TRUE))

问题是我有一些正常的单元格,其内容类似于“hello”,单元格的内容如下:= IF(SUM(P254; R254; N254)= 0; V254; VLOOKUP(Z254; Cirteria!$ Y $ 2:$ Z $ 4 ; 2; TRUE))

I don't want these formulas but their evaluated value e.g. "hello". I know it's saved in the XML but I don't know how to access it.

我不希望这些公式,但他们的评估值,例如“你好”。我知道它保存在XML中,但我不知道如何访问它。

<Cell ss:StyleID="s168"
    ss:Formula="=IF(SUM(RC[-11],RC[-9],RC[-13])=0,RC[-5],VLOOKUP(RC[-1],Kriterien!R2C25:R4C26,2,TRUE))">
    <Data ss:Type="String">hello</Data>
</Cell>
<Cell ss:StyleID="s167"><Data ss:Type="String">hello</Data></Cell>

It seems as if there is a "formula" tag SAX is always returning this value instead of the Data value. Otherwise the normal "hello" is returned.

似乎有一个“公式”标记SAX总是返回此值而不是数据值。否则返回正常的“hello”。

My Code looks like this:

我的代码看起来像这样:

public void startElement(String uri, String localName, String name,
        Attributes attributes) throws SAXException {
    if (name.equals("c")) {
        String cellType = attributes.getValue("t");
        if (cellType != null && cellType.equals("s")) {
            nextIsString = true;
        } else {
            nextIsString = false;
        }
    }
    lastContents = "";
}
public void characters(char[] ch, int start, int length)
        throws SAXException {
    lastContents = new String(ch, start, length);
}
public void endElement(String uri, String localName, String name)
        throws SAXException {
    if (nextIsString) {
        int idx = Integer.parseInt(lastContents);
        lastContents = new XSSFRichTextString(sst.getEntryAt(idx))
                .toString();
        System.out.println(lastContents);
        nextIsString = false;
    }
}

I get all the data I need except the data generated by formulas.

除了公式生成的数据外,我得到了我需要的所有数据。

1 个解决方案

#1

This is an old thread, but I was trying to figure out a similar problem, and thought I would post this in hopes it will help someone in the future. The tag structure for a formula within the cell is different than your typical cell / value structure.

这是一个老线程,但我试图找出一个类似的问题,并认为我会发布这个希望它将来会帮助某人。单元格中公式的标记结构与典型的单元格/值结构不同。

Therefore you have to be cognizant of when your formula tag opens and closes within the cell, as well as when the value opens and closes within the cell. The tag structures is similar to (of course without attributes and only one cell in the row for simplicity) ...

因此,您必须了解公式标记何时在单元格中打开和关闭,以及何时值在单元格中打开和关闭。标签结构类似于(当然没有属性,为简单起见,行中只有一个单元格)......

`<row> <c> <f> IF(SUM(RC[-11],RC[-9],RC[-13])=0,RC[-5],VLOOKUP(RC[-1],Kriterien!R2C25:R4C26,2,TRUE))</f> <v> hello </v> </c> </row> `

Note that the characters(...) method will store the value of the formula, then the concluded value in between two distinct tags.

请注意,字符(...)方法将存储公式的值,然后存储两个不同标记之间的结束值。

So, when evaluating the assigned value, you have to be cognizant of the formula tag's impact on your value.

因此,在评估指定值时,您必须认识到公式标记对您的价值的影响。

#1