Saturday, March 28, 2015

How to create Excel file from XML using C# code

Hello friends welcome back to my new post today i will show you how to create excel from XML data source using C# code in the window form application.
So lets go the steps are as below:-
Steps:
1)Open Visual studio -- Fille-New-Project-Windows

Select your project name as you want and Click OK Button.


My Project Name Is ExcelToXmlConverter.

2) Drag one button from ToolsBox ...If you are not able so see toolbox from your left hand side pls follow below step else go to 3 step.
Go to View---In the view you can able to see ToolsBox  click it.


3) Set property of button as you want and double click on button .

4) Write a below code in the button click event.

Excel.Application xlApp;
            Excel.Workbook xlWorkbook;
            Excel._Worksheet xlWorksheet;
            object misValue = System.Reflection.Missing.Value;
            DataSet dsexcel = new DataSet();
            XmlReader xmlFile;
            int i = 0;
            int j = 0;

            xlApp = new Excel.Application();
            xlWorkbook = xlApp.Workbooks.Add(misValue);
            xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1);
            xmlFile = XmlReader.Create("D:\\Product.xml", new XmlReaderSettings());
            dsexcel.ReadXml(xmlFile);
            for (i = 0; i <= dsexcel.Tables[0].Rows.Count - 1; i++)
            {
                for (j = 0; j <= dsexcel.Tables[0].Columns.Count - 1; j++)
                {
                    xlWorksheet.Cells[i + 1, j + 1] = dsexcel.Tables[0].Rows[i].ItemArray[j].ToString();
                }
           
            }
            xlWorkbook.SaveAs("D:\\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);
            }
            catch (Exception exx)
            {
                obj = null;
            }
            finally
            {
                GC.Collect();

            }
       
        }

code is completed run the program you get good output.

Thank you very much.

If you are not able to run this code pls email to me on my email id that is arjundhilod@gmail.com