Excel To Database (Sample Code)

Excel To Database (Sample Code)

  08:13    Unknown

---

i m writing this article because i tried a lot to find a better way to insert excel data in sql database table .you can download sample code from HERE .this time i m writing this article with more description.
Step 1: To complete any task ... Break into parts :)
To insert the data in sql database table logically what steps should be taken -

1. Upload the Excel to a place so we can work with it .
2. Get the data of uploaded excel in your dataset or datatable .  
3. insert the dataset or datatable in your database table.  

Step 2: Prerequisites

To export excel data in our database table some important things to do as follows -

• Add Microsoft.Office.Interop.Excel.dll  in BIN folder of your project . ( Right click on BIN folder > Add Reference > Under .Net Tab > Select  Microsoft.Office.Interop.Excel.dll > OK) .
• Namespaces   

  using System.Data.OleDb;  // oledb namsespace to create connectivity with excel
  using System.IO;    // To save Excel in a specific folder
  using System.Data;  // To insert data in Database
  

• Create a folder in  Any drive and Give the path accordingly in Code ( You can create  folder in your web app or in server's drive )  .
• Database Table should contain Columns(can contain extra column in comparison to your given Excel) accordingly to the excel which is to be upload . 

Step 3: 

As now you are ready with prerequisites create an .aspx with following code - 

Add asp file upload in .aspx page and button to launch the event . 

Step 4: Create a Method to connect with Excel using Oledb

Create a method to get the excel data in DataSet . after when data comes in dataset , data can be easily inserted in our database . 

protected void FillDataSet()
    {

        try
        {

             // create object like sheet and app for office 
            Microsoft.Office.Interop.Excel.ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Worksheet oSheet;

            string strFilename = FileUpload.PostedFile.FileName;
            strFilename = System.IO.Path.GetFileName(strFilename);
            string ext = Path.GetExtension(strFilename);
            bool hasHeaders = true;
            string HDR = hasHeaders ? "Yes" : "No";
            string strConn;

            //check extension of file 

            if (ext.ToLower() == ".xls")
            {


                //creaate connection with excel using OLEDB
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fu + " ;Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=1\"";

                Microsoft.Office.Interop.Excel.WorkbookClass workBook = (Microsoft.Office.Interop.Excel.WorkbookClass)app.Workbooks.Open(fu, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                oSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
                app.Visible = false;

                //You must use the $ after the object you reference in the spreadsheet

                OleDbDataAdapter myCmd = new OleDbDataAdapter("SELECT * FROM [" + oSheet.Name + "$]", strConn);


                myCmd.Fill(myDs);


                string source = @"D:\ExelFileForDetail\" + strFilename;
                string target = @"D:\ExelFileForDetail\temp\" + strFilename;


                //move file to another folder if exists (useful when client have to upload file on daily basis)
                if (File.Exists(target))
                    File.Delete(target);
                File.Move(source, target);

            }

            else if (ext.ToLower() == ".xlsx")
            {



                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fu + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=2\"";

                //You must use the $ after the object you reference in the spreadsheet

                Microsoft.Office.Interop.Excel.WorkbookClass workBook = (Microsoft.Office.Interop.Excel.WorkbookClass)app.Workbooks.Open(fu, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                oSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
                app.Visible = false;

                //You must use the $ after the object you reference in the spreadsheet

                OleDbDataAdapter myCmd = new OleDbDataAdapter("SELECT * FROM [" + oSheet.Name + "$]", strConn);

                //fill dataset with your excel data

                myCmd.Fill(myDs);
                string source = @"D:\ExelFileForDetail\" + strFilename;
                string target = @"D:\ExelFileForDetail\temp\" + strFilename;


                if (File.Exists(target))
                    File.Delete(target);
                File.Move(source, target);
            }
            else
            {

                Response.Write("Check the extension of uploaded file.");

            }
        }
        catch (Exception ex)
        {

            Response.Write("Error !" + ex.Message);
        }

}

Step 5 : Save File and insert Data to table

To insert Data i m using LINQ  . you can  use any technique to insert . 

protected void btnsave_Click(object sender, EventArgs e)
    {
        try
        {

          

            int x, j;

            string strFilename = FileUpload.PostedFile.FileName;
            strFilename = System.IO.Path.GetFileName(strFilename);
            string ext = Path.GetExtension(strFilename);


            // check file extension and give alert to upload the right extension
            if (ext.ToLower() != ".xls" && ext.ToLower() != ".xlsx")
            {
                string str = "";

                if (!Page.IsStartupScriptRegistered("clientScript"))
                {
                    Page.RegisterStartupScript("clientScript", str);
                }

                return;


            }
            //save file to  folder
            FileUpload.PostedFile.SaveAs(@"D:\ExelFileForDetail\" + strFilename);

            fu = @"D:\ExelFileForDetail\" + strFilename;
          
            myDs.Clear();
            int t;
         
            try
            {
                FillDataSet();
                t = myDs.Tables[0].Rows.Count;
            }
            catch (Exception ex)
            {
                //throw ex;
                Response.Write(ex.Message);
                return;
            }

            DataSet ds1 = new DataSet();

            if (myDs.Tables[0].Rows.Count == 0)
            {
                Response.Write("This file Can not Upload /error in File");
                return;

            }
            t = myDs.Tables[0].Rows.Count;

            try
            {

                for (x = 0; x < myDs.Tables[0].Rows.Count; x++)
                {

                    for (j = 0; j < 4; ) //Traverse loop to all column of your Dataset
                    {

                        Detail Dt = new Detail();
                        Dt.Name = Convert.ToString(myDs.Tables[0].Rows[x][j]).ToString(); j = j + 1;
                        Dt.BirthDate = Convert.ToDateTime(myDs.Tables[0].Rows[x][j]); j = j + 1;
                        Dt.Address = Convert.ToString(myDs.Tables[0].Rows[x][j]).ToString(); j = j + 1;
                        Dt.Mobile = Convert.ToString(myDs.Tables[0].Rows[x][j]).ToString(); j = j + 1;


                        //insert data in your database

                        db.StoredProcedure1(Dt.Name, Dt.BirthDate, Dt.Address, Dt.Mobile);
                    }

                }

            }

            catch (Exception ex)
            {
                Response.Write("Error" + ex.Message);

            }

        }
        catch (Exception ex)
        {

            Response.Write(ex.Message);
        }
        myDs.Clear();  //clear your dataset
    }