Comment créer un classeur excel de façon spécifique ?

Version imprimable

Salut à tous ?

J'ai un bouton qui permet de générer un classeur Excel (xls, xlsx) qui s'occupe uniquement de créer le classeur, et appel une méthodes pour son contenu.
La méthode du bouton de génération :

Code:

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
 
public boolean dataNEMO2Excel()
{
	String fileName = Events.getFile();
	StringBuilder builder = new StringBuilder(fileName);
	if (fileName != "")
	{
		try {
			int index = fileName.lastIndexOf(".xl");
			builder.insert(index,"_NEMO");
			fileName = builder.toString();
 
			System.out.println(fileName);
			FileOutputStream out = new FileOutputStream(fileName);
		        FileInputStream in = new FileInputStream(fileName);
			Workbook workbook = WorkbookFactory.create(in);
			utiMethods.addLog(fileName +" was created", infoMessage.info);
 
			//dataNEMO2Excel(UtilityMethods.getInstance().getMap_cable());
			//dataNEMO2Excel(UtilityMethods.getInstance().getMap_link());
			//dataNEMO2Excel(UtilityMethods.getInstance().getMap_networkList());
			//dataNEMO2Excel(UtilityMethods.getInstance().getMap_networkItem());
			dataNEMO2Excel(workbook, UtilityMethods.getInstance().getMap_port());
 
			workbook.write(out);
			in.close();
			out.close();
			workbook.close();
		} catch (EncryptedDocumentException e) {
			UtilityMethods.getStackTrace(e);
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			UtilityMethods.getStackTrace(e);
			e.printStackTrace();
		} catch (IOException e) {
			UtilityMethods.getStackTrace(e);
			e.printStackTrace();
		}		
		return true;
	}
	return false;
}

La méthode qui crée le contenu :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
public Sheet dataNEMO2Excel(Workbook workbook, MAP_Port map_port)
{
	Sheet sheet = null;
	Row row = null;
	String[] headers = new String[]{};
 
	for (String sheetName :mapHeaderNEMO.keySet() )
	{
		sheet = workbook.createSheet(sheetName);
		utiMethods.addLog(sheetName +" was created", infoMessage.info);
 
		headers = mapHeaderNEMO.get(sheetName);
 
	}      
       row = sheet.createRow(2);
 
       // Ajouter des données dans les cellules
       row.createCell(0).setCellValue("Java Execl");
       row.createCell(1).setCellValue("Mesexemple.com");
       row.createCell(2).setCellValue("Sakoba Adams");
 
	return sheet;
}

Ce code Crée un classeur sans feuille et donc avec un message d'erreur quand on l'ouvre disant que le format est incorrect, et dans la console :

Code:

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
 
HDMT_NEMO.xlsx
Exception in thread "AWT-EventQueue-0" org.apache.poi.EmptyFileException: The supplied file was empty (zero bytes long)
	at org.apache.poi.util.IOUtils.peekFirst8Bytes(IOUtils.java:55)
	at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:203)
	at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:170)
	at EVENT.UtilityMethods.dataNEMO2Excel(UtilityMethods.java:802)
	at EVENT.Events.btnGenerate_click(Events.java:162)
	at EVENT.Events.actionPerformed(Events.java:55)
	at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
	at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
	at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
	at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
	at javax.swing.plaf.basic.BasicButtonListener$Actions.actionPerformed(Unknown Source)
	at javax.swing.SwingUtilities.notifyAction(Unknown Source)
	at javax.swing.JComponent.processKeyBinding(Unknown Source)
	at javax.swing.KeyboardManager.fireBinding(Unknown Source)
	at javax.swing.KeyboardManager.fireKeyboardAction(Unknown Source)
	at javax.swing.JComponent.processKeyBindingsForAllComponents(Unknown Source)
	at javax.swing.JComponent.processKeyBindings(Unknown Source)
	at javax.swing.JComponent.processKeyEvent(Unknown Source)
	at java.awt.Component.processEvent(Unknown Source)
	at java.awt.Container.processEvent(Unknown Source)
	at java.awt.Component.dispatchEventImpl(Unknown Source)
	at java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.awt.Component.dispatchEvent(Unknown Source)
	at java.awt.KeyboardFocusManager.redispatchEvent(Unknown Source)
	at java.awt.DefaultKeyboardFocusManager.dispatchKeyEvent(Unknown Source)
	at java.awt.DefaultKeyboardFocusManager.preDispatchKeyEvent(Unknown Source)
	at java.awt.DefaultKeyboardFocusManager.typeAheadAssertions(Unknown Source)
	at java.awt.DefaultKeyboardFocusManager.dispatchEvent(Unknown Source)
	at java.awt.Component.dispatchEventImpl(Unknown Source)
	at java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.awt.Window.dispatchEventImpl(Unknown Source)
	at java.awt.Component.dispatchEvent(Unknown Source)
	at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
	at java.awt.EventQueue.access$000(Unknown Source)
	at java.awt.EventQueue$1.run(Unknown Source)
	at java.awt.EventQueue$1.run(Unknown Source)
	at java.security.AccessController.doPrivileged(Native Method)
	at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
	at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
	at java.awt.EventQueue$2.run(Unknown Source)
	at java.awt.EventQueue$2.run(Unknown Source)
	at java.security.AccessController.doPrivileged(Native Method)
	at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
	at java.awt.EventQueue.dispatchEvent(Unknown Source)
	at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.awt.EventDispatchThread.run(Unknown Source)

Merci d'avance :) :) :)

26/04/2016, 10h56
joel.drigo
Salut,

Tu ne dois pas créer ton classeur à partir d'un fichier, mais juste faire :

Code:

Workbook workbook = new XSSFWorkbook();

