In this blog we will know how to import excel records to
database.
Requirement
We have some websites they are repeated so we need the
filtered websites and duplicate websites with there counting numbers. There is
a excel sheet where we have one columns as website and this contains data. We
have to export these data to sql server database. And these records will be
shown in a grid view when we click button. In one button when we click all
duplicate records will be shown in gridview. We need the filter records from
the database removing the duplicate records in grid view then export it to
excel. One more thing, we have many excel files so after doing one file we want
to delete the records from the database and the above all process will be
repeated for those all the excel sheets.
First create a table Websites
create table Websites
(
sno int identity,
website nvarchar(200)
)
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="Default.aspx.cs"
Inherits="_Default"
%>
<!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>Untitled Page</title>
<style
type="text/css">
.style1
{
width: 50%;
}
</style>
</head>
<body>
<form
id="form1"
runat="server">
<div>
<table align="center" class="style1">
<tr>
<td align="center">
<asp:LinkButton ID="insertdata" runat="server" onclick="insertdata_Click">Insert
Data</asp:LinkButton>
</td>
<td align="center">
<asp:LinkButton ID="viewdata" runat="server" onclick="viewdata_Click">View Data</asp:LinkButton>
</td>
<td align="center">
<asp:LinkButton ID="viewfilterdata" runat="server" onclick="viewfilterdata_Click">View Filter Data</asp:LinkButton>
</td>
<td align="center">
<asp:LinkButton ID="viewduplicatedata" runat="server"
onclick="viewduplicatedata_Click">View
Duplicate Data</asp:LinkButton>
</td>
<td align="center">
<asp:LinkButton ID="deletedata" runat="server" onclick="deletedata_Click">Delete Data</asp:LinkButton>
</td>
</tr>
<tr>
<td colspan="9" align="center">
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</td>
</tr>
<tr>
<td colspan="9" align="center">
<asp:Label ID="l1" runat="server" Width="500px"></asp:Label>
</td>
</tr>
</table>
</div>
<asp:Button ID="Button1"
runat="server"
onclick="Button1_Click"
Text="Export
to Excel" />
</form>
</body>
</html>
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using
System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Text;
public partial
class _Default
: System.Web.UI.Page
{
string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
protected void insertdata_Click(object
sender, EventArgs e)
{
OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + Server.MapPath("Websites.xls")
+ ";Extended Properties=Excel 8.0");
OleDbCommand com = new OleDbCommand("select * from [Sheet1$]", conn);
conn.Open();
OleDbDataReader reader =
com.ExecuteReader();
string website = "";
while (reader.Read())
{
website = valid(reader, 0);
insertdataintosql(website);
}
conn.Close();
l1.Text = "Records
Inserted successfully";
}
protected string valid(OleDbDataReader
reader, int str1)
{
object val = reader[str1];
if (val != DBNull.Value)
return val.ToString();
else
return Convert.ToString(0);
}
public void
insertdataintosql(string website)
{
SqlConnection conn = new SqlConnection(connStr);
SqlCommand com = new SqlCommand();
com.Connection = conn;
com.CommandText = "insert
into Websites(website)values(@website)";
com.Parameters.Add("@website",
SqlDbType.NVarChar).Value = website;
com.CommandType = CommandType.Text;
conn.Open();
com.ExecuteNonQuery();
conn.Close();
}
protected void viewdata_Click(object sender, EventArgs
e)
{
l1.Text ="";
SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter sqlda = new SqlDataAdapter("SELECT * from Websites", conn);
DataSet ds = new DataSet();
sqlda.Fill(ds, "Websites");
GridView1.DataSource = ds;
GridView1.DataMember = "Websites";
GridView1.DataBind();
}
protected void viewfilterdata_Click(object
sender, EventArgs e)
{
viewdata1();
}
void viewdata1()
{
l1.Text = "";
SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter sqlda = new SqlDataAdapter("SELECT website FROM Websites GROUP BY website",
conn);
DataSet ds = new DataSet();
sqlda.Fill(ds, "Websites");
GridView1.DataSource = ds;
GridView1.DataMember = "Websites";
GridView1.DataBind();
}
protected void viewduplicatedata_Click(object
sender, EventArgs e)
{
l1.Text = "";
SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter sqlda = new SqlDataAdapter("SELECT website, COUNT(*) AS 'Count'FROM Websites
GROUP BY website", conn);
DataSet ds = new DataSet();
sqlda.Fill(ds, "Websites");
GridView1.DataSource = ds;
GridView1.DataMember = "Websites";
GridView1.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
ExportToExcel("Report.xls",
GridView1);
}
private void
ExportToExcel(string strFileName, GridView dg)
{
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false;
System.IO.StringWriter
oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter
oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
GridView1.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}
public override
void VerifyRenderingInServerForm(Control control)
{
}
protected void deletedata_Click(object
sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(connStr);
SqlCommand com = new SqlCommand();
com.Connection = conn;
//cmd.CommandText = "delete
from emp";
com.CommandText = "truncate
table Websites";
com.CommandType = CommandType.Text;
conn.Open();
com.ExecuteScalar();
conn.Close();
l1.Text = "Records Deleted
successfully";
viewdata1();
}
}
Thanks for reading