Support for XmlMaps and ImportXml

Aspose.Cells Product Family
,
1

Hi Aspose. It has been a long time since I showed up here!

I have an issue that I am trying to push the client to use your product.

I have an Excel template file that has XML field mapped.

I am trying to feed that template using code.

Currently, the client is using Excel interops (which I really hate) and I am trying to replace that.

The current code is something like this:
xlsWB = app.Workbooks.OpenXML(pathTemplate)
xlsWB.XmlMaps(“Report_Map”).ImportXml(report.Data)
xlsWB.SaveAs(…)

I have tried to find something equivalent in Aspose.Cells (for .Net).

Do you support that? XmlMaps(name of my map).ImportXml(my xml data)?

Thanks

2

@emoreau,
Aspose.Cells supports XMLMaps and ImportXML feature is also available. You may please follow the below link and write back to us if some issue is observed.
XML Maps

3

Thanks for the reply. But I may be blind becase I don’t see where ImportXML is applied to a XMLMaps.

My Excel template has a XML maps defined in it and I need to provide a corresponding XML file to automatically fill the according cells.

4

@emoreau,
You may please share your template Excel file, the XML file and expected output Excel file for our reference.

5

@emoreau

The API Workbook.ImportXml(string url, string sheetName, int row, int col) can also update the XmlMap with same root element name as the specified xml file.

6

Hi.

I have prepared 3 files.

Template.xlsx is an Excel file that I use as a template with an XmlMap defined in it.

DataSource.Xml contains the data I want to merge/import into the Template file.

Output.xlsx is the resulting file (after Template has been filled with DataSource).

TestFiles.zip (21.5 KB)

7

Hi Peyton.

Not sure we are talking about the same thing. I am not trying to update the XmlMap. I am trying to import an XML file into a template that already has the XmlMap defined.

The thing I don’t understand is that the ImportXml method requires a row and a column. I don’t have them as they are defined by the template.

8

And that would be an example of my code:

Private Sub CreateReportInterops()
Dim app As Microsoft.Office.Interop.Excel.Application
Dim xlsWB As Microsoft.Office.Interop.Excel.Workbook

Dim strFolder As String = Application.StartupPath
Dim strPathTemplate As String = IO.Path.Combine(strFolder, "Template.xlsx")
Dim strPathXmlData As String = IO.Path.Combine(strFolder, "DataSource.xml")
Dim strXmlData As String = IO.File.ReadAllText(strPathXmlData)
Dim strOutput As String = IO.Path.Combine(strFolder, "Output.xlsx")

app = New Microsoft.Office.Interop.Excel.Application()
app.DisplayAlerts = False
app.Visible = False
xlsWB = app.Workbooks.OpenXML(strPathTemplate)
xlsWB.Application.DisplayAlerts = False


xlsWB.XmlMaps("ExposureReport_Map").ImportXml(strXmlData)
xlsWB.SaveAs(strOutput, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange)
xlsWB.Close()
app.Quit()
app = Nothing
xlsWB = Nothing

End Sub

9

@emoreau,
We have noted your requirement and logged it in our database for a sample code. We will write back here once any update is ready for sharing.

This requirement is logged as:
CELLSNET-47881 - Import XML file into workbook

1 Like
10

@emoreau

I am trying to import an XML file into a template that already has the XmlMap defined.

The API Workbook.ImportXml(string url, string sheetName, int row, int col) can do the work.
Code:

Workbook wb = new Workbookpath + "Template.xlsx");
wb.ImportXml(path + "DataSource.xml", wb.Worksheets[0].Name, 0, 0);

wb.Save("output.xlsx");

Currently, the width of Column E,F of the output file is a little small. We will try to fix it.

The thing I don’t understand is that the ImportXml method requires a row and a column. I don’t have them as they are defined by the template.

The API Workbook.ImportXml(string url, string sheetName, int row, int col) can import a new XmlMap with linked data, or map/update the linked area of an exist XmlMap with same root element name as the specified xml file. When the root element name of the specifed xml file is not same as the one of any of existing XmlMap, a new XmlMap with linked data will be created at specified sheetName, row, col.

11

Thanks. That seems to work.

An other related question: my data in the XML is mostly decimal. Once the data is imported and the workbook saved, I see a lot of warnings in Excel showing “number stored as text”. Is there anyway to call the “Refresh Data” options from the Developer toolbar which seems to fix the issue?

My template as the correct formatting but it seems to be ignored by the ImportXML. I have also tried to create an xsd file specifying the correct format but that too failed.

12

@emoreau

Please share your sample Template source file and xml file to reproduce and analyze your issue.

13

Here are the files (Template, Datasource, Output) requested.

As you can see, the output is filled but the format is not applied. Numbers should be shown as percentage as defined in their formating.

Documents.zip (73.2 KB)

14

@emoreau,
Thank you for the feedback. We have noted it and will consider it while working on this issue.

15

@emoreau

Using the following code with Aspose.Cells v21.2, the result is OK, the double value is well formatted.

Workbook wb = new Workbookpath + "Template.xlsx");
wb.ImportXml(path + "DataSource.xml", wb.Worksheets[0].Name, 0, 0);

wb.Save("output.xlsx");

Output_v21.2.zip (25.0 KB)

Please try our latest release version Aspose.Cells v21.2 on your side.

16

Hi

You are right. The client is using the version 18.1. I tried using the latest version and it is working correctly.

The client is checking to update their licenses.

17

@emoreau,

Good to know that your issue is resolved by using the latest version/fix. In the event of further queries or issue, feel free to write us back.

18

@emoreau

Please try our latest version/fix: Aspose.Cells for .NET v21.2.6 (attached)
Aspose.Cells21.2.6 For .Net2_AuthenticodeSigned.Zip (5.5 MB)
Aspose.Cells21.2.6 For .Net4.0.Zip (5.5 MB)
Aspose.Cells21.2.6 For .NetStandard20.Zip (5.5 MB)

The issue is fixed.
Let us know your feedback.

19

The issues you have found earlier (filed as CELLSNET-47881) have been fixed in this update. This message was posted using Bugs notification tool by simon.zhao