none
XML import On Excel using OpenXML RRS feed

  • Question

  •  HI,

    Scenario - XmlMapping is generated and template is available. Another system generates a XML file with Data, which conforms to the mapping. Objective of the following code is to import the that xml and save the final Excel. I m using Excel 2010 or above.

    How do I achieve the same thing using DocumentFormat.OpenXml. 

    var exApp = new Microsoft.Office.Interop.Excel.Application(); var exWbk = exApp.Workbooks.Open(path); Microsoft.Office.Interop.Excel.XmlMap x ; exWbk.XmlImport(@"D:\Template\Data.xml", out x ,Type.Missing,Type.Missing);

    exWbk.SaveAs(....)

    Thanks

    Regards

    Pati

    Wednesday, October 23, 2013 1:18 AM

All replies

  • The workable way to achieve this goal:

    1. Run code you provided to import a xml file into workbook.

    2. Use OpenXmlSdkTool.exe to see how many elements was added

    3. base on the reflected code, you can easily write C# code to import a XML file. 


    Thursday, October 24, 2013 10:29 AM
  • Hi,

    Thanks for your response.

    I had a detailed look at your solution. It looks like hard coding. In my case users can create their own templates (tables, different cells), create mappings using different XMLs. Only thing I have is the xml data file conforming to the mapped XML. I am not sure If I can re-engineering this code and come up with a general solution to handle many cases. 

    Thank you

    Patip

    Thursday, October 24, 2013 10:57 PM
  • Friday, October 25, 2013 8:48 AM
  • Hi,

     That link's solution precisely stops before the steps I require. In my case user does the XML mapping manually. They Selects the data items and layout (to simple cell Values to data tables).

    My task is to import the import the xml in to the excel document and save it.

    I am after a useful function that can auto generate bindings and generate the document given a the xml data file.

    Thank you.

    Patip



    • Edited by Patip Monday, October 28, 2013 5:51 AM
    Monday, October 28, 2013 5:49 AM