Génération de classeurs Excel 2010 à l’aide du Kit de développement logiciel Open XML SDK 2.0

Résumé : Découvrez comment utiliser le Kit de développement Open XML SDK 2.0 pour manipuler un classeur Microsoft Excel 2010.

Dernière modification : jeudi 5 janvier 2012

S’applique à : Excel 2010 | Office 2010 | Open XML | SharePoint Server 2010 | VBA

Publication : Avril 2011

Auteur :  Steve Hansen, Grid Logic

Contenu

• Introduction au format de fichier Open XML

• Examen approfondi d’un fichier Excel

• Manipulation de fichiers Open XML par programmation

• Manipulation des classeurs à l’aide du Kit de développement logiciel Open XML SDK 2.0

• Conclusion

• Ressources supplémentaires

• À propos de l’auteur

Télécharger l’exemple de code (éventuellement en anglais)

Introduction au format de fichier Open XML

Open XML est un format de fichier ouvert pour les principales applications Office orientées documents. Open XML est conçu pour remplacer les documents de traitement de texte, les présentations et les feuilles de calcul existants qui sont codés en formats binaires définis par les applications Microsoft Office. Les formats de fichiers Open XML offrent plusieurs avantages. L’un de ces avantages est que les formats de fichiers Open XML garantissent que les données qui sont contenues dans les documents sont accessibles par tout programme comprenant ce format. Cela contribue à garantir aux entreprises que les documents qu’elles créent aujourd’hui seront accessibles dans le futur. Ce format simplifie également la création et la manipulation de documents dans des environnements serveurs ou d’autres environnements où il est impossible d’installer les applications clientes Office.

Comme leur nom l’indique, les fichiers Open XML sont représentés avec XML. Cependant, au lieu de représenter un document au moyen d’un seul grand fichier XML, un document Open XML est généralement représenté à l’aide d’une collection de fichiers associés, nommés parties, qui sont stockés dans un package puis compressés dans une archive ZIP. Un package de documents Open XML est conforme à la spécification OPC (Open Packaging Conventions), une technologie de fichier conteneur destinée à stocker une combinaison de fichiers XML et non-XML formant collectivement une entité unique.

Examen approfondi d’un fichier Excel

L’une des meilleures manières de commencer à comprendre l’interfonctionnement des divers éléments consiste à ouvrir un fichier de classeur et en examiner les diverses parties. Pour examiner les parties d’un package de classeur Microsoft Excel 2010, changez simplement l’extension du nom de fichier de .xlsx à .zip. Par exemple, prenons le classeur présenté dans les figures 1 et 2.

Figure 1. Classeur simple

Ce classeur contient deux feuilles de calcul : la figure 1 montre une feuille de calcul contenant les ventes par année tandis que la feuille de calcul présentée dans la figure 2 contient un graphique simple.

Figure 2. Graphique de base dans un classeur

En changeant le nom de ce classeur de Simple Sales Example.xlsx en Simple Sales Example.zip, vous pouvez inspecter la structure des parties dans le conteneur de fichier ou le package à l’aide de l’Explorateur Windows.

Figure 3. Structure en parties d’un classeur simple

La figure 3 montre les dossiers principaux à l’intérieur du package avec les parties stockées dans le dossier worksheets. Explorant plus avant, la figure 4 examine le code XML contenu dans la partie nommée sheet1.xml.

Figure 4. Exemple du code XML contenu dans une partie de feuille de calcul

Le code XML présenté dans la figure 4 fournit les informations nécessaires dont Excel a besoin pour représenter la feuille de calcul illustrée dans la figure 1. Par exemple, le nœud sheetData contient des nœuds de lignes. Il y a un nœud de ligne pour chaque ligne comportant au moins une cellule non vide. Ensuite, dans chaque ligne, il y a un nœud pour chaque cellule non vide.

Notez que la cellule C3 présentée dans la figure 1 contient la valeur 2008 en gras. La cellule C4, contient quant à elle la valeur 182, mais utilise le formatage par défaut et sans mise en gras. La représentation XML pour chacune de ces cellules est illustrée dans la figure 4. En particulier, le XML pour la cellule C3 est présenté dans l’exemple suivant.

      <c r="C3" s="1">
        <v>2008</v>
      </c>

