PivotTables are a powerful tool in Excel – one interesting feature is the ability to use large sets of data without having the raw data present on an Excel Worksheet or available through the connection types that Excel/Microsoft provide. This expands the number of possible datasources and frees the PivotTable from the data storage limits of a single worksheet.
The PivotCache.Recordset property is the key to this functionality, it takes an ADO recordset and uses it as the data for the PivotTable (from your language of choice!). Good stuff – but this poses an immediate problem in .net code if you are restricted to/committed to getting your data via ADO.NET – how to create the ADO recordset?
There are two possible solutions that I have found –
XML Conversion
One approach to the problem is to use XML and transform the data. I think the links below provide good information and code for this method.
http://support.microsoft.com/kb/316337/
http://www.codeproject.com/dotnet/ADOConversion.asp
http://codebetter.com/blogs/brendan.tompkins/archive/2004/04/27/12229.aspx
‘Direct’ Conversion
The XML solutions above are fascinating – but in my own code I liked the approach that is covered in this article:
http://www.codeproject.com/cs/database/DataTableToRecordset.asp
My coding skills are NOT good enough to easily follow the xml conversion examples – likely part of the reason that I am partial to the ‘Direct’ approach. However, even if your xml coding knowledge is vastly superior to mine, it is interesting to notice the difference in code length and complexity between the XML solutions (longer/more complex) and direct conversion method (shorter/simple!).
I have used the code below for several months in an application that: creates a datatable from a query to a user-instance of SQL Express 2005 (using ADO.NET), converts the datatable (with the code below) into an ADO recordset and pushes it into an Excel PivotTable via the RecordSet property. Performance may be an issue in some situations – in my application there is certainly a pause during the conversion process, but at this point it seems acceptable (the conversion does not seem to take any longer than getting the queried data from the DB).
Imports System.Data
Imports System.IO
Imports Microsoft.Office.Interop
Public Class ConvertDataTableToAdoRs
Public Shared Function ConvertToRecordset(ByVal inTable As DataTable) _
As ADODB.Recordset
'=============================================
'
'This is a VB conversion of the code found here:
'http://www.codeproject.com/cs/database/DataTableToRecordset.asp
'Please see the original link for a C# version and to read the
'original article.
'
'=============================================
Dim result As ADODB.Recordset = New ADODB.Recordset()
result.CursorLocation = ADODB.CursorLocationEnum.adUseClient
Dim resultFields As ADODB.Fields = result.Fields
Dim inColumns As System.Data.DataColumnCollection = inTable.Columns
For Each inColumn As DataColumn In inColumns
resultFields.Append(inColumn.ColumnName, _
TranslateType(inColumn.DataType), _
inColumn.MaxLength, _
ADODB.FieldAttributeEnum.adFldIsNullable, _
Nothing)
Next
result.Open(System.Reflection.Missing.Value _
, System.Reflection.Missing.Value _
, ADODB.CursorTypeEnum.adOpenStatic _
, ADODB.LockTypeEnum.adLockOptimistic)
For Each dr As DataRow In inTable.Rows
result.AddNew(System.Reflection.Missing.Value, _
System.Reflection.Missing.Value)
For columnIndex As Integer = 0 To inColumns.Count - 1
resultFields(columnIndex).Value = dr(columnIndex)
Next
Next
Return result
End Function
Shared Function TranslateType(ByVal columnType As Type) As ADODB.DataTypeEnum
Select Case columnType.UnderlyingSystemType.ToString()
'=============================================
'
'This is a VB conversion of the code found here:
'http://www.codeproject.com/cs/database/DataTableToRecordset.asp
'Please see the original link for a C# version and to read the
'original article.
'
'=============================================
Case "System.Boolean"
Return ADODB.DataTypeEnum.adBoolean
Case "System.Byte"
Return ADODB.DataTypeEnum.adUnsignedTinyInt
Case "System.Char"
Return ADODB.DataTypeEnum.adChar
Case "System.DateTime"
Return ADODB.DataTypeEnum.adDate
Case "System.Decimal"
Return ADODB.DataTypeEnum.adCurrency
Case "System.Double"
Return ADODB.DataTypeEnum.adDouble
Case "System.Int16"
Return ADODB.DataTypeEnum.adSmallInt
Case "System.Int32"
Return ADODB.DataTypeEnum.adInteger
Case "System.Int64"
Return ADODB.DataTypeEnum.adBigInt
Case "System.SByte"
Return ADODB.DataTypeEnum.adTinyInt
Case "System.Single"
Return ADODB.DataTypeEnum.adSingle
Case "System.UInt16"
Return ADODB.DataTypeEnum.adUnsignedSmallInt
Case "System.UInt32"
Return ADODB.DataTypeEnum.adUnsignedInt
Case "System.UInt64"
Return ADODB.DataTypeEnum.adUnsignedBigInt
End Select
'Note Strings are not cased and will return here:
Return ADODB.DataTypeEnum.adVarChar
End Function
Public Shared Sub DataTableToRange(ByVal anchorCell As Excel.Range, _
ByVal tableToCopy As DataTable, _
Optional ByVal tableHeader As String = "")
If tableHeader <> "" Then
Try
anchorCell.Value = tableHeader
anchorCell = anchorCell.Offset(1, 0)
Catch ex As Exception
End Try
End If
Dim tableHeaderOffset As Integer = 0
For Each loopHeaders As DataColumn In tableToCopy.Columns
Try
anchorCell.Offset(0, tableHeaderOffset).Value = loopHeaders.ColumnName
Catch ex As Exception
End Try
tableHeaderOffset += 1
Next
anchorCell.Offset(1, 0).CopyFromRecordset(ConvertToRecordset(tableToCopy))
End Sub
End Class
The last function in this class is an interesting way to get information in Excel – certainly there are many other ways to get data into Excel from a DataTable without conversion to an ADO recordset. However, DataTableToRange is quick and easy with the conversion code written and ready-to-use.
Comments welcome!
CM
Thank you very much for the useful code !
Best regards,
Luc VDP
Excellent, very helpful indeed! Thanks a lot, appreciate this article.
This is GREAT code!!!!!; I was looking for this long time ago
I had a problem when, in some field; there are special caracters like “�”(This char come for other systems), but it was easy to solve
Geco – Glad this was helpful and that you were able to easily solve your special characters problem. I have made some small changes over the years, but I am still using this basic solution!
CM