Generieren von Excel 2010-Arbeitsmappen mithilfe des Open XML SDK 2.0

Zusammenfassung: Informationen zum Verwenden des Open XML SDK 2.0s zum Bearbeiten einer Microsoft Excel 2010-Arbeitsmappe.

Letzte Änderung: Donnerstag, 5. Januar 2012

Gilt für: Excel 2010 | Office 2010 | Open XML | SharePoint Server 2010 | VBA

Veröffentlicht:  April 2011

Bereitgestellt von:  Steve Hansen, Grid Logic

Inhalt

• Einführung in das Open XML-Dateiformat

• Eine Excel-Datei unter der Lupe

• Programmgesteuertes Behandeln von Open XML-Dateien

• Behandeln von Arbeitsmappen mit dem Open XML SDK 2.0

• Schlussbemerkung

• Weitere Ressourcen

• Informationen zum Autor

Beispielcode herunterladen (in englischer Sprache)

Einführung in das Open XML-Dateiformat

Open XML ist ein offenes Dateiformat für die wichtigsten dokumentorientierten Office-Anwenungen. Open XML dient zur getreuen Wiedergabe von vorhandenen Textverarbeitungsdokumenten, Präsentationen und Kalkulationstabellen, die in Binärformaten codiert sind, die durch die Microsoft Office-Anwendungen definiert werden. Open XML-Dateiformate bieten zahlreiche Vorteile. Ein Vorteil besteht darin, dass Open XML-Dateiformate sicherstellen, dass jedes Programm, das dieses Dateiformat "versteht", auf in Dokumenten enthaltene Daten zugreifen kann. Damit haben Organisationen die Gewissheit, dass die Dokumente, die sie heute erstellen, auch in Zukunft noch verwendbar sind. Ein weiterer Vorteil ist, dass Open XML die Erstellung und Behandlung von Dokumenten in Serverumgebungen oder anderen Umgebungen erleichtert, in denen die Office-Clientanwendungen nicht installiert werden können.

Wie der Name schon nahelegt, werden Open XML-Dateien mithilfe von XML dargestellt. Allerdings wird ein Open XML-Dokument nicht anhand einer einzigen, großen XML-Datei dargestellt, sondern durch eine Sammlung von zusammengehörigen Dateien, so genannten Teilen, die in einem Paket gespeichert und dann in einem ZIP-Archiv komprimiert werden. Ein Open XML-Dokumentpaket erfüllt die OPC-Spezifikation (Open Packaging Conventions), eine Technologie für Containerdateien zum Speichern einer Kombination aus XML- und XML-fremden Dateien, die zusammen eine einzige Entität bilden.

Eine Excel-Datei unter der Lupe

Um ein erstes Verständnis des Zusammenspiels der einzelnen Teile zu gewinnen, öffnen Sie am besten einmal eine Arbeitsmappendatei und werfen einen Blick auf die einzelnen Teile. Zum Untersuchen der Teile eines Microsoft Excel 2010-Arbeitsmappenpakets ändern Sie einfach die Dateinamenerweiterung von XLSX auf ZIP. Betrachten Sie beispielsweise die Arbeitsmappe in Abbildung 1 und 2.

Abbildung 1. Einfache Arbeitsmappe

Diese Arbeitsmappe enthält zwei Arbeitsblätter: Abbildung 1 zeigt ein Arbeitsblatt, in dem die Verkäufe pro Jahr ausgewiesen werden, während das Arbeitsblatt in Abbildung 2 ein einfaches Diagramm enthält.

Abbildung 2. Einfaches Diagramm in einer Arbeitsmappe

Durch Ändern des Namens dieser Arbeitsmappe von Simple Sales Example.xlsx auf Simple Sales Example.zip können Sie die Struktur der Teile innerhalb des Dateicontainers oder Pakets in Windows Explorer untersuchen.

Abbildung 3. Teilestruktur einer einfachen Arbeitsmappe

Abbildung 3 zeigt die übergeordneten Ordner in dem Paket und die im Arbeitsblattordner gespeicherten Teile. Wenn Sie ein bisschen tiefer blicken wollen, sehen Sie sich Abbildung 4 mit dem XML im Teil namens sheet1.xml an.

