Génération de document Excel Open XML à partir d’un « Model »
Paru le 12 mars 2006
Par Pierre Lagarde, Microsoft France

Sur cette page

Introduction Introduction
Détail d’un fichier Excel exemple Détail d’un fichier Excel exemple
Création du Template dans Excel Création du Template dans Excel
Ajout du document Excel dans le projet Visual Studio en tant que ressource Ajout du document Excel dans le projet Visual Studio en tant que ressource
Extraction du model et manipulation pour générer le document cible Excel Extraction du model et manipulation pour générer le document cible Excel
Insérer une formule Insérer une formule
Conclusion Conclusion

Introduction

La création de document Office sur le serveur est maintenant beaucoup plus facile grâce au nouveau format de fichier Open XML que propose Office 2007. Ce format n’est, en effet, qu’un fichier ZIP contenant des fichiers XML, en opposition avec l’ancien format binaire que représentait un fichier .doc, .xls ou .ppt.

Ainsi, il est maintenant possible de créer ou de manipuler des documents Office sur un serveur, sans avoir à y installer Office.

C’est dans ce contexte qu’on va tenter de réaliser une application console à partir d’un document Excel existant et dans lequel viendront s’insérer du texte, des valeurs numériques ou même des formules, tout ça en ne manipulant que du XML.

Cet article va détailler comment on peut créer un document Excel, non pas à partir de rien mais d’un model préalablement créé dans Excel. Ce model va être embarqué dans l’application et sera manipulé en tant que ressource de l’application, pour ne pas avoir à tout générer à chaque fois et donc simplifier sa génération.

Pour simplifier la manipulation de document Open XML, on utilisera l’API du Framework 3.0 « System.IO.Package ».

Pour pouvoir importer ce package dans une solution Visual Studio, il faut installer le Framework 3.0 son SDK et l’Add-on à Visual Studio.

Détail d’un fichier Excel exemple

Un document Open XML Excel contient cette arborescence :

Arborescence d'un fichier Excel

Le répertoire « worksheets » contient un fichier XML par feuille de calcul Excel. Ces fichiers XML respectent le format SpreadSheetML.
Schéma disponible ici : http://www.ecma-international.org/news/TC45_current_work/TC45-2006-50_final_draft.htm

Création du Template dans Excel

La première étape consiste à créer un document Excel qui nous servira de model dans notre application et qu’on ajoutera comme ressource dans le projet Visual Studio.

Ouvrir Excel et créer un document avec un titre qui prend 3 colonnes, par exemple.

Exemple

PS : Le texte saisi en « [] » est juste là reconnaitre le texte à remplacer.

Ajout du document Excel dans le projet Visual Studio en tant que ressource

Pour ajouter une ressource à un projet, il faut aller dans les propriétés du projet et ajouter la ressource.

Ajouter la ressource

Ici, on ajoutera une ressource existante qui sera le document Excel qu’on vient de créer.

Un répertoire « Resources » est créé dans la solution Visual Studio contenant le fichier Excel.

Repertoire Resources

Extraction du model et manipulation pour générer le document cible Excel

Ce qui reste à faire :

  • Extraire le document des ressources.

  • Ouvrir les packages pour éditer le fichier Excel.

  • Manipuler les documents XML pour mettre à jour le model en vue du document cible.

  • Générer le document final sous forme de fichier physique.

Extraire le document des ressources

On va ouvrir le package à partir des ressources de l’application.

  
		using (PackageHelper package = 
			new PackageHelper(
                    Properties.Resources.ExcelSample))
		{…}

Ouvrir les packages pour éditer le fichier Excel

Puis charger le XML (SpreadSheetML) depuis le package.

		    Uri sheet1Uri = 
                 new Uri(@"/xl/worksheets/sheet1.xml",UriKind.Relative);
            XmlDocument sheet1Xml = 
                 package.GetWritablePart(sheet1Uri);

Une fois qu’on a accès au SpeadSheetML, la manipulation n’est pas aussi simple que pour le WordProcessiongML. Il y a plusieurs fichiers à manipuler en même temps.

Manipuler les documents XML

Regardons dans le détail le contenu du Package du Template Excel :

contenu du Package du Template Excel

Tout ce qui est numérique est stocké dans les fichiers « sheet*.xml » mais le texte (dans notre Template « [Titre] »), quant à lui, est stocké dans le fichier « sharedStrings.xml ».

Qui a cette forme :

sharedStrings.xml

Et donc le fichier « sheet1.xml » y fait référence de cette façon : <v>0</v>

