[JAVA] Liste de points addictifs Apache POI

Quel genre d'article?

Apache POI est très pratique, mais il a de nombreux points addictifs. Cet article est un enregistrement des points bloqués lors du processus de création de l'outil de comparaison de fichiers Excel "Square Diff" [^ 1]. J'espère que cela aide quelqu'un.

Encore une fois, ** Apache POI est très utile. ** Je suis reconnaissant aux contributeurs d'utiliser ce genre de chose gratuitement. ** Si vous avez des plaintes, contribuez vous-même, au moins soulevez un problème ** [^ 2]. En d'autres termes, cet article témoigne de mon manque de pouvoir pour le faire.

Veuillez noter que cet article est susceptible de contenir du contenu basé sur mes malentendus et mon manque de compréhension. Si vous les remarquez, je vous serais reconnaissant si vous pouviez commenter.

[^ 1]: C'est très pratique, alors veuillez l'utiliser. Il est publié sur ici, et article est également écrit.

[^ 2]: Un jour, contribuer à OSS est mon prochain objectif en tant que programmeur amateur.

La version assumée par cet article

• Apache POI 4.1.0 ～ (répertorié dans le tableau ci-dessous)
• Excel for Office 365
• OpenJDK 12.0.1

Liste des points de montage

Dès que je me souviendrai, je l'ajouterai.

Explication individuelle

Le type de feuille n ° 1 (feuille de calcul, feuille graphique, feuille macro, feuille de dialogue) ne peut pas être distingué

La description

Il existe les types de feuilles Excel suivants:

• feuille de travail
• Feuille graphique
• Feuille de dialogue
• Feuille de macro

Vous voudrez peut-être faire la distinction entre ces types, en particulier les feuilles de calcul, mais pour une raison quelconque, l'API du modèle utilisateur d'Apache POI ne fournit pas une telle fonctionnalité.

Le premier choix lorsque vous travaillez avec Apache POI sur des feuilles Excel est ʻorg.apache.poi, qui est inclus dans un ensemble de fonctionnalités appelées API du modèle utilisateur et peut gérer de manière transparente les fichiers au format .xls / .xlsx / .xlsm. Il s'agit d'utiliser .ss.usermodel.Sheet`. Aucune API n'est fournie pour cette interface comme «Sheet # isWorksheet ()».

Alors que devons-nous faire? L'interface de feuille a la structure hiérarchique suivante.

① ** Feuille **: feuille au format .xls / .xlsx / .xlsm ├─ ② ** HSSFSheet **: feuille au format .xls ├─ ③ ** XSSFSheet **: feuille au format .xlsx / .xlsm │ ├─ ④ ** XSSFChartSheet **: Feuille graphique au format .xlsx / .xlsm │ └─ ⑤ ** XSSFDialogsheet **: Feuille de dialogue au format .xlsx / .xlsm └─ ⑥ ** SXSSFSheet **: feuille au format .xlsx (méthode de streaming)

Pour les feuilles au format .xlsx / .xlsm, vous pouvez vérifier le type de feuille en vérifiant s'il s'agit d'une instance de ④⑤ [^ 3]. Cependant, pour les feuilles au format .xls, ② fournit une méthode pour renvoyer s'il s'agit d'une feuille de dialogue ou non [^ 4], mais il ne fournit pas de méthode pour distinguer s'il s'agit d'une feuille graphique ou non. C'est une impasse.

[^ 3]: Cependant, en réalité, il y a un écueil introduit dans le n ° 5. [^ 4]: Cependant, en réalité, cette méthode ne fonctionne pas comme introduite dans le n ° 4.

Solution

Cela peut être résolu en utilisant une solution de couche inférieure.

• Pour les feuilles au format .xls, vous pouvez identifier le type de feuille à l'aide de l'API de modèle d'événement fournie par Apache POI [^ 5].
• Pour les feuilles au format .xlsx / .xlsm, vous pouvez identifier le type de feuille en utilisant SAX (Simple API for XML).

Les détails sont présentés dans cet article.

[^ 5]: Cependant, en réalité, cela a aussi les écueils introduits dans le n ° 6.

N ° 2 Les majuscules / minuscules du nom de classe sont-elles un type?

La description

La hiérarchie dérivée de l'interface Sheet introduite dans le n ° 1 est réimprimée.

org.apache.poi.ss.usermodel.Sheet 　├─ org.apache.poi.hssf.usermodel.HSSFSheet 　├─ org.apache.poi.xssf.usermodel.XSSFSheet 　│　　├─ org.apache.poi.xssf.usermodel.XSSFChartSheet 　│　　└─ org.apache.poi.xssf.usermodel.XSSFDialogsheet 　└─ org.apache.poi.xssf.streaming.SXSSFSheet

Comprenez vous? Seule la feuille XSSFDialogsheet a un "s" inférieur. Intentionnel? Non, c'est une faute de frappe.

Solution

Si l'implémenteur l'a nommé ainsi, c'est le nom de classe correct. Ichamon basé sur des croyances n'est pas une solution. Tirez parti des compléments et suggestions IDE.

N ° 3 Notez le nom de la méthode qui est différent de la convention de dénomination générale

La description

Dans l'explication du n ° 1, j'ai écrit que l'interface interface HSSFSheet fournit une méthode pour renvoyer s'il s'agit d'une feuille de dialogue ou non. La méthode suivante est celle-là. (Réimprimé à partir du [Document API] fourni par Apache POI (https://poi.apache.org/apidocs/4.1/org/apache/poi/hssf/usermodel/HSSFSheet.html#getDialog--))

getDialog 　　public boolean getDialog() get whether sheet is a dialog sheet or not Returns: 　　isDialog or not

get renvoie booléen. N'est-ce pas bon? Il n'y a pas de règle selon laquelle il doit être nommé ʻisDialogSheet () `.