Abbildung 4. Beispiel für den XML-Code innerhalb eines Arbeitsmappenteils

Der XML-Code in Abbildung 4 stellt die Informationen bereit, die Excel zur Darstellung des in Abbildung 1 gezeigten Arbeitsblatt benötigt. Beispielsweise enthält der Knoten sheetData zwei Zeilenknoten. Für jede Zeile, die mindestens eine nicht leere Zelle enthält, gibt es einen Zeilenknoten. Innerhalb jeder Zeile wiederum ist für jede nicht leere Zelle ein Knoten vorhanden.

Wie Sie sehen, enthält die Zelle C3 in Abbildung 1 den Wert 2008 in Fettschrift. Zelle C4 enthält den Wert 182, allerdings in Standardformatierung und nicht in Fettschrift. Die XML-Darstellung für diese beiden Zellen wird in Abbildung 4 gezeigt. Die XML-Darstellung für Zelle C3 wird im folgenden Beispiel gezeigt.

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

Um die Open XML-Dateien so kompakt wie möglich zu halten, haben viele der XML-Knoten und -Attribute sehr kurze Namen. Im vorherigen Fragment steht das c für eine Zelle. Diese spezielle Zelle gibt zwei Attribute an: r (Reference, Verweis) und s (Style Index, Formatvorlagenindex). Das Referenzattribut gibt einen Positionsverweis für die Zelle an.

Der Formatvorlagenindex ist ein Verweis auf die Formatvorlage, die zum Formatieren der Zelle verwendet wird. Formatvorlagen werden im Formatteil (styles.xml) definiert, der sich im Ordner xl befindet (siehe Ordner xl in Abbildung 3). Vergleichen Sie die XML-Darstellung von Zelle C3 mit der von Zelle C4 im folgenden Beispiel.

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

Da in Zelle C4 Standardformatierung verwendet wird, müssen Sie keinen Wert für das Attribut für den Formatvorlagenindex angeben. Weiter unten in diesem Artikel wird etwas ausführlicher erklärt, wie Sie Formatvorlagenindizes in einem Open XML-Dokument verwenden.

Natürlich ist es für Sie sehr hilfreich, sich eingehender mit den Feinheiten der Open XML-Dateiformate zu beschäftigen. Der Hauptzweck dieses Artikels liegt aber darin, Ihnen zu zeigen, wie Sie mit dem Open XML SDK 2.0 für Microsoft Office programmgesteuert Open XML-Dokumente behandeln können, insbesondere Excel-Arbeitsmappen.

Programmgesteuertes Behandeln von Open XML-Dateien

Um ein programmgesteuertes Erstellen oder Behandeln von Open XML-Dokumenten zu ermöglichen, können Sie unter anderem nach dem folgenden allgemeinen Schema vorgehen:

1. Öffnen/Erstellen eines Open XML-Pakets

2. Öffnen/Erstellen der Paketteile

3. Analysieren des XML-Codes in den Teilen, die behandelt werden müssen

4. Behandeln des XML-Codes je nach Bedarf

5. Speichern des Teils

6. Erneutes Packen des Dokuments

Alle Schritte außer Schritt 3 und 4 können Sie mithilfe der Klassen im System.IO.Packaging-Namespace relativ einfach durchführen. Diese Klassen dienen zur Vereinfachung der Handhabung von Open XML-Paketen und -Aufgaben bei der allgemeinen Behandlung von Teilen.

Der schwierigste Teil dieses Prozesses ist Schritt 4, das Behandeln des XML-Codes. Für diesen Teil ist es unbedingt notwendig, dass der Entwickler mit den unzähligen mühsamen Detailschritten bestens vertraut ist, die für die erfolgreiche Arbeit mit den vielen Aspekten der Open XML-Dateiformate erforderlich sind. Beispielsweise haben Sie weiter oben erfahren, dass Formatierungsinformationen für eine Zelle nicht zusammen mit einer Zelle gespeichert werden, sondern vielmehr als Formatvorlage in einem anderen Dokumentteil definiert sind, und dass in einer Excel-Zelle der Formatvorlagenindex gespeichert wird, der der Formatvorlage zugeordnet ist.

