environnement

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'
}

Chose que tu veux faire

Je veux lire un fichier Excel avec Apache POI et obtenir le résultat de la formule dans la cellule.

Le code est ci-dessous.

`SampleApachePoiUtil.java`


public class SampleApachePoiUtil {

    /**
     *Calcule la formule dans la cellule et renvoie le résultat sous forme de chaîne.
     * @param formuleCellule Cellule contenant la formule
     * @return Formula result (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);
        //[Attention] En supposant qu'il s'agit d'une chaîne
        return value.getStringValue();
    }


    public static void sample(File file) throws IOException, InvalidFormatException {
        Workbook workbook = WorkbookFactory.create(file);
        Sheet sheet = workbook.getSheet("main");
        //Cellule contenant la formule("main"Cellule A1 de la feuille)
        Cell formulaCell = sheet.getRow(0).getCell(0);
        //Calculez la formule
        String result = getStringFormulaValue(formulaCell);
        System.out.println("result = " + result);
        workbook.close();
    }
}

Site de référence

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

problème

Quand j'ai essayé d'obtenir le résultat de = Ai! A1, une formule qui fait référence à la cellule de la feuille" Ai ", j'ai obtenu une 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)

On m'a dit que la plage de cellules nommée «A» n'existe pas. Apparemment, le nom de la feuille n'a pas pu être analysé correctement, et il a été jugé comme "nom de cellule" pour une raison quelconque.

Recherche de cause

Modèles qui posent des problèmes

J'ai changé le nom de la feuille référencée et l'extension du fichier et vérifié si une erreur s'est produite.

extension	Nom de la feuille référencé par la formule	résultat
xlsx	Ah	NG
xlsx	Ai	OK
xlsx	・	NG
xlsx	Oh 〒	NG
xls	Ah	OK

Caractères spéciaux tels que "・" et "〒", non dus à des caractères non ASCII? Semble être la cause.

Ce qui suit est une trace de pile lorsque le nom de la feuille est "・". Le message était différent de lorsque le nom de la feuille était "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)

Vérifiez la cellule de formule avec le débogueur

Dans le débogage, j'ai vérifié le contenu de formulaCell._cell.

<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>

La formule était stockée dans «<main: f>» et le résultat de la formule était stocké dans «<main: v>». Les valeurs stockées sont comme prévu, donc la lecture du fichier Excel semble correcte.

Peut-être que "f" est une formule et "v" est une valeur
La valeur stockée dans <main: v> n'est pas la valeur calculée par Apache POI, mais la valeur stockée dans le fichier Excel. Le résultat du calcul semble être mis en cache.

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 Citation

"·"sur

Lecture japonaise: Midpoint
Unicode: U + 30FB --12539 en décimal
Nom Unicode: KATAKANA MIDDLE DOT

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

Solution

Lorsque j'ai augmenté la version Apache POI de «3.15» à «3.16» (strictement «3.16-beta2»), l'erreur a disparu et cela a fonctionné comme prévu.

Historique des modifications d'Apache POI 3.16-beta2

Vous trouverez ci-dessous des rapports de bogues connexes. Ici, il semble qu'une erreur se soit produite lorsque la méthode shiftRows a été exécutée pour le nom de feuille contenant" ・ ". Partial support for unicode sheet names

C'est la différence de source modifiée. https://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java?r1=1778418&r2=1778417&pathrev=1778418

Cause d'erreur

La méthode ʻisUnquotedSheetNameChar de FormulaParser.java` en était la cause directe.

Le nom de la feuille inclus dans la formule est obtenu par la méthode parseSheetName de ʻorg.apache.poi.ss.formula.FormulaParser.java`.

`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;
        }
//...

C'est la définition de la méthode ʻisUnquotedSheetNameChar appelée par la méthode parseSheetName`.

`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;
    }

La valeur de retour de la méthode ʻisUnquotedSheetNameChar` a le résultat suivant:

lettre	Catégorie Unicode	valeur de retour de isUnquotedSheetNameChar
Ah	Letter, Other [Lo]	true
・	Punctuation, Other [Po]	false
je	Letter, Other [Lo]	true

Lorsque vous passez ・ à la méthode ʻisUnquotedSheetNameChar`, false est renvoyé, de sorte que le nom de la feuille n'a pas pu être obtenu correctement et une erreur s'est produite.

Version modifiée de la méthode ʻ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;
    }

Il y a deux changements:

Compatible Unicode
Les caractères non ASCII retournent toujours vrai

"・" Renvoie maintenant true.

Résumé

Dans Apache POI 3.15, lors du calcul d'une formule qui remplit les conditions suivantes, FormulaParseException se produit.

Format XLSX
Fait référence à une cellule d'une feuille qui contient des caractères tels que "・" dans le nom de la feuille.

Caractères que la méthode Character.isLetterOrDigit renvoie faux

Apache POI 3.16 résout le problème ci-dessus

Supplément

Formule Excel BNE

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

 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

La liste des jetons est créée dans l'ordre RPN (notation polonaise inversée).
Inspiré de Let's Build a Compiler, par Jack Crenshaw
- https://compilers.iecc.com/crenshaw/

Catégorie Unicode de "・"

Cela n'a rien à voir avec Apache POI. La catégorie «・» est «Ponctuation, Autre [Po]», mais avant Unicode 4.1, c'était «Ponctuation, Connecteur [Pc]». http://www.unicode.org/reports/tr44/tr44-4.html#Change_History

À cet égard, il semble qu'il y ait eu un problème que "Dans Java 6," ・ "a été utilisé dans le nom de la méthode, mais il ne peut plus être utilisé dans Java 7." C'est horrible. .. ..

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

Ce que je n'ai pas compris

Pourquoi aucune erreur ne s'est produite au format XLS (format Excel 2003)

En utilisant le débogueur, j'ai trouvé ce qui suit.

La méthode parseSheetName de FormulaParser ne passe pas
ʻOrg.apache.poi.ss.formula.WorkbookEvaluator` est utilisé

Le JavaDoc de WorkbookEvaluator dit" Gardez un cache des résultats de calcul ".

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 Citation

N'avez-vous pas reçu d'erreur parce que vous faites référence au cache des résultats de calcul?

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

[JAVA] Dans Apache POI 3.15, lorsque j'obtiens le résultat d'une formule, FormulaParseException se produit (la formule fait référence à "cellule avec le nom de la feuille comprenant" ・ ")

environnement

build.gradle

Chose que tu veux faire

SampleApachePoiUtil.java

Site de référence

problème

Recherche de cause

Modèles qui posent des problèmes

Vérifiez la cellule de formule avec le débogueur

"·"sur

Solution

Cause d'erreur

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

Version modifiée de la méthode ʻIsUnquotedSheetNameChar` (Apache POI 3.16 beta2)

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

Résumé

Supplément

Formule Excel BNE

Catégorie Unicode de "・"

Ce que je n'ai pas compris

Pourquoi aucune erreur ne s'est produite au format XLS (format Excel 2003)

`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`