Read Excel Data in csharp

You can get excel sheet data in c# by two methods one is by opening connection and 2nd is by using interop.

In this post I am describing the method 1 by opening connections

METHOD I

        public static DataTable GetExcelData(string _path)
        {
            DataTable ExcelData = new DataTable();
            string ConnectionString = @”Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + _path + “;Extended Properties=’Excel 12.0 Xml;HDR=YES’;”;


            using (OleDbConnection con = new OleDbConnection(ConnectionString))
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();


                    using (OleDbCommand cm = new OleDbCommand(“SELECT * FROM [Sheet1$]”, con))
                    {
                        using (OleDbDataAdapter DA = new OleDbDataAdapter(cm))
                        {
                            DA.Fill(ExcelData);
                        }
                    }
                }
            }




            return ExcelData;
        }

SQL To XML

Certain times we need to export our grid data or our custom query data to xml format.
.Net provides a very easy way to that , The two data object i.e. Dataset and DataTable has built-in overloads to do this.

public void WriteSQLToXML()
        {
 using (SqlConnection con = new SqlConnection(“server=your_sql_server_name;Database=your_db_name;Integrated Security=true;”))
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
using (SqlCommand cm = new SqlCommand(“Select * from your_table_name” , con))
                {
using (SqlDataAdapter DA = new SqlDataAdapter(cm))
                    {
                        DataSet DS = new DataSet(“mytable”);
                        DA.Fill(DS, “mytable”);


    //for winforms
   string _path = Application.StartupPath + “myData.xml”;


   //for webform
   string _path = Server.MapPath(“”) + “\\myData.xml”;


//it will save myData.xml to your local root directory of your application
                       
DS.WriteXml(_path);
                    }
                }
                
            }
        }