Selbst Entwickler mit gründlichen Kenntnissen der Open XML-Spezifikation sind beim Gedanken daran, dass eine so große Menge an XML-Rohdaten programmgesteuert behandelt werden muss, wenig begeistert. Doch hier kommt das Open XML SDK 2.0 ins Spiel.

Das Open XML SDK 2.0 wurde konzipiert, um die Behandlung von Open XML-Paketen und der zugrunde liegenden Open XML-Schemaelemente in einem Paket zu vereinfachen. Das Open XML SDK 2.0 kapselt viele gängige Aufgaben, die Entwickler für Open XML-Pakete ausführen, sodass sie statt der XML-Rohdaten .NET-Klassen verwenden können. Diese bieten zahlreiche Vorteile für die Entwurfszeit, etwa IntelliSense-Unterstützung und typsichere Entwicklung.

Behandeln von Arbeitsmappen mit dem Open XML SDK 2.0

Zur Veranschaulichung des Vorgangs der Behandlung einer Excel-Arbeitsmappe mithilfe des Open XML SDK 2.0s wird in diesem Artikel das Erstellen eines Bericht-Generators Schritt für Schritt erklärt. Stellen Sie sich vor, Sie arbeiten für eine Börsenmaklerfirma namens Contoso. Auf der ASP.NET-Website von Contoso können sich die Kunden anmelden und verschiedene Portfolioberichte online ansehen. Die Kunden wünschen jedoch eine Möglichkeit, Berichte in Excel anzuzeigen oder herunterzuladen, um weitere Ad-hoc-Portfolioanalysen durchführen zu können.

Das gewünschte Ergebnis ist ein Prozess, der für einen Kunden einen Excel-Portfoliobericht generiert. Ein solcher Prozess kann mit zwei gängigen Vorgehensweisen erstellt werden. Bei der ersten wird das gesamte Dokument ganz neu erstellt. Für einfache Arbeitsmappen mit wenig bis gar keinen Formatierungen ist dieser Ansatz geeignet. Bei der zweiten Vorgehensweise wird allgemein das Erstellen von Dokumenten auf der Basis einer Vorlage bevorzugt. Beachten Sie, dass die Verwendung der Word-Vorlage sich hier nicht auf tatsächliche Excel-Vorlagen (XLTX) bezieht. Vielmehr geht es um die Verwendung einer Arbeitsmappe (XLSX), die die gewünschten Formatierungen, Diagramme usw. enthält, die in der endgültigen Arbeitsmappe benötigt werden. Zur Verwendung der Vorlage müssen Sie als Erstes eine Kopie der Vorlagendatei erstellen. Anschließend fügen Sie die Daten für den Kunden hinzu, für den Sie einen Bericht erstellen.

Abbildung 5. Beispiel für den Portfoliobericht

Einrichten des Projekts

Zum Erstellen eines Portfoliobericht-Generators öffnen Sie Microsoft Visual Studio 2010 und erstellen eine neue Konsolenanwendung namens PortfolioReportGenerator.

Abbildung 6. Erstellen der Portfoliobericht-Generator-Lösung

Fügen Sie als Nächstes dem Projekt zwei Klassen hinzu: PortfolioReport und Portfolio. Die PortfolioReport-Klasse ist die Hauptklasse, die die gesamte Dokumentbehandlung mithilfe des Open XML SDK 2.0s ausführt. Die Portfolio-Klasse ist im Prinzip eine Datenstruktur, die die für die Darstellung eines Kundenportfolios erforderlichen Eigenschaften enthält.

Bevor Sie mit dem Schreiben von Code beginnen, ist der erste Schritt bei einem Projekt, in dem Open XML und das Open XML SDK 2.0 verwendet werden, das Hinzufügen der benötigten Verweise auf das Projekt. Die folgenden zwei Verweise werden benötigt: DocumentFormat.OpenXml und WindowsBase.

