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

[Scott]

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]

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. 

 

Sample Link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/XlsIO_Sample1749471021.zip  

 

Regards, 

Abirami. 

[Scott]

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]

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.