Pour maintenir la taille des fichiers Open XML aussi compacte que possible, de nombreux nœuds et attributs XML ont des noms très courts. Dans le fragment précédent, le c représente une cellule. Cette cellule particulière spécifie deux attributs : r (Référence) et s (Index de style). L’attribut de référence spécifie une référence d’emplacement pour la cellule.

L’index de style est une référence au style utilisé pour formater la cellule. Les styles sont définis dans la partie styles (styles.xml) qui se trouve dans le dossier xl (voir le dossier xl dans la figure 3). Comparez le XML de la cellule C3 au XML de la cellule C4 présenté dans l’exemple suivant.

      <c r="C4">
        <v>182</v>
      </c>

Comme la cellule C4 utilise le formatage par défaut, vous n’avez pas à spécifier de valeur pour l’attribut d’index de style. Plus loin dans cet article, vous en apprendrez un peu plus sur l’utilisation des index de style dans un document Open XML.

Bien qu’il soit très utile d’en savoir plus sur les nuances des formats de fichiers Open XML, l’objet réel de cet article est de montrer comment utiliser le Kit de développement Open XML SDK 2.0 pour Microsoft Office pour manipuler par programmation des documents Open XML, spécifiquement des classeurs Excel.

Manipulation de fichiers Open XML par programmation

L’une des manières de créer ou de manipuler par programmation des documents Open XML consiste à utiliser le modèle de haut niveau suivant :

1. Ouvrir/créer un package Open XML

2. Ouvrir/créer des parties de package

3. Analyser le XML dans les parties à manipuler

4. Manipuler le XML selon les besoins

5. Enregistrer la partie

6. Recomposer le package du document

À l’exception des étapes trois et quatre, tout peut être réalisé assez facilement en utilisant les classes se trouvant dans l’espace de noms System.IO.Packaging. Ces classes sont conçues pour faciliter la gestion des packages Open XML et simplifier les tâches associées à la manipulation des parties de haut niveau.

La partie la plus complexe de ce processus correspond à l’étape quatre, manipulation du XML. Pour cette partie, il est crucial pour le développeur de bénéficier d’un degré élevé de compréhension des nombreux menus détails requis pour travailler efficacement avec les nombreuses nuances de formats de fichiers Open XML. Par exemple, vous avez précédemment appris que les informations de formatage pour une cellule ne sont pas stockées avec la cellule. Les détails de formatage sont plutôt définis comme un style dans une partie de document différente, et l’index de style associé au style correspond à ce qu’Excel stocke dans une cellule.

Même avec une connaissance approfondie de la spécification Open XML, la manipulation de tant de code XML brut par programmation n’est pas une tâche que beaucoup de développeurs aiment effectuer. C’est ici que le Kit de développement Open XML SDK 2.0 intervient.

Le Kit de développement Open XML SDK 2.0 a été développé pour simplifier la manipulation de packages Open XML et des éléments du schéma Open XML sous-jacent à l’intérieur d’un package. Le Kit de développement Open XML SDK 2.0 encapsule de nombreuses tâches courantes que les développeurs effectuent sur des packages Open XML afin qu’au lieu de devoir travailler avec du XML brut, vous puissiez utiliser des classes .NET vous offrant de nombreux avantages lors de la conception tels que la prise en charge d’IntelliSense et des conditions de développement sécurisées.

Manipulation des classeurs à l’aide du Kit de développement logiciel Open XML SDK 2.0

Pour vous montrer le processus de manipulation d’un classeur Excel à l’aide du Kit de développement Open XML SDK 2.0, cet article vous guide dans la construction d’un générateur de rapports. Imaginez que vous travaillez pour une société de courtage nommée Contoso. Le site Web ASP.NET de Contoso permet aux clients de se connecter et d’afficher divers rapports de portefeuille en ligne. Cependant, une demande fréquente des utilisateurs est la possibilité d’afficher ou de télécharger des rapports dans Excel afin qu’ils puissent effectuer une analyse supplémentaire sur le portefeuille.