DocumentFormat.OpenXml enthält die Klassen, die mit dem Open XML SDK 2.0 installiert werden. Falls dieser Verweis nach dem Installieren des Open XML SDK 2.0s nicht vorhanden ist, können Sie danach suchen. Er befindet sich standardmäßig unter C:\Programme (x86)\Open XML SDK\V2.0\lib\. Diesen Verweis benötigen Sie nur, wenn Sie vorhaben, das Open XML SDK 2.0 zu verwenden. Wenn Open XML-Dokumente stattdessen durch Optimieren von XML-Rohdaten behandelt werden sollen, brauchen Sie diesen Verweis nicht.

WindowsBase enthält die Klassen im System.IO.Packaging-Namespace. Diesen Verweis benötigen Sie für alle Open XML-Projekte, unabhängig davon, ob Sie das Open XML SDK 2.0 verwenden oder nicht. Die Klassen im System.IO.Packaging-Namespace stellen Funktionen zum Öffnen von Open XML-Paketen bereit. Darüber hinaus sind Klassen zum Behandeln (Hinzufügen, Entfernen, Bearbeiten) von Teilen innerhalb eines Open XML-Pakets vorhanden.

An diesem Punkt sollte Ihr Projekt etwa wie in Abbildung 7 aussehen.

Abbildung 7. Portfoliobericht-Generator-Projekt nach der ersten Projekteinrichtung

Initialisieren des Portfolioberichts

Wie schon erwähnt, funktioniert die Berichtgenerierung so, dass eine Kopie der Berichtsvorlage erstellt wird und anschließend dem Bericht Daten hinzugefügt werden. Die Berichtsvorlage ist eine vorformatierte Excel-Arbeitsmappe namens PortfolioReport.xlsx. Fügen Sie der PortfolioReport-Klasse, die diesen Prozess ausführt, einen Konstruktor hinzu. Zum Kopieren der Datei müssen Sie auch den System.IO-Namespace importieren. Fügen Sie beim Hinzufügen des System.IO-Namespace auch die Namespaces für das Open XML SDK 2.0 hinzu.

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;
        }
    }
}

Wie Sie sehen, erfordert der PortfolioReport-Konstruktor einen einzigen Parameter, der den Kunden darstellt, für den der Bericht generiert wird.

Um zu vermeiden, dass Parameter an Methoden übergeben werden müssen oder immer wieder das Dokument geöffnet und der Arbeitsmappenteil extrahiert werden muss, fügen Sie der PortfolioReport-Klasse zwei auf die Klasse beschränkte private Variablen hinzu. Fügen Sie ebenfalls eine auf die Klasse beschränkte private Variable für die Aufnahme eines Verweises auf das aktuelle Portfolio-Objekt hinzu, mit dessen Daten der Bericht generiert wird. Durch die direkte Verwendung können Sie diese Variablen dann innerhalb des PortfolioReport-Konstruktors initialisieren, wie im folgenden Beispiel gezeigt.

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

Dieses Codesegment veranschaulicht, wie einfach es ist, mithilfe von Open XML SDK 2.0 ein Dokument zu öffnen und einen Teil zu extrahieren. Im PortfolioReport-Konstruktor wird die Arbeitsmappendatei mithilfe der Open-Methode der SpreadsheetDocument-Klasse geöffnet. SpreadsheetDocument ist Teil des DocumentFormat.OpenXml.Packaging-Namespace. SpreadsheetDocument stellt einen einfachen Zugriff auf den Arbeitsmappenteil innerhalb des Dokumentpakets über die Eigenschaft WorkbookPart bereit. An diesem Punkt des Prozesses hat der Bericht-Generator

1. eine Kopie der Datei PortfolioReport.xlsx erstellt,

2. die Kopie nach dem Namen des Kunden benannt,

3. den Kundenbericht für die Bearbeitung geöffnet und

4. den Arbeitsmappenteil extrahiert.

Ändern der Werte von Arbeitsblattzellen mit dem Open XML SDK

