和前面一篇用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();//关闭进程
}
}
}
}
Blog同步更新
http://blog.csdn.net/jinjazz/archive/2008/08/06/2775725.aspx
我的历史攒分帖子
http://blog.csdn.net/jinjazz/category/407229.aspx希望大家支持我的blog
我们需要引用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();//关闭进程
}
}
}
}
Blog同步更新
http://blog.csdn.net/jinjazz/archive/2008/08/06/2775725.aspx
我的历史攒分帖子
http://blog.csdn.net/jinjazz/category/407229.aspx希望大家支持我的blog
range.CopyFromRecordset(rs, 65535, 255);
excel2007之前也不支持65535以上的数据
http://topic.csdn.net/u/20080805/14/a6f56d54-e009-4dce-99c6-c27ce40dd4a5.html
然后就用ado打开excel文件,导入数据,这样就不用Excel的com对象了
没见过这种写法,这句话如何理解?谢谢