Free Support Forum - aspose.com

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:
https://docs.aspose.com/display/cellsnet/Xml+Maps
(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.