Die größte Herausforderung bei der Erstellung des Bericht-Generators besteht darin, herauszufinden, wie die Werte in einer Excel-Arbeitsmappe mithilfe des Open XML SDK 2.0s geändert werden können. Wenn Sie das Excel-Objektmodell mit Microsoft Visual Basic für Anwendungen (VBA) oder .NET verwenden, können Zellenwerte auf einfache Weise geändert werden. Zum Ändern des Werts einer Zelle (die im Excel-Objektmodell ein Range-Objekt ist), ändern Sie den Wert der Value-Eigenschaft. Beispielsweise könnten Sie mit der folgenden Anweisung den Wert von Zelle B4 in einem Arbeitsblatt namens Sales auf 250 ändern:

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

Das Open XML SDK 2.0 funktioniert etwas anders. Einer der Hauptunterschiede besteht darin, dass Sie bei Verwendung des Excel-Objektmodells jede beliebige Zelle eines Arbeitsblatts manipulieren können, unabhängig davon, ob Daten enthalten sind. Anders gesagt: Beim Objektmodell sind alle Zellen in einem Arbeitsblatt vorhanden. Wenn Sie mit Open XML arbeiten, sind Objekte standardmäßig nicht vorhanden. Enthält eine Zelle keinen Wert, ist sie nicht vorhanden. Das ist absolut sinnvoll, wenn Sie es unter dem Gesichtspunkt des Angebens eines Dateiformats betrachten. Um eine Datei so klein wie möglich zu halten, werden nur relevante Informationen gespeichert. Sehen Sie sich z. B. in Abbildung 4 den ersten Zeilenknoten unterhalb von sheetData an. Die erste Zeile beginnt bei 3, und die Zeilen 1 und 2 werden übersprungen. Das liegt daran, dass alle Zellen in den ersten beiden Zeilen leer sind. Entsprechend lautet innerhalb des ersten Zeilenknotens (Zeile 3) die Adresse der ersten Zelle C3, weil A3 und B3 leer sind.

Weil Sie in einem Open XML-Dokument nicht davon ausgehen können, dass eine Zelle vorhanden ist, müssen Sie erst überprüfen, ob sie vorhanden ist. Wenn nicht, müssen Sie die Zelle der Datei hinzufügen. Im folgenden Beispiel werden eine Methode namens InsertCellInWorksheet, die diese Funktion ausführt, sowie die weiteren Methoden in der Liste gezeigt. Fügen Sie diese Methoden der PortfolioReport-Klasse hinzu.

        // 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;
        }

Ein weiterer Unterschied zwischen der Verwendung des Objektmodells von Excel und der Behandlung eines Open XML-Dokuments besteht darin, dass bei Einsatz des Excel-Objektmodells der Datentyp des Werts, den Sie der Zelle oder dem Bereich bereitstellen, irrelevant ist. Wird der Wert einer Zelle dagegen mit Open XML geändert, hängt der Prozess vom Datentyp des Werts ab. Für numerische Werte ähnelt der Prozess der Verwendung des Excel-Objektmodells. Im Open XML SDK 2.0 ist einem Cell-Objekt eine Eigenschaft namens CellValue zugeordnet. Diese Eigenschaft können Sie verwenden, um einer Zelle numerische Werte zuzuweisen.

Das Speichern von Zeichenfolgen oder Text in einer Zelle funktioniert anders. Anstatt direkt in einer Zelle wird der Text von Excel in einer so genannten SharedString-Tabelle (Tabelle mehrfach verwendeter Zeichenfolgen) gespeichert. Die SharedString-Tabelle ist einfach nur eine Auflistung aller eindeutigen Zeichenfolgen in der Arbeitsmappe, wobei jede eindeutige Zeichenfolge einem Index zugeordnet ist. Damit einer Zelle eine Zeichenfolge zugeordnet werden kann, enthält die Zelle statt der Zeichenfolge selbst einen Verweis auf den Zeichenfolgenindex. Wenn Sie den Wert einer Zelle auf eine Zeichenfolge ändern, müssen Sie zuerst prüfen, ob die Zeichenfolge in der SharedString-Tabelle enthalten ist. Wenn ja, suchen Sie den Zeichenfolgenindex heraus und speichern diesen in der Zelle. Ist die Zeichenfolge nicht in der SharedString-Tabelle vorhanden, müssen Sie sie hinzufügen, ihren Zeichenfolgenindex abrufen und diesen dann in der Zelle speichern. Im folgenden Beispiel wird die Methode UpdateValue gezeigt, die gemeinsam mit InsertSharedStringItem zum Aktualisieren der SharedString-Tabelle verwendet wird, um die Werte einer Zelle zu ändern.

        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;
        }

