Export excel java

[MANTA..]

Bonjour, j'ai besoin de votre aide car je développe un site et je dois permettre à l'utilisateur d'exporter un grid vers excel. J'ai fini par trouvé un moyen de faire ca en créant un fichier csv dynamiquement. Mon probléme c'est que quand j'ouvre le fichier dans excel les accents sont remplacé par des caractères bizarre et en plus avant de pouvoir l'ouvrir il faut choisir le bon séparateur. Est-ce que vous avez une idée de comment je pourrai régler ces problemes? Voici le code que j'utilise pour l'instant (j'utilise GWT, smart gwt)(:

Creation du string à mettre dans le csv

private StringBuilder exportCSV(boolean all) {
    StringBuilder stringBuilder = new StringBuilder(); // csv data in here
    ListGridField[] fields = grid.getFields();
    for (int i = 0; i < fields.length; i++) {
        ListGridField listGridField = fields[i];
        stringBuilder.append("\"");
        stringBuilder.append(listGridField.getName());
        stringBuilder.append("\",");
    }
    stringBuilder.deleteCharAt(stringBuilder.length() - 1); // remove last
    stringBuilder.append("\n");
 
    ListGridRecord[] recs = null;
    if (all) {
        recs = grid.getRecords();
    }
 
    //      else {
    //          recs = grid.getSelection();
    //      }
    for (int i = 0; i < recs.length; i++) {
        ListGridRecord listGridRecord = recs[i];
        ListGridField[] listGridFields = grid.getFields();
        for (int j = 0; j < listGridFields.length; j++) {
            ListGridField listGridField = listGridFields[j];
            stringBuilder.append("\"");
            stringBuilder.append(listGridRecord.getAttribute(listGridField
                    .getName()));
            stringBuilder.append("\",");
        }
        stringBuilder.deleteCharAt(stringBuilder.length() - 1); // remove last ","
        stringBuilder.append("\n");
    }
    return stringBuilder;
}

Ecriture

public String greetServer(String input) throws IllegalArgumentException {
    FileWriter fw;
    String url = "";
    try {
        fw = new FileWriter("Result.xls");
        PrintWriter pw = new PrintWriter(fw);
        // Write to file for the first row
        url = "http://127.0.0.1:8888/Result.xls";
        pw.print(input);
        pw.flush();
        // Close the Print Writer
        pw.close();
        // Close the File Writer
        fw.close();
 
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return url;
 
}

Après avoir recu l'url on propose le telechargement

serverResponseLabel.removeStyleName("serverResponseLabelError");
                serverResponseLabel.setHTML(result);
 
Window.open(result, "_blank", "");

Si vous avez une autre méthode pour faire ca n'hesitez pas

Merci d'avance
-----

[MANTA..]

Finalement j'ai fini par trouvé une meilleur solution, coté client je met le nom des colonnes dans un String en séparant les nom par des "-", je fais pareil pour le contenu. Ensuite je recupere chaque valeur grace à un StringTokenizer. Pour savoir quand crée une nouvelle ligne il suffit de savoir le nombre de colonne qu'il y a dans le grid(ici pour mon test j'en ai mis 2). Voici le code qui me permet de remplir les string et de les envoyer au servlet (c'est le formPanel.submit(); qui envoie le tous au servlet que j'appel en ajoutant /userDataRequest à l'url):

button.addClickHandler(new ClickHandler() {

			@Override
			public void onClick(ClickEvent event) {
				StringBuilder colonnesstringBuilder = new StringBuilder(); // csv data in here
				StringBuilder contenustringBuilder = new StringBuilder(); // csv data in here


				String test;
				ListGridField[] fields = grid.getFields();
				for (int i = 0; i < fields.length; i++) {
					ListGridField listGridField = fields[i];

					if(i!=0) {
						colonnesstringBuilder.append("-");
					}
					colonnesstringBuilder.append(listGridField.getTitle());

				}


				ListGridRecord[] recs = null;
				recs = grid.getRecords();
				for (int i = 0; i < recs.length; i++) {
					ListGridRecord listGridRecord = recs[i];
					ListGridField[] listGridFields = grid.getFields();
					for (int j = 0; j < listGridFields.length; j++) {

						ListGridField listGridField = listGridFields[j];
						contenustringBuilder.append("-");
						contenustringBuilder.append(listGridRecord.getAttribute(listGridField
								.getName()));

					}

				}

				contenustringBuilder.deleteCharAt(0);

				final FormPanel formPanel = new FormPanel();
				formPanel.setAction("/userDataRequest");
				formPanel.setMethod(FormPanel.METHOD_POST);

				VerticalPanel verticalPanel = new VerticalPanel();

				Hidden data = new Hidden();
				data.setName("colonnes");
				data.setValue(colonnesstringBuilder.toString());
				verticalPanel.add(data);

				Hidden exportType = new Hidden();
				exportType.setName("contenu");
				exportType.setValue(contenustringBuilder.toString());
				verticalPanel.add(exportType);

				Hidden fileType = new Hidden();
				fileType.setName("fileType");
				fileType.setValue("valueee");
				verticalPanel.add(fileType);

				formPanel.add(verticalPanel);
				formPanel.setVisible(false);
				RootPanel.get().add(formPanel);   
				formPanel.submit();


			}
		});

Voici le code de mon servlet (l'api que j'utilise est celle donnée ici dans le premier message --> http://www.presence-pc.com/forum/ppc...ujet-251-1.htm)

package com.test.client;

import java.io.IOException;
import java.io.OutputStream;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import com.ibm.icu.util.StringTokenizer;

public class GenerateExcelServlet extends HttpServlet {


	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	public GenerateExcelServlet() {
		super();
		// TODO Auto-generated constructor stub
	}

	protected void processRequest(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		String theReportFile = "Result requeteur.xls";

		response.setContentType ("application/octet-stream");
		response.setHeader ("Content-Disposition", "attachment; filename=\""+theReportFile+"\"");

		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("new sheet");

		int nbRow = 0;
		int j = 0;
		int i = 0;
		HSSFRow row = sheet.createRow(nbRow);
		HSSFCell cell = null;


		HSSFCellStyle st1 = wb.createCellStyle();
		HSSFFont f1 = wb.createFont();
		f1.setItalic(true);
		f1.setColor((short) HSSFColor.DARK_BLUE.index);
		f1.setFontHeightInPoints((short)12);
		f1.setBoldweight(f1.BOLDWEIGHT_BOLD);

		st1.setFont(f1);
		st1.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);


		//		HSSFCell cell = row.createCell(0);
		//
		//		cell.setCellValue(1);

		String colonnes = request.getParameter("colonnes");
		String contenu = request.getParameter("contenu");

		StringTokenizer  colonnesStringTokenizer = new StringTokenizer(colonnes, "-");
		StringTokenizer  fieldsStringTokenizer = new StringTokenizer(contenu, "-");


		while(colonnesStringTokenizer.hasMoreTokens()) {
			cell = row.createCell(i);

			cell.setCellStyle(st1);

			cell.setCellValue(colonnesStringTokenizer.nextToken());

			//			row.createCell(i).setCellValue(colonnesStringTokenizer.nextToken());
			sheet.autoSizeColumn(i, true);
			i++;
			cell = null;
		}

		nbRow = nbRow+1;
		row = sheet.createRow(nbRow);

		while(fieldsStringTokenizer.hasMoreTokens()) {
			if(j==2) {
				j=0;
				nbRow = nbRow+1;
				row = sheet.createRow(nbRow);
			}
			row.createCell(j).setCellValue(fieldsStringTokenizer.nextToken());
			j=j+1;
		}


		//		// Or do it on one line.
		//		row.createCell(1).setCellValue(1.2);
		//		row.createCell(2).setCellValue("This is a string");
		//		row.createCell(3).setCellValue(true);
		//		
		//		// Create a row and put some cells in it. Rows are 0 based.
		//		row = sheet.createRow(1);
		//
		//		// Or do it on one line.
		//		row.createCell(1).setCellValue(1.2);
		//		row.createCell(2).setCellValue("This is a string");
		//		row.createCell(3).setCellValue(true);

		// Write the output
		OutputStream out = response.getOutputStream();
		wb.write(out);
		out.close();


	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub

		processRequest(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// TODO Auto-generated method stub

		processRequest(request, response);
	}



}

[bisou10]

Bonjour MANTA,

Pour l'export vers Excel en JAVA, je te conseille d'utiliser Apache POI, plutot que du CSV.
C'est un ensemble de librairies qui te permet de travailler au niveau de la cellule, de son formatage, voire d'utiliser des templates.

Bonne chance !

[MANTA..]

Bonjour, merci pour ta réponse. Oui dans la deuxiéme solution que j'ai donné j'utilise cette POI. Elle est vraiment bien!!!

J'en profite pour poster la derniere version de mon code, j'utilise '||' pour séparer les valeurs et je recupere le nombre puis je l'envoie.

IButton exportToExcelButton = new IButton(project.res.msgExportToExcel());
		exportToExcelButton.addClickHandler(new ClickHandler() {

			@Override
			public void onClick(ClickEvent event) {
				//				sendTOServer(exportCSV(true));

				ListGridRecord[] recs = null;
				recs = grid.getRecords();
				if(recs.length!=0) {
					StringBuilder colonnesstringBuilder = new StringBuilder(); // csv data in here
					StringBuilder contenustringBuilder = new StringBuilder(); // csv data in here

					int nbColonnes = 0;

					ListGridField[] fields = grid.getFields();
					for (int i = 0; i < fields.length; i++) {
						ListGridField listGridField = fields[i];

						if(i!=0) {
							colonnesstringBuilder.append("||");
						}
						colonnesstringBuilder.append(listGridField.getTitle());

						nbColonnes++;

					}



					for (int i = 0; i < recs.length; i++) {
						ListGridRecord listGridRecord = recs[i];
						ListGridField[] listGridFields = grid.getFields();
						for (int j = 0; j < listGridFields.length; j++) {

							ListGridField listGridField = listGridFields[j];
							contenustringBuilder.append("||");

							String value;
							try {
								if(listGridField.getType().equals(ListGridFieldType.DATE)) {
									Date date = listGridRecord.getAttributeAsDate(listGridField.getName());

									if(date!=null) {
										String newDate = gridDateFormatter.format(date);
										if(newDate==null||newDate.contains("null")) {
											contenustringBuilder.append(" ");
										}
										else {
											contenustringBuilder.append(newDate);
										}

									}

									else {
										contenustringBuilder.append(" ");
									}
								}
								else {
									value = listGridRecord.getAttribute(listGridField.getName());
									if(value==null||value.equals("null")) {
										contenustringBuilder.append(" ");
									}
									else {
										contenustringBuilder.append(listGridRecord.getAttribute(listGridField
												.getName()));
									}
								}
							} catch (Exception e) {
								value = listGridRecord.getAttribute(listGridField.getName());
								if(value==null||value.equals("null")) {
									contenustringBuilder.append(" ");
								}
								else {
									contenustringBuilder.append(listGridRecord.getAttribute(listGridField
											.getName()));
								}
							}


						}

					}

					contenustringBuilder.deleteCharAt(0);

					final FormPanel formPanel = new FormPanel();
					formPanel.setAction("/export");
					FormElement.as(formPanel.getElement()).setAcceptCharset("UTF-8");
					formPanel.setEncoding("UTF-8");
					formPanel.setMethod(FormPanel.METHOD_POST);

					VerticalPanel verticalPanel = new VerticalPanel();

					Hidden colonnesParam = new Hidden();
					colonnesParam.setName("colonnes");
					colonnesParam.setValue(colonnesstringBuilder.toString());
					verticalPanel.add(colonnesParam);

					Hidden valuesParam = new Hidden();
					valuesParam.setName("contenu");
					valuesParam.setValue(contenustringBuilder.toString());
					verticalPanel.add(valuesParam);

					Hidden nbColonnesParam = new Hidden();
					nbColonnesParam.setName("nbColonnes");
					Integer nbColonnesInteger = new Integer(nbColonnes);
					nbColonnesParam.setValue(nbColonnesInteger.toString());
					verticalPanel.add(nbColonnesParam);

					formPanel.add(verticalPanel);
					formPanel.setVisible(false);
					RootPanel.get().add(formPanel);   
					formPanel.submit();
				}
				else {
					Window.alert(project.res.errorNoDataToExport());
				}

			}
		});

Côté servlet j'ai personnalisé un peu le style des colonnes

import java.io.IOException;
import java.io.OutputStream;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import com.ibm.icu.util.StringTokenizer;

public class ExportExcelServlet extends HttpServlet {


	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	public ExportExcelServlet() {
		super();
		// TODO Auto-generated constructor stub
	}

	protected void processRequest(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		String theReportFile = "Result requeteur.xls";

		response.setContentType ("application/octet-stream");
		response.setCharacterEncoding("UTF-8");
		response.setHeader ("Content-Disposition", "attachment; filename=\""+theReportFile+"\"");
		response.setContentType("text/html;charset=UTF-8");
		

		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("new sheet");
		int nbRow = 0;
		int j = 0;
		int i = 0;
		HSSFRow row = sheet.createRow(nbRow);
		HSSFCell cell = null;


		HSSFCellStyle st1 = wb.createCellStyle();
		HSSFFont f1 = wb.createFont();
		f1.setItalic(true);
		f1.setColor((short) HSSFColor.DARK_BLUE.index);
		f1.setFontHeightInPoints((short)12);
		f1.setBoldweight(f1.BOLDWEIGHT_BOLD);

		st1.setFont(f1);
		st1.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);

		//		HSSFCell cell = row.createCell(0);
		//
		//		cell.setCellValue(1);

		String colonnes = request.getParameter("colonnes");
		String contenu = request.getParameter("contenu");
		int nbcolonnes = Integer.parseInt(request.getParameter("nbColonnes"));

		StringTokenizer  colonnesStringTokenizer = new StringTokenizer(colonnes, "||");
		StringTokenizer  fieldsStringTokenizer = new StringTokenizer(contenu, "||");


		while(colonnesStringTokenizer.hasMoreTokens()) {
			cell = row.createCell(i);

			cell.setCellStyle(st1);

			cell.setCellValue(colonnesStringTokenizer.nextToken());

			//			row.createCell(i).setCellValue(colonnesStringTokenizer.nextToken());
			i++;
			cell = null;
		}

		nbRow = nbRow+1;
		row = sheet.createRow(nbRow);

		while(fieldsStringTokenizer.hasMoreTokens()) {
			if(j==nbcolonnes) {
				j=0;
				nbRow = nbRow+1;
				row = sheet.createRow(nbRow);
			}
			row.createCell(j).setCellValue(fieldsStringTokenizer.nextToken());
			j=j+1;
		}

		for(int m=0; m<nbcolonnes;m++) {
			sheet.autoSizeColumn(m, true);
		}

		//		// Or do it on one line.
		//		row.createCell(1).setCellValue(1.2);
		//		row.createCell(2).setCellValue("This is a string");
		//		row.createCell(3).setCellValue(true);
		//		
		//		// Create a row and put some cells in it. Rows are 0 based.
		//		row = sheet.createRow(1);
		//
		//		// Or do it on one line.
		//		row.createCell(1).setCellValue(1.2);
		//		row.createCell(2).setCellValue("This is a string");
		//		row.createCell(3).setCellValue(true);

		// Write the output
		OutputStream out = response.getOutputStream();
		wb.write(out);
		out.close();


	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub

		processRequest(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// TODO Auto-generated method stub

		processRequest(request, response);
	}

[A voir en vidéo sur Futura]

[bisou10]

Super !
Comme je dis plus haut, j'utilise un fichier template.xls et je ne remplis que les cellules que je souhaite, ca m'evite de faire ce que tu fais: suer sang et eau sur la définition précise des autres éléments du XLS ( lignes, colonnes, formats de ces derniers etc...)

Je me focalise donc sur la cellule et son formatage. Ce n'est pas applicable tout le temps, mais si tu tombes dans ce cas de figure, songes-y tu gagneras du temps