Saturday, 31 August 2013

Export Excel to SQL database..




Export Excel To SQL DataBase:



In this article I'm trying to explain how to export excel sheet data to sql database.

Follow the below simple steps to achieve this.



Source Code:



Design a Page like below.
 


 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExportExcelToSQL.aspx.cs" Inherits="ExportExcelToSQL" %>  
 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
 < html xmlns="http://www.w3.org/1999/xhtml">  
 < head runat="server">  
   < title>< /title>  
   < style type="text/css">  
     .style1  
     {  
       width: 420px;  
     }  
   < /style>  
 < /head>  
 < body>  
   < form id="form1" runat="server">  
   < div>  
     < table width="100%">  
       < thead>  
         < tr>  
           < td colspan="4" align="center" style="font-size:xx-large; font-style:italic; color:Red">  
              Export Excel To SQL  
           < /td>  
         < /tr>  
         < tr>  
           < td>< /td>  
         < /tr>  
         < tr>  
           < td>< /td>  
         < /tr>                   
       < /thead>  
       < tbody>  
         < tr>  
           < td colspan="3" align="right">  
             < asp:FileUpload ID="FileUpload1" runat="server" Width="354px"   
               style="margin-left: 0px" />  
           < /td>  
           < td colspan="1" width="30%">  
             < asp:Button ID="btnView" runat="server" Text="View" onclick="btnView_Click" />  
           < /td>  
         < /tr>  
         < tr>  
           < td colspan="3" align="right">  
             < asp:DropDownList ID="ddlSheet" runat="server" AutoPostBack="True" Width="49%" Visible="false"   
               Height="18px" OnSelectedIndexChanged="ddlSheet_OnSelectedIndexChanged">  
             < /asp:DropDownList>  
           < /td>  
           < td colspan="1" width="30%">  
           < /td>  
         < /tr>  
         < tr>  
           < td colspan="3" align="center">  
             < asp:Button ID="btnShow" runat="server" Text="Show" Visible="false"   
               onclick="btnShow_Click" />  
           < /td>  
           < td colspan="1" width="30%">  
             < asp:Button ID="btnInsert" runat="server" Text="Insert To SQL DB"   
               Visible="false" onclick="btnInsert_Click" />  
           < /td>            
         < /tr>  
         < tr>  
           < td>< /td>  
           < td class="style1">< /td>  
         < /tr>  
         < tr>  
           < td>< /td>  
           < td class="style1">< /td>  
         < /tr>  
         < tr>  
           < td colspan="4" width="100%" align="center">  
             < asp:GridView ID="gvExcelData" runat="server" CellPadding="4"   
               ForeColor="#333333" GridLines="None" >  
               < AlternatingRowStyle BackColor="White" />  
               < EditRowStyle BackColor="#7C6F57" />  
               <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />  
               <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />  
               <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />  
               <RowStyle BackColor="#E3EAEB" />  
               <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />  
               <SortedAscendingCellStyle BackColor="#F8FAFA" />  
               <SortedAscendingHeaderStyle BackColor="#246B61" />  
               <SortedDescendingCellStyle BackColor="#D4DFE1" />  
               <SortedDescendingHeaderStyle BackColor="#15524A" />  
             </asp:GridView>  
             <br />  
             <asp:Label ID="lblResult" runat="server"></asp:Label>  
           </td>  
         </tr>  
       </tbody>  
     </table>  
   </div>  
   </form>  
 </body>  
 </html>  

 

Code Behind:



