C#後期綁定操作Excel

後期綁定的好處是：對于不同版本的軟體都能支援。如果機器A上安裝的Office 2003，機器B上安裝Office 2007，沒問題，下面代碼都能正常運作。但是如果采用前期綁定就不行了。關于“前期綁定和後期綁定”，“Excel專業開發”一書的3.3.3節（43頁）說的非常清楚。

                private void Test()

                {

                        DataTable table = new DataTable();

                        table.Columns.Add("ID", System.Type.GetType("System.Int32"));

                        table.Columns.Add("Name", System.Type.GetType("System.String"));

                        for (int i = 0; i < 22; i++)

                        {

                                DataRow row = table.NewRow();

                                row["ID"] = i;

                                row["Name"] = "name" + i;

                                table.Rows.Add(row);

                        }

                        try

                                this.Cursor = Cursors.AppStarting;

                                //例如在中文系統下安裝的是英文的Office，就需要指定CultureInfo為en-US

                                ExportToExcel(table, new System.Globalization.CultureInfo("en-US"));

                                this.Cursor = Cursors.Default;

                        catch (Exception ex)

                                MessageBox.Show("Exception \n" + ex.Message + "\nStack Trace: \n" + ex.StackTrace.ToString(), "資訊提示",

                                        MessageBoxButtons.OK, MessageBoxIcon.Information);

                }

                public void ExportToExcel(System.Data.DataTable table)

                        ExportToExcel(table, System.Globalization.CultureInfo.CurrentCulture);

                public void ExportToExcel(System.Data.DataTable table, System.Globalization.CultureInfo cultureInfoOfOffice)

                        object excel;

                        object book;

                        object books;

                        object sheet;

                        object sheets;

                        object range;

                        object[] parameters;

                        Type ExcelType;

                        parameters = new object[1];

                        //擷取Excel類型

                        ExcelType = Type.GetTypeFromProgID("Excel.Application");

                        excel = Activator.CreateInstance(ExcelType);

                        //擷取workbooks集合

                        books = excel.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, excel, null);

                        //新增workbook.

                        //BUG：自動化 Excel 時出現“格式太舊或是類型庫無效”錯誤

                        //http://support.microsoft.com/kb/320369/zh-cn

                        //如果滿足以下條件，在調用某個 Excel 方法時會收到此錯誤：

                        //* 該方法需要一個 LCID（區域設定辨別符）。

                        //* 運作的是英語版本的 Excel。但是，計算機的區域設定是針對非英語語言配置的。

                        //如果用戶端計算機運作的是英語版本的 Excel 并且目前使用者的區域設定配置為英語之外的某個語言，則 Excel 将嘗試查找針對所配置語言的語言包。

                        //如果沒有找到所需語言包，則會報告錯誤。 

                        book = books.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, books, null, cultureInfoOfOffice);

                        //擷取worksheets集合

                        sheets = book.GetType().InvokeMember("Worksheets", BindingFlags.GetProperty, null, book, null);

                        //擷取第一個 worksheet.

                        parameters[0] = 1;

                        sheet = sheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, sheets, parameters);

                        //擷取A1單元格所在區域

                        object[] header = new Object[table.Columns.Count];

                        parameters[0] = "A1:" + Convert.ToString(Convert.ToChar(64 + table.Columns.Count)) + "1";

                        range = sheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, sheet, parameters);

                        //在A1單元格中填充資料.

                        for (int i = 0; i < table.Columns.Count; i++)

                                header[i] = table.Columns[i].ToString();

                        parameters[0] = header;

                        range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, range, parameters, cultureInfoOfOffice);

                        parameters = new object[2];

                        parameters[0] = "A2:" + Convert.ToString(Convert.ToChar(64 + table.Columns.Count)) + (table.Rows.Count + 1).ToString().Trim();

                        parameters[1] = Missing.Value;

                        Object[,] data = new Object[table.Rows.Count, table.Columns.Count];

                        for (int i = 0; i < table.Rows.Count; i++)

                                for (int j = 0; j < table.Columns.Count; j++)

                                {

                                        data[i, j] = table.Rows[i][j].ToString();

                                }

                        parameters[0] = data;

                        //啟動 Excel

                        parameters[0] = true;

                        excel.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, excel, parameters);

                        excel.GetType().InvokeMember("UserControl", BindingFlags.SetProperty, null, excel, parameters);

DataTable的使用技巧可參考：

http://www.cnblogs.com/dreamof/archive/2008/07/31/1257660.html

本文轉自 h2appy  51CTO部落格，原文連結：http://blog.51cto.com/h2appy/240830，如需轉載請自行聯系原作者