[攒分贴]用QueryTable向excel批量导入数据,该如何处理

[攒分贴]用QueryTable向excel批量导入数据
前面写过两篇excel操作的文章，有朋友质疑大批量数据下的性能问题，这个时候最好用批量复制的方法，可以用excel的QueryTable来直接查询数据库，但是必须引用com组件。

参考代码

C# code

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();//关闭进程            }        }    }}