Excel-Export ohne Excel (Snippet)

Beschreibung:

Microsoft Excel ist ein beliebtes Werkzeug, um mit Tabellen zu arbeiten. Deshalb wird es bei den Endanwendern hoch geschätzt, wenn eine Anwendung Daten im Excel-Format exportieren kann.
Da viele Geschäftsanwendungen heute allerdings Webanwendungen sind, gibt es mit Excel ein Problem. Auf dem Webserver ist Excel nicht installiert. Selbst wenn, würde ein Excel-Export mit COM Fernsteuerung des Excel-Objektmodells niemals schnell und zuverlässig funktionieren. Was nun?
Seit Excel 2003 gibt es einen sehr einfachen Weg, Excel-Dokumente ganz ohne Excel zu erzeugen, und zwar mit XML. Das kann eine PHP- oder ASP.NET-Anwendung ohne Probleme. XML ist ja nur Text und kann theoretisch sogar mit einfacher Stringverkettung erzeugt werden.

Da sich scheinbar viele Leute vom komplexen Schema des Excel-XML-Dialekts SpreadsheetML einschüchtern lassen, möchte ich mit diesem Snippet das Eis brechen und zeigen, wie einfach man mit XML-Technologie Excel-Dokumente erzeugen kann.

Die Referenz-Schemas für Office 2003 und damit auch für SpreadsheetML gibts hier zum kostenfreien Download: http://www.microsoft.com/downloads/details.aspx?FamilyId=FE118952-3547-420A-A412-00A2662442D9&displaylang=en
Tipp: Der XML-Editor von Visual Studio bietet Intellisense und Beschreibungstexte als Tiptext der Tags, wenn man die verwendeten Schemata angibt (Geht über das Fenster "Eigenschaften", Eigenschaft "Schemata").
Außerdem lässt sich sehr leicht herausfinden, wie Excel was in XML darstellt, indem man eine Excel-Mappe mit dem gewünschten Inhalt von Hand erstellt und im XML-Format speichert. Dann muss man sich nur noch das erzeugte XML zu Gemüte führen und schauen, was Excel gemacht hat.

Das Snippet besteht aus einer Klasse mit nur einer statischen Funktion. Diese Exportiert eine beliebige DataTable in ein neues Excel-Dokument.
Es ist nur ein Beispiel und exportiert alle Zellen als Strings. Mit einer Switch-Anweisung an der richtigen Stelle, lässt sich das aber sehr einfach erweitern.

Getestet wurde das Snippet mit .NET 2.0 und Excel 2003 Professional. Es sollte aber auch ohne Probleme mit Excel 2007 funktionieren.

using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Data;