Le résultat souhaité est un processus qui, pour un client donné, génère un rapport de portefeuille Excel. Il existe deux approches générales à ce type de processus. Une première approche consiste à générer tout le document à partir de zéro. Pour les classeurs simples avec peu ou pas de formatage, cette approche convient. La seconde approche, créant des documents qui utilisent un modèle, est généralement la méthode préférée. Notez que l’utilisation du modèle Word ne se réfère pas ici à des modèles Excel réels (*.xltx). Elle se réfère plutôt à l’utilisation d’un classeur (*.xlsx) qui contient tout le formatage, les graphiques et autres éléments qui sont souhaités dans le classeur final. Pour utiliser le modèle, la première étape du processus consiste à faire une copie du fichier du modèle. Ensuite, vous ajoutez les données associées au client pour lequel vous construisez un rapport.

Figure 5. Exemple de rapport Portefeuille

Configuration du projet

Pour créer un générateur de rapports de portefeuille, ouvrez Microsoft Visual Studio 2010 et créez une nouvelle application Console nommée PortfolioReportGenerator.

Figure 6. Créer la solution Générateur de rapports de portefeuille

Ensuite, ajoutez deux classes au projet : PortfolioReport et Portfolio. La classe PortfolioReport est la classe clé qui effectue la manipulation de document à l’aide du Kit de développement Open XML SDK 2.0. La classe Portfolio est essentiellement une structure de données qui contient les propriétés nécessaires pour représenter un portefeuille client.

Avant d’écrire du code, la première étape dans tout projet impliquant Open XML et le Kit de développement Open XML SDK 2.0 consiste à ajouter les références nécessaires au projet. Deux références spécifiques sont requises : DocumentFormat.OpenXml et WindowsBase.

DocumentFormat.OpenXml contient les classes qui sont installées avec le Kit de développement Open XML SDK 2.0. Si vous ne trouvez pas cette référence après l’installation du Kit de développement Open XML SDK 2.0, vous pouvez la rechercher. Par défaut elle se trouve dans le dossier C:\Program Files (x86)\Open XML SDK\V2.0\lib\. Cette référence est requise uniquement si vous compter utiliser le Kit de développement Open XML SDK 2.0. Si vous préférez manipuler les documents Open XML en agissant directement sur le XML brut, vous n’en avez pas besoin.

WindowsBase inclut les classes dans l’espace de noms System.IO.Packaging. Cette référence est requise pour tous les projets Open XML, que vous utilisiez ou non le Kit de développement Open XML SDK 2.0. Les classes dans l’espace de noms System.IO.Packaging fournissent une fonctionnalité pour ouvrir des packages Open XML. En outre, des classes vous permettent de manipuler (ajouter, retirer, modifier) des parties à l’intérieur d’un package Open XML.

À ce stade, votre projet doit avoir l’aspect présenté à la figure 7.

Figure 7. Projet Générateur de rapports de portefeuille après la configuration initiale du projet

Initialisation du rapport de portefeuille

Comme mentionné plus haut, le processus de génération de rapports fonctionne en créant une copie du modèle de rapport et en ajoutant ensuite des données au rapport. Le modèle de rapport est un classeur Excel préformaté nommé PortfolioReport.xlsx. Ajoutez un constructeur à la classe PortfolioReport qui effectue ce processus. Pour copier les fichiers, vous devez également importer l’espace de noms System.IO. Pendant l’ajout de l’espace de noms System.IO, ajoutez les espaces de noms associés au Kit de développement Open XML SDK 2.0.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;

namespace PortfolioReportGenerator
{
    class PortfolioReport
    {
        string path = "c:\\example\\";
        string templateName = "PortfolioReport.xlsx";

        public PortfolioReport(string client)
        {
            string newFileName = path + client + ".xlsx";
            CopyFile(path + templateName, newFileName);
        }

        private string CopyFile(string source, string dest)
        {
            string result = "Copied file";
            try
            {
                // Overwrites existing files
                File.Copy(source, dest, true);
            }
            catch (Exception ex)
            {
                result = ex.Message;
            }
            return result;
        }
    }
}

Notez que le constructeur PortfolioReport nécessite un paramètre spécifique qui représente le client pour lequel le rapport est généré.

