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.
GFU-Schulungen  [Anzeige]

C# 2017/2015/2013 Aufbau

In dieser Schulung lernen Sie fortgeschrittene Techniken im Bereich .Net C#. Dabei stehen neben den eigentlichen Techniken auch architektonische Aspekte im Mittelpunkt.

VB.NET Aufbau

Sie verfügen nach der Schulung über fundierte Kenntnisse in der Arbeit mit objektorientierten Modellen in VB.NET und können wiederverwendbare Komponenten eigenständig erzeugen.

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!