Excel: DataTable mittels OLEDB in Excel-Dokument exportieren

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158

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