Pour éviter de devoir passer des paramètres dans des méthodes ou constamment réouvrir le document et extraire la partie classeur, ajoutez deux variables privées de portée classe à la classe PortfolioReport. De même, ajoutez une variable de portée classe pour contenir une référence à l’objet Portfolio actuel dont les données sont utilisées pour générer le rapport. En utilisant ces variables sur place, vous pouvez les initialiser à l’intérieur du constructeur PortfolioReport comme illustré dans l’exemple suivant.

        string path = "c:\\example\\";
        string templateName = "PortfolioReport.xlsx";

        WorkbookPart wbPart = null;
        SpreadsheetDocument document = null;
        Portfolio portfolio = null;

        public PortfolioReport(string client)
        {
            string newFileName = path + client + ".xlsx";
            CopyFile(path + templateName, newFileName);
            document = SpreadsheetDocument.Open(newFileName, true);
            wbPart = document.WorkbookPart;
            portfolio = new Portfolio(client);
        }

Ce segment de code illustre à quel point il est facile d’ouvrir un document et d’en extraire une partie à l’aide du Kit de développement Open XML SDK 2.0. Dans le constructeur PortfolioReport, le fichier du classeur est ouvert à l’aide de la méthode Open de la classe SpreadsheetDocument. SpreadsheetDocument fait partie de l’espace de noms DocumentFormat.OpenXml.Packaging. SpreadsheetDocument fournit un accès pratique à la partie classeur dans le package du document via la propriété nommée WorkbookPart. À ce stade du processus, le générateur de rapports a :

1. Créé une copie du fichier PortfolioReport.xlsx

2. Nommé la copie d’après le nom du client

3. Ouvert le rapport du client pour modification

4. Extrait la partie classeur

Modification des valeurs de cellules du classeur à l’aide du Kit de développement logiciel Open XML SDK

La tâche principale à résoudre pour terminer le générateur de rapports consiste à déterminer comment modifier les valeurs à l’intérieur d’un classeur Excel à l’aide du Kit de développement Open XML SDK 2.0. Lors de l’utilisation du modèle objet d’Excel avec Microsoft Visual Basic pour Applications (VBA) ou .NET, la modification de la valeur d’une cellule est facile. Pour modifier la valeur d’une cellule (qui est un objet Range dans le modèle objet d’Excel), vous modifiez la valeur de la propriété Value. Par exemple pour modifier la valeur de la cellule B4 sur une feuille de calcul nommée Sales à la valeur 250, vous pouvez utiliser cette instruction :

ThisWorkbook.Worksheets("Sales").Range("B4").Value = 250

Le Kit de développement Open XML SDK 2.0 fonctionne un peu différemment. La principale différence est que lors de l’utilisation du modèle objet Excel vous pouvez manipuler n’importe quelle cellule sur une feuille de calcul, qu’elle contienne quelque chose ou non. En d’autres mots, pour autant que le modèle objet est concerné, toutes les cellules sur une feuille de calcul existent. Lors d’un travail avec Open XML, les objets n’existent pas. C’est la configuration par défaut. Si une cellule ne contient pas de valeur, elle n’existe pas. Cela est parfaitement logique dans une perspective de spécification de format de fichier. Pour maintenir la taille d’un fichier aussi petite que possible, seules les informations pertinentes sont enregistrées. Par exemple, dans la figure 4, observez le premier nœud de ligne sous sheetData. La première ligne commence à 3 et ignore les lignes 1 et 2. En effet, toutes les cellules des deux premières lignes sont vides. De même, notez qu’à l’intérieur du premier nœud de ligne (ligne 3), l’adresse de la première cellule est C3. C’est parce que A3 et B3 sont vides.

