override all PivotGlobalizationSettings method and not found localization about “Function” or “Count”, and Counting field still output “Count of ColumnName”
It seems you’re experiencing issues with the localization of the “Count” function in your pivot table using Aspose.Cells. If you’ve overridden all PivotGlobalizationSettings methods and still see “Count of ColumnName,” it may be due to the default behavior of the library.
To address this, ensure that you are setting the localization settings correctly. You can customize the function names using the following approach:
PivotGlobalizationSettings settings = new PivotGlobalizationSettings();
settings.FunctionNames["Count"] = "YourLocalizedCountName"; // Set your localized name here
PivotTable pivotTable = worksheet.PivotTables[0];
pivotTable.GlobalizationSettings = settings;
Make sure to replace "YourLocalizedCountName" with the desired localized string for “Count.” If this does not resolve the issue, consider reviewing the Aspose.Cells documentation for any additional settings or updates related to localization.
If you continue to face challenges, it may be helpful to consult community forums for similar issues and solutions.
@Flithor
Please override the method GetTextOfSubTotal(PivotFieldSubtotalType) to get the text of PivotFieldSubtotalType in the PivotTable.
About globalization settings for Pivot Table, please refer to the following document.
@Flithor
About function localization, please override relative methods of GlobalizationSettings and refer to the following documents.
@John.He not work, it never invoke GetGrandTotalName and GetTotalName, still output “Sum of xx”, “Count of xx”
Could you please share your sample runnable code or, if possible, a standalone sample console application (source code without any compilation errors) with sample files to help us reproduce the issue on our end? We will check your issue soon.
PS. please zip the files prior attaching here.
void Main()
{
var testFolder = @"D:\TestFolder\";
using var workbook = new Workbook(testFolder + "pivot table with smart markers.xlsx");
workbook.Settings.GlobalizationSettings = new ChineseGlobalizationSettings();
workbook.Settings.LanguageCode = CountryCode.China;
workbook.Settings.Region = CountryCode.China;
var designer = new WorkbookDesigner(workbook);
designer.SetDataSource("columnName", "My Col Name");
designer.SetDataSource("Data", new[] {
new{Value=1},
new{Value=2},
new{Value=4},
new{Value=2},
new{Value=1},
new{Value=3},
new{Value=2},
new{Value=1},
new{Value=1},
new{Value=3},
new{Value=4},
new{Value=2},
});
var pivotSheet = workbook.Worksheets[1];
var pivotTable = pivotSheet.PivotTables[0];
var baseFields = pivotTable.BaseFields.OfType<PivotField>().ToList();
var baseFieldNames = baseFields.Select(i => i.Name).ToList();
var rowFieldsIndex = pivotTable.RowFields.OfType<PivotField>().Select(f => (f, index: baseFields.IndexOf(f))).Where(t => t.index >= 0).ToList();
var columnFieldsIndex = pivotTable.ColumnFields.OfType<PivotField>().Select(f => (f, index: baseFields.IndexOf(f))).Where(t => t.index >= 0).ToList();
var dataFieldsIndex = pivotTable.DataFields.OfType<PivotField>().Select(f => (f, index: baseFieldNames.IndexOf(f.Name), func: f.Function, oldName: f.Name)).Where(t => t.index >= 0).ToList();
var pageFieldsIndex = pivotTable.PageFields.OfType<PivotField>().Select(f => (f, index: baseFields.IndexOf(f))).Where(t => t.index >= 0).ToList();
designer.Process();
pivotTable.RefreshData();
pivotTable.DataFields.Clear();
foreach (var (orgField, index) in rowFieldsIndex)
pivotTable.RowFields.AddByBaseIndex(index);
foreach (var (orgField, index) in columnFieldsIndex)
pivotTable.ColumnFields.AddByBaseIndex(index);
foreach (var (orgField, index, orgFunc, oldName) in dataFieldsIndex)
pivotTable.DataFields.AddByBaseIndex(index);
foreach (var (orgField, index) in pageFieldsIndex)
pivotTable.PageFields.AddByBaseIndex(index);
pivotTable.CalculateData();
workbook.Save(testFolder + @"pivot table with smart markers-out.xlsx", SaveFormat.Xlsx);
Process.Start(new ProcessStartInfo(testFolder + "pivot table with smart markers-out.xlsx") { UseShellExecute = true });
}
public class ChineseGlobalizationSettings : GlobalizationSettings
{
public ChineseGlobalizationSettings()
{
PivotSettings = new ChinesePivotGlobalizationSettings();
}
public override string GetPivotTotalName() => "总计";
public override string GetPivotGrandTotalName() => "总计";
public override string GetGrandTotalName(ConsolidationFunction functionType)
{
return functionType switch
{
ConsolidationFunction.Sum => "求和",
ConsolidationFunction.Count => "计数",
ConsolidationFunction.Average => "平均值",
ConsolidationFunction.Max => "最大值",
ConsolidationFunction.Min => "最小值",
ConsolidationFunction.Product => "乘积",
ConsolidationFunction.CountNums => "数值计数",
ConsolidationFunction.StdDev => "标准偏差",
ConsolidationFunction.StdDevp => "总体标准偏差",
ConsolidationFunction.Var => "方差",
ConsolidationFunction.Varp => "总体方差",
ConsolidationFunction.DistinctCount => "去重计数",
_ => base.GetGrandTotalName(functionType)
};
}
public override string GetTotalName(ConsolidationFunction functionType)
{
return functionType switch
{
ConsolidationFunction.Sum => "求和项：",
ConsolidationFunction.Count => "计数项：",
ConsolidationFunction.Average => "平均值项：",
ConsolidationFunction.Max => "最大值项：",
ConsolidationFunction.Min => "最小值项：",
ConsolidationFunction.Product => "乘积项：",
ConsolidationFunction.CountNums => "计数项：",
ConsolidationFunction.StdDev => "标准偏差项：",
ConsolidationFunction.StdDevp => "总体标准偏差项：",
ConsolidationFunction.Var => "方差项：",
ConsolidationFunction.Varp => "总体方差项：",
ConsolidationFunction.DistinctCount => "计数项：",
_ => base.GetGrandTotalName(functionType)
};
}
}
public class ChinesePivotGlobalizationSettings : PivotGlobalizationSettings
{
public override string GetTextOfTotal() => "总计";
public override string GetTextOfGrandTotal() => "总计";
public override string GetTextOfMultipleItems() => "多项";
public override string GetTextOfAll() => "全部";
public override string GetTextOfProtectedName(string protectedName) => $"受保护：{protectedName}";
public override string GetTextOfColumnLabels() => "列标签";
public override string GetTextOfRowLabels() => "行标签";
public override string GetTextOfEmptyData() => "无数据";
public override string GetTextOfDataFieldHeader() => "数值字段";
public override string[] GetShortTextOf12Months() => ["1月", "2月", "3月", "4月", "5月", "6月", "7月", "8月", "9月", "10月", "11月", "12月"];
public override string[] GetTextOf4Quaters() => ["第一季度", "第二季度", "第三季度", "第四季度"];
public override string GetTextOfYears() => "年";
public override string GetTextOfQuarters() => "季度";
public override string GetTextOfMonths() => "月";
public override string GetTextOfDays() => "日";
public override string GetTextOfHours() => "小时";
public override string GetTextOfMinutes() => "分钟";
public override string GetTextOfSeconds() => "秒";
public override string GetTextOfRange() => "范围";
public override string GetTextOfSubTotal(PivotFieldSubtotalType subTotalType)
{
return subTotalType switch
{
PivotFieldSubtotalType.Sum => "求和小计",
PivotFieldSubtotalType.Count => "计数小计",
PivotFieldSubtotalType.Average => "平均值小计",
PivotFieldSubtotalType.Max => "最大值小计",
PivotFieldSubtotalType.Min => "最小值小计",
PivotFieldSubtotalType.Product => "乘积小计",
PivotFieldSubtotalType.CountNums => "数字计数小计",
PivotFieldSubtotalType.Stdev => "标准差小计",
PivotFieldSubtotalType.Stdevp => "总体标准差小计",
PivotFieldSubtotalType.Var => "方差小计",
PivotFieldSubtotalType.Varp => "总体方差小计",
PivotFieldSubtotalType.None => "无",
PivotFieldSubtotalType.Automatic => "自动",
_ => "小计"
};
}
}
xlsx:
pivot table with smart markers.zip (9.5 KB)
Btw: this code is work for make smart marker work with pivot table.
It’s workaround, I’d like a better compatibility of them.
Thanks for the template file and sample code snippet.
After initial testing, I found the issue for pivot table when implementing custom GlobalizationSettings.
We require thorough evaluation of the issue. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-59170
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
Sure, we will thoroughly evaluate the issue and try to address your concerns as well. Rest assured, we will provide you with an update here as soon as we have one.
@Flithor
We have added GetNameOfDataField() new method for PivotGlobalizationSettings for the next version 25.11. Please implement it as the following :
public override string GetNameOfDataField(ConsolidationFunction subTotalType,string name)
{
switch (subTotalType)
{
case ConsolidationFunction.Sum:
return "求和项 : " + name;
case ConsolidationFunction.Count:
return "小计 : " + name;
default:
return "小计";
}
}
Now you can set PivotField.DisplayName to support your need as following:
foreach (var (orgField, index, orgFunc, oldName) in dataFieldsIndex)
{
int t = pivotTable.DataFields.AddByBaseIndex(index);
PivotField field = pivotTable.DataFields[t];
switch(field.Function)
{
case ConsolidationFunction.Sum:
field.DisplayName = "求和项 : " + field.Name;
break;
}
}