Solution

Lorsque vous recherchez une méthode, n'assumez pas ce nom de méthode. Si vous voulez une méthode qui retourne boolean, vous devez frapper la méthode dont le type de retour est boolean. C'est la vérité. Vous devriez lire la documentation de l'API de haut en bas sans aucun préjugé et rechercher la fonctionnalité que vous recherchez.

No.4 HSSFSheet # getDialog () ne fonctionne pas

La description

Notez que la méthode HSSFSheet # getDialog () introduite dans le n ° 3 ne fonctionne pas. En particulier,

• L'exécution de cette méthode sur une feuille graphique entraînera une NullPointerException.
• Au moins lorsque j'exécute cette méthode sur une feuille de dialogue créée dans Excel pour Office 365, elle renvoie false au lieu de true.

Je vais le démontrer.

1) Créer un fichier Excel de test (format .xls)

Vous pouvez voir que vous pouvez créer les types de feuilles suivants en cliquant avec le bouton droit de la souris sur l'onglet du nom de la feuille et en sélectionnant Insérer ...

• feuille de travail
• Feuille "Graphique"
• Feuille "Macro Excel 4.0"
• Fiche "Dialogue MS Excel 5.0"

Créez chaque feuille et décrivez le contenu approprié.

Enregistrez-le en tant que classeur Excel au format .xls.

2) Création du code de test

Par exemple, écrivez le code de test suivant.

package mypackage;

import java.io.File;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class Main {
    
    public static void main(String[] args) {
        
        try (Workbook wb = WorkbookFactory.create(
                new File("C:\\Users\\xxx\\No.4 tests.xls"))) {
            
            System.out.println(wb.getNumberOfSheets());
            
            wb.sheetIterator().forEachRemaining(s -> {
                System.out.print(String.format(
                        "%s : %s : ",
                        s.getSheetName(),
                        s.getClass().getName()));
                
                if (s instanceof HSSFSheet) {
                    HSSFSheet sheet = (HSSFSheet) s;
                    try {
                        System.out.println(sheet.getDialog());
                    } catch (NullPointerException e) {
                        System.out.println("★ occurrence NPE ★");
                    }
                } else {
                    System.out.println();
                }
            });
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

3) Exécution des tests

L'exécution du code de test ci-dessus donne les résultats suivants:

4
A_feuille de travail: org.apache.poi.hssf.usermodel.HSSFSheet : false
B_Feuille graphique: org.apache.poi.hssf.usermodel.HSSFSheet :★ occurrence NPE ★
C_Feuille de macro: org.apache.poi.hssf.usermodel.HSSFSheet : false
D_Feuille de dialogue: org.apache.poi.hssf.usermodel.HSSFSheet : false

Vous pouvez voir que la «feuille B_Graph» lève une NullPointerException et la «feuille D_Dialog» renvoie false au lieu de true.

Solution

Je pense que vous ne devriez pas utiliser la méthode HSSFSheet # getDialog ().

La feuille de dialogue / feuille macro au format n ° 5 .xlsx / .xlsm est ignorée

La description

Il semble que l'API du modèle utilisateur Apache POI ne puisse pas charger les feuilles de dialogue et les feuilles de macro, au moins pour les fichiers au format .xlsx / .xlsm créés avec Excel pour Office 365. Je vais le démontrer.

1) Créer un fichier Excel de test (format .xlsm)

Enregistrez à nouveau le fichier de test utilisé dans le n ° 4 au format .xlsm.

• Puisqu'il contient une feuille de macro, il est enregistré en tant que livre avec macro.

2) Création du code de test

