[POI] Création d'une liste déroulante dans Excel

**tomlrt53** · 17/04/2018, 11h23

Bonjour tout le monde ! Depuis maintenant quelques heures j'essaye de créer un document EXCEL à l'aide d'un programme JAVA. J'arrive pour l'instant à créer mon document, insérer du texte dans certains cellules et leurs appliquer un style ainsi que fusionner des cellules. Par contre il y a quelque chose que je n'arrive pas à faire. J'aimerai créer une liste déroulante dans mon Excel grâce à mon programme Java.

Voyons ça en image :

J'ai pour l'instant ceci

Nom : Capture.PNG
Affichages : 1316
Taille : 5,6 Ko

Nom : Capture.PNG
Affichages : 1316
Taille : 5,6 Ko

Mais j'aimerais avoir ceci :

Nom : 2018-04-17 11_00_05-Microsoft Excel - tracaE2.png
Affichages : 1343
Taille : 6,6 Ko

Etant donnée que j'utilise la libraire POI de Apache, j'ai regarder la documentation. Cependant, tout cela m'a l'air ancien et très peu complet.

J'ai donc rechercher sur Internet et voici sur quoi je suis tombé :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
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);

J'ai donc repris ce code et je l'ai modifié pour qu'il correspondes à ce que je veux vraiment, voici donc mon code complet :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
 
package org.apache.poi.ss.examples;
 
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 
import java.util.Map;
import java.util.HashMap;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.util.CellRangeAddressList;
 
public class Excel {
 
    public static void main(String[] args) throws Exception {
        Workbook wb;
 
        if (args.length > 0 && args[0].equals("-xls")) {
            wb = new HSSFWorkbook();
        } else {
            wb = new XSSFWorkbook();
        }
 
        Map<String, CellStyle> styles = createStyles(wb);
        Sheet sheet = wb.createSheet("Configurateur E2 | Fichier de traçabilité");
        sheet.setPrintGridlines(false);
        sheet.setDisplayGridlines(false);
 
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
 
        //Taille des colonnes
        sheet.setColumnWidth(0, 15 * 256);
        sheet.setColumnWidth(1, 15 * 256);
        sheet.setColumnWidth(2, 15 * 256);
        sheet.setColumnWidth(3, 15 * 256);
        sheet.setColumnWidth(4, 15 * 256);
        sheet.setColumnWidth(5, 15 * 256);
        sheet.setColumnWidth(6, 15 * 256);
 
        //Création des lignes à utiliser
        Row titleRow = sheet.createRow(0);
        Row row1 = sheet.createRow((short) 1);
        Row row2 = sheet.createRow((short) 2);
        Row row3 = sheet.createRow((short) 3);
        Row row4 = sheet.createRow((short) 4);
 
        //Création des cellulles et assigner les lignes aux cellules
        titleRow.createCell(0).setCellStyle(styles.get("title")); //Créer une cellule et appliquer un style à cette cellule
        row1.createCell(0).setCellStyle(styles.get("rowS"));
        row2.createCell(0).setCellStyle(styles.get("rowS"));
        row2.createCell(2).setCellStyle(styles.get("rowS"));
        row3.createCell(0).setCellStyle(styles.get("rowS"));
        row3.createCell(2).setCellStyle(styles.get("rowS"));
        row4.createCell(0).setCellStyle(styles.get("rowS"));
        row4.createCell(2).setCellStyle(styles.get("rowS"));
 
        CellRangeAddressList addressList = new CellRangeAddressList(2, 2, 2, 2);
        DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(new String[]{"10", "20", "30"});
        DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
        dataValidation.setSuppressDropDownArrow(false);
        sheet.addValidationData(dataValidation);
 
        //Renseigner sur quelle cellule nous voulons écire
        Cell titleCell = titleRow.getCell(0);
        Cell row1Cell = row1.getCell(0);
        Cell row2Cell = row2.getCell(0);
        Cell row2Cell1 = row2.getCell(2);
        Cell row3Cell = row3.getCell(0);
        Cell row3Cell1 = row3.getCell(2);
        Cell row4Cell = row4.getCell(0);
        Cell row4Cell1 = row4.getCell(2);
 
        //Texte qui est insérer dans telles cellules
        titleCell.setCellValue("Fichier de traçabilité");
        row1Cell.setCellValue("NOM DU PRODUIT");
        row2Cell.setCellValue("N° référence");
        row2Cell1.setCellValue("aa");
        row3Cell.setCellValue("N° CI");
        row3Cell1.setCellValue("bb");
        row4Cell.setCellValue("N° MF");
        row4Cell1.setCellValue("cc");
 
        //Fusion des différentes cellulles
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$F$2"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$B$3"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$C$3:$D$3"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$4:$B$4"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$C$4:$D$4"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$5:$B$5"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$C$5:$D$5"));
 
        //Création du fichier EXCEL
        String file = "tracaE2.xls";
        if (wb instanceof XSSFWorkbook) {
            file += "x";
        }
        try (FileOutputStream out = new FileOutputStream(file)) {
            wb.write(out);
        }
    }
 
