自定义数据透视表的全球化设置

可能的使用场景

有时您希望根据自己的需求自定义数据透视表总计、子总计、总计、所有项、多个项、列标签、行标签、空值文本。Aspose.Cells允许您自定义数据透视表的全球化设置,以处理此类情况。您还可以使用此功能将标签更改为其他语言,如阿拉伯语、印度语、波兰语等。

自定义数据透视表的全球化设置

以下示例代码说明了如何自定义数据透视表的全球化设置。它创建了一个从基类PivotGlobalizationSettings派生的类CustomPivotTableGlobalizationSettings,并覆盖了其所有必要的方法。这些方法返回数据透视表总计、子总计、总计、所有项、多个项、列标签、行标签、空值的自定义文本。然后,将此类的对象分配给WorkbookSettings.GlobalizationSettings.PivotSettings属性。该代码加载包含数据透视表的源Excel文件,刷新并计算其数据,并将其保存为输出PDF文件。以下屏幕截图显示了示例代码对输出PDF的影响。正如您在屏幕截图中所看到的,数据透视表的不同部分现在具有由PivotGlobalizationSettings类的覆盖方法返回的自定义文本。

todo:image_alt_text

示例代码

// 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);
}