If at first you don't succeed, call it version 1.0

Contact Me

Sarvesh Kushwaha
Email : sarveshkushwaha@outlook.com

Total Pageviews

Powered by Blogger.

Saturday 3 November 2012

Excel To Database (Sample Code)


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
    }


6 comments:

  1. THANK YOU MUCH SIR ! THIS WILL HELP ME SO MUCH

    ReplyDelete
  2. Sir can we use SSIS for this purpose?.

    ReplyDelete
  3. Hi Sarvesh,

    your code is amazing thanks.
    Can you help to upload and save multi sheets in one click(the hall workbook), I try to use your code with forech loop but it does'nt work for me,

    ReplyDelete
  4. Hi Mohamed EL AZZOUZI
    try this ...
    http://dontbreakthebuild.com/2011/01/30/excel-and-c-interop-with-net-4-how-to-read-data-from-excel/

    or

    try this to see can we iterate using this
    using (StreamWriter sw = File.CreateText("ExtractedText.txt"))
    {
    var excelApp = new Excel.Application();
    var workBook = excelApp.Workbooks.Open(thisFile);

    foreach (var sheet in workBook.Worksheets)
    {
    foreach (var row in sheet.UsedRange.Rows)
    {
    foreach (var cell in row.Columns)
    {
    sw.Write(cell.Value + " ");
    }
    sw.WriteLine();
    }
    }
    }

    ReplyDelete