I have a table containt a column named “值”(value).
this column contains some values.
If I tried to set this column as a DataField, it will failt: the saved file will lost this DataField(unpack this file and open pivot xml file, not contains this data field).
If I create a pivot manually, the column of “值” will be named as “值2”(in Microsoft Excel 2016).
Open this file by ASPOSE.Cells and got this pivot object, the DataField property is null, and DataFields property is empty.
My system environment language is Chinese, I don’t know if this bug will appear on system in other languages.
I am using us-english environment/locale and I got errors when open your template Excel file into MS Excel manually, see the screenshots for your reference. sc_shot1.png (53.7 KB) sc_shot2.png (56.8 KB)
By the way, if you are not using Smart Markers and create the pivot table with your desired data field(s), does it work fine? Also, could you share your output file generated on your end. We will check it further.
Uh… looks recover not broken any valid pivot table, you can save this recovered file and continue.
I copied a new sheet after proecessed smark marker in code, it names “SetDataField”, and I set that pivot field. But it not work.
Here’s the output file: output.zip (29.8 KB)
Could you please share sample code (runnable) to generate your desired PivotTable (when using “值” as pivot field name) from the scratch (without using Smart Markers)? We need to evaluate if the issue is due to Smart Markers or the issue exists in the PivotTable APIs itself. Also, provide your output Excel file for reference.
I have evaluated your issue in details. I guess this is not an issue with Aspose.Cells rather specific behavior of MS Excel regarding “值” field name when set it as data field of pivot table.
As you himself said and we confirmed, the data field name is changed automatically to “值2” in MS Excel manually. So, please change the marker in C1 cell of Template sheet to “值2” and re-save the file. Now use your code and it will work fine as I tested. It looks MS Excel does not seem to allow adding “值” as data field name, so we got to change to what MS Excel wants (“值2”).
Let us know if you still have any issue or confusion.
You means I need to change my table column name to “值2”(Value 2)? Really?
For now, I can choose other column name (such as “记录值”(Record Value)) to bypass this BUG. But I am not sure whether there will be cannot bypass scenarios in the future. I think Aspose.Cells should consider this issue - After all, it is inconsistent with the behavior of Excel.
When using Aspose.Cells to create a pivot table, the field object of “值” column is not set the name to “值2” correctly.
In Chinese environment, column name “值” will become “值2” when creating pivot table. In English environment, column name “Values” will become “Values2”. "值"or “Values” is the protection name of excel when creating pivot table.Therefore, when designing the column name, please avoid the reserved name in the corresponding environment.
Please inherit and implement Globalizationsettings class as needed.
The sample code as follows:
private class CustomGlobal48080 : GlobalizationSettings
public override string GetProtectionNameOfPivotTable()
Workbook wb = new Workbook(filePath + “sample.xlsx”);
//sets GlobalizationSettings object
wb.Settings.GlobalizationSettings = new CustomGlobal48080();
Your issue should be fixed in it.
Let us know your feedback.