Trucs & Astuces : Librairie org.apache.poi

Dans cet article, je vais vous donner quelques astuces pour agrémenter la construction de votre export Excel.

Comment générer un fichier Excel via JAVA en verrouillant certaines cellules ?

Comment figer un volet avec POI ?

Comment créer un menu déroulant (liste de valeurs ou Drop Down List) ?

Un billet qui peut vous apporter quelques astuces et vous épargner quelques mauvaises surprises au moment de votre développement.

Controverse sur l'utilisation de POI : Il est dit dans la littérature trouvée sur Internet que POI consomme jusqu'à deux fois plus de mémoire que la librairie Jexcel. A vous donc d'utiliser la librairie la plus adaptée à votre projet, sachant que POI est à mon avis plus complet  et gère aussi les documents Word et XML. 

1) Générer un fichier Excel et verrouiller certaines cellules

Dans Excel, il suffit de faire un clic droit sur une cellule puis aller dans format, protection et cocher la case « Verrouillée ».
Les cellules que vous souhaitez laisser éditables ne doivent pas avoir la case « Verrouillée » cochée.
Ensuite il faut verrouiller la feuille avec un mot de passe (révision > protéger la feuille). Seules les cellules non « Verrouillées » sont éditables.

Dans POI pour verrouiller des cellules, il faut dans un premier temps créer un CellStyle, (Style de cellules) au niveau du classeur (workbook)
et valoriser l’option de verrouillage à « true ».

e.g. :

HSSFWorkbook wb=new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("feuille_1");
//style pour verouiller les cellules
CellStyle lockedCellStyle = wb.createCellStyle();
lockedCellStyle.setLocked(true);


HSSFRow fRow=sheet.createRow(row);
HSSFCell fCell=fRow.createCell(cell);
//affectation de la cellule cell avec un valeur et

//le style qui verrouille l’écriture

fCell.setCellValue(« value »);
fCell.setCellStyle(lockedCellStyle);

Le verrouillage de la cellule est vraiment effectif si la feuille est verrouillée par un mot de passe.

Ajoutez :

sheet.protectSheet("secret");

Si une feuille est verrouillée, toutes les cellules sont verrouillées par défaut, il ne sera plus possible d’éditer une cellule.
Le seul moyen à ma connaissance est de spécifiquement appliquer un style « déverrouillée » à chaque cellule que l’on veut rendre éditable.

Ajoutez le bloc suivant et l’appliquer aux cellules éditables :

//style pour déverrouiller les cellules

CellStyle unlockedCellStyle = wb.createCellStyle();
unlockedCellStyle.setLocked(false);

Comment faire si l’on souhaite que toutes les cellules soient éditables sauf certaines ?

Un moyen pour rendre toutes les cellules éditables sans avoir à parcourir toutes les cellules de la feuille dans JAVA :
Allez dans Excel, ouvrez un nouveau classeur, sélectionnez toutes les cellules et décochez le format > protection > « verrouillée ».
Sauvegardez et importez le template dans votre application.
En résumé, au lieu de partir d’un tableur vierge que vous créez vous-même en JAVA avec POI, vous partez d’un tableur vierge que vous importez
(en ayant simplement préparé les cellules à l’avance).

2) Figer un volet

Pour figer un volet, c’est-à-dire fixer un groupe de cellules qui sera toujours visible lors du parcours de la feuille avec les ascenseurs (vertical et horizontal),
il suffit d’ajouter ceci :

// le groupe de cellules à figer est dans les 3 premières colonnes des 2 premières lignes.

sheet.createFreezePane(3,2) ;

Il est d'usage de figer un volet par exemple pour garder le menu ou les entêtes visibles.

3) Menu déroulant

Pour créer une menu déroulant (ou liste de valeurs ou encore DropDownList), c’est-à-dire une cellule ou groupe de cellules avec un nombre de valeurs possibles déterminées, il faut créer une contrainte qui sera appliquée à une cellule ou un groupe de cellules.
Si vous souhaitez verrouiller votre feuille, pensez
à déverrouiller la cellule dans laquelle vous souhaitez héberger votre menu déroulant.

L’usage d’une dataValiation (validation de données) permet de créer une liste de valeur, contrôler le contenu d’une cellule ou d’une plage de données,
émettre des messages d’avertissement aux usagers, créer des menus déroulants (source moncherwatson).
En effet, si nous saisissons
une valeur aléatoire dans la case contenant la liste, un message d’erreur apparait car seules les valeurs de la liste sont autorisées.

xes.fillCell(sheet,0,3,"VALEUR",unlockedCellStyle);
CellRangeAddressList addressList = new CellRangeAddressList(0,0,3,3);
//Ici la liste ne s'affichera que dans la cellule (D1) mais il est possible d'étendre la zone. Le premier 0
//correspond à la première ligne, le deuxième à la dernière ligne,le premier 3 à la première colonne et le
//deuxième à la dernière colonne
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(new String { "10", "20", "30" });
DataValidation dataValidation = new HSSFDataValidation(addressList,dvConstraint);
// affichage de la flèche
dataValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(dataValidation);

Le billet est terminé, à une prochaine fois pour d'autres Trucs & Astuces.

Sources :

http://poi.apache.org/
http://jexcelapi.sourceforge.net/
http://www.developpez.net/forums/d254949/java/general-java/apis/documents/conso-memoire-poi-vs-jexcel/
http://stackoverflow.com/questions/3746441/making-excel-sheet-read-only/
http://moncherwatson.wordpress.com/2011/03/14/astuce-excel-validation-de-donnees/

Un commentaire

  1. Bonjour,

    Je cherchais l’astuce pour figer les volets en utilisant NPOI (c#), il se trouve que c’est exactement pareil que pour POI (Java). La fonctionnalité a bien été retranscrise ! Merci 🙂

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Captcha *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.