Comme vous ne pouvez pas partir du principe qu’une cellule existe dans un document Open XML, vous devez d’abord vérifier si elle existe, puis si elle n’existe pas, l’ajouter au fichier. L’exemple suivant montre une méthode nommée InsertCellInWorksheet qui effectue cette fonction, avec les autres méthodes dans le listing. Ajoutez ces méthodes à la classe PortfolioReport.

        // Given a Worksheet and an address (like "AZ254"), either return a 
        // cell reference, or create the cell reference and return it.
        private Cell InsertCellInWorksheet(Worksheet ws, string addressName)
        {
            SheetData sheetData = ws.GetFirstChild<SheetData>();
            Cell cell = null;

            UInt32 rowNumber = GetRowIndex(addressName);
            Row row = GetRow(sheetData, rowNumber);

            // If the cell you need already exists, return it.
            // If there is not a cell with the specified column name, insert one.  
            Cell refCell = row.Elements<Cell>().
                Where(c => c.CellReference.Value == addressName).FirstOrDefault();
            if (refCell != null)
            {
                cell = refCell;
            }
            else
            {
                cell = CreateCell(row, addressName);
            }
            return cell;
        }
        
        // Add a cell with the specified address to a row.
        private Cell CreateCell(Row row, String address)
        {
            Cell cellResult;
            Cell refCell = null;

            // Cells must be in sequential order according to CellReference. 
            // Determine where to insert the new cell.
            foreach (Cell cell in row.Elements<Cell>())
            {
                if (string.Compare(cell.CellReference.Value, address, true) > 0)
                {
                    refCell = cell;
                    break;
                }
            }

            cellResult = new Cell();
            cellResult.CellReference = address;

            row.InsertBefore(cellResult, refCell);
            return cellResult;
        }

        // Return the row at the specified rowIndex located within
        // the sheet data passed in via wsData. If the row does not
        // exist, create it.
        private Row GetRow(SheetData wsData, UInt32 rowIndex)
        {
            var row = wsData.Elements<Row>().
            Where(r => r.RowIndex.Value == rowIndex).FirstOrDefault();
            if (row == null)
            {
                row = new Row();
                row.RowIndex = rowIndex;
                wsData.Append(row);
            }
            return row;
        }

        // Given an Excel address such as E5 or AB128, GetRowIndex
        // parses the address and returns the row index.
        private UInt32 GetRowIndex(string address)
        {
            string rowPart;
            UInt32 l;
            UInt32 result = 0;

            for (int i = 0; i < address.Length; i++)
            {
                if (UInt32.TryParse(address.Substring(i, 1), out l))
                {
                    rowPart = address.Substring(i, address.Length - i);
                    if (UInt32.TryParse(rowPart, out l))
                    {
                        result = l;
                        break;
                    }
                }
            }
            return result;
        }

Une autre différence entre l’utilisation du modèle objet d’Excel et la manipulation d’un document Open XML est que lorsque vous utilisez le modèle objet d’Excel, le type de données de la valeur que vous fournissez à la cellule ou à la plage n’a pas d’importance. Cependant, lors de la modification de la valeur d’une cellule à l’aide d’Open XML, le processus varie selon le type de données de la valeur. Pour les valeurs numériques, le processus est assez similaire à l’utilisation du modèle objet d’Excel. Une propriété est associée à un objet Cell dans le Kit de développement Open XML SDK 2.0 et se nomme CellValue. Vous pouvez utiliser cette propriété pour affecter des valeurs numériques à une cellule.