    /**
     * cell styles used for formatting calendar sheets
     */
    private static Map<String, CellStyle> createStyles(Workbook wb) {
        Map<String, CellStyle> styles = new HashMap<>();
 
        CellStyle style;
        Font titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short) 14);
        titleFont.setFontName("Trebuchet MS");
        style = wb.createCellStyle();
        style.setFont(titleFont);
        style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
        style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
        styles.put("title", style);
 
        style = wb.createCellStyle();
        Font rowFont = wb.createFont();
        rowFont.setFontHeightInPoints((short) 9);
        rowFont.setFontName("Trebuchet MS");
        style.setFont(rowFont);
        style.setAlignment(HorizontalAlignment.CENTER);
        styles.put("rowS", style);
 
        return styles;
    }
}

J'execute donc ce programme, je précise qu'aucune erreur était signaler avant compilation, et une fois exécuter : problème. Voici le message d'erreur qui arrive dans la console. Je n'arrive pas à comprendre ce message, j'ai essayer différentes choses mais je ne sais pas d'où ça peut venir.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
Exception in thread "main" java.lang.ClassCastException: org.apache.poi.hssf.usermodel.HSSFDataValidation cannot be cast to org.apache.poi.xssf.usermodel.XSSFDataValidation
    at org.apache.poi.xssf.usermodel.XSSFSheet.addValidationData(XSSFSheet.java:3918)
    at org.apache.poi.ss.examples.Excel.main(Excel.java:66)

Merci beaucoup à ceux qui m'aideront ! Pour information supplémentaire, je travaille grâce à l'IDE Netbeans 8.2.

**joel.drigo** · 17/04/2018, 11h31

Salut,

Dans l'API POI tout ce qui commence par HSSF concerne l'ancien Excel (les fichiers XLS) et tout ce qui commence par XSSF concerne le nouveau Excel (les fichiers XLSX, à partir de Excel 2007). On ne doit pas mélanger les deux. De préférence, en "accès", utiliser les interfaces communes, et en création, les implémentations concrètes adaptées.

**tomlrt53** · 17/04/2018, 11h41

J'ai donc changer mes HSSF en XSSF et mes librairies ont donc été changer. Cependant j'ai une "suggestion" de Netbeans avant de compiler sur la ligne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DataValidation dataValidation = new XSSFDataValidation(addressList,dvConstraint);

Il me conseille de faire ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DataValidation dataValidation = new XSSFDataValidation(addressList, (CTDataValidation) dvConstraint);

Cependant, lorsque je fais ceci un panneau interdiction apparaît et le programme ne peux pas s’exécuter. Le programme ne peux pas non plus s'executer sans toucher à la première ligne de code.

De préférence, en "accès", utiliser les interfaces communes, et en création, les implémentations concrètes adaptées.

Ceci, c'est pour simplifier mon programme tu veux dire?

Remplacer ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
titleRow.createCell(0).setCellStyle(styles.get("title")); //Créer une cellule et appliquer un style à cette cellule
row1.createCell(0).setCellStyle(styles.get("rowS"));
row2.createCell(0).setCellStyle(styles.get("rowS"));
row2.createCell(2).setCellStyle(styles.get("rowS"));
row3.createCell(0).setCellStyle(styles.get("rowS"));
row3.createCell(2).setCellStyle(styles.get("rowS"));
row4.createCell(0).setCellStyle(styles.get("rowS"));
row4.createCell(2).setCellStyle(styles.get("rowS"));

par une boucle for par exemple ?

**joel.drigo** · 17/04/2018, 11h54

Envoyé par tomlrt53

Cependant, lorsque je fais ceci un panneau interdiction apparaît et le programme ne peux pas s’exécuter. Le programme ne peux pas non plus s'executer sans toucher à la première ligne de code.

