Hello all,
I am trying to add values in SQL 2005 particular table from excel sheet containing same column names as table.
How to read the Excel values??
Help me out.
Regards,
Zuber Kazi
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Jignesh TrivediPosted Feb 8, 2012, 10:53 PM
please refer
http://www.codeproject.com/Tips/323427/Uploading-Zip-File-in-Asp-net
http://www.codeproject.com/Articles/16210/Excel-Reader
hope this help.
Satyapriya NayakPosted Feb 6, 2012, 7:45 AM
Try this...
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
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("test.xls") + ";Extended Properties=Excel 8.0");
OleDbCommand com = new OleDbCommand("select * from [Sheet1$]", conn);
conn.Open();
OleDbDataReader reader = com.ExecuteReader();
string Name = "";
string Address = "";
string salary = "";
while (reader.Read())
{
Name = valid(reader, 0);
Address = valid(reader, 1);
salary = valid(reader, 2);
insertdataintosql(Name, Address, salary);
}
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 Name, string Address, string salary)
{
SqlConnection conn = new SqlConnection(connStr);
SqlCommand com = new SqlCommand();
com.Connection = conn;
com.CommandText = "insert into employee(Name,Address,salary)values(@Name,@Address,@salary)";
com.Parameters.Add("@Name", SqlDbType.NVarChar).Value = Name;
com.Parameters.Add("@Address", SqlDbType.NVarChar).Value = Address;
com.Parameters.Add("@salary", SqlDbType.Int).Value = Convert.ToInt32(salary);
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 employee", conn);
DataSet ds = new DataSet();
sqlda.Fill(ds,"employee");
GridView1.DataSource = ds;
GridView1.DataMember = "employee";
GridView1.DataBind();
}
protected void viewfilterdata_Click(object sender, EventArgs e)
{
l1.Text = "";
SqlConnection conn = new SqlConnection(connStr);
//SqlDataAdapter sqlda = new SqlDataAdapter("SELECT salary FROM employee GROUP BY salary", conn);
SqlDataAdapter sqlda = new SqlDataAdapter("SELECT Name FROM employee GROUP BY Name", conn);
DataSet ds = new DataSet();
sqlda.Fill(ds, "employee");
GridView1.DataSource = ds;
GridView1.DataMember = "employee";
GridView1.DataBind();
}
protected void viewduplicatedata_Click(object sender, EventArgs e)
{
l1.Text = "";
SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter sqlda = new SqlDataAdapter("SELECT Name, COUNT(*) AS 'Count'FROM employee GROUP BY Name", conn);
DataSet ds = new DataSet();
sqlda.Fill(ds, "employee");
GridView1.DataSource = ds;
GridView1.DataMember = "employee";
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 employee";
com.CommandType = CommandType.Text;
conn.Open();
com.ExecuteScalar();
conn.Close();
l1.Text = "Records Deleted successfully";
}
}
Thanks
If this post helps you mark it as answer