用QueryTable向excel批量導入數據

前面寫過兩篇excel操作的文章，有朋友質疑大批量數據下的性能問題，這個時候最好用批量復制的 方法，可以用excel的QueryTable來直接查詢數據庫，但是必須引用com組件。

參考代碼：

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
namespace ConsoleApplication18
{
    class Program
    {
        static void Main(string[] args)
        {
            ExportDataToExcel("Provider=SQLOLEDB.1;sever=localhost;uid=sa;password=***;database=master;",
                "select * from sysobjects",@"c:\testOle.xls","sysobjects");
        }
        /// <summary>
        /// 直接導出數據到excel
        /// </summary>
        /// <param name="connectionString">連接字符串</param>
        /// <param name="sql">查詢語句</param>
        /// <param name="fileName">文件名</param>
        /// <param name="sheetName">表名</param>
        static void ExportDataToExcel(string connectionString,string sql,string fileName,string sheetName)
        {
            Excel.Application app = new Excel.ApplicationClass();
            Excel.Workbook wb = (Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);
            Excel.Worksheet ws = wb.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet;
            ws.Name = sheetName;
            try
            {
                Excel.QueryTable qt = ws.QueryTables.Add("OLEDB;" + connectionString,
                    ws.get_Range("A1", Missing.Value), sql);
                qt.Refresh(false);//是否異步查詢
            }
            catch (Exception ex)
            {
                string str = ex.Message;
            }
            finally
            {
                wb.Saved = true;
                wb.SaveCopyAs(fileName);//保存
                app.Quit();//關閉進程
            }
        }
    }
}