Comment générer un fichier Excel en ASP.NET

Philippe Vialatte

Ce bref tutoriel va vous montrer comment générer à peu de frais un fichier lisible par Excel, depuis une machine ne disposant pas d'un Excel installé.

N'hésitez pas à laisser votre avis sur le contenu de l'article via le forum : Commentez ♪

- Titre : Comment générer un fichier Excel en ASP.NET

- Auteur : Philippe Vialatte

- Parution : 23 novembre 2009

- Mise à jour : 2 décembre 2009

- Licence :

Sommaire

- I. Introduction

- II. Génération d'un fichier csv

- II-A. Principe

- II-B. Implémentation

- II-C. Limitations

- III. Export d'un tableau HTML

- III-A. Principe

- III-B. Implémentation

- III-C. Limitations

- IV. Génération d'un fichier au format Excel XML

- IV-A. Principe

- IV-B. Implémentation

- IV-C. Limitations

- V. Conclusion

- VI. Remerciements

I - Introduction

Un problème récurrent en développement web, que ce soit dans les projets ou dans les forums, est celui de la génération des documents Excel à partir d'une application web.

En effet, si, dans le cadre du développement d'application Windows, il est souvent logique de se baser sur de l'automation (voir cet article pour plus d'informations), dés que l'on se place dans le cas du développement web, l'utilisation de l'automation devient problématique, voire impossible.

Plutôt que de se concentrer sur les problèmes engendrés par, l'utilisation d'automation dans un environnement serveur (qui est expliqué en détail dans le lien suivant sur la KB Microsoft), cet article va aborder trois manières simples (et gratuites) de générer depuis le serveur un fichier qui pourra être lu par Excel.

II - Génération d'un fichier csv

II-A - Principe

CSV, pour Comma Separated Values est un format informatique ouvert représentant des données tabulaires sous forme de valeurs séparées par des virgules.

Le principe est simple, c'est un fichier texte dont chaque ligne représente un ensemble de données, chaque « colonne » étant séparée par une virgule. La première ligne représente généralement le titre des colonnes. Ainsi, le fichier suivant :

Commercial, Janvier, Février, Mars, Avril
Jean Dupont, 5, 8, 6, 10
Victor Hugo, 54, 26, 34, 75

Représente les mêmes données que le fichier Excel suivant :

La norme csv est légèrement réinterprétée par Excel. En effet, si, avec un Excel anglais, le texte ci-dessus est bien interprété, avec un Excel français, il faudra remplacer les virgules par des points-virgules…

Ceci se produit parce que le séparateur de liste par défaut est la virgule dans les environnements anglais, et le point-virgule en français.

Pour changer (ou vérifier) ces valeurs, il faut se rendre dans les options régionales et linguistiques, puis dans « Personnaliser ce format », et travailler sur la valeur « Séparateur de listes »

II-B - Implémentation

L'implémentation de ce fichier csv est très simple.

Il suffit en fait de renvoyer, en réponse à la requête, un fichier texte, auquel on va donner l'extension csv, et le type de contenu text/csv

HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=resultat.csv");
HttpContext.Current.Response.ContentType = "text/csv";

string contenu = "Commercial, Janvier, Février, Mars, Avril";
contenu += Environment.NewLine;
contenu += "Jean Dupont, 5, 8, 6, 10";
contenu += Environment.NewLine;
contenu += "Victor Hugo, 54, 26, 34, 75";
contenu += Environment.NewLine;

HttpContext.Current.Response.Write(contenu);
HttpContext.Current.Response.End();

On va retrouver sensiblement le même code tout au long du tutoriel, en ce qui concerne l'interaction avec la réponse. Ce code va effacer les entêtes existants, effacer le contenu de la réponse, puis indiquer que la réponse va être au format texte/csv.

On va ensuite écrire le contenu de notre fichier, et terminer la réponse.

Il est aussi possible de générer, pour un résultat à peu près équivalent, le fichier Excel à partir d'une connexion ODBC, en traitant le fichier Excel comme une base de données, puis en créant une « table » correspondant aux colonnes de notre fichier Excel, et en faisant autant d'INSERT que nécessaire pour ajouter les données.

II-C - Limitations

Le fichier généré sera effectivement lu par Excel, et converti sous forme tabulaire. Cela dit, le résultat n'est pas particulièrement enthousiasmant.

De plus, les limitations de CSV sont nombreuses, il est souvent nécessaire de « tricher » en forçant tout le texte généré à être identifié comme texte, en encadrant le contenu par des guillemets.

On va voir une solution pour rendre le résultat plus sexy, et plus stable.

III - Export d'un tableau HTML

III-A - Principe

Le but est relativement simple. Il s'agit simplement de générer un tableau au format HTML que l'on va renvoyer sur la réponse, exactement de la même façon que pour le fichier csv précédent.

La différence est qu'Excel sachant gérer le format HTML, le formatage des cellules, des lignes et du texte sera conservé.

Toujours avec notre exemple précédent de liste de personnes, on va désormais ajouter un entête de couleur bleu foncé, avec un texte en gras, des lignes de séparation noires, et on va aligner la colonne âge à droite.

III-B - Implémentation

Le code que l'on va utiliser pour générer notre tableau sera le suivant (on va omettre la partie de manipulation de la réponse).

Table tbl = new Table() { BorderWidth = new Unit(1) };
TableHeaderRow th = new TableHeaderRow() { BackColor = Color.DarkBlue };
th.Font.Bold = true;
th.Cells.Add(new TableCell() { Text = "Ventes par mois", ColumnSpan = 5, HorizontalAlign = HorizontalAlign.Center });
tbl.Rows.Add(th);

th = new TableHeaderRow() { BackColor = Color.DarkBlue, ForeColor = Color.White  };
th.Font.Bold = true;

th.Cells.Add(new TableCell() { Text = "Commercial" });
th.Cells.Add(new TableCell() { Text = "Janvier" });
th.Cells.Add(new TableCell() { Text = "Février" });
th.Cells.Add(new TableCell() { Text = "Mars" });
th.Cells.Add(new TableCell() { Text = "Avril" });
tbl.Rows.Add(th);

var tr = new TableRow();
tr.Cells.Add(new TableCell() { Text = "Jean Dupont" });
tr.Cells.Add(new TableCell() { Text = "5", HorizontalAlign = HorizontalAlign.Right });
tr.Cells.Add(new TableCell() { Text = "8", HorizontalAlign = HorizontalAlign.Right });
tr.Cells.Add(new TableCell() { Text = "6", HorizontalAlign = HorizontalAlign.Right });
tr.Cells.Add(new TableCell() { Text = "10", HorizontalAlign = HorizontalAlign.Right });
tbl.Rows.Add(tr);

tr = new TableRow();
tr.Cells.Add(new TableCell() { Text = "Victor Hugo" });
tr.Cells.Add(new TableCell() { Text = "54", HorizontalAlign = HorizontalAlign.Right });
tr.Cells.Add(new TableCell() { Text = "26", HorizontalAlign = HorizontalAlign.Right });
tr.Cells.Add(new TableCell() { Text = "34", HorizontalAlign = HorizontalAlign.Right });
tr.Cells.Add(new TableCell() { Text = "75", HorizontalAlign = HorizontalAlign.Right });
tbl.Rows.Add(tr);

Il nous reste ensuite à transformer ce composant en une chaîne de caractères pour la renvoyer sur la sortie standard. Pour cela, on va utiliser la méthode RenderControl du contrôle table.

StringBuilder stb = new StringBuilder();
StringWriter sw = new StringWriter(stb);
HtmlTextWriter textWriter = new HtmlTextWriter(sw);

tbl.RenderControl(textWriter);
Response.Write(stb.ToString());

Ce morceau de code va instancier une nouvelle instance de la classe StringWriter, qui va écrire dans le StringBuilder spécifié. On va ensuite passer ce StringWriter à un HtmlTextWriter, de façon à ce que lorsque du texte est envoyé dans le HtmlTextWriter, par la méthode RenderControl de la table, il soit redirigé, par l'intermédiaire du StringWriter, à notre Stringbuilder…Ouf !

Le résultat est déjà plus sympathique :

Pourquoi ne pas avoir utilisé directement du code HTML ?

Il est évidemment possible de renvoyer directement du code HTML, mais il est aussi intéressant de voir ce que l'on peut faire sans avoir à sortir du C#. De plus, ce style de code permet de remplacer la table par une GridView sans modifier beaucoup de code, ce qui est, en général, demandé dans de nombreux projets.

III-C - Limitations

Le fichier généré ne peut pas comporter de formules ni de cellules calculées. De plus, pour que le formatage soit pris en compte, les informations de style doivent être envoyées en même temps que les informations contenues dans les cellules. Par conséquent, si vous utilisez une CSS, il faut rendre la CSS au bon endroit dans la réponse pour que la mise en forme soit prise en compte.

IV - Génération d'un fichier au format Excel XML

IV-A - Principe

Depuis la sortie d'Office XP, un ensemble de nouveaux formats, basés sur XML, est apparu.

Ces formats, nommés Microsoft Office XML (attention, à ne pas confondre avec Open XML) comprennent le format nommé SpreadSheetML, qui permet de gérer des fichiers lisibles par Excel.

Je ne parle pas ici du format OpenXML, mais d'un format intermédiaire qui le précède.

À quoi va nous servir ce format ? C'est simple, il permet de gérer une fonctionnalité d'Excel qui nous intéresse fortement, à savoir les formules. On va ajouter à notre feuille Excel une ligne de totaux trimestriels, ainsi qu'une colonne de total par utilisateur, et une cellule de total absolu.

Pour ce faire, plutôt que d'écrire un fichier XML à la main, on va utiliser la bibliothèque ExcelXMLWriter, développée par CarlosAG, ou Carlos Aguilar Mares, qui, en plus de travailler dans l'équipe responsable d'IIS, fournit, sur son site web, de nombreux utilitaires.

IV-B - Implémentation

Pour générer notre feuille Excel, on va retrouver un mode de fonctionnement un peu identique à celui de l'automation Excel. Comme la manipulation de cette bibliothèque est moins triviale d'utilisation que la génération du fichier csv ou du fichier HTML, on va la détailler un peu plus.

Dans un premier temps, on va commencer par créer un nouveau classeur Excel, dans lequel on va créer un style pour mettre en forme notre entête.

Workbook book = new Workbook();

WorksheetStyle style = book.Styles.Add("Entete");
style.Font.Bold = true;
style.Font.Color = "White";
style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
style.Interior.Color = "Blue";
style.Interior.Pattern = StyleInteriorPattern.Solid;

On va ensuite ajouter une nouvelle feuille dans le classeur, y ajouter une ligne, et lui indiquer de s'étendre sur quatre colonnes supplémentaires, puis ajouter une seconde ligne avec nos colonnes.

Worksheet sheet = book.Worksheets.Add("Exemple");
WorksheetRow row = sheet.Table.Rows.Add();
row.Cells.Add(new WorksheetCell("Ventes par mois", DataType.String, "Entete") { MergeAcross = 5 });

row = sheet.Table.Rows.Add();
row.Cells.Add(new WorksheetCell("Commercial", DataType.String, "Entete"));
row.Cells.Add(new WorksheetCell("Janvier", DataType.String, "Entete"));
row.Cells.Add(new WorksheetCell("Février", DataType.String, "Entete"));
row.Cells.Add(new WorksheetCell("Mars", DataType.String, "Entete"));
row.Cells.Add(new WorksheetCell("Avril", DataType.String, "Entete"));
row.Cells.Add(new WorksheetCell("Total", DataType.String, "Entete"));

On va, enfin, ajouter le contenu de notre premier utilisateur, et terminer par une cellule contenant une formule

row = sheet.Table.Rows.Add();
row.Cells.Add(new WorksheetCell("Jean Dupont"));
row.Cells.Add(new WorksheetCell("5", DataType.Number));
row.Cells.Add(new WorksheetCell("8", DataType.Number));
row.Cells.Add(new WorksheetCell("6", DataType.Number));
row.Cells.Add(new WorksheetCell("10", DataType.Number));
row.Cells.Add(new WorksheetCell() { Formula = "=SUM(RC[-4]:RC[-1])" });

Notez que la formule est au format interne à Excel, avec la zone sélectionnée exprimée en relatif par rapport à la cellule en cours.

On va passer sur notre second commercial, et voir comment on gère la ligne de total. Les quatre premiers Totaux font la somme des deux nombres se situant au-dessus, tandis que le cinquième fait la somme des quatre colonnes se situant à sa gauche.

row.Cells.Add(new WorksheetCell("Total", DataType.String, "Entete"));
row.Cells.Add(new WorksheetCell() { Formula = "=SUM(R[-2]C:R[-1]C)", StyleID = "Entete" });
row.Cells.Add(new WorksheetCell() { Formula = "=SUM(R[-2]C:R[-1]C)", StyleID = "Entete" });
row.Cells.Add(new WorksheetCell() { Formula = "=SUM(R[-2]C:R[-1]C)", StyleID = "Entete" });
row.Cells.Add(new WorksheetCell() { Formula = "=SUM(R[-2]C:R[-1]C)", StyleID = "Entete" });
row.Cells.Add(new WorksheetCell() { Formula = "=SUM(RC[-4]:RC[-1])", StyleID = "Entete" });

Le résultat final se présente ainsi :

IV-C - Limitations

La principale limitation de ce format est qu'il ne permette pas de faire de graphes, ou de gérer du code VBA.

Cette limitation ne va pas forcément nous impacter, et, personnellement, j'utilise cette bibliothèque dans la plupart de mes développements.

V - Conclusion

J'espère que ce petit article vous permettra de vous tourner vers des solutions plus performantes (et moins risquées) de génération de documents Excel. À l'heure qu'il est, la génération des graphiques dans les documents Excel n'est supportée par aucune bibliothèque gratuite ou Open Source.

Les bibliothèques suivantes permettent, elles aussi, de générer des fichiers Excel :

- NPOI est un portage en .NET de la bibliothèque java POI. On peut la trouver à l'adresse suivante : http://npoi.codeplex.com/. Pour le moment, elle ne gère que le format Excel 2003, mais gère aussi bien la création que la lecture et la modification de ce format ;

- ExcelPackage est une bibliothèque permettant de générer des classeurs Excel au format XLSX. Cette bibliothèque est disponible à l'adresse suivante : http://excelpackage.codeplex.com/. Elle semble cependant être désormais inactive.

À l'heure actuelle, le seul moyen fiable de générer des fichiers Excel ayant toutes les fonctionnalités d'un fichier créé par Excel est d'utiliser une bibliothèque payante telle que celles proposées par Aspose ou IndependentSoft.

Télécharger le code des exemples.

VI - Remerciements

Merci à toute l'équipe de rédaction .Net, et en particulier à Greybird et à jacques_jean pour leurs corrections.