namespace Rainbird.Examples.SpreadsheetML
{
    /// <summary>
    /// Enthält Hilfsfunktionen zum Erzeugen von Excel-Dateien mit SpreadsheetML.
    /// </summary>
    public class SpreadsheetMLHelper
    {
        /// <summary>
        /// Erzeugt aus einer DataTable ein Excel-XML-Dokument mit SpreadsheetML.
        /// </summary>        
        /// <param name="dataSource">Datenquelle, die in Excel exportiert werden soll</param>
        /// <param name="fileName">Dateiname der Ausgabe-XML-Datei</param>
        public static void ExportDataTableToWorksheet(DataTable dataSource, string fileName)
        {
            // XML-Schreiber erzeugen
            XmlTextWriter writer = new XmlTextWriter(fileName, Encoding.UTF8);

            // Ausgabedatei für bessere Lesbarkeit formatieren (einrücken etc.)
            writer.Formatting = Formatting.Indented;

            // <?xml version="1.0"?>
            writer.WriteStartDocument();

            // <?mso-application progid="Excel.Sheet"?>
            writer.WriteProcessingInstruction("mso-application", "progid=\"Excel.Sheet\"");

            // <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet >"
            writer.WriteStartElement("Workbook", "urn:schemas-microsoft-com:office:spreadsheet");

            // Definition der Namensräume schreiben 
            writer.WriteAttributeString("xmlns", "o", null, "urn:schemas-microsoft-com:office:office");
            writer.WriteAttributeString("xmlns", "x", null, "urn:schemas-microsoft-com:office:excel");
            writer.WriteAttributeString("xmlns", "ss", null, "urn:schemas-microsoft-com:office:spreadsheet");
            writer.WriteAttributeString("xmlns", "html", null, "http://www.w3.org/TR/REC-html40");

            // <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
            writer.WriteStartElement("DocumentProperties", "urn:schemas-microsoft-com:office:office");

            // Dokumenteingeschaften schreiben
            writer.WriteElementString("Author", Environment.UserName);
            writer.WriteElementString("LastAuthor", Environment.UserName);
            writer.WriteElementString("Created", DateTime.Now.ToString("u") + "Z");
            writer.WriteElementString("Company", "Unknown");
            writer.WriteElementString("Version", "11.8122");

            // </DocumentProperties>
            writer.WriteEndElement();

            // <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
            writer.WriteStartElement("ExcelWorkbook", "urn:schemas-microsoft-com:office:excel");

            // Arbeitsmappen-Einstellungen schreiben
            writer.WriteElementString("WindowHeight", "13170");
            writer.WriteElementString("WindowWidth", "17580");
            writer.WriteElementString("WindowTopX", "120");
            writer.WriteElementString("WindowTopY", "60");
            writer.WriteElementString("ProtectStructure", "False");
            writer.WriteElementString("ProtectWindows", "False");

            // </ExcelWorkbook>
            writer.WriteEndElement();

            // <Styles>
            writer.WriteStartElement("Styles");

            // <Style ss:ID="Default" ss:Name="Normal">
            writer.WriteStartElement("Style");
            writer.WriteAttributeString("ss", "ID", null, "Default");
            writer.WriteAttributeString("ss", "Name", null, "Normal");

            // <Alignment ss:Vertical="Bottom"/>
            writer.WriteStartElement("Alignment");
            writer.WriteAttributeString("ss", "Vertical", null, "Bottom");
            writer.WriteEndElement();

            // Verbleibende Sytle-Eigenschaften leer schreiben
            writer.WriteElementString("Borders", null);
            writer.WriteElementString("Font", null);
            writer.WriteElementString("Interior", null);
            writer.WriteElementString("NumberFormat", null);
            writer.WriteElementString("Protection", null);

            // </Style>
            writer.WriteEndElement();

            // </Styles>
            writer.WriteEndElement();

            // <Worksheet ss:Name="xxx">
            writer.WriteStartElement("Worksheet");
            writer.WriteAttributeString("ss", "Name", null, dataSource.TableName);

            // <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="3" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="60">
            writer.WriteStartElement("Table");
            writer.WriteAttributeString("ss", "ExpandedColumnCount", null, dataSource.Columns.Count.ToString());
            writer.WriteAttributeString("ss", "ExpandedRowCount", null, dataSource.Rows.Count.ToString());
            writer.WriteAttributeString("x", "FullColumns", null, "1");
            writer.WriteAttributeString("x", "FullRows", null, "1");
            writer.WriteAttributeString("ss", "DefaultColumnWidth", null, "60");

            // Alle Zeilen der Datenquelle durchlaufen
            foreach (DataRow row in dataSource.Rows)
            {
                // <Row>
                writer.WriteStartElement("Row");

                // Alle Zellen der aktuellen Zeile durchlaufen
                foreach (object cellValue in row.ItemArray)
                {
                    // <Cell>
                    writer.WriteStartElement("Cell");

                    // <Data ss:Type="String">xxx</Data>
                    writer.WriteStartElement("Data");
                    writer.WriteAttributeString("ss", "Type", null, "String");
                    
                    // Wenn die Zelle keinem leeren Datenbankwert entspricht ...
                    if (cellValue!=DBNull.Value)
                        // Zelleninhakt schreiben
                        writer.WriteValue(cellValue);

                    // </Data>
                    writer.WriteEndElement();

                    // </Cell>
                    writer.WriteEndElement();
                }
                // </Row>
                writer.WriteEndElement();
            }
            // </Table>
            writer.WriteEndElement();

            // <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
            writer.WriteStartElement("WorksheetOptions", "urn:schemas-microsoft-com:office:excel");

            // Seiteneinstellungen schreiben
            writer.WriteStartElement("PageSetup");
            writer.WriteStartElement("Header");
            writer.WriteAttributeString("x", "Margin", null, "0.4921259845");
            writer.WriteEndElement();
            writer.WriteStartElement("Footer");
            writer.WriteAttributeString("x", "Margin", null, "0.4921259845");
            writer.WriteEndElement();
            writer.WriteStartElement("PageMargins");
            writer.WriteAttributeString("x", "Bottom", null, "0.984251969");
            writer.WriteAttributeString("x", "Left", null, "0.78740157499999996");
            writer.WriteAttributeString("x", "Right", null, "0.78740157499999996");
            writer.WriteAttributeString("x", "Top", null, "0.984251969");
            writer.WriteEndElement();
            writer.WriteEndElement();

            // <Selected/>
            writer.WriteElementString("Selected", null);

            // <Panes>
            writer.WriteStartElement("Panes");

            // <Pane>
            writer.WriteStartElement("Pane");

            // Bereichseigenschaften schreiben
            writer.WriteElementString("Number", "1");
            writer.WriteElementString("ActiveRow", "1");
            writer.WriteElementString("ActiveCol", "1");

            // </Pane>
            writer.WriteEndElement();

            // </Panes>
            writer.WriteEndElement();

            // <ProtectObjects>False</ProtectObjects>
            writer.WriteElementString("ProtectObjects", "False");

            // <ProtectScenarios>False</ProtectScenarios>
            writer.WriteElementString("ProtectScenarios", "False");

            // </WorksheetOptions>
            writer.WriteEndElement();

            // </Worksheet>
            writer.WriteEndElement();

            // </Workbook>
            writer.WriteEndElement();

            // Datei auf Festplatte schreiben
            writer.Flush();
            writer.Close();
        }
    }
}