Le stockage de chaîne, ou de texte, dans une cellule fonctionne différemment. Plutôt que de stocker du texte directement dans une cellule, Excel le stocke dans un tableau de chaînes partagé. Le tableau de chaînes partagé n’est qu’une simple liste de toutes les chaînes uniques dans le classeur où chaque chaîne unique est associée à un index. Pour associer une cellule à une chaîne, la cellule contient une référence à l’index de chaînes et non la chaîne elle-même. Lorsque vous changez la valeur d’une cellule en une chaîne, vous devez d’abord vérifier si la chaîne se trouve dans le tableau de chaînes partagé. Si elle s’y trouve, vous recherchez l’index de chaîne partagé et le stockez dans la cellule. Si la chaîne ne se trouve pas dans le tableau de chaînes partagé, vous devez l’y ajouter, récupérer son index de chaîne, puis stocker l’index de chaîne dans la cellule. L’exemple suivant illustre une méthode nommée UpdateValue servant à modifier les valeurs d’une cellule avec InsertSharedStringItem pour actualiser le tableau de chaînes partagé.

        public bool UpdateValue(string sheetName, string addressName, string value, 
                                UInt32Value styleIndex, bool isString)
        {
            // Assume failure.
            bool updated = false;

            Sheet sheet = wbPart.Workbook.Descendants<Sheet>().Where(
                (s) => s.Name == sheetName).FirstOrDefault();

            if (sheet != null)
            {
                Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(sheet.Id))).Worksheet;
                Cell cell = InsertCellInWorksheet(ws, addressName);

                if (isString)
                {
                    // Either retrieve the index of an existing string,
                    // or insert the string into the shared string table
                    // and get the index of the new item.
                    int stringIndex = InsertSharedStringItem(wbPart, value);

                    cell.CellValue = new CellValue(stringIndex.ToString());
                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                }
                else
                {
                    cell.CellValue = new CellValue(value);
                    cell.DataType = new EnumValue<CellValues>(CellValues.Number);
                }

                if (styleIndex > 0)
                    cell.StyleIndex = styleIndex;
                
                // Save the worksheet.
                ws.Save();
                updated = true;
            }

            return updated;
        }

        // Given the main workbook part, and a text value, insert the text into 
        // the shared string table. Create the table if necessary. If the value 
        // already exists, return its index. If it doesn't exist, insert it and 
        // return its new index.
        private int InsertSharedStringItem(WorkbookPart wbPart, string value)
        {
            int index = 0;
            bool found = false;
            var stringTablePart = wbPart
                .GetPartsOfType<SharedStringTablePart>().FirstOrDefault();

            // If the shared string table is missing, something's wrong.
            // Just return the index that you found in the cell.
            // Otherwise, look up the correct text in the table.
            if (stringTablePart == null)
            {
                // Create it.
                stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
            }

            var stringTable = stringTablePart.SharedStringTable;
            if (stringTable == null)
            {
                stringTable = new SharedStringTable();
            }

            // Iterate through all the items in the SharedStringTable. 
            // If the text already exists, return its index.
            foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == value)
                {
                    found = true;
                    break;
                }
                index += 1;
            }

            if (!found)
            {
                stringTable.AppendChild(new SharedStringItem(new Text(value)));
                stringTable.Save();
            }

            return index;
        }

L’un des aspects intéressants de l’exemple de code précédent concerne le formatage d’une cellule. Comme mentionné plus haut dans cet article, un format de cellule n’est pas stocké dans le nœud de la cellule. Une cellule stocke plutôt un index de style qui pointe vers un style qui est défini dans une partie différente (styles.xml). Lors de l’utilisation du modèle présenté dans ce document et du modèle objet d’Excel via VBA ou .NET, vous appliquez généralement le formatage souhaité à une plage d’une ou de plusieurs cellules. Lorsque vous ajoutez des données au classeur par programmation, tout formatage que vous avez appliqué au sein de la plage est fidèlement mis en œuvre.

Comme les fichiers Open XML contiennent uniquement des informations associées aux cellules contenant des données, chaque fois que vous ajoutez une nouvelle cellule au fichier, si la cellule nécessite un formatage, vous devez mettre à jour l’index de style. Par conséquent, la méthode UpdateValue accepte un paramètre styleIndex qui indique quel index de style il convient d’appliquer à la cellule. Si vous passez la valeur zéro, aucun index de style n’est défini et la cellule utilise le formatage par défaut d’Excel.

Une méthode simple pour déterminer l’index de style approprié pour chaque cellule est de formater le fichier de modèle de classeur comme vous le souhaitez, puis d’ouvrir les parties appropriées du classeur en mode XML (illustré à la figure 4) et d’observer l’index de style des cellules que vous avez formatées.

Une fois les méthodes du listing de code précédent en place, la génération du rapport consiste maintenant à obtenir les données du portefeuille et à appeler de façon répétitive UpdateValue pour créer le rapport. En effet, si vous ajoutez le code nécessaire pour cette opération, tout semble fonctionner correctement à l’exception d’un problème : toute cellule contenant une formule faisant référence à une cellule dont la valeur a été changée par une manipulation Open XML n’affiche pas le bon résultat. Cela se produit car Excel met en cache le résultat d’une formule dans la cellule. Comme Excel pense qu’il dispose de la valeur correcte en cache, il ne recalcule pas la cellule. Même si vous avez activé le calcul automatique ou si vous appuyez sur F9 pour imposer un recalcul manuel, Excel ne recalcule pas la cellule.

