C#用QueryTable向excel批量导入数据

必须引用com组件

参考代码：

1. using System;
2. using System.Collections.Generic;
3. using System.Text;
4. using System.Reflection;
5. using Excel = Microsoft.Office.Interop.Excel;
6. namespace ConsoleApplication18
7. {
8.     class Program
9.     {
10.         static void Main(string[] args)
11.         {
12.             ExportDataToExcel("Provider=SQLOLEDB.1;sever=localhost;uid=sa;password=***;database=master;",
13.                 "select * from sysobjects",@"c:/testOle.xls","sysobjects");
14.         }
15.         /// <summary>
16.         /// 直接导出数据到excel
17.         /// </summary>
18.         /// <param name="connectionString">连接字符串</param>
19.         /// <param name="sql">查询语句</param>
20.         /// <param name="fileName">文件名</param>
21.         /// <param name="sheetName">表名</param>
22.         static void ExportDataToExcel(string connectionString,string sql,string fileName,string sheetName)
23.         {
24.             Excel.Application app = new Excel.ApplicationClass();
25.             Excel.Workbook wb = (Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);
26.             Excel.Worksheet ws = wb.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet;
27.             ws.Name = sheetName;
28.             try
29.             {
30.                 Excel.QueryTable qt = ws.QueryTables.Add("OLEDB;" + connectionString,
31.                     ws.get_Range("A1", Missing.Value), sql);
32.                 qt.Refresh(false);//是否异步查询
33.             }
34.             catch (Exception ex)
35.             {
36.                 string str = ex.Message;
37.             }
38.             finally
39.             {
40.                 wb.Saved = true;
41.                 wb.SaveCopyAs(fileName);//保存
42.                 app.Quit();//关闭进程
43.             }
44.         }
45.     }
46. }