Schlagwörter: Excel,SpreadsheetML,Export,Exportieren,XML,Office,OLEDB

Hallo,

für die Interessierten dieser Technik möchte ich noch auf die kostenlose Library von CarlosAg
hinweisen.

Ergänzend zum sehr guten Einstiegsnippet sei noch erwähnt, daß die Stylesheets unabhängig vom Code gehalten werden können und dann mit der Klasse XslTransform aus dem Stylesheet und den Daten, der XML-Datei, die Exceldatei erzeugt werden kann.

Gruß falangkinjau

Danke für den Hinweis. Super Library!

Damit gehört fehleranfällige und lahme COM-Fernsteuerung zu 90% der Vergangenheit an.

Die hatte ich "vor Jahren" hier auch mal gepostet.

Wenn ihr euch für eine VS.NET Express registrieren lasst, bekommt ihr
auf der Bonus Seite auch eine Lizenz für Spreatsheetgear.
Das solltest Ihr euch auch mal anschauen.

Da hätte ich mal öfter die Forensuche benutzen sollen.

Aber es hat auch ein Gutes. Wenn man etwas ohne Hilfmittel "von Hand" gemacht hat, weiss man wie es wirklich funktioniert.

Ist richtig.

Auf codeproject ist auch ein artikel für vor MathML Zeiten, der das per html macht,
denn Excel versteht ja auch html.

Aber hast Du dir mal die Lib von Spreatsheetgear angeschaut?

Noch nicht, aber das werde ich nachholen.

coole sache, genau was ich suche, funzt auch perfekt 🙂

kann mir noch jemand sagen, wie ich die spaltennamen gleich mitexportieren kann?

thnx!

Du machst vor der Schleife für die DataRows einfach eine andere Schleife für die DataColumns und schreibst den ColumnName in die Excel-Zelle (selbes Verfahren).

tjo ich hab nur leider null ahnung von spreadsheetML 🤔

Wenn Du keine Ahnung hast, dann nimm einfach den Excel XML Writer: http://www.carlosag.net/Tools/ExcelXmlWriter/Default.aspx

Der kapselt SpreadsheetML in einem intuitiven Objektmodell. Einfacher gehts wirklich nicht mehr.

Danke rainbird, werd ich mir auf jeden fall anschaun.

Trotzdem bin ich neugierig und will ja was lernen 😉
Vl kannst du mir ja sagen wie es aussehen würde, wenn auch die spaltennamen mitexportiert werden sollen?!

kann mir aber auch jemand anders zeigen, nur keine scheu 😉

Hallo,
das snippet ist sher gut, bei mir läuft es fehlerfrei, nur möchte ich noch gerne wissen, wie ich den Pfad der Datei ändere.
Bishher ist die Datei nur im Debug ordner zu finden.
Außerdem möchte ich noch gerne wissen wie ich einzelne Zellen einfärben kann.

MFG LingLing

Hallo LingLing,

nur möchte ich noch gerne wissen, wie ich den Pfad der Datei ändere

Die Methode hat doch ein fileName-Parameter!? Da kannst du den Pfad mit übergeben. Und wegen den einfärben von Zellen, empfehle ich dir in die Spezifikationen von OpenXml zu schauen. Ist gar nicht so schwer.

Hi, der Thread ist doch schon etwas älter aber ich versuch es einfach einmal,
ich muss sagen diese Dll ist einfach Weltklasse und hat bis jetzt ihre zweck bei mir für eine einfache Ausgabe sehr gut erfüllt, wollte jetzt auch noch zusätzlich Rechnungsanhänge für Kunden generieren, in der einfachen Ausgabe funktionierts auch super, nur wenn ich mit WorksheetStyle arbeiten will, scheint das Programm diese auch zu generieren, aber wenn ich die generierten Files in Excel öffnen will bekomme ich einen Hinweis das es Probleme beim laden hab.
Vielleicht hat jemand von euch schon Erfahrung mit Styles hier

so erstelle ich styles

WorksheetStyle sozds = book.Styles.Add("dsoberezeile";);
sozds.Font.Bold = false;
sozds.Font.FontName = "Verdana";
sozds.Font.Size = 10;

so wende ich diese auf cellen an

row.Cells.Add(my.dr["Frachtbriefnummer"].ToString(), DataType.String, "dsoberezeile";);

wäre echt hilfreich, Vielen Dank im Voraus!