Ein wichtiger Bereich im vorherigen Codebeispiel behandelt das Formatieren einer Zelle. Wie weiter oben erwähnt, wird das Format einer Zelle nicht innerhalb des Zellknotens gespeichert. Vielmehr wird in einer Zelle ein Formatvorlagenindex gespeichert, der auf eine Formatvorlage verweist, die in einem anderen Teil definiert ist (styles.xml). Wenn Sie das in diesem Dokument demonstrierte Verfahren mit der Vorlage und das Excel-Objektmodell über VBA oder .NET nutzen, wenden Sie die gewünschte Formatierung in der Regel auf einen Bereich von Zellen an. Wenn Sie der Arbeitsmappe Daten programmgesteuert hinzufügen, werden alle in dem Bereich angewendeten Formatierungen zuverlässig übernommen.

Open XML-Dateien enthalten nur Informationen zu Zellen, die Daten enthalten. Deshalb müssen Sie bei jedem Hinzufügen einer neuen Zelle zu der Datei, wenn die Zelle eine Formatierung erfordert, den Formatvorlagenindex aktualisieren. Demzufolge akzeptiert die UpdateValue-Methode einen styleIndex-Parameter, der angibt, welcher Formatvorlagenindex auf die Zelle angewendet werden soll. Wenn Sie den Wert Null übergeben, wird kein Formatvorlagenindex festgelegt, und die Zelle verwendet die Standardformatierung von Excel.

Ein einfaches Verfahren zum Ermitteln des betreffenden Formatvorlagenindex für jede Zelle besteht darin, die Vorlagendatei für die Arbeitsmappe wie gewünscht zu formatieren, dann die entsprechenden Arbeitsmappenteile im XML-Modus zu öffnen (siehe Abbildung 4) und den Formatvorlagenindex der Zellen zu beobachten, die Sie formatiert haben.

Nachdem die Methoden aus der vorherigen Codeauflistung vorhanden sind, werden zum Generieren des Berichts nun die Portfoliodaten abgerufen, und UpdateValue wird wiederholt aufgerufen. Wenn Sie den erforderlichen Code für diese Vorgänge hinzufügen, scheint alles perfekt zu funktionieren – es gibt nur ein Problem: in jeder Zelle mit einer Formel, die eine Zelle referenziert, deren Wert mittels Open XML-Behandlung geändert wurde, wird ein falsches Ergebnis angezeigt. Der Grund hierfür ist, dass Excel das Ergebnis einer Formel innerhalb der Zelle zwischenspeichert. Da Excel davon ausgeht, es hätte den korrekten Wert zwischengespeichert, berechnet es die Zelle nicht neu. Auch wenn automatische Berechnung aktiviert ist oder Sie F9 drücken, um eine manuelle Neuberechnung zu erzwingen, wird die Zelle in Excel nicht neu berechnet.

Dieses Problem können Sie beheben, indem Sie den zwischengespeicherten Wert aus diesen Zellen entfernen, sodass Excel den Wert neu berechnet, sobald die Datei in Excel geöffnet wird. Fügen Sie die im folgenden Beispiel gezeigte RemoveCellValue-Methode der PortfolioReport-Klasse hinzu, um diese Funktionalität bereitzustellen.

        // 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;
        }

