If You’re a VBA Developer You should know what best practices is to export data to MS Excel. Two expressions summarize it; Microsoft ActiveX Data Object Library and CopyFromRecordset.
However, if You’re a .NET developer and face a situation where You must find a solution to export data to MS Excel then there is a chance that You don’t know the best practices. In general, working on the .NET platform means that we use .NET classes to solve various tasks. When it comes to data acquiring two of the more popular approaches are to use ADO.NET and LINQ. However, as this blog article shows using classic ADO is the fastest way to dump data into a worksheet.
When lurking around various public Q&A forums I have noticed that the question “how to export data to MS Excel” is asked over and over. This article is the answer to that question and it will provide You with the best approach when it comes to speed.
Initially we need to set a reference to “Microsoft ActiveX Data Object x.x Library” where “x.x” refers to the version number in our .NET solution. Next, we must explicit add a reference to the Object Library which is done in the below code. Don’t forget to add the library to the prerequisites or copy the required files to the project.
Imports ADODB
Imports System.Text
Imports excel = Microsoft.Office.Interop.Excel
Public Class frmMain
Private Sub btnExport_Click(sender As System.Object, e As System.EventArgs) _
Handles btnExport.Click
Const stcon As String = _
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\NorthWind.accdb"
Const stsql As String = "SELECT * FROM Invoices"
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
'Open the database connection.
cnt.Open(stcon)
'Open the Recordset.
With rst
.CursorLocation = CursorLocationEnum.adUseClient
.Open(stsql, cnt, ADODB.CursorTypeEnum.adOpenForwardOnly, _
ADODB.LockTypeEnum.adLockReadOnly, _
ADODB.CommandTypeEnum.adCmdText)
.ActiveConnection = Nothing
End With
'Closing the database connection.
cnt.Close()
'Variables for Excel and the created workbook.
Dim xlapp As New excel.Application
Dim xlwbook As excel.Workbook = xlapp.Workbooks.Add( _
excel.XlWBATemplate.xlWBATWorksheet)
Dim xlwsheet As excel.Worksheet = _
CType(xlwbook.Worksheets(1), excel.Worksheet)
Dim xlrange As excel.Range = xlwsheet.Range("A2")
Dim xlcalc As excel.XlCalculation
'Temporarily turning off the auto calculation.
With xlapp
xlcalc = .Calculation
.Calculation = excel.XlCalculation.xlCalculationManual
End With
Dim ifieldcounter As Integer = Nothing
'Writing the field names to the worksheet.
For Each fld In rst.Fields
xlrange.Offset(0, ifieldcounter).Value = fld.Name
ifieldcounter = ifieldcounter + 1
Next
'Dump the recordset into the worksheet.
xlrange.Offset(1, 0).CopyFromRecordset(rst)
'Closing the recordset.
rst.Close()
With xlapp
.Visible = True
.UserControl = True
'Restore the calculation mode.
.Calculation = xlcalc
End With
fld = Nothing
rst = Nothing
cnt = Nothing
xlrange = Nothing
xlwsheet = Nothing
xlwbook = Nothing
xlapp = Nothing
End Sub
End Class
That’s it!
Kind regards,
Dennis
VSTO & .NET & Excel 