Create Excel file from XML file

I will discuss how to create Excel file from XML Data using C#

XML is platform independent language, any operating system and platform can able to parse XML document. It is a format to represent the data. As it is platform independent, so that most of the programming languages can able to parse it without any plug-in or third party tool.

Microsoft C# and ASP.net application also support parsing of XML using it's in built class library. You can parse XML data and using the data you can able to generate Excel sheet. For this you have to add Microsoft Excel reference in your ASP.Net web page. Follow the following steps to make reference.

Step 1) Go to Project Tab of your visual studio environment àAdd reference

Step 2) Goto COM tab  àMicrosoft Excel 12.2 object library à OK.

Now you have added successfully the Microsoft object library in your project.

Step 3) Add the following namespace in your ASP.Net page

using Excel = Microsoft.Office.Interop.Excel; 

Step 4) Create Product.xml file with the code below.

<Table>
<Product>
<Product_id>1</Product_id>
<Product_name>Product 1</Product_name>
<Product_price>1000</Product_price>
</Product>
<Product>
<Product_id>2</Product_id>
<Product_name>Product 2</Product_name>
<Product_price>2000</Product_price>
</Product>
<Product>
<Product_id>3</Product_id>
<Product_name>Product 3</Product_name>
<Product_price>3000</Product_price>
</Product>
<Product>
<Product_id>4</Product_id>
<Product_name>Product 4</Product_name>
<Product_price>4000</Product_price>
</Product>
</Table> 

Step 5) Put the following code in buttons on click event.

Excel.Application xlApp ;

Excel.Workbook xlWorkBook ;

Excel.Worksheet xlWorkSheet ;

object misValue = System.Reflection.Missing.Value;

DataSet ds = new DataSet();
XmlReader xmlFile ;
int i = 0;
int j = 0;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xmlFile = XmlReader.Create("Product.xml", new XmlReaderSettings());
ds.ReadXml(xmlFile);
for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
    for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
    {
        xlWorkSheet.Cells[i + 1, j + 1] = ds.Tables[0].Rows[i].ItemArray[j].ToString ();
    }
}
xlWorkBook.SaveAs("D\\filename.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue,misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue,misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlApp);
releaseObject(xlWorkBook);
releaseObject(xlWorkSheet);
Response.write("File has saved in D drive");