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

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 :

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.

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.

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.

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 :

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.

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.

À 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.

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])" });

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 :

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.