1) using System.Data.OleDb; , using this namespace we can read the excel sheet data.
 


 using System;  
 using System.Collections.Generic;  
 using System.Linq;  
 using System.Web;  
 using System.Web.UI;  
 using System.Web.UI.WebControls;  
 using System.Data.OleDb;  
 using System.Data;  
 using System.IO;  
 using System.Configuration;  
 using System.Data.SqlClient;  
 public partial class ExportExcelToSQL : System.Web.UI.Page  
 {  
   protected void Page_Load(object sender, EventArgs e)  
   {  
   }  
   //here it's fetching all sheets available in excel book  
   private void Get_Sheets()  
   {  
     OleDbConnection oconn = null;  
     DataTable dt = null;  
     try  
     {  
       string FilePath = string.Empty;  
       string FileName = string.Empty;  
       if (FileUpload1.HasFile)  
       {  
   //Get File name  
         FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);  
   // Get File extension  
         string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);  
         string FolderPath = "~/ExcelSheets/";  
         FilePath = Server.MapPath(FolderPath + FileName);  
         ViewState["FilePath"] = FilePath;  
         ViewState["FileName"] = FileName;  
    //File save  
         FileUpload1.SaveAs(FilePath);  
       }  
       //Microsoft Office 12.0 Access Database Engine OLE DB Provider  
       oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0");  
       oconn.Open();  
       dt = null;  
  // Get all tables include in that work sheet  
       dt = oconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);  
       if (dt == null)  
       {  
       }  
       String[] sheet = new String[dt.Rows.Count];  
       int i = 0;  
   //Read All sheet names and store in one string Builder  
       foreach (DataRow dr in dt.Rows)  
       {  
         sheet[i] = dr["TABLE_NAME"].ToString();  
         i++;  
       }  
       string[] a = sheet;  
       int j = 0;  
  // Assign all sheet names to DropDownList  
       if (a != null && a.Length > 0)  
       {  
         ddlSheet.Visible = true;  
         //lblsheet.Visible = true;  
         for (j = 0; j < a.Length; j++)  
         {  
           ddlSheet.Items.Add(a[j]);  
         }  
   //Default selected value for DropDown  
         ddlSheet.Items.Insert(0, "<--- Select Excel Sheet --->");  
       }  
       else  
       {  
         ddlSheet.Visible = false;  
         //lblsheet.Visible = false;  
       }  
     }  
     catch (Exception ex)  
     {  
     }  
     finally  
     {  
  //Close the connection  
       if (oconn != null)  
       {  
         oconn.Close();  
         oconn.Dispose();  
       }  
       if (dt != null)  
       {  
         dt.Dispose();  
       }  
     }  
   }  
  // Using this button we fetch all sheets included in WrkBook  
   protected void btnView_Click(object sender, EventArgs e)  
   {  
     Get_Sheets();  
   }  
   protected void ddlSheet_OnSelectedIndexChanged(object sender, EventArgs e)  
   {  
     btnShow.Visible = true;  
   }  
  //Using this we display Excel sheet data into GridView control  
   private void Display()  
   {  
     OleDbConnection oconn = null;  
     DataTable dt1 = new DataTable();  
  //Add dummy columns to datatable.  
     dt1.Columns.Add("ENAME");  
     dt1.Columns.Add("JOB");  
     dt1.Columns.Add("MGR");  
     dt1.Columns.Add("SAL");  
     dt1.Columns.Add("COMM");  
     try  
     {  
       string FileName = ViewState["FileName"] as string;  
       string FilePath = ViewState["FilePath"] as string;  
       oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0");  
  //select file name based upon dropdown selecteditem.  
       OleDbCommand ocmd = new OleDbCommand("select * from [" + ddlSheet.SelectedItem.ToString() + "]", oconn);  
       oconn.Open();  
  //Read all rows and then store in DataTable  
       OleDbDataReader odr = ocmd.ExecuteReader();  
       string ENAME=string.Empty;  
       string JOB=string.Empty;  
       string MGR=string.Empty;  
       string SAL=string.Empty;  
       string COMM=string.Empty;  
       while (odr.Read())  
       {  
         ENAME = odr["ENAME"].ToString();  
         JOB = odr["JOB"].ToString();  
         MGR = odr["MGR"].ToString();  
         SAL = odr["SAL"].ToString();  
         COMM = odr["COMM"].ToString();  
         DataRow dr = dt1.NewRow();  
         dr["ENAME"] = ENAME;  
         dr["JOB"] = JOB;  
         dr["MGR"] = MGR;  
         dr["SAL"] = SAL;  
         dr["COMM"] = COMM;  
         dt1.Rows.Add(dr);       
       }  
  //Display data to gridview if Records are found  
       if (dt1.Rows.Count > 0)  
       {  
         Session["Table"] = dt1;  
         gvExcelData.Visible = true;  
         gvExcelData.DataSource = dt1;  
         gvExcelData.DataBind();  
       }  
     }  
     catch (DataException ex)  
     {  
     }  
     finally  
     {  
       if (oconn != null)  
       {  
         oconn.Close();  
         oconn.Dispose();  
       }  
       if (dt1 != null)  
       {  
         dt1.Dispose();  
       }  
     }  
   }  
   //here it's displaying all the records into one gridview control  
   protected void btnShow_Click(object sender, EventArgs e)  
   {  
     btnInsert.Visible = true;  
     Display();  
   }  
   private void InsertIntoDB()  
   {  
  int count=0;  
     SqlConnection con = new SqlConnection("DataBase=ENGSOFT;User Id=sa;Password=P@ssword9");  
     try  
     {        
       con.Open();  
       SqlCommand cmd;  
       DataTable dt = Session["Table"] as DataTable;  
       if (dt.Rows.Count > 0)  
       {  
    //Read each and everyrow of datatable and insert each and every row to SQL DataBase.  
         foreach (DataRow dr in dt.Rows)  
         {  
           string Ename = dr["ENAME"].ToString();  
           string Job = dr["JOB"].ToString();  
           string MGR = dr["MGR"].ToString();  
           string Sal = dr["SAL"].ToString();  
           string comm = dr["COMM"].ToString();  
   //normal insert query.  
           cmd = new SqlCommand("INSERT INTO EMP(ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(@ename,@job,@mgr,@hiredate,@sal,@comm,@deptno)", con);  
           cmd.Parameters.AddWithValue("@ename", Ename);  
           cmd.Parameters.AddWithValue("@job", Job);  
           cmd.Parameters.AddWithValue("@mgr", MGR);  
           cmd.Parameters.AddWithValue("@hiredate", DateTime.Now);  
           cmd.Parameters.AddWithValue("@sal", Sal);  
           cmd.Parameters.AddWithValue("@comm", comm);  
           cmd.Parameters.AddWithValue("@deptno", "1");  
           int n = cmd.ExecuteNonQuery();  
   count = count + n;  
         }  
       }  
     }  
     catch (Exception ex)  
     {  
     }  
     finally  
     {  
       con.Close();  
       con.Dispose();  
       lblResult.Text = count + " Records Inserted Successfully...!!!";  
     }  
   }    
   //here total records inserted into sql database  
   protected void btnInsert_Click(object sender, EventArgs e)  
   {  
     InsertIntoDB();  
   }  
 }  

OutPut: