CODEWITHSUNDEEP
c#.netxmlexcel
John Smith
John Smith

Reputation: 4516

How can you programmatically import XML data into an Excel file?

The following code should help me export XML data from Excel:

 Excel.Application application = new Excel.Application();
            application.Workbooks.Open(excelFilepath,
                                    Missing.Value,
                                    Missing.Value,
                                    Missing.Value,
                                    Missing.Value,
                                    Missing.Value,
                                    Missing.Value,
                                    Missing.Value,
                                    Missing.Value,
                                    Missing.Value,
                                    Missing.Value,
                                    Missing.Value,
                                    Missing.Value,
                                    Missing.Value,
                                    Missing.Value);

        string data = null;
        application.ActiveWorkbook.XmlMaps[1].XmlExport(out data);

Now I'm looking for code that will help me Import XML data into Excel, just as I would import a file manually by clicking on the "Developer" tab and then "Import XML".

Can someone tell me how to do it?

Upvotes: 2

Views: 12551

Answers (2)

psydollar
psydollar

Reputation: 39

I meet the same problem too: XmlMaps[1].Import() does not work, it just crashed, and I don't know how to define the required XmlMaps. I found the XmlMaps.Add(string schema) method, but I don't have the xml schema to pass into it. All in my hand is a xml file.

Here is my situation: I have a xml, and I can manually import the xml to excel2007 by draging the xml file into excel2007. Then it will popup a dialogue, ask me how to open the xml. There are 3 options: as xml, as readonly excel worksheet, as xml source. Choose the first option, and my xml data is automatically imported into excel.

Then I try to do the same thing via c#, but every method I found is no use.

I stuck at it for 3 hours.

And very fortunately, I found the solution finally: You don't need to use XmlMaps, just use the OpenXml() instead.

Here is the c# way to import legal xml into excel.

  1. create a new c# project. and right mouse key on your project, click "Add Reference", choose ".Net" Tab, and select "Microsoft.Office.Interop.Excel"(If not found it, check if your visual studio have installed office develop utility), click "OK". By this, you can operate excel in c#.

  2. paste the code following. Notice the "xApp.Workbooks.OpenXML()" method, we pass the "xlXmlLoadImportToList" option into it, its function is the same as my draging xml into excel and select first option operate. Here is the link explaining the different options http://msdn.microsoft.com/zh-cn/library/microsoft.office.interop.excel.workbooks.openxml(v=office.11).aspx

  3. replace the "xml" and "xlsx" variable to your own filepath, suce as "d:\my.xml" "d:\my.xlsx"

Then your xml is saved as xlsx. Good luck!

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Reflection;
    using Microsoft.Office.Interop.Excel;
    using Microsoft.Office.Interop;
    using System.IO;

    namespace SaveXmlAsExcel
    {
        class Program
        {
            static void Main(string[] args)
            {
                string xml  = args[0];
                string xlsx = args[1];

                if (false == File.Exists(xml))
                {
                    Console.WriteLine("{0} file not exist", xlsx);
                    return;
                }

                Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook excelWorkBook = xApp.Workbooks.OpenXML(xml, Type.Missing, Microsoft.Office.Interop.Excel.XlXmlLoadOption.xlXmlLoadImportToList);

                excelWorkBook.SaveAs(xlsx, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange);

                excelWorkBook.Close();
                xApp.Workbooks.Close();
            }
        }
    }

Here is my xml: it must have a legal format that excel can parse it successfully.

<AwardProps xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <AwardPropRecord>
    <g_AwardProps>
      <entry>
        <Id>0</Id>
        <IsElite>0</IsElite>
        <GoldCost>0</GoldCost>
        <Exp>0</Exp>
      </entry>
      <entry>
        <Id>255</Id>
        <IsElite>255</IsElite>
        <GoldCost>255</GoldCost>
        <Exp>255</Exp>
      </entry>
    </g_AwardProps>
  </AwardPropRecord>
</AwardProps>

Upvotes: 2

Nevyn
Nevyn

Reputation: 2683

Using the same system, and the MSDN website. It took me a very short time to find the following Link:

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xmlmap.import%28v=office.14%29

The Methods related to XmlMap within the Excel Interop. That specific link is to the XmlMap.Import function, which takes a url (filePath) and imports xml data.

There is also on that page a link to the ImportXml() function, which can accept xml strings as direct input to the function rather than using a URL.

The code assumes, for both of these, that the XmlMap is already defined. I myself am not aware of how to programmatically define an XmlMap in an Excel Document via the interface.

Upvotes: 1

Related Questions