Create pivot table from another pivot table

Aspose.Cells Product Family
1

Hi,

Our scenario is we have 2 workbook with the same data, but 1 contains pivot tables while the other one doesn't.

Is there a way that we could create a pivot table in workbook 2 from the pivot table in workbook 1? And the created pivot table must refrence the range in workbook 2 instead of workbook 1.

Or could we copy pivot table from workbook1 to workbook2?

Regards

Bill

Solvexia

2

Hi Bill,


I am afraid, we do not copy pivot table from one sheet to another sheet. Also, we do not support to create a pivot table whose source is in external/other workbooks, the data source should be there in the same workbook. I think you may either try Worksheet.Copy() method or Workbook.Combine() method if it fits your need.


Thank you.
3

Hi Amjad,

Thank you for your response! I may not express our issue clearly and I will try again:)

Our issue is we have 2 workbook A and B and they both have a worksheet with the same data called "worksheet1".

However, A has got a pivot table but B hasn't. Out issue is to create a pivot table in B based on the information of pivot table in A. The pivot table will refrence data in "worksheet1" in B, so it source is not external.

I know how to create a pivot table ann retrieve pivot tables using Aspose, but I dont know how to

get the source range and other relavant information from pivot table using Aspose. Could you help me please?

Regards

Bill

4

Hi,


Well, you may get source data of the pivot table from Sheet1 of Workbook A using Range.Copy() method and paste it to your destination Worksheet of Workbook B.

See the topic on how to copy ranges:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/copy-range-data-with-style.html


Thank you.

5

Hi,

Thanks for your reply!

We don't need to do range copy because A and B have the same data. What we want is to create a pivot table in B same as the pivot table in A.

I have referred to the pivot creating code and the underlined part is the information we are looking for from pivot table in A:

//Adding a PivotTable to the worksheet

int index = pivotTables.Add("=Data!A1:F30", "B3", "PivotTable1");

//Accessing the instance of the newly added PivotTable

Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];

//Showing the grand totals

pivotTable.RowGrand = true;

pivotTable.ColumnGrand = true;

//Setting the PivotTable report is automatically formatted

pivotTable.IsAutoFormat = true;

//Setting the PivotTable autoformat type.

pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report6;

//Draging the first field to the row area.

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);

//Draging the fourth field to the column area.

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 3);

//Draging the fifth field to the data area.

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 5);

//Setting the number format of the first data field

pivotTable.DataFields[0].NumberFormat = "$#,##0.00";

Especially, whcih property/field/method in pivotable reflect the "=Data!A1:F30" and "B3" and "PivotTable1"?

6

Hi,


"Especially, whcih property/field/method in pivotable reflect the “=Data!A1:F30” and “B3” and “PivotTable1”?"

Well, you may use PivotTable.GetSource() to get the source data range of the pivot table.

Thank you.
7

Thanks! Will try it next week:)

8

It worked. thx.