指定数据透视项的绝对位置

Contents
[ ]

以下示例代码创建了一个数据透视表,然后指定了数据透视项在同一父节点中的位置。您可以下载源Excel和输出Excel文件进行参考。如果打开输出Excel文件,您将看到数据透视项“4H12”位于父节点“K11”中的第0个位置,而“DIF400”位于第3个位置。同样,CA32位于第1个位置,AAA3位于第2个位置。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
Workbook wb = new Workbook(dataDir + "source.xlsx");
Worksheet wsPivot = wb.Worksheets.Add("pvtNew Hardware");
Worksheet wsData = wb.Worksheets["New Hardware - Yearly"];
// Get the pivottables collection for the pivot sheet
PivotTableCollection pivotTables = wsPivot.PivotTables;
// Add PivotTable to the worksheet
int index = pivotTables.Add("='New Hardware - Yearly'!A1:D621", "A3", "HWCounts_PivotTable");
// Get the PivotTable object
PivotTable pvtTable = pivotTables[index];
// Add vendor row field
pvtTable.AddFieldToArea(PivotFieldType.Row, "Vendor");
// Add item row field
pvtTable.AddFieldToArea(PivotFieldType.Row, "Item");
// Add data field
pvtTable.AddFieldToArea(PivotFieldType.Data, "2014");
// Turn off the subtotals for the vendor row field
PivotField pivotField = pvtTable.RowFields["Vendor"];
pivotField.SetSubtotals(PivotFieldSubtotalType.None, true);
// Turn off grand total
pvtTable.ColumnGrand = false;
/*
* Please call the PivotTable.RefreshData() and PivotTable.CalculateData()
* before using PivotItem.Position,
* PivotItem.PositionInSameParentNode and PivotItem.Move(int count, bool isSameParent).
*/
pvtTable.RefreshData();
pvtTable.CalculateData();
pvtTable.RowFields["Item"].PivotItems["4H12"].PositionInSameParentNode = 0;
pvtTable.RowFields["Item"].PivotItems["DIF400"].PositionInSameParentNode = 3;
/*
* As a result of using PivotItem.PositionInSameParentNode,
* it will change the original sort sequence.
* So when you use PivotItem.PositionInSameParentNode in another parent node.
* You need call the method named "CalculateData" again.
*/
pvtTable.CalculateData();
pvtTable.RowFields["Item"].PivotItems["CA32"].PositionInSameParentNode = 1;
pvtTable.RowFields["Item"].PivotItems["AAA3"].PositionInSameParentNode = 2;
// Save file
wb.Save(dataDir + "output_out.xlsx");