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. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

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

Thread ID:





125142 Jul 25,2016 09:26 AM UTC Jul 27,2016 04:53 AM UTC ASP.NET Web Forms 3
Tags: XlsIO
Asked On July 25, 2016 09:26 AM UTC

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

Abirami Varadharajan [Syncfusion]
Replied On 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. 

Replied On 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

Abirami Varadharajan [Syncfusion]
Replied On 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. 


This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon