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">

<title>Untitled Page</title>

.style1

{

width: 50%;

}

</style>

</head>

<body>

<div>

<tr>

<asp:LinkButton ID="insertdata" runat="server" onclick="insertdata_Click">Insert

Data</asp:LinkButton>

</td>

<asp:LinkButton ID="viewdata" runat="server" onclick="viewdata_Click">View Data</asp:LinkButton>

</td>

<asp:LinkButton ID="viewfilterdata" runat="server" onclick="viewfilterdata_Click">View Filter Data</asp:LinkButton>

</td>

<asp:LinkButton ID="viewduplicatedata" runat="server" onclick="viewduplicatedata_Click">View Duplicate Data</asp:LinkButton>

</td>

<asp:LinkButton ID="deletedata" runat="server" onclick="deletedata_Click">Delete Data</asp:LinkButton>

</td>

</tr>

<tr>

<asp:GridView ID="GridView1" runat="server">

</asp:GridView>

</td>

</tr>

<tr>

<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