Umgebung

JDK 1.8.0_162
Apache POI 3.15
Excel2016

`build.gradle`


dependencies {
    compile group: 'org.apache.poi', name: 'poi', version: '3.15'
    compile group: 'org.apache.poi', name: 'poi-ooxml', version: '3.15'
}

Was du machen willst

Ich möchte eine Excel-Datei mit Apache POI lesen und das Ergebnis der Formel in der Zelle erhalten.

Der Code ist unten.

`SampleApachePoiUtil.java`


public class SampleApachePoiUtil {

    /**
     *Berechnet die Formel in der Zelle und gibt das Ergebnis als String zurück.
     * @param FormelZelle Zelle, die die Formel enthält
     * @Formelergebnis zurückgeben (String)
     */
    public static String getStringFormulaValue(Cell formulaCell) {
        Workbook book = formulaCell.getSheet().getWorkbook();
        CreationHelper helper = book.getCreationHelper();
        FormulaEvaluator evaluator = helper.createFormulaEvaluator();
        CellValue value = evaluator.evaluate(formulaCell);
        //[Achtung] Angenommen, es handelt sich um eine Zeichenfolge
        return value.getStringValue();
    }


    public static void sample(File file) throws IOException, InvalidFormatException {
        Workbook workbook = WorkbookFactory.create(file);
        Sheet sheet = workbook.getSheet("main");
        //Zelle mit der Formel("main"A1 Zelle des Blattes)
        Cell formulaCell = sheet.getRow(0).getCell(0);
        //Berechnen Sie die Formel
        String result = getStringFormulaValue(formulaCell);
        System.out.println("result = " + result);
        workbook.close();
    }
}

Referenzseite

https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/FormulaEvaluator.html
https://poi.apache.org/spreadsheet/eval.html

Problem

Als ich versuchte, das Ergebnis von = Ai! A1 zu erhalten, einer Formel, die sich auf die Zelle des Blattes" Ai "bezieht, erhielt ich eine FormulaParseException.

Exception in thread "main" org.apache.poi.ss.formula.FormulaParseException: Specified named range 'Ah' does not exist in the current workbook.
	at org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:898)
	at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:490)
	at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:311)
	at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1509)
	at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1467)
	at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1454)
	at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1827)
	at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1955)
	at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1939)
	at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1896)
	at org.apache.poi.ss.formula.FormulaParser.intersectionExpression(FormulaParser.java:1869)
	at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1849)
	at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1997)
	at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:170)
	at org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getFormulaTokens(XSSFEvaluationWorkbook.java:85)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:315)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:259)
	at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:65)
	at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluate(BaseFormulaEvaluator.java:101)
	at SampleApachePoiUtil.getStringFormulaValue(SampleApachePoiUtil.java:21)
	at SampleApachePoiUtil.sample(SampleApachePoiUtil.java:33)
	at Main.main(Main.java:7)

Mir wurde gesagt, dass der Zellbereich "A" nicht existiert. Anscheinend konnte der Blattname nicht richtig analysiert werden, und er wurde aus irgendeinem Grund als "Zellenname" beurteilt.

Ursachenforschung

Muster, die Probleme verursachen

Ich habe den Namen des referenzierten Blattes und die Erweiterung der Datei geändert und überprüft, ob ein Fehler aufgetreten ist.

Erweiterung	Blattname, auf den die Formel verweist	Ergebnis
xlsx	Ah	NG
xlsx	Ai	OK
xlsx	・	NG
xlsx	Oh 〒	NG
xls	Ah	OK

Sonderzeichen wie "・" und "〒", nicht aufgrund von Nicht-ASCII-Zeichen? Scheint die Ursache zu sein.

Das Folgende ist eine Stapelverfolgung, wenn der Blattname "・" ist. Die Nachricht war anders als bei dem Blattnamen "Ai".

Exception in thread "main" org.apache.poi.ss.formula.FormulaParseException: Parse error near char 0 '・' in specified formula '・!A1'. Expected cell ref or constant literal
	at org.apache.poi.ss.formula.FormulaParser.expected(FormulaParser.java:262)
	at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1514)
	at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1467)
	at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1454)
	at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1827)
	at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1955)
	at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1939)
	at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1896)
	at org.apache.poi.ss.formula.FormulaParser.intersectionExpression(FormulaParser.java:1869)
	at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1849)
	at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1997)
	at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:170)
	at org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getFormulaTokens(XSSFEvaluationWorkbook.java:85)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:315)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:259)
	at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:65)
	at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluate(BaseFormulaEvaluator.java:101)
	at SampleApachePoiUtil.getStringFormulaValue(SampleApachePoiUtil.java:21)
	at SampleApachePoiUtil.sample(SampleApachePoiUtil.java:33)
	at Main.main(Main.java:7)

