Net-informations.com
SiteMap  | About    

How to create Excel file from XML

XML is a platform independent language, so the information formatted in XML can be used in any other platforms (Operating Systems). XML is a self describing language and it gives the data as well as the rules to identify what the data it contains.

Here we are going to read from an XML file content and write the same content to an Excel file. Here using an XmlReader for read the XML file to the Dataset . Loop through the Dataset and add the content to the Excel file . For create an excel file you have to add reference of Excel library to you project , follow the link C# Create Excel File for details.




using System;
using System.Data;
using System.Windows.Forms;
using System.Xml;
using System.Data;
using Excel = Microsoft.Office.Interop.Excel; 

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            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("Specify path here\\xml2excel.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);

            MessageBox.Show("Done .. ");
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        } 
    }
}

Click here to download the input file product.xml : product.xml




net-informations.com (C) 2016    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.