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:
No comments:
Post a Comment