Utilisez le même code de test que le n ° 4.

• Changez uniquement la partie où le nom de fichier est spécifié.

3) Exécution des tests

Lorsque vous exécutez le code de test, vous obtenez les résultats suivants:

2
A_feuille de travail: org.apache.poi.xssf.usermodel.XSSFSheet : 
B_Feuille graphique: org.apache.poi.xssf.usermodel.XSSFChartSheet : 

Vous pouvez voir que "C_ Macro Sheet" et "D_ Dialog Sheet" ne sont pas chargés.

Solution

Si vous souhaitez charger des feuilles de macro et des feuilles de dialogue à partir d'un fichier Excel au format .xlsx / .xlsm, vous devez renoncer à utiliser l'API du modèle utilisateur de POI et utiliser l'API du modèle d'événement ou SAX (API simple pour XML). Je pense (non vérifié).

La feuille de dialogue au format N ° 6 .xls ne peut pas être identifiée même par l'API du modèle d'événement

La description

Comme présenté dans le n ° 1, l'API du modèle utilisateur Apache POI ne peut pas faire la distinction entre les types de feuilles de calcul au format .xls. Une fonctionnalité appelée API Event Model est votre prochain choix.

Le format de fichier Excel .xls est appelé le format de fichier d'échange binaire (BIFF) et son contenu est une collection d'enregistrements BIFF. Pour un aperçu du BIFF, ce blog est facile à comprendre, et [spécifications détaillées](https://docs.microsoft.com/en-us/openspecs/office_file_formats/ ms-xls / cd03cb5f-ca02-4934-a391-bb674cb8aa06) a été publié par Micorosoft.

Je peux lire le contenu de l'enregistrement BIFF avec l'API du modèle d'événement d'Apache POI, mais apparemment la méthode WSBoolRecord # getDialog () ne fonctionne pas correctement et je ne peux toujours pas dire correctement la feuille de dialogue. Je vais le démontrer ci-dessous.

1) Préparez un fichier Excel de test (format .xls)

Utilisez le même fichier que celui utilisé dans le n ° 4.

2) Code de test

Par exemple, écrivez le code de test suivant.

package mypackage;

import java.io.FileInputStream;

import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
import org.apache.poi.hssf.eventusermodel.HSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFRequest;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class Main {
    
    private static class TestListener implements HSSFListener {
        
        @Override
        public void processRecord(Record record) {
            System.out.println(record);
        }
    }
    
    public static void main(String[] args) throws Exception {
        
        try (FileInputStream fin = new FileInputStream(
                "C:\\Users\\xxx\\No.6 tests.xls");
                POIFSFileSystem poifs = new POIFSFileSystem(fin)) {
            
            HSSFRequest req = new HSSFRequest();
            TestListener listener = new TestListener();
            req.addListenerForAllRecords(listener);
            HSSFEventFactory factory = new HSSFEventFactory();
            factory.abortableProcessWorkbookEvents(req, poifs);
        }
    }
}

3) Exécution des tests

Lorsque vous exécutez le code de test, le contenu de l'enregistrement BIFF est généré.

• Les numéros de ligne à l'extrémité gauche sont uniquement à des fins d'explication et ne sont pas réellement affichés.