Überprüfen Sie die Formelzelle mit dem Debugger

Beim Debuggen habe ich den Inhalt von formulaCell._cell überprüft.

<xml-fragment r="A1" s="1" t="str" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <main:f>Ah!A1</main:f>
  <main:v>hoge</main:v>
</xml-fragment>

Die Formel wurde in <main: f> gespeichert, und das Ergebnis der Formel wurde in <main: v> gespeichert. Die gespeicherten Werte sind wie erwartet, sodass das Lesen der Excel-Datei gut aussieht.

Vielleicht ist "f" eine Formel und "v" ein Wert
Der in <main: v> gespeicherte Wert ist nicht der vom Apache POI berechnete Wert, sondern der in der Excel-Datei gespeicherte Wert. Das Berechnungsergebnis scheint zwischengespeichert zu sein.

The Excel file format (both .xls and .xlsx) stores a "cached" result for every formula along with the formula itself.

https://poi.apache.org/spreadsheet/eval.html Zitat

"·"Über

Japanische Lesung: Mittelpunkt
Unicode: U + 30FB --12539 in Dezimalzahl
Unicode-Name: KATAKANA MIDDLE DOT

https://www.fileformat.info/info/unicode/char/30fb/index.htm https://ja.wikipedia.org/wiki/%E4%B8%AD%E9%BB%92

Lösung

Als ich die Apache POI-Version von "3.15" auf "3.16" (ausschließlich "3.16-beta2") erhöhte, verschwand der Fehler und es funktionierte wie erwartet.

Änderungsverlauf von Apache POI 3.16-beta2

Unten finden Sie verwandte Fehlerberichte. Es scheint, dass ein Fehler aufgetreten ist, als die Methode "shiftRows" für einen Blattnamen ausgeführt wurde, der "・" enthält. Partial support for unicode sheet names

Dies ist der modifizierte Quellendifferenz. https://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java?r1=1778418&r2=1778417&pathrev=1778418

Fehlerursache

Die isUnquotedSheetNameChar -Methode von FormulaParser.java war die direkte Ursache.

Der in der Formel enthaltene Blattname wird mit der Methode "parseSheetName" von "org.apache.poi.ss.formula.FormulaParser.java" ermittelt.

`java:poi-3.15-source.jar\org.apache.poi.ss.formula.FormulaParser.java`


