VSTO & .NET & Excel

January 30, 2012

Best Practices Export Data to Excel

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

Advertisements

January 23, 2012

New Q&A Forum for PowerPivot!

Filed under: .NET & Excel, Excel, Forums, PowerPivot Books, VSTO & Excel — Dennis M Wallentin @ 1:23 am

With the release of Excel 2010 Microsoft released a new powerful tool, PowerPivot, to work with large datasets and reports. The beta of version 2.0 has been out for a while and it seems that Microsoft is about to ship the final version soon.

Meanwhile, I was pleasant surprised to learn that Mr Excel has recently launched a public Q&A Forum that explicit target PowerPivot. This is a welcome contribution as it’s seems that the number of users (and questions) is increasing.

For more information and participation please use the following link: MrExcel PowerPivot Q&A Forum.

Good luck!

Kind regards,
Dennis

PS: If You are new to PowerPivot and would like to take part of a summary may read Rob Collie’s blog post Top 5 Ways PowerPivot Helps Excel Pros

Create a free website or blog at WordPress.com.