Free Support Forum - aspose.com

What do these extra pivot table parameters mean?

I want to create a pivot table with multiple source data ranges, however the API call to do that requires two extra parameters compared to the normal API call:

isAutoPage - Whether auto create a single page field. If true,the following param pageFields will be ignored.
pageFields - The pivot page field items.

These short descriptions don't adequately explain their usage. What is a "single page field" and how is one "auto created"? What are "pivot page field items"? Are those the items you normally add afterwards via AddFieldToArea? If so why do you have to pre-define them here, and how?

Do you have any example code demonstrating how these parameters are used, what they mean, and what they do?

Thanks.

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

We will create a sample demo to explain these properties and share with you soon.


Thank You & Best Regards,

We will create a sample demo to explain these properties and share with you soon.

<o:p></o:p>


Please hurry :slight_smile: Thanks.

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Please see the following sample code and attached template & Resultant file which will give you a better idea regarding Page Fields which are actually used in pivot tables with multiple data source.

Workbook workbook = new Workbook();

workbook.Open("D:\\Book1.xls");

//workbook.Worksheets.Add();

Worksheet sheet = workbook.Worksheets[0];

PivotTables pivotTables = sheet.PivotTables;

String[] sourceData = new String[] { "=Sheet1!A1:C8", "=Sheet2!A1:C8" };

PivotPageFields pageField = new PivotPageFields();

String[] pageItems = new String[2];

pageItems[0] = "Item1";

pageItems[1] = "Item2";

pageField.AddPageField(pageItems);

pageItems = new String[2];

pageItems[0] = "Item3";

pageItems[1] = "Item4";

pageField.AddPageField(pageItems);

int[] TBPG = new int[2];

TBPG[0] = 0;

TBPG[1] = 1;

//Sets which item label in each page field to use to identify the data range.

pageField.AddIdentify(0, TBPG);

TBPG = new int[2];

TBPG[0] = 1;

TBPG[1] = -1;

pageField.AddIdentify(1, TBPG);

int index = pivotTables.Add(sourceData, false, pageField, "E3", "PivotTable1");

workbook.Save("D:\\dest.xls");

Thank You & Best Regards,

Thanks. I don’t entirely understand everything in that example code but I do see that you’re creating fields for selecting items from each page. I don’t need that level of flexibility. I would be fine with a multi-page-spanning range like “=Sheet2:Sheet5!A1:O65536” but when I try to use something like that I get this exception:


Unhandled Exception: Aspose.Cells.CellsException: The PivotTable field name is invalid.
at x484486ddc8e52eef.xe47e1210b3db4891…ctor (x9d2539cac3622db3.xfc686d5bce097028 pivotCache, System.String name, Boolean isGathered) [0x00000] in :0
at x9d2539cac3622db3.xfc686d5bce097028.xb4f3449554925fdb () [0x00000] in :0
at x9d2539cac3622db3.xfc686d5bce097028…ctor (x9d2539cac3622db3.x39c734adf660f639 pivotCaches, PivotTableSourceType sourceType, System.String[] sourceData, Aspose.Cells.PivotPageFields pageFields, Boolean autoPage, Int32 streamId, Aspose.Cells.Worksheet sheet) [0x00000] in :0
at x9d2539cac3622db3.x39c734adf660f639.xd6b6ed77479ef68c (System.String xeaa4008a82e55001, Aspose.Cells.Worksheet x99ecbc7e3ae0e29b, Boolean xb98ed8e9d52fed49) [0x00000] in :0
at Aspose.Cells.PivotTables.Add (System.String sourceData, Int32 row, Int32 column, System.String tableName) [0x00000] in :0
at Aspose.Cells.PivotTables.Add (System.String sourceData, System.String destCellName, System.String tableName) [0x00000] in :0
at Report.buildPivotTable (Aspose.Cells.Worksheets worksheets, Int32 overallRowCount) [0x00000] in :0
at Report.Main (System.String[] args) [0x00000] in :0

Hi,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for the feedback.

We do not support to set data source as "=Sheet2:Sheet5!A1:O65536", please create your data source as mentioned below:

String[] sourceData = new String[] { "=Sheet1!A1: IO65536", "=Sheet2!A1: IO6553","=Sheet3!A1: IO65536","=Sheet4!A1: IO65536","=Sheet5!A1: IO65536" };

Thank You & Best Regards,