Excel Data

The second method is by using office interop , If you want to get excel data in c# you can get it by using office interop object  , excel data then can be used or transferred any were.

METHOD II

      public static System.Data.DataTable GetExcelData(string _path)
        {


            System.Data.DataTable ExcelData = new System.Data.DataTable();
            ExcelData.Columns.Add(new DataColumn(“ProductName”, typeof(string)));
           


            Excel.Application xlApp = new Excel.ApplicationClass();
            Excel._Workbook xlWorkbook = xlApp.Workbooks.Open(_path);
            Excel._Worksheet xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets.get_Item(1);
            Excel.Range xlRange = xlWorksheet.UsedRange;




            for (int r = 2; r < = xlRange.Rows.Count; r++)
            {
                DataRow _r = ExcelData.NewRow();


                _r[0] = (xlRange.Cells[r, 1] as Excel.Range).Value2.ToString();


                ExcelData.Rows.Add(_r);
            }


            xlWorkbook.Close(true, Type.Missing, Type.Missing);
            xlApp.Quit();


            return ExcelData;
        }

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;
        }