La solution à ce problème consiste à supprimer la valeur mise en cache de ces cellules afin que Excel recalcule la valeur dès l’ouverture du fichier dans Excel. Ajoutez la méthode RemoveCellValue présentée dans l’exemple suivant à la classe PortfolioReport pour mettre en œuvre cette fonctionnalité.

        // This method is used to force a recalculation of cells containing formulas. The
        // CellValue has a cached value of the evaluated formula. This
        // prevents Excel from recalculating the cell even if 
        // calculation is set to automatic.
        private bool RemoveCellValue(string sheetName, string addressName)
        {
            bool returnValue = false;

            Sheet sheet = wbPart.Workbook.Descendants<Sheet>().
                Where(s => s.Name == sheetName).FirstOrDefault();
            if (sheet != null)
            {
                Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(sheet.Id))).Worksheet;
                Cell cell = InsertCellInWorksheet(ws, addressName);

                // If there is a cell value, remove it to force a recalculation
                // on this cell.
                if (cell.CellValue != null)
                {
                    cell.CellValue.Remove();
                }
                
                // Save the worksheet.
                ws.Save();
                returnValue = true;
            }

            return returnValue;
        }

Pour compléter la classe PortfolioReport, ajoutez la méthode CreateReport présentée dans l’exemple suivant à la classe PortfolioReport. Elle utilise la méthode CreateReportUpdateValue pour placer les informations de portefeuille dans les cellules souhaitées. Après la mise à jour de toutes les cellules nécessaires, elle appelle RemoveCellValue sur chaque cellule devant être recalculée. Enfin, CreateReport appelle la méthode Close sur SpreadsheetDocument pour enregistrer toutes les modifications et fermer le fichier.

        // Create a new Portfolio report
        public void CreateReport()
        {
            string wsName = "Portfolio Summary";

            UpdateValue(wsName, "J2", "Prepared for " + portfolio.Name, 0, true);
            UpdateValue(wsName, "J3", "Account # " + 
                        portfolio.AccountNumber.ToString(), 0, true);
            UpdateValue(wsName, "D9", portfolio.BeginningValueQTR.ToString(), 0, false);
            UpdateValue(wsName, "E9", portfolio.BeginningValueYTD.ToString(), 0, false);
            UpdateValue(wsName, "D11", portfolio.ContributionsQTR.ToString(), 0, false);
            UpdateValue(wsName, "E11", portfolio.ContributionsYTD.ToString(), 0, false);
            UpdateValue(wsName, "D12", portfolio.WithdrawalsQTR.ToString(), 0, false);
            UpdateValue(wsName, "E12", portfolio.WithdrawalsYTD.ToString(), 0, false);
            UpdateValue(wsName, "D13", portfolio.DistributionsQTR.ToString(), 0, false);
            UpdateValue(wsName, "E13", portfolio.DistributionsYTD.ToString(), 0, false);
            UpdateValue(wsName, "D14", portfolio.FeesQTR.ToString(), 0, false);
            UpdateValue(wsName, "E14", portfolio.FeesYTD.ToString(), 0, false);
            UpdateValue(wsName, "D15", portfolio.GainLossQTR.ToString(), 0, false);
            UpdateValue(wsName, "E15", portfolio.GainLossYTD.ToString(), 0, false);

            int row = 7;
            wsName = "Portfolio Holdings";

            UpdateValue(wsName, "J2", "Prepared for " + portfolio.Name, 0, true);
            UpdateValue(wsName, "J3", "Account # " + 
                        portfolio.AccountNumber.ToString(), 0, true);
            foreach (PortfolioItem item in portfolio.Holdings)
            {
                UpdateValue(wsName, "B" + row.ToString(), item.Description, 3, true);
                UpdateValue(wsName, "D" + row.ToString(), 
                            item.CurrentPrice.ToString(), 24, false);
                UpdateValue(wsName, "E" + row.ToString(), 
                            item.SharesHeld.ToString(), 27, false);
                UpdateValue(wsName, "F" + row.ToString(), 
                            item.MarketValue.ToString(), 24, false);
                UpdateValue(wsName, "G" + row.ToString(), 
                            item.Cost.ToString(), 24, false);
                UpdateValue(wsName, "H" + row.ToString(), 
                            item.High52Week.ToString(), 28, false);
                UpdateValue(wsName, "I" + row.ToString(), 
                            item.Low52Week.ToString(), 28, false);
                UpdateValue(wsName, "J" + row.ToString(), item.Ticker, 11, true);
                row++;
            }

            // Force re-calc when the workbook is opened
            this.RemoveCellValue("Portfolio Summary", "D17");
            this.RemoveCellValue("Portfolio Summary", "E17");

            // All done! Close and save the document.
            document.Close();
        }

