Import data from excel c# code

Here i share how to import data from excel to sql server using c#.

Follow steps to import data from excel using c# :

1.    Import Namespace :

using System.Reflection;
using Microsoft.Office.Interop.Excel

2. Add common function for generate dataset from excel data.

  private DataSet generateData()

    {

        DataSet ds = new DataSet();

        string savepath;

        string fileName = Server.MapPath("..//TempFiles//") + FileUploadEx.PostedFile.FileName;

        FileUploadEx.SaveAs(Server.MapPath("..//TempFiles//") + FileUploadEx.FileName);

        savepath = Server.MapPath("..//TempFiles//") + FileUploadEx.PostedFile.FileName;

        Application oXL = new ApplicationClass();

        Workbook oWB;

        Worksheet oSheet;

        Range oRng;

        Range oRng1;

        try

        {

            oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value,

            Missing.Value, Missing.Value, Missing.Value,

            Missing.Value, Missing.Value, Missing.Value,

            Missing.Value, Missing.Value, Missing.Value, Missing.Value,

            Missing.Value, Missing.Value);

            oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[1];

            System.Data.DataTable dt = new System.Data.DataTable("dtExcel");

         

            ds.Tables.Add(dt);

            DataRow dr;

            System.Data.DataTable dtValidData = new System.Data.DataTable("dtValid");

            System.Data.DataTable dtInvalidData = new System.Data.DataTable("dtInvalid");

            DataSet dsSummary = new DataSet();

            dsSummary.Tables.Add(dtValidData);

            dsSummary.Tables.Add(dtInvalidData);

            DataRow drValid;

            DataRow drInValid;

            StringBuilder sb = new StringBuilder();

            int jValue = oSheet.UsedRange.Cells.Columns.Count;

            int iValue = oSheet.UsedRange.Cells.Rows.Count;

            //  get data columns

            for (int j = 1; j <= jValue; j++)

            {

                oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j];

                string strValue = oRng.Text.ToString().Replace(" ", "").Replace("/", "");

                //if (strValue != "Mobile" && j == 1)

                //{

                //    RegisterStartupScript("key", "<script>alert('File is not in proper Format..Column Name is mismatch');</script>");

                //    //return;

                //}

                dt.Columns.Add(strValue, System.Type.GetType("System.String"));

                dtValidData.Columns.Add(strValue, System.Type.GetType("System.String"));

                dtInvalidData.Columns.Add(strValue, System.Type.GetType("System.String"));

            }

            string stlmntdate = DateTime.Now.ToShortDateString();

            for (int i = 2; i <= iValue; i++)

            {

                dr = ds.Tables["dtExcel"].NewRow();

                for (int j = 1; j <= jValue; j++)

                {

                    oRng1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j];

                    string ColValue = oRng1.Text.ToString().Replace(" ", "").Replace("/", "");

                    oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];

                    string strValue = oRng.Text.ToString();

                    dr[ColValue] = strValue;

                }

                ds.Tables["dtExcel"].Rows.Add(dr);

            }

            oWB.Close(Missing.Value, fileName, Missing.Value);

            return ds;

           

    }

    catch(Exception ex)

    {

        Common.InsertErorLog(Common.GetCurrentPageName(), ex.ToString());

        RegisterStartupScript("key", "<script>alert('" + ex.Message.Replace("'", "") + "');</script>");

   

    }

        return ds;

 

    }

3. Now Call this function where you want:

DataSet ds = generateData();

4. Now Do operation "DataSet ds" as you want