操纵数据透视表
Contents
[
Hide
]
可能的使用场景
除了创建新的数据透视表之外,您还可以操作新的和现有的数据透视表。您可以更改数据透视表的源范围内的数据,然后刷新并计算它并获得数据透视表单元格的新值。请用IPivotTable.RefreshData()和IPivotTable.CalculateData()更改数据透视表源范围内的值以刷新数据透视表后的方法。
操纵数据透视表
下面的示例代码加载示例 excel 文件并访问其第一个工作表内的现有数据透视表。它更改数据透视表源范围内的单元格 B3 的值,然后刷新数据透视表。在刷新数据透视表之前,它访问数据透视表单元格 H8 的值为 15,刷新数据透视表后,其值变为 6。请参阅输出excel文件使用此代码生成,屏幕截图显示示例代码对示例 excel 文件的影响。另请参阅下面的控制台输出,其中显示刷新数据透视表前后数据透视表单元格 H8 的值。
示例代码
This file contains hidden or 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-C | |
//Source directory path | |
StringPtr dirPath = new String("..\\Data\\PivotTables\\"); | |
//Output directory path | |
StringPtr outPath = new String("..\\Data\\Output\\"); | |
//Path of input excel file | |
StringPtr sampleManipulatePivotTable = dirPath->StringAppend(new String("sampleManipulatePivotTable.xlsx")); | |
//Path of output excel file | |
StringPtr outputManipulatePivotTable = outPath->StringAppend(new String("outputManipulatePivotTable.xlsx")); | |
//Load the sample excel file | |
intrusive_ptr<IWorkbook> wb = Factory::CreateIWorkbook(sampleManipulatePivotTable); | |
//Access first worksheet | |
intrusive_ptr<IWorksheet> ws = wb->GetIWorksheets()->GetObjectByIndex(0); | |
//Change value of cell B3 which is inside the source data of pivot table | |
intrusive_ptr<String> str = new String("Cup"); | |
ws->GetICells()->GetObjectByIndex(new String("B3"))->PutValue(str); | |
//Get the value of cell H8 before refreshing pivot table | |
intrusive_ptr<String> val = ws->GetICells()->GetObjectByIndex(new String("H8"))->GetStringValue(); | |
StringPtr str1 = new String(L"Before refreshing Pivot Table value of cell H8: "); | |
Console::WriteLine(str1->StringAppend(val)); | |
//Access pivot table, refresh and calculate it | |
intrusive_ptr<IPivotTable> pt = ws->GetIPivotTables()->GetObjectByIndex(0); | |
pt->RefreshData(); | |
pt->CalculateData(); | |
//Get the value of cell H8 after refreshing pivot table | |
val = ws->GetICells()->GetObjectByIndex(new String("H8"))->GetStringValue(); | |
StringPtr str2 = new String(L"After refreshing Pivot Table value of cell H8: "); | |
Console::WriteLine(str2->StringAppend(val)); | |
//Save the output excel file | |
wb->Save(outputManipulatePivotTable); |
控制台输出
以下是使用提供的执行时上述示例代码的控制台输出示例 excel 文件.
Before refreshing Pivot Table value of cell H8: 15
After refreshing Pivot Table value of cell H8: 6