asp.net教程:用ADO向Excel批量导入数据 和前面一篇用OleDB的方法类似,我们可以用ADO从RecordSet对象向Excel批量插入数据,这个方法无法自动复制字段名。
我们需要引用ADO和Excel的com对象
参考代码如下
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Text;
usingSystem.Reflection;
usingExcel=Microsoft.Office.Interop.Excel;
{
ExportDataToExcel("server=(local);uid=sa;pwd=sqlgis;database=master",
"select*fromsysobjects",@"c:testADO.xls","sysobjects");
}
staticvoidExportDataToExcel(stringconnectionString,stringsql,stringfileName,stringsheetName)
{
Excel.Applicationapp=newExcel.ApplicationClass();
Excel.Workbookwb=(Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);
Excel.Worksheetws=wb.Worksheets.Add(Missing.Value,Missing.Value,Missing.Value,Missing.Value)asExcel.Worksheet;
ws.Name=sheetName;
try
{
ADODB.Connectionconn=newADODB.ConnectionClass();
conn.Open("driver={SQLServer};"+connectionString,"","",0);
ADODB.Recordsetrs=newADODB.RecordsetClass();
rs.Open(sql,conn,ADODB.CursorTypeEnum.adOpenStatic,ADODB.LockTypeEnum.adLockReadOnly,0);
Excel.Rangerange=ws.get_Range("A2",Missing.Value);
range.CopyFromRecordset(rs,65535,65535);
}
catch(Exceptionex)
{
stringstr=ex.Message;
}
finally
{
wb.Saved=true;
wb.SaveCopyAs(fileName);//保存
app.Quit();//关闭进程
}
}
}
}
usingSystem.Collections.Generic;
usingSystem.Text;
usingSystem.Reflection;
usingExcel=Microsoft.Office.Interop.Excel;
namespaceConsoleApplication18
{
classProgram
{
staticvoidMain(
{
ExportDataToExcel("server=(local);uid=sa;pwd=sqlgis;database=master",
"select*fromsysobjects",@"c:testADO.xls","sysobjects");
}
staticvoidExportDataToExcel(stringconnectionString,stringsql,stringfileName,stringsheetName)
{
Excel.Applicationapp=newExcel.ApplicationClass();
Excel.Workbookwb=(Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);
Excel.Worksheetws=wb.Worksheets.Add(Missing.Value,Missing.Value,Missing.Value,Missing.Value)asExcel.Worksheet;
ws.Name=sheetName;
try
{
ADODB.Connectionconn=newADODB.ConnectionClass();
conn.Open("driver={SQLServer};"+connectionString,"","",0);
ADODB.Recordsetrs=newADODB.RecordsetClass();
rs.Open(sql,conn,ADODB.CursorTypeEnum.adOpenStatic,ADODB.LockTypeEnum.adLockReadOnly,0);
Excel.Rangerange=ws.get_Range("A2",Missing.Value);
range.CopyFromRecordset(rs,65535,65535);
}
catch(Exceptionex)
{
stringstr=ex.Message;
}
finally
{
wb.Saved=true;
wb.SaveCopyAs(fileName);//保存
app.Quit();//关闭进程
}
}
}
}