asp.net教程:用ADO向Excel批量导入数据 和前面一篇用OleDB的方法类似,我们可以用ADO从RecordSet对象向Excel批量插入数据,这个方法无法自动复制字段名。
我们需要引用ADO和Excel的com对象
参考代码如下
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Text;
usingSystem.Reflection;
usingExcel = Microsoft.Office.Interop.Excel;
namespaceConsoleApplication18
{
classProgram
{
staticvoidMain( string []args)
{
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( string []args)
{
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(); // 关闭进程
}
}
}
}