We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Create pivot table from another pivot table

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

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.

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

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.

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"?

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.

Thanks! Will try it next week:)

It worked. thx.