Le fichier n'interviendra que lors de la sauvegarde :
Code:

1 2 3 try( OutputStream out = Files.newInputStream(Paths.get("bidule.xlsx")) ) { wokkbook.write(out); }
26/04/2016, 14h35
nadong94

le truc c'est que je ne sais pas à l'avance s'il s’agit d'un xls ou xlsx donc je ne peux pas faire un new XSSFWorkbook()

Tu demandes à l'utilisateur le nom du fichier, tu extrais l'extension, ce qui te permet de connaitre le type. Et ensuite :

Code:

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
 
File file = jfilechooser.getSelectedFile();
 
if ( file!=null ) {
String extension = extratcExtension(file);
 
 
final WorkBook workbook;
if ( "xls".equalsIgnoreCase( extension) ) {
     workbook = new HSSFWorkBook();
}
else if ( "xlsx".equalsIgnoreCase( extension )) {
     workbook = new XSSFWorkBook();
}
else {
    throw new IllegalStateException("Type inconnu"); // ou faire ce que tu veux...
}
 
 
// là tu remplies ton classeur
 
 
// puis tu le sauvegardes...
 
try(InputStream....
}
 
}

Très bien MERCI :) :) :)

Voici le code final, ou en tout cas une partie :

Code:

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
 
public boolean dataNEMO2Excel()
{
	String fileName = Events.getFile();
	StringBuilder builder = new StringBuilder(fileName);
	if (fileName != "")
	{
		try {
			int index = fileName.lastIndexOf(".");
			String extension = builder.substring(index+1).toString();
			builder.insert(index,"_NEMO");
			fileName = builder.toString();
			File file = new File(fileName);
 
			final Workbook workbook;
			if ( "xls".equalsIgnoreCase(extension) )
			{
			     workbook = new HSSFWorkbook();
			}
			else if ( "xlsx".equalsIgnoreCase( extension ))
			{
			     workbook = new XSSFWorkbook();
			}
			else
			{
				throw new IllegalStateException("Unknow type");
			}
			// là tu remplies ton classeur
			FileOutputStream out = new FileOutputStream(fileName);
	        // puis tu le sauvegardes...
			//Workbook workbook = WorkbookFactory.create(file);
			utiMethods.addLog(fileName +" was created", infoMessage.info);
 
			for (String sheetName :mapHeaderNEMO.keySet() )
			{	
				if (sheetName.equalsIgnoreCase("Port"))
				{
					if (dataNEMO2Excel(workbook, sheetName, UtilityMethods.getInstance().getMap_port()))
						utiMethods.addLog(sheetName +" was created", infoMessage.info);
					else
						utiMethods.addLog("An error occured when creating the Excel sheet -> "+ sheetName +" ", infoMessage.error);
				}
				else if (sheetName.equalsIgnoreCase("Cable"))
				{
					//dataNEMO2Excel(UtilityMethods.getInstance().getMap_cable());
				}
				else if (sheetName.equalsIgnoreCase("Link"))
				{
					//dataNEMO2Excel(UtilityMethods.getInstance().getMap_link());
				}
				else if (sheetName.equalsIgnoreCase("Link"))
				{
				//dataNEMO2Excel(UtilityMethods.getInstance().getMap_networkList());
				}
				else if (sheetName.equalsIgnoreCase("Link"))
				{
				//dataNEMO2Excel(UtilityMethods.getInstance().getMap_networkItem());
				}
			}
		        workbook.write(out);
			workbook.close();
			out.flush();
			out.close();
		} catch (EncryptedDocumentException e) {
			UtilityMethods.getStackTrace(e);
			e.printStackTrace();
		/*} catch (InvalidFormatException e) {
			UtilityMethods.getStackTrace(e);
			e.printStackTrace();
		*/} catch (IOException e) {
			UtilityMethods.getStackTrace(e);
			e.printStackTrace();
		}
 
		return true;
	}
	return false;
}
 
public boolean dataNEMO2Excel(Workbook workbook, String sheetName, MAP_Port map_port)
{
	try {
		String[] headers = mapHeaderNEMO.get(sheetName);
		Sheet sheet = workbook.createSheet(sheetName);
		int rowNumber = 0;
		Row rowHeader = sheet.createRow(rowNumber++);
		int ind = 0;
		for (String header : headers)
			rowHeader.createCell(ind++).setCellValue(header);
 
		//ajouter les headers de colonnes
		for (N_Port port :map_port.values())
		{
			String[] values = port.getValues();
			Row row = sheet.createRow(rowNumber++);
			ind = 0;
			for (String val : values)
			{
				row.createCell(ind++).setCellValue(val);
			}
		}
		utiMethods.addLog(sheetName +" was created", infoMessage.info);
 
		return true;
	} catch (Exception e) {
		utiMethods.addLog("An error occured when creating the Excel sheet -> "+ sheetName +" ", infoMessage.error);
 
		return false;
	}
}

@++

26/04/2016, 17h29
joel.drigo

Créer au plus tard le FileOutputStream : toujours éviter de laisser des ressources ouvertes trop longtemps. Tu n'en a pas besoin avant de faire le write.

Le commentaire // là tu remplies ton classeur veut bien dire ce qu'il veut dire !
26/04/2016, 18h00
nadong94
Citation:

Envoyé par joel.drigo

Créer au plus tard le FileOutputStream : toujours éviter de laisser des ressources ouvertes trop longtemps. Tu n'en a pas besoin avant de faire le write.

Le commentaire // là tu remplies ton classeur veut bien dire ce qu'il veut dire !

Très bien ! merci.
Code:

1 2 3 4 FileOutputStream out = new FileOutputStream(fileName); workbook.write(out); workbook.close();