sheet1.xml

Voilà le document cible :

Document cible

Il faut donc :

  • Remplacer [Titre] par « Rapport Activité »

  • Ajouter Janvier sur la ligne 2

  • Ajouter 123 et 234 sur la ligne 2, colonnes B et C

Remplacer [Titre] par « Rapport Activité »

On va commencer par lire le fichier « sharedStrings.xml » et charger son contenu dans un Dictionnaire<string,int>.

Uri sharedStringsUri =
    new Uri("/xl/sharedStrings.xml", UriKind.Relative);

// Chargement du XML

XmlDocument sharedStringsXml =
    package.GetWritablePart(sharedStringsUri);

 XmlElement sharedStringsElt = 
    sharedStringsXml.DocumentElement;

// Parcourt de chaînes de caractère
XmlNodeList shareStrings = sharedStringsElt.SelectNodes("/x:sst/x:si/x:t",
Namespaces.NamespaceManager);
// Ajout dans le dictionnaire
foreach (XmlNode node in shareStrings)
{
    m_sharedStrings.Add(node.InnerText, m_sharedStrings.Count);
}

Il faut maintenant remplacer « [Titre] » dans le dictionnaire. Le fichier « shareStrings.xml » sera mis à jour à partir du dictionnaire, juste avant la création du fichier physique.

int i = m_sharedStrings[oldText];
m_sharedStrings.Remove(oldText);
m_sharedStrings.Add(text, i);

Ajouter « Janvier » sur la ligne 2

Il faut modifier le fichier « sheet1.xml » pour lui ajouter ce nœud : (où id est l’item dans le dictionnaire de chaîne de caractères).

<c    r="A1"   s="1"   t="s">
	<v>id</v>
</c>

Et donc ajouter « Janvier » au dictionnaire.

// Récupérer l’index s’il existe déjà 
if (m_sharedStrings.ContainsKey(text))
return m_sharedStrings[text];

// Sinon ajouter une valeur dans le dictionnaire
int newIndex = m_sharedStrings.Count;
m_sharedStrings.Add(text, newIndex);
return newIndex;

Ajouter 123 et 234 sur la ligne 2, colonne B et C

Pour ajouter des valeurs numériques, c’est plus simple : on peut directement écrire dans le fichier « sheet1.xml ».

// Créer un nœud <x:c r="A1">
writer.WriteStartElement(Prefixes.SpreadsheetML, 
  "c", Namespaces.SpreadsheetML);
writer.WriteAttributeString("r", column + row.ToString());
// Ecrire la valeur numérique dans un nœud  <x:v>732.00</x:v>
writer.WriteElementString(Prefixes.SpreadsheetML, "v",
    Namespaces.SpreadsheetML, value.ToString("#0.00"));
// Fermeture </x:c>
writer.WriteEndElement();

Générer le document final sous forme de fichier physique

Il ne reste plus qu’à sauvegarder le Dictionnaire des chaînes de caractères dans le fichier « sharedStrings.xml ».

// Uri sur sharedStrings
Uri sharedStringsUri =
    new Uri("/xl/sharedStrings.xml", UriKind.Relative);


// Charger le XML
XmlDocument sharedStringsXml = 
    package.GetWritablePart(sharedStringsUri);

// Nettoyage
sharedStringsXml.DocumentElement.RemoveAll();

// Sérialiser le contenu de m_sharedString dans le fichier « sharedStrings.xml »
XPathNavigator sharedStringsNav = 
    sharedStringsXml.DocumentElement.CreateNavigator();
using (XmlWriter writer = sharedStringsNav.AppendChild())
{
    // Parcourt du dictionnaire
    foreach (KeyValuePair<string, int>
        sharedString in m_sharedStrings)
    {
        // Ecriture du nœud <x:si><x:t>[à remplacer]</x:t></x:si>
        writer.WriteStartElement("si", Namespaces.SpreadsheetML);
        writer.WriteElementString("t", Namespaces.SpreadsheetML,
            sharedString.Key);
        writer.WriteEndElement();
    }
}

// Sauvegarde de la Part XML vers le package
package.SavePart(sharedStringsUri, sharedStringsXml);

Puis sauvegarder le package dans un fichier physique.

// Flush
m_package.Flush();
m_package.Close();

// Ecriture dans un fichier physique
using (FileStream outputStream = File.Create(filename))
    m_packageData.WriteTo(outputStream);

// Fermeture du Package
m_packageData.Close();

Insérer une formule