private SheetIdentifier parseSheetName() {
//...
        // unquoted sheet names must start with underscore or a letter
        if (look =='_' || Character.isLetter(look)) {
            StringBuilder sb = new StringBuilder();
            // can concatenate idens with dots
            while (isUnquotedSheetNameChar(look)) {
                sb.append(look);
                GetChar();
            }
            NameIdentifier iden = new NameIdentifier(sb.toString(), false);
            SkipWhite();
            if (look == '!') {
                GetChar();
                return new SheetIdentifier(bookName, iden);
            }
            // See if it's a multi-sheet range, eg Sheet1:Sheet3!A1
            if (look == ':') {
                return parseSheetRange(bookName, iden);
            }
            return null;
        }
//...

Dies ist die Definition der Methode "isUnquotedSheetNameChar", die von der Methode "parseSheetName" aufgerufen wird.

`java:poi-3.15-source.jar\org.apache.poi.ss.formula.FormulaParser.java`


    /**
     * very similar to {@link SheetNameFormatter#isSpecialChar(char)}
     */
    private static boolean isUnquotedSheetNameChar(char ch) {
        if(Character.isLetterOrDigit(ch)) {
            return true;
        }
        switch(ch) {
            case '.': // dot is OK
            case '_': // underscore is OK
                return true;
        }
        return false;
    }

Der Rückgabewert der Methode "isUnquotedSheetNameChar" lautet wie folgt:

Brief	Unicode-Kategorie	Rückgabewert von isUnquotedSheetNameChar
Ah	Letter, Other [Lo]	true
・	Punctuation, Other [Po]	false
ich	Letter, Other [Lo]	true

Wenn Sie "・" an die "isUnquotedSheetNameChar" -Methode übergeben, wird false zurückgegeben, sodass der Blattname nicht korrekt abgerufen werden konnte, was zu einem Fehler führte.

Geänderte Version der Methode "isUnquotedSheetNameChar" (Apache POI 3.16 beta2)

`java:poi-3.16-beta2-source.jar\org.apache.poi.ss.formula.FormulaParser.java`


    /**
     * very similar to {@link SheetNameFormatter#isSpecialChar(char)}
     * @param ch unicode codepoint
     */
    private static boolean isUnquotedSheetNameChar(int ch) {
        if(Character.isLetterOrDigit(ch)) {
            return true;
        }
        // the sheet naming rules are vague on whether unicode characters are allowed
        // assume they're allowed.
        if (ch > 128) {
            return true;
        }
        switch(ch) {
            case '.': // dot is OK
            case '_': // underscore is OK
                return true;
        }
        return false;
    }

Es gibt zwei Änderungen:

Unicode-kompatibel
Nicht-ASCII-Zeichen geben immer true zurück

"・" Gibt jetzt true zurück.

Zusammenfassung

In Apache POI 3.15 tritt bei der Berechnung einer Formel, die die folgenden Bedingungen erfüllt, "FormulaParseException" auf.

XLSX-Format
Bezieht sich auf eine Zelle in einem Blatt, die Zeichen wie "・" im Blattnamen enthält.

Zeichen, bei denen die Methode "Character.isLetterOrDigit" false zurückgibt

Apache POI 3.16 behebt das oben genannte Problem

Ergänzung

Excel-Formel BNE

https://github.com/apache/poi/blob/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java Zitiert

 This class parses a formula string into a List of tokens in RPN order.
 Inspired by
           Lets Build a Compiler, by Jack Crenshaw
 BNF for the formula expression is :
 <expression> ::= <term> [<addop> <term>]*
 <term> ::= <factor>  [ <mulop> <factor> ]*
 <factor> ::= <number> | (<expression>) | <cellRef> | <function>
 <function> ::= <functionName> ([expression [, expression]*])
 
 For POI internal use only

Die Token-Liste wird in der Reihenfolge RPN (Reverse Polish Notation) erstellt.
Inspiriert von Let's Build a Compiler, von Jack Crenshaw
- https://compilers.iecc.com/crenshaw/

Unicode-Kategorie von "・"

Es hat nichts mit Apache POI zu tun. Die Kategorie "・" ist "Interpunktion, Andere [Po]", aber vor Unicode 4.1 war es "Interpunktion, Konnektor [Pc]". http://www.unicode.org/reports/tr44/tr44-4.html#Change_History

In diesem Zusammenhang scheint es ein Problem gegeben zu haben, dass "In Java 6" method "im Methodennamen verwendet wurde, aber in Java 7 nicht mehr verwendet werden kann." Das ist schrecklich. .. ..

https://www.hos.co.jp/blog/20111004/

Was ich nicht verstanden habe

Warum ist im XLS-Format (Excel 2003-Format) kein Fehler aufgetreten?

Mit dem Debugger habe ich Folgendes gefunden.

Die parseSheetName Methode von FormulaParser wird nicht bestanden
org.apache.poi.ss.formula.WorkbookEvaluator wird verwendet

Das JavaDoc von "WorkbookEvaluator" sagt "Cache mit Berechnungsergebnissen behalten".

For performance reasons, this class keeps a cache of all previously calculated intermediate cell values.

https://poi.apache.org/apidocs/org/apache/poi/ss/formula/WorkbookEvaluator.html Zitat

Haben Sie keinen Fehler erhalten, weil Sie sich auf den Berechnungsergebnis-Cache beziehen?

https://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java?r1=1778418&r2=1778417&pathrev=1778418

[JAVA] Wenn ich in Apache POI 3.15 das Ergebnis einer Formel erhalte, erhalte ich eine FormulaParseException (die Formel bezieht sich auf "Zellen mit Blattnamen, die" ・ "enthalten).

Umgebung

build.gradle

Was du machen willst

SampleApachePoiUtil.java

Referenzseite

Problem

Ursachenforschung

Muster, die Probleme verursachen

Überprüfen Sie die Formelzelle mit dem Debugger

"·"Über

Lösung

Fehlerursache

java:poi-3.15-source.jar\org.apache.poi.ss.formula.FormulaParser.java

java:poi-3.15-source.jar\org.apache.poi.ss.formula.FormulaParser.java

Geänderte Version der Methode "isUnquotedSheetNameChar" (Apache POI 3.16 beta2)

java:poi-3.16-beta2-source.jar\org.apache.poi.ss.formula.FormulaParser.java

Zusammenfassung

Ergänzung

Excel-Formel BNE

Unicode-Kategorie von "・"

Was ich nicht verstanden habe

Warum ist im XLS-Format (Excel 2003-Format) kein Fehler aufgetreten?

`build.gradle`

`SampleApachePoiUtil.java`

`java:poi-3.15-source.jar\org.apache.poi.ss.formula.FormulaParser.java`

`java:poi-3.15-source.jar\org.apache.poi.ss.formula.FormulaParser.java`

`java:poi-3.16-beta2-source.jar\org.apache.poi.ss.formula.FormulaParser.java`