Generate Excel from XML and Generate XML from Excel

Introduction

The following sections demonstrate how to generate Excel from XML as well as how to generate XML from Excel. Some of the methods presented here are for personal interest and research; some of the methods are to share a quick solution with developers in need.

Background

XML, short for Extensible Markup Language, is a markup language that defines a set of rules for encoding documents in a format that is both human-readable and machine-readable. It emphasizes simplicity, generality, and usability over the Internet and means easy solution to transfer and store data across applications. Like HTML , XML is a subset of SGML (Standard Generalized Markup Language). XML is a platform independent language, so the information formatted in XML can be used in any other platforms (Operating Systems). For more details about XML, please visit this link. Meanwhile, Excel is one of the most used format around the world. So, it is pretty necessary and helpful to convert between XML and Excel.

Generate Excel from XML

Below we are going to read from an existing XML file content and write its content to an Excel file. Though many have switched away from XMLReader to System.XML.Linq.XDocument to manage XML data files. But we still take XmlReader class for example as this way allows us easily parse XML files and manage them.

First, we create an Excel file using Excel Object Model with the following code:

 object misValue = System.Reflection.Missing.Value;            
 Excel.Application myApp=new Excel.Application() ;
 Excel.Workbook myWbk = myApp.Workbooks.Add(misValue);
 Excel.Worksheet myWst = (Excel.Worksheet)myWbk.Worksheets.get_Item(1);

Next we load XML files:

  DataSet ds = new DataSet();
  XmlReader xmlFile ;
  xmlFile = XmlReader.Create("D:\\Product.xml", new XmlReaderSettings());
  ds.ReadXml(xmlFile);

Finally, we have to write into Excel file:

int i = 0;
   int j = 0;
   for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
        {
           for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
               {
                    myWst.Cells[i + 1, j + 1] = ds.Tables[0].Rows[i].ItemArray[j].ToString ();
               }
        }

Generate XML from Excel

The following C# code explains how to create XML from Excel. This time, we use a Microsoft Jet Engine to connect the Excel file and store its content to a Dataset. The simple method WriteXml of Dataset helps us to write to the XML file.

  // Connect to Excel files
     System.Data.OleDb.OleDbConnection MyConnection ;
     System.Data.DataSet ds ;
     System.Data.OleDb.OleDbDataAdapter MyCommand ;
     MyConnection = new System.Data.OleDb.OleDbConnection
     ("provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:\\18.xls';
     Extended Properties=Excel 8.0;");
    MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * 
    from [Sheet1$]", MyConnection);
     MyCommand.TableMappings.Add("Table", "Product");
     ds = new System.Data.DataSet();
     MyCommand.Fill(ds);
     MyConnection.Close();
  // Write to XML
     ds.WriteXml("Product.xml");

The above is the method for personal research. while the one below demonstrates how to quickly achieve such operations. With the help of Spire.XLS for .NET, developers can code as below.

To Convert Excel to XML

Workbook workbook = new Workbook(); 
workbook.LoadFromFile(@"../../Data/test.xls"); 
workbook.SaveAsXml("sample.xml");

To Convert XML to Excel

Workbook workbook = new Workbook(); 
workbook.LoadFromFile(@"../../Data/test.xml"); 
workbook.SaveToFile(@"..\..\result.xlsx", ExcelVersion.Version2010);

That's all about generating Excel from XML as well as generating XML from Excel with different methods for different requirements.