[攒分贴]用QueryTable向excel批量导入数据

jinjazz 2008-08-05 02:13:02

加精

前面写过两篇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");

        }

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

            }

        }

    }

}