This project has moved and is read-only. For the latest updates, please go here.

PivotCache

Feb 12, 2012 at 10:02 AM
Edited Feb 12, 2012 at 10:03 AM

Hi,

>> First of all .... I Love this project! And I am really happy to have run into this before I started coding my own small version of you have already built.

- Greatly appreciated.

I'm investigating the possibility to replace the usage of Interop in my software with ClosedXML. This would in many cases make life easier! :o)

A feature I'm using very frequently is a pivotTable based on a pivotCache. This is by far the fastest way to enable easy dashboarding on large amounts of data in Excel. Not only does it write huge amounts of data fast to the Excel, but it will also improve Excel's performance. I've thoroughly tested the following approaches with the same demo data set of 30.000 records (below numbers are all rounded average values out of 10 attempts)

- Ms Jet writes the dataset to Excel in 42 seconds

- Ms Ace writes the dataset to Excel in 49 seconds

- ClosedXML writes the dataset to Excel in a whopping 18 seconds

- Interop writes the dataset to Excel in 574 seconds

- Interop writes the dataset to a pivotCache in 1 second

Although ClosedXML is -really- fast compared to the other approaches. It doesn't come close to the writing the data directly into a pivotcache using interop.

Is it possible to implement below example using ClosedXML? / Could you help me out with a code example? / I'm very interested in other opinions and experiences?

Cheers,

John

Example

 

 ''' <summary>
  ''' Writes results of a SQL query directly into pivotcache of ExcelFile
  ''' </summary>
  ''' <param name="WorksheetName">Name of the sheet where to display Pivot Table</param>
  ''' <param name="Connection">OleDb Connection String to Data source </param>
  ''' <param name="SQL">SQL Command to retrieve results from the Data Source</param>
  ''' <remarks></remarks>
  Public Sub QueryToPivotCache(ByVal WorksheetName As String, ByVal Connection As String, ByVal SQL As String)
    Try
      'In case the default port is used, to many pre-requisites have to be met in order to propertly connect (eg. named pipes, tcp/ip, etc.)
      'Prevent application from prompting

      '>> Add pivotCache object to -Workbook-, NOT to Worksheet, to limit size if a pivot is duplicated.
      Dim pivotCache As Microsoft.Office.Interop.Excel.PivotCache
      pivotCache = Workbook.PivotCaches().Add(XlPivotTableSourceType.xlExternal, Type.Missing)
      pivotCache.Connection = Connection
      pivotCache.MaintainConnection = False
      pivotCache.CommandText = SQL
      pivotCache.CommandType = XlCmdType.xlCmdSql

      'Check if the worksheet is already present
      If WorkSheetExists(WorksheetName) = True Then
        'Only do a refresh then
        RefreshPivotTables()
        Exit Sub
      Else
        'Add the sheet
        Dim Sheet As Worksheet = Workbook.Worksheets.Add
        With Sheet
          .Name = WorksheetName
          .Tab.Color = 255 '= red you can alter this to your liking
        End With

        'Add the PivotTable
        Dim pivotTables As Microsoft.Office.Interop.Excel.PivotTables
        Dim pivotTable As Microsoft.Office.Interop.Excel.PivotTable

        pivotTables = Sheet.PivotTables(Type.Missing)
        pivotTable = pivotTables.Add(pivotCache, Sheet.Cells(1, 1), WorksheetName, Type.Missing, Type.Missing)

        With pivotTable
          .SmallGrid = False
          .ShowTableStyleRowStripes = True
          .DisplayImmediateItems = True
          .ShowDrillIndicators = False
          .ColumnGrand = False
          .RowGrand = False
          .TableStyle2 = "PivotStyleLight1" '= Just some fancy canned style, adjust to your liking
        End With
      End If
    Catch ex As Exception
      HandleError(ex)
    End Try
  End Sub
Feb 13, 2012 at 12:43 AM

Even if I knew what interop is doing I still wouldn't be able to replicate it without implementing pivot tables.