Si vous regardez de près, ce sera long, je vais donc vous l'expliquer brièvement. L'enregistrement BOF suivant marque le début de la définition de la feuille de dialogue. Il est difficile de comprendre pourquoi, mais l'attribut .type de l'enregistrement BOF dans la feuille de dialogue est défini comme «0x0010», qui est le même que dans la feuille de calcul. (D'autre part, la feuille de graphique est définie comme «0x0020» et la feuille de macro est définie comme «0x0040», ce qui peut être distingué.)

...
5201: 
5202: [BOF RECORD]
5203:     .version  = 0x0600
5204:     .type     = 0x0010 (worksheet)
5205:     .build    = 0x4F5A
5206:     .buildyear= 1997
5207:     .history  = 0x000200C9
5208:     .reqver   = 0x00000806
5209: [/BOF RECORD]
5210: 
...

Les feuilles de calcul et les feuilles de dialogue sont censées être distinguées par l'attribut .dialog des enregistrements WSBOOL suivants (https://docs.microsoft.com/en-us/openspecs/office_file_formats/ms-xls/ccbd73f9- Pour une raison quelconque, ff1d-4069-be31-13d16c074ec4) est en fait affiché comme .dialog = false malgré la feuille de dialogue.

...
5263: 
5264: [WSBOOL]
5265:     .wsbool1        = 4
5266:         .autobreaks = false
5267:         .dialog     = false
5268:         .rowsumsbelw= false
5269:         .rowsumsrigt= false
5270:     .wsbool2        = ffffffd1
5271:         .fittopage  = true
5272:         .displayguts= false
5273:         .alternateex= true
5274:         .alternatefo= true
5275: [/WSBOOL]
5276: 
...

Solution

WSBoolRecord Je soupçonne qu'il y a un problème avec l'implémentation de la classe. Cette spécification BIFF et [Code source publié par Apahe] Vous pourrez peut-être trouver la cause et les contre-mesures en jetant un coup d'œil à (https://poi.apache.org/download.html#POI-4.1.0).

Alternativement, une feuille qui est un peu délicate mais qui n'a pas d'enregistrement de ROW peut être considérée comme une feuille de dialogue. (Non vérifié)

Le format de livre n ° 7 (.xls vs .xlsx / .xlsm) peut ou non reproduire des blancs dans la formule

La description

L'API du modèle utilisateur Apache POI ne conserve pas les espaces vides dans les formules. Ceci est [spécifié] dans la documentation Apache POI (https://poi.apache.org/components/spreadsheet/formula.html).

Ce serait bien si les blancs étaient toujours supprimés sans être conservés, mais le comportement dépend du format du fichier Excel d'origine.

Plus précisément, lorsqu'une cellule est remplie avec = 1 + 2 et que cela est lu par la méthode Cell # getCellFormula (),

• Si le fichier Excel d'origine est au format .xls, vous obtiendrez la chaîne «1 + 2». Autrement dit, les blancs sont supprimés.
• Si le fichier Excel d'origine est au format .xlsx / .xlsm, vous obtiendrez la chaîne `` 1 + 2. Autrement dit, les blancs de la formule sont reproduits.

Cela peut être un problème, par exemple, si vous souhaitez comparer des formules dans des fichiers Excel dans différents formats.

Solution

Une approche consiste à créer votre propre fonction pour supprimer les espaces inutiles de la chaîne de formule obtenue par la méthode Cell # getCellFormula () et à standardiser la chaîne de formule. À ce stade, il y a quelques points à prendre en compte.

La première est que vous ne devez pas supprimer l'espace blanc dans la chaîne littérale. C'est parce que les formules =" Hello, World !! " et les formules =" Hello, World !! " ont des significations différentes. Cependant, il ne devrait pas être trop difficile de traiter ce genre de problème.

Le deuxième point à noter est que dans Excel, les espaces (espaces demi-largeur) ont également un rôle d '"opérateur de référence". Par exemple, lorsque la formule = MAX (A1: C3 B2: E5) est entrée dans la cellule, c'est "dans la partie partagée de la zone de cellule A1: C3 et de la zone de cellule B2: E5, c'est-à-dire dans la zone de cellule B2: C3. Représente la valeur maximale de. Excel vous permet de nommer les zones de cellule. Par conséquent, la chaîne «= zone A zone B» peut également être une bonne formule. (Si la partie partagée de "Zone A" et "Zone B" est composée de plusieurs cellules, l'erreur "#VALUE!" Se produira, et s'il s'agit d'une seule cellule, la valeur de cette cellule sera le résultat du calcul.) Bien entendu, vous ne devez pas supprimer ces espaces en tant qu '«opérateurs de référence».

C'est une façon de créer une fonction standardisée avec ces points à l'esprit.

Vous pouvez également comprendre le format de fichier Excel en vous référant au fichier PDF ici et créer votre propre analyseur.

Quoi qu'il en soit, c'est une tâche ardue.

L'API n ° 8 pour la manipulation des bordures diagonales n'est pas fournie

La description

CellStyle Interface et son interface dérivée [HSSFCellStyle](https: //poi.apache) .org / apidocs / 4.1 / org / apache / poi / hssf / usermodel / HSSFCellStyle.html), [XSSFCellStyle](https://poi.apache.org/apidocs/4.1/org/apache/poi/xssf/usermodel/ L'interface XSSFCellStyle.html) fournit plusieurs API pour manipuler les bordures.

• CellStyle
• Obtention du style de bordure - getBorderBottom(): BorderStyle - getBorderLeft(): BorderStyle - getBorderRight(): BorderStyle - getBorderTop(): BorderStyle
• Obtenir la couleur de la bordure - getBottomBorderColor(): short - getLeftBorderColor(): short - getRightBorderColor(): short - getTopBorderColor(): short
• Réglage du style de bordure - setBorderBottom(BorderStyle): void - setBorderLeft(BorderStyle): void - setBorderRight(BorderStyle): void - setBorderTop(BorderStyle): void
• Réglage de la couleur de la frontière - setBottomBorderColor(short): void - setLeftBorderColor(short): void - setRightBorderColor(short): void - setTopBorderColor(short): void
• HSSFCellStyle -(Omis)
• XSSFCellStyle -(Omis)

Comme vous pouvez le voir, seule l'API pour les bordures verticales (gauche, droite) et horizontales (haut, bas) est fournie, et non l'API pour les bordures diagonales.

Solution

Il est publié sur ce blog (Program memorandum etc --Java Apache POI xls file only to get diagonal bordures) Vous devez vous référer aux informations et mettre en œuvre vous-même le traitement lié aux bordures diagonales. Dans ce cas, il est nécessaire de comprendre la structure de classe à l'intérieur de POI.

N ° 9 Le comportement concernant les commentaires vides diffère selon le format de fichier

La description

Pour les commentaires vides (cellules avec commentaires mais rien dans les commentaires), le classeur au format xls renvoie la chaîne vide " ", tandis que xlsx / xlsm Le classeur de format renvoie «null».

Cell cell = (Obtenir l'objet de cellule d'une manière ou d'une autre)
Comment comment = cell.getCellComment();  //S'il n'y a pas de commentaire, null est retourné
RichTextString richText = comment.getString();  //Une valeur non nulle est également renvoyée pour les commentaires vides
String str = richText.getString();  //★ Le comportement des commentaires vides diffère selon le format du livre ★

Solution

Par exemple, il serait préférable de normaliser en une chaîne de caractères vide " " par la méthode suivante.

String str = Optional.ofNullable(richText.getString).orElse("");

No 10 XSSFComment # setVisible (booléen) ne fonctionne pas

La description

L'exécution de setVisible (false) sur le commentaire de cellule affiché ne le masque pas, et l'exécution de setVisible (true) sur le commentaire de cellule masqué ne l'affiche pas. Hmm. Je me suis demandé si vrai et faux étaient le contraire, mais cela n'a pas fonctionné.

Solution

La méthode XSSFComment # setVisible (boolean) ne peut pas être utilisée. Aucune alternative n'a été trouvée.

Serrage moyen

Je suis un peu fatigué, alors je vais m'arrêter ici.

Cela semble persistant, mais Apache POI est très utile. C'est très pratique, mais comme mentionné ci-dessus, il est également vrai que lorsque vous essayez de l'utiliser, vous devenez fou.

Un jour, je veux contribuer moi-même, mais je veux partir un moment pour maintenir ma santé mentale.