自定义数据透视表的全球化设置
可能的使用场景
有时您希望根据自己的需求自定义数据透视表总计、子总计、总计、所有项、多个项、列标签、行标签、空值文本。Aspose.Cells允许您自定义数据透视表的全球化设置,以处理此类情况。您还可以使用此功能将标签更改为其他语言,如阿拉伯语、印度语、波兰语等。
自定义数据透视表的全球化设置
以下示例代码说明了如何自定义数据透视表的全球化设置。它创建了一个从基类PivotGlobalizationSettings派生的类CustomPivotTableGlobalizationSettings,并覆盖了其所有必要的方法。这些方法返回数据透视表总计、子总计、总计、所有项、多个项、列标签、行标签、空值的自定义文本。然后,将此类的对象分配给WorkbookSettings.GlobalizationSettings.PivotSettings属性。该代码加载包含数据透视表的源Excel文件,刷新并计算其数据,并将其保存为输出PDF文件。以下屏幕截图显示了示例代码对输出PDF的影响。正如您在屏幕截图中所看到的,数据透视表的不同部分现在具有由PivotGlobalizationSettings类的覆盖方法返回的自定义文本。
示例代码
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
private class CustomPivotTableGlobalizationSettings : PivotGlobalizationSettings | |
{ | |
//Gets the name of "Total" label in the PivotTable. | |
//You need to override this method when the PivotTable contains two or more PivotFields in the data area. | |
public override string GetTextOfTotal() | |
{ | |
Console.WriteLine("---------GetPivotTotalName-------------"); | |
return "AsposeGetPivotTotalName"; | |
} | |
//Gets the name of "Grand Total" label in the PivotTable. | |
public override string GetTextOfGrandTotal() | |
{ | |
Console.WriteLine("---------GetPivotGrandTotalName-------------"); | |
return "AsposeGetPivotGrandTotalName"; | |
} | |
//Gets the name of "(Multiple Items)" label in the PivotTable. | |
public override string GetTextOfMultipleItems() | |
{ | |
Console.WriteLine("---------GetMultipleItemsName-------------"); | |
return "AsposeGetMultipleItemsName"; | |
} | |
//Gets the name of "(All)" label in the PivotTable. | |
public override string GetTextOfAll() | |
{ | |
Console.WriteLine("---------GetAllName-------------"); | |
return "AsposeGetAllName"; | |
} | |
//Gets the name of "Column Labels" label in the PivotTable. | |
public override string GetTextOfColumnLabels() | |
{ | |
Console.WriteLine("---------GetColumnLabelsOfPivotTable-------------"); | |
return "AsposeGetColumnLabelsOfPivotTable"; | |
} | |
//Gets the name of "Row Labels" label in the PivotTable. | |
public override string GetTextOfRowLabels() | |
{ | |
Console.WriteLine("---------GetRowLabelsNameOfPivotTable-------------"); | |
return "AsposeGetRowLabelsNameOfPivotTable"; | |
} | |
//Gets the name of "(blank)" label in the PivotTable. | |
public override string GetTextOfEmptyData() | |
{ | |
Console.WriteLine("---------GetEmptyDataName-------------"); | |
return "(blank)AsposeGetEmptyDataName"; | |
} | |
//Gets the name of PivotFieldSubtotalType type in the PivotTable. | |
public override string GetTextOfSubTotal(PivotFieldSubtotalType subTotalType) | |
{ | |
Console.WriteLine("---------GetSubTotalName-------------"); | |
switch (subTotalType) | |
{ | |
case PivotFieldSubtotalType.Sum: | |
return "AsposeSum";//polish | |
case PivotFieldSubtotalType.Count: | |
return "AsposeCount"; | |
case PivotFieldSubtotalType.Average: | |
return "AsposeAverage"; | |
case PivotFieldSubtotalType.Max: | |
return "AsposeMax"; | |
case PivotFieldSubtotalType.Min: | |
return "AsposeMin"; | |
case PivotFieldSubtotalType.Product: | |
return "AsposeProduct"; | |
case PivotFieldSubtotalType.CountNums: | |
return "AsposeCount"; | |
case PivotFieldSubtotalType.Stdev: | |
return "AsposeStdDev"; | |
case PivotFieldSubtotalType.Stdevp: | |
return "AsposeStdDevp"; | |
case PivotFieldSubtotalType.Var: | |
return "AsposeVar"; | |
case PivotFieldSubtotalType.Varp: | |
return "AsposeVarp"; | |
} | |
return "AsposeSubTotalName"; | |
} | |
} | |
public static void Run() | |
{ | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
//Load your excel file | |
Workbook wb = new Workbook(dataDir + "samplePivotTableGlobalizationSettings.xlsx"); | |
//Setting Custom Pivot Table Globalization Settings | |
wb.Settings.GlobalizationSettings.PivotSettings = new CustomPivotTableGlobalizationSettings(); | |
//Hide first worksheet that contains the data of the pivot table | |
wb.Worksheets[0].IsVisible = false; | |
//Access second worksheet | |
Worksheet ws = wb.Worksheets[1]; | |
//Access the pivot table, refresh and calculate its data | |
PivotTable pt = ws.PivotTables[0]; | |
pt.RefreshDataFlag = true; | |
pt.RefreshData(); | |
pt.CalculateData(); | |
pt.RefreshDataFlag = false; | |
//Pdf save options - save entire worksheet on a single pdf page | |
PdfSaveOptions options = new PdfSaveOptions(); | |
options.OnePagePerSheet = true; | |
//Save the output pdf | |
wb.Save(dataDir + "outputPivotTableGlobalizationSettings.pdf", options); | |
} |