Zum Fertigstellen der PortfolioReport-Klasse fügen Sie die im folgenden Beispiel gezeigte CreateReport-Methode der PortfolioReport-Klasse hinzu. Sie platziert mithilfe der UpdateValue-Methode der CreateReport-Methode Portfolioinformationen in den gewünschten Zellen. Nach dem Aktualisieren aller erforderlichen Zellen ruft sie RemoveCellValue für jede Zelle auf, die neu berechnet werden muss. Zum Schluss ruft CreateReport die Close-Methode im SpreadsheetDocument auf, um alle Änderungen zu speichern und die Datei zu schließen.

        // 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();
        }

Verwenden der "PortfolioReport"-Klasse

Im letzten Schritt (sofern Sie die Quelle für die Portfolio-Klasse kopiert haben) fügen Sie Code zur Main-Methode in der Program-Klasse hinzu. Ändern Sie die Main-Methode so, dass sie den im folgenden Beispiel gezeigten Code enthält. Beachten Sie, dass die Quelle für die Portfolio-Klasse Beispieldaten für zwei Kunden enthält: Steve und 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();
        }

Wenn Sie diesen Code ausführen, werden Sie bemerken, wie schnell die Dateien generiert werden. Dies ist in einem Szenario mit hohen Datenvolumen auf einem Server ideal. Die Leistung ist bei ähnlichem Code, in dem das Excel-Objektmodell zum gleichen Zweck verwendet wird, nicht annähernd so hoch – die Open XML-Methode ist weitaus schneller.

Schlussbemerkung

Beginnend mit dem 2007 Microsoft Office System wurden die wichtigsten dokumentorientierten Microsoft Office-Anwendungen von proprietären binären Dateiformaten auf Open XML-Dateiformate umgestellt. Die Open XML-Dateiformate sind offene, auf Standards basierende Dateiformate, die auf XML aufbauen. Die Umstellung auf Open XML-Dateiformate eröffnet Entwicklern zahlreiche neue Möglichkeiten beim Erstellen von Lösungen. Gleichwohl müssen Entwickler, die diese Möglichkeiten nutzen wollen, viel Zeit aufwenden, um die Open XML-Spezifikationen zu verstehen, und die mühsame Arbeit der Manipulation von XML-Rohdaten auf sich nehmen.

Das Open XML SDK 2.0 trägt zur Verkürzung der Lernkurve für Entwicklungsverfahren bei, indem es viele Details der Open XML-Spezifikation in eine einfach zu verwendende Klassenbibliothek zum Arbeiten mit Open XML-Dokumenten kapselt. Außerdem können Entwickler mithilfe des Open XML SDK 2.0s ihre Produktivität steigern, indem Sie die darin enthaltenen Funktionen für die Entwurfszeit nutzen, z. B. IntelliSense-Unterstützung und typsichere Entwicklung.

In diesem Artikel wurde demonstriert, wie Sie mit dem Open XML SDK 2.0 einen Portfoliobericht-Generator erstellen können. Dabei wurden ein allgemeiner Lösungsansatz und ein Ansatz für gängige Excel-bezogene Aufgaben gezeigt, z. B. das Öffnen von Arbeitsmappen, Verweisen auf Arbeitsblätter, Abrufen von Zellen oder Aktualisieren von Zellenwerten.

Weitere Ressourcen

Weitere Informationen zu den in diesem Artikel besprochenen Themen finden Sie in den folgenden Ressourcen.

• Open XML SDK 2.0 (in englischer Sprache) herunterladen

• OpenXML Developer (in englischer Sprache)

• Introducing the Office Open XML File Formats

• Office XML Developer Center (in englischer Sprache)

Informationen zum Autor

Steve Hansen ist Gründer von Grid Logic, eines in Minnesota ansässigen Consultingunternehmens, das auf Business Intelligence- und Information Worker-Lösungen spezialisiert ist. Als Entwickler, regelmäßiger Autor sowie Referent auf technischen Konferenzen ist Steve ein Microsoft MVP für seinen Arbeitsbereich Visual Studio-Tools für Office. Er jongliert jedoch nicht nur meisterhaft mit Code, sondern verfügt auch über Expertenwissen im Bereich Finanzen. Den Grundstein dafür hat er mit einem MBA der University of Minnesota mit Schwerpunkt Finanzwesen gelegt.