Excel: DataTable mittels OLEDB in Excel-Dokument exportieren

Beschreibung:

Dieses Snippet erzeugt aus einer gewöhnlichen DataTable ein neues Excel-Dokument. Die Excel-Zellen werden mit OLEDB-Datenzugriff gefüllt (Also wesentlich schneller als COM-Automatisierung). Das Snippet sollte ab Excel 97 oder höher laufen. Getestet wurde es mit Excel 2000.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Runtime.Remoting;
using System.Reflection;
using System.Runtime.InteropServices;

namespace Rainbird.Examples.Office.Excel.OLEDBAccess
{
    /// <summary>
    /// Exportiert den Inhalt einer DataTable in ein neues Excel-Dokument.
    /// </summary>
    public class ExcelExport
    {
        /// <summary>
        /// Privater Standardkonstruktor.
        /// </summary>
        private ExcelExport() { }

        /// <summary>
        /// Schreibt das Schema einer bestimmten Tabelle in eine neue Excel-Datei.
        /// </summary>
        /// <param name="table">Tabelle</param>
        /// <param name="fileName">Dateiname</param>
        public static void WriteTableSchemaToExcelFile(DataTable table, string fileName)
        {
            // Excel im Hintergrund öffnen
            object excel = Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application"));

            // Auflistung der Mappeen abrufen
            object books = excel.GetType().InvokeMember("Workbooks", BindingFlags.IgnoreCase | BindingFlags.GetProperty | BindingFlags.OptionalParamBinding, null, excel, new object[0]);

            // Neue Mappe erstellen
            object book = books.GetType().InvokeMember("Add", BindingFlags.InvokeMethod | BindingFlags.OptionalParamBinding, null, books, new object[0]);

            // Auflistung der Tabellenblätter abrufen
            object sheets = book.GetType().InvokeMember("Sheets", BindingFlags.IgnoreCase | BindingFlags.GetProperty | BindingFlags.OptionalParamBinding, null, book, new object[0]);

            // Neues Tabellenblatt erstellen
            object sheet = sheets.GetType().InvokeMember("Add", BindingFlags.InvokeMethod | BindingFlags.OptionalParamBinding, null, sheets, new object[0]);

            // Name des Tabellenblatts festlegen
            sheet.GetType().InvokeMember("Name", BindingFlags.SetProperty, null, sheet, new object[1] { table.TableName });

            // Zähler
            int i = 0;

            // Spalten der Tabelle durchlaufen
            foreach (DataColumn column in table.Columns)
            {
                // Zähler erhöhen
                i++;

                // Feldnamen einfügen
                object range = sheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty | BindingFlags.OptionalParamBinding, null, sheet, new object[2] { 1, i });
                range.GetType().InvokeMember("Value", BindingFlags.SetProperty | BindingFlags.OptionalParamBinding, null, range, new object[1] { column.ColumnName });
            }

            // Dokument speichern
            book.GetType().InvokeMember("SaveAs", BindingFlags.InvokeMethod | BindingFlags.OptionalParamBinding, null, book, new object[1] { fileName });

            // COM-Verweise freigeben
            Marshal.ReleaseComObject(sheet);
            Marshal.ReleaseComObject(sheets);
            Marshal.ReleaseComObject(book);
            Marshal.ReleaseComObject(books);

            // Excel schließen
            excel.GetType().InvokeMember("Quit", BindingFlags.InvokeMethod, null, excel, new object[0]);

            // Excel.Application COM-Verweis freigeben
            Marshal.ReleaseComObject(excel);
        }

        /// <summary>
        /// Erzeugt eine OLEDB-Verbindungszeichenfolge für ein bestimmtes Excel-Dokument.
        /// </summary>
        /// <param name="fileName">Dateiname (.XLS)</param>
        /// <returns>Verbindungszeichenfolge</returns>
        private static string BuidExcelConnectionString(string fileName)
        {
            // Verbindungszeichenfolge erzeugen un zurückgeben
            return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0";
        }

