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.
Unfortunately, activation email could not send to your email. Please try again.

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

Thread ID:

Created:

Updated:

Platform:

Replies:

125142 Jul 25,2016 05:26 AM Jul 27,2016 12:53 AM ASP.NET Web Forms 3
loading
Tags: XlsIO
Scott
Asked On July 25, 2016 05:26 AM

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")
      pivotSheet.Activate()

      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)

      pivotSheet.Activate()

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

      pivotBook.Close()
      qExcelEngine.Dispose()

Any help much appreciated
thanks scott


Abirami Varadharajan [Syncfusion]
Replied On July 26, 2016 06:24 AM

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. 
 
 
Regards, 
Abirami. 


Scott
Replied On July 26, 2016 12:41 PM

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
scott


Abirami Varadharajan [Syncfusion]
Replied On July 27, 2016 12:53 AM

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. 
 
Regards, 
Abirami. 


CONFIRMATION

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.

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.

;