Utilisation de la classe PortfolioReport

L’étape finale (en partant du principe que vous avez copié le code source de la classe Portfolio), consiste à ajouter du code à la méthode Main dans la classe Program. Modifiez la méthode Main afin qu’elle contienne le code présenté dans l’exemple suivant. Notez que le code source de la classe Portfolio inclut des exemples de données pour deux clients : Steve et Kelly.

        static void Main(string[] args)
        {
            PortfolioReport report = new PortfolioReport("Steve");
            report.CreateReport();
            report = new PortfolioReport("Kelly");
            report.CreateReport();
            Console.WriteLine("Reports created!");
            Console.WriteLine("Press ENTER to quit.");
            Console.ReadLine();
        }

Lors de l’exécution de ce code, vous pourrez noter la rapidité de génération des fichiers. Cela est idéal dans un scénario de serveur à grand volume. Les performances obtenues par rapport à un code similaire utilisant le modèle objet Excel pour obtenir le même résultat sont largement supérieures, la méthode Open XML est beaucoup, beaucoup plus rapide.

Conclusion

À partir de Microsoft Office System 2007, les principales applications Microsoft Office orientées documents sont passées des formats de fichiers binaires propriétaires aux formats de fichiers Open XML. Les formats de fichiers Open XML sont ouverts, basés sur des normes et sur XML. Le passage aux formats de fichiers Open XML ouvre de nouvelles opportunités de développement pour les développeurs. Cela étant dit, pour pleinement tirer parti de ces opportunités il a fallu investir beaucoup de temps et d’efforts pour comprendre les spécifications Open XML et beaucoup de manipulations du XML brut, souvent fastidieuses.

Le Kit de développement Open XML SDK 2.0 contribue à atténuer la courbe d’apprentissage avec une technique de développement, en encapsulant de nombreux détails de la spécification Open XML dans une bibliothèque de classes faciles à utiliser pour travailler avec des documents Open XML. Outre l’atténuation de la courbe d’apprentissage, le Kit de développement Open XML SDK 2.0 permet aux développeurs d’être plus productifs grâce à des possibilités en phase de conception telles que la prise en charge d’IntelliSense et des conditions de développement sécurisées.

Cet article a démontré comment utiliser le Kit de développement Open XML SDK 2.0 pour construire un générateur de rapports de portefeuille. Cet exercice a illustré un modèle de solution courant et une approche pour des tâches orientées Excel courantes telles que l’ouverture de classeurs, les références à des feuilles de calcul, la récupération de cellules et la mise à jour des valeurs des cellules.

Ressources supplémentaires

Pour plus d’informations sur les sujets traités dans cet article, voir les ressources suivantes.

• Télécharger le Kit de développement logiciel Open XML SDK 2.0 (éventuellement en anglais)

• Développeur OpenXML (éventuellement en anglais)

• Introducing the Office Open XML File Formats

• Centre pour développeurs Office XML (éventuellement en anglais)

À propos de l’auteur

Steve Hansen est le fondateur de Grid Logic, une société de conseil basée au Minnesota, spécialisée dans les solutions pour les professionnels de l’information et les solutions d’aide à la décision. Développeur, auteur et intervenant régulier dans des conférences techniques, Steve est devenu collaborateur MVP Microsoft pour le travail accompli sur Visual Studio pour Office. Passionné d’informatique, Steve est aussi un adepte de la finance et a obtenu un MBA à l’université du Minnesota avec une spécialisation dans ce domaine.