We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

Pivot Table code runs but No PivotTable, Rows, Columns, Data visible

Hi, I'm using XLSIO 12.2.0
I'm trying to create a Pivot Table from data added to the default worksheet of a new workbook from a datatable.

I've followed the examples given in the online help http://help.syncfusion.com/file-formats/xlsio/working-with-pivot-tables#create-a-pivot-table
Largely the code following "Following code example illustrates how to create a pivot table with existing data in the worksheet, using XlsIO."

The workbook that is created contains the data on the first sheet, the entire data is correctly assigned to the named range
A second sheet ("Pivot") is created, however this is empty ... other than a range A1:B3 seems to exist which contains no data, formatting, names, ...

Here's my code:
Stepping through I can see that 
pivotCache contains the correct number of rows and columns
pivotTable contains the correctly named fields from the raw data

      Dim rawDataSheet As IWorksheet = pivotBook.Worksheets(0)
      rawDataSheet.ImportDataTable(myDataTable, True, 1, 1)

      pivotBook.Names.Add("PivotRawDataRange").RefersToRange = rawDataSheet.Range(1, 1, myDataTable.Rows.Count + 1, myDataTable.Columns.Count)

      Dim pivotSheet As IWorksheet = pivotBook.Worksheets.Create("Pivot")

      Dim pivotCache As IPivotCache = pivotBook.PivotCaches.Add(pivotBook.Names("PivotRawDataRange").RefersToRange)
      Dim pivotTable As IPivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet("A1"), pivotCache)

      pivotTable.Fields(5).Axis = PivotAxisTypes.Page
      pivotTable.Fields(1).Axis = PivotAxisTypes.Row 
      pivotTable.Fields(10).Axis = PivotAxisTypes.Column 

      Dim field As IPivotField = pivotSheet.PivotTables(0).Fields(19) 
      pivotTable.DataFields.Add(field, "Sum", PivotSubtotalTypes.Sum)


      pivotBook.SaveAs("QuantaExtractKPI.xlsx", Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2010)


Any help much appreciated
thanks scott

3 Replies

AV Abirami Varadharajan Syncfusion Team July 26, 2016 10:24 AM UTC

Hi Scott, 
Thank you for contacting Syncfusion support. 
We are unable to reproduce the issue and have prepared a sample as per your scenario in which the pivot table is created properly. Kindly refer the sample from the following link and get back to us with your issue reproducing sample to provide a prompt solution at the earliest. 

SC Scott July 26, 2016 04:41 PM UTC

Hi Abirami,
Thanks for your help.

I've been able to fix my solution following your approach of including a template workbook in the App_Data folder.
I added the sample.xlsx to my solution (deleting the sample data from it) and added the two lines of code
      Dim fileName As String = Server.MapPath("App_data/Sample.xlsx")
      Dim pivotBook As IWorkbook = application.Workbooks.Open(fileName, ExcelOpenType.Automatic)

This replaces my declaration of pivotBook 
Dim pivotBook as IWorkbook
pivotBook = application.Workbooks.create(1)

Now I see the pivot table along with the raw data in separate sheets as required.

Thanks for your time

AV Abirami Varadharajan Syncfusion Team July 27, 2016 04:53 AM UTC

Hi Scott, 
Thank you for updating us. 
We are glad that the issue is resolved at your end. Please let us know if you need any further assistance. 

Live Chat Icon For mobile
Up arrow icon