Aspose.Cells issue with CustomXml is corrupted

Aspose.Cells Product Family
1

Hi Team,

I was trying to use CustomXML to store metadata on the workbook, while experimenting I found the file Test_XML.xlsx which was created was corrupted. Can you please let me know if I am doing something wrong.

Code Attached.TestAspose - CustomXML.zip (165.8 KB)

2

@Joel_Peter
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-53031

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.

3

@Joel_Peter
1,Some invalid xml files were inserted as custom xml part, so the generated xlsx is corrupted.

{“Book”:“Name1”}


{“Book”:“Name2”}

2, If you only want to add some metadata, please try workbook.ContentTypeProperties as the following codes:
Workbook workbook = new Workbook(FileFormatType.Xlsx);
int index = workbook.ContentTypeProperties.Add(“MK31”, “Simple Data”);
workbook.ContentTypeProperties[index].IsNillable = true;
//index= workbook.ContentTypeProperties.Add(“MK32”, “2019-10-17T16:00:00+00:00”, “DateTime”);
index = workbook.ContentTypeProperties.Add(“MK32”,
DateTime.Now.ToString(“yyyy-MM-dd’T’hh:mm:ss”), “DateTime”);
workbook.ContentTypeProperties[index].IsNillable = true;
3, If you still has any issue about custom xml, please share us an expceted xlsx here? We will evaluate it as soon as possible

4

@simon.zhao

Please find inputs below:

  1. Can you point at the xml that is incorrect?, When I extract the xlsx file to a folder, I get the xml file in customXml folder Test_Xml.zip (9.8 KB)
  2. The reason I do not want to use ContentTypeProperties is I want to hide the data to anyone using an excel, i.e. If I use ContentTypeProperties, then the information can be found in the info section of the excel
  3. I have attached the unzipped version of the excel in point 1
5

@Joel_Peter
1, Please unzip the Test_Xml.zip and check “item1.xml” and “itemProp1.xml”, the xml data is not valid.
I changed it and save it again as the attached:
Test_Xml_m.zip (9.8 KB)
It can open fine in MS Excel.
“ShemaData” is the definition of xml part and “data” is an xml binary data you wanted to input

2, If you only want to hide data , you can hide a worksheet as the following:
using (var workbook = new Workbook(dir +“Test1.xlsx”))
{
int index = workbook.Worksheets.Add();
Worksheet sheet = workbook.Worksheets[index];
sheet.VisibilityType = VisibilityType.VeryHidden;
//hide your data to the worksheet

                workbook.Save(dir + "dest.xlsx");
            }
6

  1. I updated the code to do the exact same thing as the Test_Xml_m.zip provided by you, still the excel is corrupted. Updated Code is attached UpdatedCode.zip (6.8 MB), it produces the same xml files as the one you shared, but still on opening Test_Xml.xlsx, I get a popup as shown below.
    image.png (29.0 KB)

  2. I think you have misunderstood my explaination about ContentTypeProperties appearing in the excel and I do not want it to be exposed in Excel, For eg, when using ContentTypeProperties with the code snippet provided by you, I can open the excel and go to File => Info. I can see this following.
    image.png (107.2 KB)
    . I do not want the metadata to be viewed by user and should only be accessed by code. Hope that helps understanding the requirement

7

@Joel_Peter,

Thanks for the resource files and screenshots.

We are sorry that your issue is still not resolved. We will look into it and get back to you soon.

8

@Joel_Peter
1). It’s corrupted because there is a bug in your codes, please change it as the following:
workbook.CustomXmlParts.Add(Encoding.GetEncoding("UTF-16").GetBytes(SerializeToXML(metadata)), Encoding.GetEncoding("UTF-16").GetBytes(SerializeToXML(metadata)));
Xml returned by XmlSerializer contains encoding definition :<?xml version="1.0" encoding="utf-16"?>

2). As far as I know, custom xml part works for two features: Custom UI and ContentTypeProperties.
I do not think it’s good to hide data to custom xml.

If you do not want the metadata to be viewed by the user, you can hide a worksheet with VisibilityType.VeryHidden too as codes in the previous, then hide you data to this sheet. Very hidden worksheet can not be accessed in MS Excel viewer.

9

Thank you so much, your code snippet works.

10

@Joel_Peter,
I’m glad your issue has been solved. If you have any questions, please feel free to contact us.