jl99000

使用 ADO.NET 将数据传输到工作表

// Establish a connection to the data source.
System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(
	"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_strSampleFolder +
	"Book7.xls;Extended Properties=Excel 8.0;");
objConn.Open();

// Add two records to the table named 'MyTable'.
System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand();
objCmd.Connection = objConn;
objCmd.CommandText = "Insert into MyTable (FirstName, LastName)" +
	" values ('Bill', 'Brown')";
objCmd.ExecuteNonQuery();
objCmd.CommandText = "Insert into MyTable (FirstName, LastName)" +
	" values ('Joe', 'Thomas')";
objCmd.ExecuteNonQuery();

// Close the connection.
objConn.Close();
		

和前面一篇用OleDB的方法类似，我们可以用ADO从RecordSet对象向Excel批量插入数据，这个方法无法自动复制字段名。
我们需要引用ADO和Excel的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("server=(local);uid=sa;pwd=sqlgis;database=master",
                "select * from sysobjects",@"c:\testADO.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
            {
                ADODB.Connection conn = new ADODB.ConnectionClass();
                conn.Open("driver={SQL Server};"+connectionString,"","",0);
                ADODB.Recordset rs = new ADODB.RecordsetClass();
                rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, 0);
                Excel.Range range = ws.get_Range("A2", Missing.Value);
                range.CopyFromRecordset(rs, 65535, 65535);
            }
            catch (Exception ex)
            {
                string str = ex.Message;
            }
            finally
            {
                wb.Saved = true;
                wb.SaveCopyAs(fileName);//保存
                app.Quit();//关闭进程
            }
        }
    }
}