用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*fromsysobjects" ,@ "c:\testOle.xls" , "sysobjects" );
- }
- ///<summary>
- ///直接导出数据到excel
- ///</summary>
- ///<paramname="connectionString">连接字符串</param>
- ///<paramname="sql">查询语句</param>
- ///<paramname="fileName">文件名</param>
- ///<paramname="sheetName">表名</param>
- static void ExportDataToExcel( string connectionString, string sql, string fileName, string sheetName)
- {
- Excel.Applicationapp= new Excel.ApplicationClass();
- Excel.Workbookwb=(Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);
- Excel.Worksheetws=wb.Worksheets.Add(Missing.Value,Missing.Value,Missing.Value,Missing.Value) as Excel.Worksheet;
- ws.Name=sheetName;
- try
- {
- Excel.QueryTableqt=ws.QueryTables.Add( "OLEDB;" +connectionString,
- ws.get_Range( "A1" ,Missing.Value),sql);
- qt.Refresh( false ); //是否异步查询
- }
- catch (Exceptionex)
- {
- string str=ex.Message;
- }
- finally
- {
- wb.Saved= true ;
- wb.SaveCopyAs(fileName); //保存
- app.Quit(); //关闭进程
- }
- }
- }
- }
相关文章