Excel To Database in C# & asp.net

Excel To Database in C# & asp.net

  21:58    Sarvesh Kushwaha

---

Convert your excel to database table in c# using asp.net ,use microsoft Excel reference

Find Code and better Description over here - LINK

public partial class excel : System.Web.UI.Page
{
    public string filename1 { get; set; }
    protected string UploadFolderPath = "~/Uploads/";


    DataSet ds = new DataSet();
    //System.Web.HttpContext.Current.Server.MapPath("Book12.xls");
    public DataSet GetExcel(string fileName)
    {

        Application oXL;
        Workbook oWB;
        Worksheet oSheet;
        Range oRng;
        try
        {
            //  creat a Application object 
            oXL = new ApplicationClass();
            //   get   WorkBook  object 
            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);

            //   get  WorkSheet object 
            oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[1];
            System.Data.DataTable dt = new System.Data.DataTable("dtExcel");

            ds.Tables.Add(dt);
            DataRow dr;

            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++)
            {
                dt.Columns.Add("column" + j, System.Type.GetType("System.String"));

            }

            //string colString = sb.ToString().Trim(); 
            //string[] colArray = colString.Split(':'); 

            //  get data in cell 
            for (int i = 1; i <= iValue; i++)
            {
                dr = ds.Tables["dtExcel"].NewRow();
                for (int j = 1; j <= jValue; j++)
                {
                    oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];
                    string strValue = oRng.Text.ToString();
                    dr["column" + j] = strValue;
                }
                ds.Tables["dtExcel"].Rows.Add(dr);


            }


            return ds;
        }
        catch (Exception ex)
        {
            Label1.Text = "Error: ";
            Label1.Text += ex.Message.ToString();
            return null;
        }

    }
    protected void btninsert_Click(object sender, EventArgs e)
    {       
        if (fileupload.HasFile)
        {
            foreach (Process process in Process.GetProcessesByName("EXCEL"))
            {
                if (process.MainModule.ModuleName.ToUpper().Equals("EXCEL.EXE"))
                {
                    process.Kill();
                    Thread.Sleep(2000);
                    break;
                }
            }
            try
            {

                filename1 = Path.GetFileName(fileupload.FileName);
                if (Path.GetExtension(filename1) == ".xls" || Path.GetExtension(filename1) == ".xlsx" || Path.GetExtension(filename1) == ".csv")
                {
                    fileupload.SaveAs(Server.MapPath(this.UploadFolderPath) + filename1);
                }
                Label1.Text = "Upload status: File uploaded!" + Path.GetExtension(filename1);
                string Patfilename = System.Web.HttpContext.Current.Server.MapPath("~/uploads/") + filename1;
                GetExcel(Patfilename);
                string gettablename = ddltable.SelectedItem.Text.ToString();
                //Server.MapPath(this.UploadFolderPath) + filename ;
                //First create a connection string to destination database
                string connectionString;
                connectionString = "Data Source=.;Initial Catalog=classic;Trusted_connection=True";
                //Open a connection with destination database;
                using (SqlConnection connection =
                       new SqlConnection(connectionString))
                {
                    connection.Open();

                    //Open bulkcopy connection.
                    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
                    {
                        //Set destination table name
                        //to table previously created.

                        bulkcopy.DestinationTableName = gettablename;

                        try
                        {
                            bulkcopy.WriteToServer(ds.Tables["dtExcel"]);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }
                        finally
                        {
                            connection.Close();
                        }
                        Label2.Text = "inserted succesfully";
                    }
                }
            }
            catch (Exception ex)
            {
                Label1.Text = "Upload status: The file could not be uploaded.The following error occured:" + ex.Message;

            }
        }
        else
        {
            Label1.Text = "Upload status: The file could not be uploaded.";
        }
    }
}