Export Excel To SQL DataBase
In this Artical i'm trying to explain how to export excel data to sql database.? For this i refer the namespace OLEDB provider for reading Excel sheet and for inserting into SQL Database using SqlClient namespace.
Export Excel To SQL DataBase:
In this artical 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:
Follow
nice job sir, can you send me database scripts....