        /// <summary>
        /// Erzeugt aus einer Tabelle ein Excel-Dokument.
        /// </summary>
        /// <param name="table">Tabelle</param>
        /// <param name="fileName">Dateiname des Ziel-Excel-Dokuments</param>
        public static void FillExcelSheet(DataTable table, string fileName)
        {
            // Neue leere Excel-Datei aus dem Tabellenschema erzeugen
            WriteTableSchemaToExcelFile(table, fileName);

            // Verbindungszeichenfolge erzeugen
            string connectionString = BuidExcelConnectionString(fileName);

            // Neue OLEDB-Verbindung erzeugen
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();

            // String-Generator für Parameter erzeugen
            StringBuilder parameterBuilder = new StringBuilder(") VALUES (");

            // Spalten zählen
            int columnCount=table.Columns.Count;

            // INSERT SQL-Anweisung für Excel erzeugen
            StringBuilder builder = new StringBuilder("INSERT INTO [");
            builder.Append(table.TableName);
            builder.Append("$] (");

            // Alle Spalten durchlaufen
            for (int i = 0; i < columnCount; i++)
            {
                // Spaltennamen anfügen
                builder.Append(table.Columns[i].ColumnName);

                // Parameter anfügen
                parameterBuilder.Append("?");

                // Wenn eine weitere Spalte folgt ...
                if (i < (columnCount - 1))
                {
                    // Kommas anfügen
                    builder.Append(",");
                    parameterBuilder.Append(",");
                }
            }
            // SQL-Anweisung fertigstellen
            builder.Append(parameterBuilder.ToString());
            builder.Append(")");
            string insertStatement = builder.ToString();
            
            // Preisliste durchlaufen
            foreach (DataRow row in table.Rows)
            {
                // Neuen OLEDB-Befehl erzeugen
                OleDbCommand command = new OleDbCommand(insertStatement, connection);

                // Alle Spalten durchlaufen
                foreach(DataColumn column in table.Columns)
                {
                    // Parameter übergeben                    
                    command.Parameters.Add(new OleDbParameter(column.ColumnName,row[column]));
                }
                // Befehl ausführen
                command.ExecuteNonQuery();
            }
            // Verbindung schließen
            connection.Close();
        }
    }
}

Schlagwörter: Excel, Export, exportieren, OLEDB, DataTable, ADO.NET

Quelle: .NET-Snippets

kann man denn bevor man den datatable einfügt auch noch in zellen schreiben ?

würde nämlich gern ne Überschrift drüber setzten smile

MfG haxXxy

Klar kann man. Dieses Snippet ist nur ein schlichtes Beispiel und wartet nicht mit grafischen Finessen auf. Aber Du kannst doch das Excel-Dokument nach dem Einfügen der Daten mittels Excel-Objektmodell formatieren und weiter bearbeiten.

Super Sache, funk leider unter 2007 nicht.

Fehler:
Altes Format oder ungültige Typbibliothek. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))
Bei
[php]
// Neue Mappe erstellen
object book = books.GetType().InvokeMember("Add", BindingFlags.InvokeMethod | BindingFlags.OptionalParamBinding, null, books, new object[0]);
[/php]

Hallo bigpoint,

ich hatte leider keine Gelegenheit, es mit Version 2007 zu testen.
Als Alternative kann ich Dir aber folgendes anbieten: Excel-Export ohne Excel (Snippet)
(Wurde allerdings auch nur mit Excel 2003 Pro getestet, sollte aber auch mit 2007 laufen).

Hallo,

ist zwar schonälter dieses Thema aber hab ein Problem mit dem Snippet. Grundsätzlich funktioniert alles wunderbar, wenn ich jedoch in meiner DataTable sehr lange Texte stehen hab, die auch noch Zeilenumbrüche haben, dann bekomm ich folgende Fehlermeldung:

"Das Feld ist zu klein für die Datenmenge, die sie hinzufügen wollten. Versuchen Sie, weniger Daten einzufügen OleDbException"

Ich würde jetzt mal vermuten, dass man hier irgendwo das Format der Zellen anpassen muss. Frage ist nur WO und WIE. Jemand eine Idee?

Vielen Dank schonmal!