[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


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

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.

image

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.

image

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

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:

"・" Renvoie maintenant true.

Résumé

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

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.

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

Recommended Posts

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" ・ ")
Je veux obtenir la valeur de Cell de manière transparente quel que soit CellType (Apache POI)
Comment obtenir le nom de classe de l'argument de LoggerFactory.getLogger lors de l'utilisation de SLF4J en Java
Obtenez le résultat de POST en Java
Je souhaite obtenir l'adresse IP lors de la connexion au Wi-Fi avec Java
Je veux obtenir le nom de champ du champ [Java]. (Vieux ton d'histoire)
Comment obtenir une liste de noms de feuilles Excel en Java (POI vs SAX)
Ce que j'ai essayé quand je voulais obtenir tous les champs d'un haricot
J'ai réussi à obtenir un blanc lorsque j'ai apporté le contenu de Beans dans la zone de texte
Quand tu te perds dans le nom de la classe
Je veux obtenir la valeur en Ruby
Je veux trouver la somme de contrôle MD5 d'un fichier en Java et obtenir le résultat sous forme de chaîne de caractères en notation hexadécimale.
Obtenez le nom du scénario de test dans la classe de test JUnit
Je souhaite afficher le nom de l'affiche du commentaire
Lorsque je suis passé à IntelliJ, il y avait une grande différence dans l'encodage du fichier de propriétés.
Comment obtenir le nom d'une classe / méthode exécutée en Java
Quand j'étais inquiet des méthodes statiques dans l'interface java, je suis arrivé à l'ordre d'interprétation des noms
Fixez le nom du fichier de guerre à celui défini dans Maven
Comment obtenir l'identifiant de la clé PRIMAY incrémentée automatiquement dans MyBatis
Je veux changer la valeur de l'attribut dans Selenium of Ruby
[Android] Je souhaite obtenir l'auditeur à partir du bouton de ListView
Comment obtenir la longueur d'un fichier audio avec Java