指定数据透视项的绝对位置
Contents
[
Hide
]
有时,用户需要指定数据透视项的绝对位置,Aspose.Cells API公开了一些新属性和方法来实现用户需求。
- 添加了PivotItem.Position属性,可用于指定所有数据透视项的位置索引,而不论父节点如何。添加了PivotItem.PositionInSameParentNode属性,可用于指定在同一父节点下的数据透视项的位置索引。
- 添加了PivotItem.Move(int count, bool isSameParent)方法,以根据计数值将项上移或下移,其中计数是要将数据透视项上移或下移的位置数。如果计数值小于零,则数据透视项将上移,如果计数值大于零,则数据透视项将下移,Boolean类型的isSameParent参数指定移动操作是否必须在同一父节点中执行。
- 废弃了*PivotItem.Move(int count)*方法,因此建议使用新添加的方法PivotItem.Move(int count, bool isSameParent)。
以下示例代码创建了一个数据透视表,然后指定了数据透视项在同一父节点中的位置。您可以下载源Excel和输出Excel文件进行参考。如果打开输出Excel文件,您将看到数据透视项“4H12”位于父节点“K11”中的第0个位置,而“DIF400”位于第3个位置。同样,CA32位于第1个位置,AAA3位于第2个位置。
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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"); |
请注意,在使用PivotItem.Position、PivotItem.PositionInSameParentNode属性和PivotItem.Move(int count, bool isSameParent)方法之前,必须先调用PivotTable.RefreshData和PivotTable.CalculateData方法。