Feedback

C# - Excel: DataTable mittels OLEDB in Excel-Dokument exportieren

Veröffentlicht von am 25.10.2006
(12 Bewertungen)
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.
Snippet in VB übersetzen
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();
        }
    }
}

Abgelegt unter Excel, Export, exportieren, OLEDB, DataTable, ADO.NET.

7 Kommentare zum Snippet

tomtom62 schrieb am 25.10.2006:
Ich hätte zumindest eine 9 gegeben, bin aber unsicher, weil keine Fehlerbehandlung implementiert ist. Zumindest einen Rückgabewert hätte ich mir gewünscht.
regalis schrieb am 12.03.2010:
Mit Excel 2007 und VS2008 Pro funktioniert das Snippet leider nicht mehr...
Es wirft eine Exception bei Zeile 151 (Also irgendwas scheint an dem Command nicht zu stimmen :( )
regalis schrieb am 21.03.2010:
Kann jemand vielleicht einen Workaround posten damit die Funktionalität wieder hergestellt ist?
Denn grundsätzlich gefällt mir das Snippet sehr gut...bis eben auf die Exception...
Wäre nett :)
Mario Noack schrieb am 23.03.2010:
Kann es sein, dass das Problem nur 64Bit ist... Lasst es mal als 32Bit laufen, die Jet-Treiber dürften als 64Bit nicht verfügbar sein.
regalis schrieb am 23.03.2010:
Das wäre ne Möglichkeit!
XNA läuft auch nicht als 64bit Anwendung..werde dies mal bei Gelegenheit ausprobieren.
Vielen Dank für den Tipp :)
regalis schrieb am 23.03.2010:
Also ich bekomme diese Fehlermeldung: Die externe Tabelle hat nicht das erwartete Format.
Ich habe in meiner Tabelle nur Strings...0o

[quote]************** Ausnahmetext **************
System.Data.OleDb.OleDbException: Die externe Tabelle hat nicht das erwartete Format.
bei System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
bei System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
bei System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
bei System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
bei System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
bei System.Data.OleDb.OleDbConnection.Open()
bei FilmDBExtractor.ExcelExport2.FillExcelSheet(DataTable table, String fileName) in C:\Users\regalis\Documents\Visual Studio 2008\Projects\FilmDBExtractor\FilmDBExtractor\ExcelExport.cs:Zeile 295.
bei FilmDBExtractor.Form1.ExportMoviesToExcel() in C:\Users\regalis\Documents\Visual Studio 2008\Projects\FilmDBExtractor\FilmDBExtractor\Form1.cs:Zeile 1090.
bei FilmDBExtractor.Form1.excelsheetErzeugenToolStripMenuItem_Click(Object sender, EventArgs e) in C:\Users\regalis\Documents\Visual Studio 2008\Projects\FilmDBExtractor\FilmDBExtractor\Form1.cs:Zeile 607.
bei System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
bei System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)
bei System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
bei System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
bei System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
bei System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
bei System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
bei System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea)
bei System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
bei System.Windows.Forms.Control.WndProc(Message& m)
bei System.Windows.Forms.ScrollableControl.WndProc(Message& m)
bei System.Windows.Forms.ToolStrip.WndProc(Message& m)
bei System.Windows.Forms.ToolStripDropDown.WndProc(Message& m)
bei System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
bei System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
bei System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)[/quote]
Nob schrieb am 16.06.2010:
Ich hatte das gleiche Problem: "Die externe Tabelle hat nicht das erwartete Format."
Die Lösung findet man hier:
http://www.codeproject.com/KB/office/JetOledbAndExcel.aspx
 

Logge dich ein, um hier zu kommentieren!