前面寫過兩篇excel操作的文章，有朋友質疑大批量數據下的性能問題，這個時候最好用批量復制的方法，可以用Excel的QueryTable來直接查詢數據庫，但是必須引用com組件。
參考代碼：
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Text;
usingSystem.Reflection;
usingExcel=Microsoft.Office.Interop.Excel;
namespaceConsoleApplication18
{
classProgram
{
staticvoidMain(string[]args)
{
ExportDataToExcel("Provider=SQLOLEDB.1;sever=localhost;uid=sa;passWord=***;database=master;",
"select*fromsysobjects",@"c:testOle.xls","sysobjects");
}
///<summary>
///直接導出數據到Excel
///</summary>
///<paramname="connectionString">連接字符串</param>
///<paramname="sql">查詢語句</param>
///<paramname="fileName">文件名</param>
///<paramname="sheetName">表名</param>
staticvoidExportDataToExcel(stringconnectionString,stringsql,stringfileName,stringsheetName)
{
Excel.Applicationapp=newExcel.ApplicationClass();
Excel.Workbookwb=(Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);
Excel.Worksheetws=wb.Worksheets.Add(Missing.Value,Missing.Value,Missing.Value,Missing.Value)asExcel.Worksheet;
ws.Name=sheetName;
try
{
Excel.QueryTableqt=ws.QueryTables.Add("OLEDB;"+connectionString,
ws.get_Range("A1",Missing.Value),sql);
qt.Refresh(false);//是否異步查詢
}
catch(Exceptionex)
{
stringstr=ex.Message;
}
finally
{
wb.Saved=true;
wb.SaveCopyAs(fileName);//保存
app.Quit();//關閉進程
}
}
}
}