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.
Please give us your template Excel file, output file and share sample code (runnable) to reproduce the issue on our end. We will check your issue soon.
PS. please zip the Excel file(s) prior attaching here.
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.
@Flithor,
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.
@Flithor,
As a temporary solution, you can change the column name to avoid this issue. We will continue to fix this situation, and we will inform you as soon as there is an update.
@Flithor,
We have observed this issue and logged it in our database for further investigation. You will be notified here once any update is ready for sharing.
This issue is logged as: CELLSNET-48080 - PivotTable can't use "值" or "Values" as column name when creating PivotTable
We are pleased to inform you that your issue has been resolved. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.
Please inherit and implement Globalizationsettings class as needed.
The sample code as follows:
private class CustomGlobal48080 : GlobalizationSettings
{
public override string GetProtectionNameOfPivotTable()
{
return “值”;
}
}
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.
The issues you have found earlier (filed as CELLSNET-48080) have been fixed in this update. This message was posted using Bugs notification tool by simon.zhao