Import data into Excel sheet with XML Mapping

Hi.

Is it possible to do the Following.

  1. I will manually create a Excel file, with an XML Map.
  2. With Apose i want to open the existing file, update the XMLdata that is mapped, with a new XML file.
  3. Call Update XML Data, to ensure the new data from XML file is shown.
    The Idea is the Create an Excel file with XML map as template for sending out Excel files, with transaction specifications, we want to then runtime, change the XML with new transactions.

@optimate,

Thanks for your query.

Your requirements should be supported in Aspose.Cells APIs, see the documents/articles in the section for your reference:

(Please note, to update XMLMap linked data, you may try the API, i.e., Workbook.ImportXml(string url, string sheetName, int row, int col) which can update data).

Hi.

I tried The Following.

  1. Created a XLSX Document Manually.
  2. Created XML Mapping, by importing Example XML file.
  3. Saved the XLSX File.
  4. With Aspose.Cells Open the Saved Excel File.
  5. With Aspose.Cells Importing, new XMLData.
  6. With Aspose.Cells Saving The Document.

However when i open the document, the new XMLData is not shown.

I use the following code.

Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(“Aspose.Total.lic”);
Workbook workbook = new Workbook(@“C:\temp\Excel7.xlsx”);
System.Xml.XmlDocument document = new System.Xml.XmlDocument();
document.Load(@“C:\temp\salesConfirmstripped.xml”);
System.IO.Stream stream = new System.IO.MemoryStream();
document.Save(stream);
workbook.ImportXml(stream, “Ark1”, 5, 2);
workbook.Save(@“C:\temp\output_out11.xlsx”);

@optimate,

Thanks for the code segment and details.

Please provide your template Excel file, XML document and output file, we will check it soon.

Hi.

I have attached source Excel file.
Source XMLFile.
And outputFile.
If i manually open the Outputfile, go to developer / XML, clikc import, and points to the XML file, data is imported, However i want to do this with aspose.

Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(“Aspose.Total.lic”);

        string workDir = @"C:\temp\wordpopulator\";
        Workbook workbook = new Workbook(workDir + "Example1.xlsx");
       
        System.Xml.XmlDocument document = new System.Xml.XmlDocument();
        document.Load(workDir + "XMLExample1.xml");
        System.IO.Stream stream = new System.IO.MemoryStream();
        document.Save(stream);
        workbook.ImportXml(stream, "Ark1", 3, 3);
        workbook.Save(workDir + "XMLExample1_output.xlsx");

XMLExample1.zip (15.5 KB)

Just added the files aswell

@optimate,

Thanks for the template file, sample code, XML file and output file.

Please add a line to your code segment (see the line in bold), it will work fine as I tested:
e.g
Sample code:

System.Xml.XmlDocument document = new System.Xml.XmlDocument();
document.Load(workDir + “XMLExample1.xml”);
System.IO.Stream stream = new System.IO.MemoryStream();
document.Save(stream);
stream.Position = 0;
workbook.ImportXml(stream, “Ark1”, 3, 3);
workbook.Save(workDir + “XMLExample1_output.xlsx”);

Hope, this helps a bit.

Hi.

Above solution solved the issue.

I do have another issue.

If your template Excel Document, contains a column in the table which is not mapped, you will get a null reference exception ?

@optimate,

Please provide your template file and sample code (runnable) to show the issue, we will check it soon.

I have attached simple Example.

XML File with 3 Fields, Excel file with Table containing 4 Columns, 3 is mapped last is left for comments.
When 1 column is not mapped you get null reference exception.

Code Example:

Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(“Aspose.Total.lic”);

        string workDir = @"C:\temp\wordpopulator\";
        Workbook workbook = new Workbook(workDir + "unmapped.xlsx");

        System.Xml.XmlDocument document = new System.Xml.XmlDocument();
        document.Load(workDir + "unmapped.xml");
        System.IO.Stream stream = new System.IO.MemoryStream();
        document.Save(stream);
        stream.Position = 0;
        string name = workbook.Worksheets[0].Name.ToString();

        workbook.ImportXml(stream, name, 1, 1);

        workbook.Save(workDir + "mapped.xlsx", Aspose.Cells.SaveFormat.Xlsx);

Unmapped.zip (8.0 KB)

@optimate,

Thanks for the template file, XML file and sample code.

After an initial test, I am able to reproduce the issue as you mentioned by using your sample code with your template file. I found an exception “Object reference not set to an instance of an object” while importing XML data where a column in the table not mapped. I have logged a ticket with an id “CELLSNET-47105” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.

@optimate,
Please try our latest version/fix: Aspose.Cells for .NET v20.1.1:
Aspose.Cells20.1.1 For .Net2_AuthenticodeSigned.Zip (5.0 MB)
Aspose.Cells20.1.1 For .Net4.0.Zip (5.0 MB)

Your issue should be fixed in it.

Let us know your feedback.

Hi.

I have tested above, and can confirm it solved the Issue.

@optimate,

Good to know that you are up and running again. In the event of any further query or issue, feel free to write us back, we will be happy to assist you soon.

The issues you have found earlier (filed as CELLSNET-47105) have been fixed in Aspose.Cells for .NET v20.2. This message was posted using Bugs notification tool by Amjad_Sahi