|
当在大批量对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");
- }
-
-
-
-
-
-
-
- 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();
- }
- }
- }
- }
| |
