When we try to import a date with the fomat yyyy-MM-DD , the cell contains a text , not a date. When we look your example for the importxml you can see the date is not formated correctly. If I import the same xml file using the import action in excel, the date is correctly formated.
The workbook has a schema associated and the type of the element in the schema is xsd:date.
The value must be imported in the correct format because it is used after in excel formula.
You will find attached the zip , with the schema, the excel file and the xml file I try to import. The excel is already mapped to the schema.Test.zip (10.5 KB)
The import of the xml work well with excel import feature and the date format are applied correcty. The importxml import the date but with a wrong format and excel doesn’t understand the field as a date. When we observe the part corresponding to the sheet generated with exportxml and the save methods, the structure is completly different than when we use the import action provided by MS.
Do you garanty the macro, formula in the sheet are working after? The same xml data imported using the import feature provided by MS in Excel and the importxml and save provided by aspose don’t drive the same result for the document obtained. We would use this feature to isolate the data from the excel sheet, but we would garanty the type remains the same and the logic of the workbook continue to work.
@apacifico1,
Thanks for the XML file and details.
Please notice, after an initial test, I am able to reproduce the issue as you mentioned by using the following sample code with your XML file. I found the date is not formatted correctly and is not inserted as Date type rather it is inserted as text.
e.g.
Sample code:
using (Workbook lWorkBook = new Workbook())
{
var lWorksheet = lWorkBook.Worksheets[0];
System.Xml.XmlDocument document = new System.Xml.XmlDocument();
document.Load("e:\\test2\\testGen2.xml");
System.IO.Stream stream = new System.IO.MemoryStream();
document.Save(stream);
stream.Position = 0;
lWorkBook.ImportXml(stream, "Sheet1", 0, 0);
lWorkBook.Save("e:\\test2\\out1.xlsx");
}
I have logged a ticket with an id “CELLSNET-51072” for your issue. We will look into it soon.
Once we have an update on it, we will let you know.
Thanks for your rapid feedback.
The most important is to garanty the type of the field on the cell are not modified. The excel file we use are template provided by the business.
We ammend the template with a schema and we map the field. The application injects the field values. These excel templates contain formula, macro… and they are doing the business logic.
So we would be sure we don’t break the logic after the data are bound in the cells.
@apacifico1,
Sure, we will try to figure it out soon. Please spare us little time to evaluate your issue in details first.
Once we have any information available, we will update you.
@apacifico1
Actually, Aspose.Cells doesn’t change the format of Cells after updating XmlMap linked data by Workbook.ImportXml. e.g. for Cell B6 and A13, the date format is still there(you can check it in Microsoft Excel), but the updated cell value is not right. We will fix the issue. see BookTest_hotfix_preview.zip (9.7 KB)
In Excel, e.g. for Cell A10, the format is General before imporing xml, however after importing xml, the format is changed is Date. It seems Excel changes the Cell format if it is General.
Do you want Aspose.Cells also change the Cell format like Excel if it is General format or keep the Cell format be unchanged like in BookTest_hotfix_preview.xlsx(e.g. all Cell are well formated in template file by you)?
The implementation of the importxml feature provided by aspose must follow what Microsoft provides for the import xml feature. But the most important when the import is driven by a schema providing strong types (date, decimal, boolean,…) we keep this storage in the cell. The format apply on the cell is just a way to interpret the content of the cell. If the cell must be a date because we use the date in a formula the date type must remain, same for a number if we use it in a calculation on the cell. It is the initial purpose of the schema, transport the semantic of the field between the application and the workbook. Hope my explanation are clear. For a date type for exemple in the schema I will never set the format general for the cell, because the semantic of the field is a date. So the format must remain a date. For a percentage format, inside the cell the value is a numeric and will be sent as a floating number in the excel. For the numeric type , the aspose importxml works as expected for the test I have done.
@Peyton.Xu,
Thanks for your feedback and details.
We will evaluate it and get back to you soon.
In the importxml function provided by Aspose , why you need to specify a target sheet name in the second parameter? A schema could be mapped on different sheets in the workbook with the Microsoft approach.
Does it means only the cells presents in the sheet name specified in the function will be mapped?
This must be transparent for the user. If we have a schema and the data are mapped for exemple on 10 different sheets, I hope we don’t need to call 10 times the importxml methods for all the sheets composing the workbook! Why the user needs to provide the sheetname on the importxml method? The workbook contains the schema and the map between the schema field and the cells.
@apacifico1
We will try to fix the issue as Microsoft Excel’s behavior.
ImportXml function also support to create new XmlMap and link data from the Cell as specified by the params sheetName, row and 'col`.(e.g. import an xml into a blank workbook).
No, ImportXml function support to update all the data in different sheets linked by the matched XmlMap. If the matched XmlMaps are only one, the params SheetName, row and col are ignored.
@apacifico1,
This is to inform you that your issue (logged earlier as “CELLSNET-51072”) has been resolved. The fix will be included in the next release (Aspose.Cells for .NET v22.6) which is scheduled in the second week of June 2022. You will also be notified when the next version is published.
Thanks a lot for your fast reactivity. I wait it because we are preparing a poc using intensively the xml integration and it’s a corner stone for this phase of the project.
The issue seems also touching boolean type. If the schema element is a boolean and binded to a cell. When a document is bound containing boolean value , the resulting cell type in excel is a string and not a boolean.
In the project we work with check box control , when the boolean value are bound to a cell, the type must be a boolean and not a string as today. Could you have a look in this issue and include it with the release you are doing for the date type.
@apacifico1,
Could you please provide us sample code and sample files to reproduce the issue, we will check it soon.
testcaseforaspose.zip (13.1 KB)
You will find attached in the zip , the excel file template for the test having a schema mapped. I have also attached the schema file and an xml data file to test the mapping. The focus are the cells B2 and B5 defined as boolean in the schema. The importxml function after import the cells are transformed in string type. Excel import feature provided by MS doesn’t change the type of the data imported following the schema. if you do a type(B2) with Microsoft you obtain 4 => boolean and after aspose importxml the same method returns 2 meaning a string. Hope this sample will help you to reproduce the issue.
@apacifico1,
Thanks for the template file and XML file.
We reproduced the issue and logged a new ticket for it. Please follow up your other thread for reply and for updates on the new issue.
The issues you have found earlier (filed as CELLSNET-51072) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi
The issues you have found earlier have been fixed in this update. If you have downloaded the prior one(22.6.0), please use this update to replace the downloaded one because 22.6 has a bug which sometimes may cause generated file corrupted.
Thanks a lot. I have downloaded the version 2.6.1 to proceed to new test. I have reported issue last week about file corrupted after generation. I did some test but the issue doesn’t appear systematically and I have reported this issue in another thread. Could you provide some explanation why the file could be sometime corrupted. With the version 2.5 I never had this issue.
I have reported here the corruption of the file I discovered last week : Workbook.importXml() issue with boolean type - Aspose.Cells Product Family - Free Support Forum - aspose.com
Yes, the new version v22.6.1 fixes a bug which was there in previous release (22.6.0) that had a bug which sometimes produced corrupted file. That’s why we asked/recommended you to kindly try using latest version/fix: Aspose.Cells for .NET v22.6.1.