Après avoir inséré du texte et des valeurs, insérons maintenant une formule. Ici, nous allons insérer, à la cellule D2, la formule de la somme de B2 et C2 : « =SUM(B2:C2) »

Insertion de la formule =SUM

Le principe est le suivant : il faut insérer la formule dans le fichier « sheet1.xml ».

<x:c r="D2">
        <x:f>SUM(B2:C2)</x:f>
</x:c>

Puis indiquer dans le fichier « calcChain.xml » que la cellule D2 est une cellule à calculer.

<calcChain xmlns="…">
        <c r="E2" i="1" />
</calcChain>

Mais contrairement au fichier « sharedStrings.xml » qui existait déjà dans le package, le fichier « calcChain.xml » n’existe pas, il faudra donc le créer.

  • Insérer la formule.

  • Créer la part « calcChain.xml ».

  • Créer le fichier XML.

Insérer la formule

Comme pour les numériques, on va simplement manipuler le XML de la part sheet1.xml.

// Créer le nœud <x:c r="A1">
writer.WriteStartElement(Prefixes.SpreadsheetML, "c",
    Namespaces.SpreadsheetML);
writer.WriteAttributeString("r", column + row.ToString());

// insérer la formule <x:f>SUM(B2:C2)</x:f>
writer.WriteElementString(Prefixes.SpreadsheetML, "f",
    Namespaces.SpreadsheetML, formula);

// Fermer le nœud </x:c>
writer.WriteEndElement();

// Ajoutez la référence à la cellule dans une liste
// List<Cell> m_cells
m_cells.Add(new Cell(sheetId, cell, childChain));
L’objet Cell :
private struct Cell
{
    public int SheetId;
    public string CellName;
    public bool ChildChain;

    public Cell(int sheetId, string cellName, bool childChain)
    {
        SheetId = sheetId;
        CellName = cellName;
        ChildChain = childChain;
    }
}

Créer la part « calcChain.xml »

Pour créer une Part, il faut partir du Package et d’une URI, puis créer une relation entre « sheet1.xml » et « calcChain.xml ».

Pour simplifier la création du fichier « calcChain.xml », on peut en créer un vide, et l’insérer en tant que ressource dans l’application.

Création d'un fichier 
// Les URIs
Uri workbookUri =
    new Uri("/xl/workbook.xml", UriKind.Relative);
Uri calcChainUri =
    new Uri("/xl/calcChain.xml", UriKind.Relative);

// Création de la nouvelle part à partir du fichier stocké en ressource
package.CreateNewPart(calcChainUri,
    "application/vnd.Open XMLformats-" +
    "officedocument.spreadsheetml.calcChain+xml",
    Properties.Resources.CalcChainTemplate);

// Création de la relation
package.CreateInternalRelationship(
    workbookUri, calcChainUri,
    "http://schemas.Open XMLformats.org/" +
    "officeDocument/2006/relationships/calcChain");

Créer le fichier XML

Avant la création du fichier physique Excel, il faut donc remplir le fichier « calcChain.xml » à partir de la List<Cell> m_cells.

// Création du fichier XML
XmlDocument calcChainXml = package.GetWritablePart(calcChainUri);

// Création du XmlWriter
XPathNavigator calcChainNav = 
    calcChainXml.DocumentElement.CreateNavigator();
using (XmlWriter writer = calcChainNav.AppendChild())
{
    // Pour chaque formule du dictionnaire
    foreach (Cell cell in m_cells)
    {
        // Ecrire le nœud 
        writer.WriteStartElement("c",
            Namespaces.SpreadsheetML);
        writer.WriteAttributeString("r", cell.CellName);
        writer.WriteAttributeString("i",
            cell.SheetId.ToString());
        if (cell.ChildChain)
            writer.WriteAttributeString("s", "1");
        writer.WriteEndElement();
    }
}

// Sauvegarde du XML dans le package
package.SavePart(calcChainUri, calcChainXml);

Conclusion

Nous avons donc réussi à créer un document Excel à partir d’un Template. Nous avons vu comment modifier et insérer du texte, des données numériques et des formules dans un fichier Excel Open XML, en manipulant du « SpreatSheetML ».

Ce scénario peut donc être appliqué dans des solutions purement serveur, sans qu’Excel ne soit installé sur le serveur. En définitive, ce n’est que de la manipulation de package (facilitée par le Framework 3.0) et de la manipulation de XML.

Téléchargez

OpenXML-Creation-document-Excel-a-partir-Template-Sample.zip
88 Ko

Page view tracker