Hum, je ne vois pas de quoi tu parles par "panneau interdiction". Ni "s'executer sans toucher à la première ligne de code". Si tu as une erreur de compilation, donne-moi l’intitulé de l'erreur et la ligne de code concernée. S'il y a une exception, donne-moi la stacktrace et la ligne de code concernée.

Envoyé par tomlrt53

Ceci, c'est pour simplifier mon programme tu veux dire?

Oui et pour éviter de dupliquer le code qui n'a pas besoin de l'être.

Envoyé par tomlrt53

Remplacer ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
titleRow.createCell(0).setCellStyle(styles.get("title")); //Créer une cellule et appliquer un style à cette cellule
row1.createCell(0).setCellStyle(styles.get("rowS"));
row2.createCell(0).setCellStyle(styles.get("rowS"));
row2.createCell(2).setCellStyle(styles.get("rowS"));
row3.createCell(0).setCellStyle(styles.get("rowS"));
row3.createCell(2).setCellStyle(styles.get("rowS"));
row4.createCell(0).setCellStyle(styles.get("rowS"));
row4.createCell(2).setCellStyle(styles.get("rowS"));

par une boucle for par exemple ?

Non, je ne parlais pas spécifiquement de ce genre de choses (c'est un autre sujet).

faire comme tu as fait là :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
Workbook wb;
 
        if (args.length > 0 && args[0].equals("-xls")) {
            wb = new HSSFWorkbook();
        } else {
            wb = new XSSFWorkbook();
        }

Tu n'as pas un code pour les HSSFWorkbook et un pour les XSSFWorkbook. Tu manipules des Workbook. Il faut généraliser ça. Du coup, quand tu seras tenté d'utiliser une implémentation spécifique, l'IDE va te rappeler d'utiliser l'interface générique. Sauf pour les new, où tu seras obligé d'utiliser des HSSFxxx pour XLS et XSSFxxx pour XLSX. Si tu travailles vraiment avec les deux, il peut être intéressant de faire un helper basée sur une abstraction qui gère la partie commune (interface) et deux implémentations concrètes pour la partie spécifique, ou deux méthodes dans une classe commune. Ainsi, le programme principal ne contient pas plein de if : avec une fabrique spécifique à chaque type de fichier, tout le reste du code est le même.

Par exemple, j'ai écrit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
public abstract class AbstractValidation implements Validation {
 
	protected final MessageBox prompt;
	protected final boolean emptyCellAllowed;
 
	public AbstractValidation(MessageBox prompt, boolean emptyCellAllowed) {
		this.prompt=prompt;
		this.emptyCellAllowed=emptyCellAllowed;
	}
 
	@Override
	public void create(Sheet sheet, ColumnIndexHandler index, CellRangeAddressList regions) {
		final DataValidation dataValidation;
		if( sheet instanceof HSSFSheet ) {
			dataValidation = createHSSFDataValidation(sheet, index, regions);
		}
		else if ( sheet instanceof XSSFSheet ) {
			dataValidation = createXSSFDataValidation(sheet, index, regions);
		}
		else {
			throw new IllegalStateException();
		} 
		if ( prompt!=null ) {
 
			final String message;
			if ( sheet instanceof XSSFSheet ) {
				message = ExcelUtils.escapeWhitespaces(prompt.message); // workaround bug
			}
			else {
				message = prompt.message;
			}
			dataValidation.createPromptBox(prompt.title, message);
			dataValidation.setShowPromptBox(true); 
 
		}
		if ( emptyCellAllowed ) {
			dataValidation.setEmptyCellAllowed(true);
		}
		//dataValidation.setSuppressDropDownArrow(false);
		dataValidation.setShowErrorBox(true);
		sheet.addValidationData(dataValidation); 
	}
 
	private XSSFDataValidation createXSSFDataValidation(Sheet sheet, ColumnIndexHandler index,
			CellRangeAddressList regions) {
		final XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
		final XSSFDataValidationConstraint constraint = createXSSFConstraint(dvHelper, index, regions);
		return (XSSFDataValidation)dvHelper.createValidation(constraint, regions);
	}
 
	private DataValidation createHSSFDataValidation(Sheet sheet, ColumnIndexHandler index,
			CellRangeAddressList regions) {
		final DVConstraint constraint = createHSSFConstraint(index, regions);
		return new HSSFDataValidation(regions, constraint);
	}
 
	protected abstract XSSFDataValidationConstraint createXSSFConstraint(XSSFDataValidationHelper helper, ColumnIndexHandler index,
			CellRangeAddressList regions);
 
	protected abstract DVConstraint createHSSFConstraint(ColumnIndexHandler index,
			CellRangeAddressList regions);
 
}

et

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
public class ListValidation extends AbstractValidation {
 
	private final String[] values;
 
	public ListValidation(MessageBox prompt, boolean emptyCellAllowed, String...values) {
		super(prompt, emptyCellAllowed);
		this.values=values;
	}
 
	protected String[] getValues() {
		return values;
	}
 
	@Override
	protected DVConstraint createHSSFConstraint(ColumnIndexHandler index, CellRangeAddressList regions) {
		return DVConstraint.createExplicitListConstraint(getValues());
	}
 
 
	@Override
	protected XSSFDataValidationConstraint createXSSFConstraint(XSSFDataValidationHelper helper,
			ColumnIndexHandler index, CellRangeAddressList regions) {
		return (XSSFDataValidationConstraint) helper.createExplicitListConstraint(getValues());
	}
 
}

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
public class NamedRangeValidation extends AbstractValidation {
 
	private final String namedRange;
 
	public NamedRangeValidation(MessageBox prompt, boolean emptyCellAllowed, String namedRange) {
		super(prompt, emptyCellAllowed);
		this.namedRange=namedRange;
	}
 
	protected String getNamedRange() {
		return namedRange;
	}
 
	@Override
	protected DVConstraint createHSSFConstraint(ColumnIndexHandler index, CellRangeAddressList regions) {
		return DVConstraint.createFormulaListConstraint(getNamedRange());
	}
 
 
	@Override
	protected XSSFDataValidationConstraint createXSSFConstraint(XSSFDataValidationHelper helper,
			ColumnIndexHandler index, CellRangeAddressList regions) {
		return (XSSFDataValidationConstraint) helper.createFormulaListConstraint("="+getNamedRange());
	}
 
}

On pourrait simplifier, en utilisant les interfaces (donc il n'y aurait plus createHSSFConstraint et createXSSFConstraint, mais createConstraint) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
protected DataValidationConstraint createConstraint(DataValidationHelper helper,
			ColumnIndexHandler index, CellRangeAddressList regions) {
		return helper.createExplicitListConstraint(getValues());
	}

(bon, y a petit schmilblick qui obligerait à faire un if pour le "=" à concaténer (à moins que le problème ait été corrigé dans une version plus récente que celle que j'ai utilisée à l'époque).

**tomlrt53** · 17/04/2018, 13h14

J'ai donc ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
CellRangeAddressList addressList = new CellRangeAddressList(2, 2, 2, 2);
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(new String[]{"10", "20", "30"});
DataValidation dataValidation = new XSSFDataValidation(addressList,dvConstraint);
dataValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(dataValidation);

cependant il me dit qu'il y a une erreur, et que je dois faire ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
CellRangeAddressList addressList = new CellRangeAddressList(2, 2, 2, 2);
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(new String[]{"10", "20", "30"});
DataValidation dataValidation = new XSSFDataValidation(addressList, (CTDataValidation) dvConstraint);
dataValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(dataValidation);

Cependant, lorsque je fais ceci, j'ai une erreur, et voici ce qui est écrit :
Nom : 2018-04-17 13_11_26-excel - NetBeans IDE 8.2.png
Affichages : 1083
Taille : 5,3 Ko

et donc je ne peux pas exécuter le programme.

Si je laisse tel quel et que je ne modifie pas mon code par leur suggestion (première partie de cette réponse) alors je ne peux toujours pas compiler.

**joel.drigo** · 17/04/2018, 13h47

Un DVConstraint n'est pas castable en CTDataValidation. Utilise le XSSFDataValidationHelper, ça te simplifiera la création.

**tomlrt53** · 18/04/2018, 09h09

Mais je ne sais même pas comment utiliser ceci

J'ai accès a cette documentation mais aucun exemple et donc je ne comprends trop l’utilisation et surtout comment la mettre dans mon programme :

https://poi.apache.org/apidocs/org/a...geAddressList-

**joel.drigo** · 18/04/2018, 09h27

Il y'a un exemple pour le helper : https://poi.apache.org/spreadsheet/q...xlsx+format%29

[POI] Création d'une liste déroulante dans Excel

Documents Java

Discussions similaires

Partager

Partager