Create excel file as Data.xls and change the name of sheet1 to users

in users sheet type ID,Name,Address,Phoneno in first row to inserting data

and for display the data in drag gridview control.

file name: Data.xls

sheet name: users

Step1: In Default.aspx page take four textbox’s(ID,Name,Address,Phoneno) and one submit button

change Textbox ID in Properties

ID textbox =txtID

Name textbox =txtName

Address textbox=txtAddres

Phoneno textbox=txtPhoneno

step2: Connectionstring write below code

“ConnectionString” for for Office 97-2003
string  connectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\\lokeshoracle\\data.xls;Extended Properties=Excel 8.0;”;

“ConnectionString” for for Office 2007

string  connectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\\excel\\data.xls;Extended Properties=\”Excel 8.0;HDR=YES;\“”;

step3: Displaying excel data in gridview

In Page_load event

Dont forget to use below namespaces

using System.Data;
using System.Data.OleDb;
using System.IO;

protected void Page_Load(object sender, EventArgs e)
{
string  connectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\\lokeshoracle\\data.xls;Extended Properties=Excel 8.0;”;
OleDbConnection objConn = new OleDbConnection(connectionString);

string query = “select * from [users$]“;
OleDbCommand objCmd = new OleDbCommand(query,objConn);

DataSet objDs = new DataSet();
OleDbDataAdapter objDa = new OleDbDataAdapter(objCmd);

objConn.Open();
objDa.Fill(objDs);
objCmd.ExecuteNonQuery();
GridView1.DataSource = objDs;
GridView1.DataBind();
objConn.Close();
}

step4: Inserting the data into excel sheet

protected void btnSubmitExcelData_Click(object sender, EventArgs e)
{
OleDbConnection objConn = new OleDbConnection(connectionString);
//string query = “insert into [users$] values(1,’bbb’,’bbb’,’2345′)”;
string query = “insert into [users$](ID,Name,Address,Phoneno) values(”+txtID.Text+”,’”+txtName.Text+”‘,’”+txtAddress.Text+”‘,’”+txtPhoneno.Text+”‘)”;
OleDbCommand objCmd = new OleDbCommand(query, objConn);

objConn.Open();
objCmd.ExecuteNonQuery();
objConn.Close();
}

Happy coding…

Step1: Create table in MS-Access

Open Microsoft Access –  Goto File menu in menubar – click New – choose Blank Database – give database file name (choose the path E drive or D drive) – save the file name with Employee.mdb so the path is E:\Employee.mdb

Employee is Database name

Choose Tables in object – Double click in Create table in Design View

Then Add FieldNames and DataType

save table name as TblEmployee

Step2: Drag four textbox’s and submit button from toolbox into Default.aspx

Change ID in properties of TextBox’s

TextBox1    —–    txtEmployeecode

TextBox2    —–    txtEmployeeName

TextBox3    —–    txtDesignation

TextBox4    —–    txtAddress

Source Code in Default.aspx page

<table align=”center”

style=”border: 1px solid #3366CC; font-family: Arial, Helvetica, sans-serif; font-size: 10pt;”>

<tr>

<td align=”center” colspan=”2″ bgcolor=”#D2D2FF”>

<asp:Label ID=”Label5″ runat=”server” Text=”Employee Details” Font-Bold=”True”></asp:Label>

</td>

</tr>

<tr>

<td >

<asp:Label ID=”Label1″ runat=”server” Text=”EmployCode”></asp:Label>

</td>

<td>

<asp:TextBox ID=”txtEmployeecode” runat=”server”></asp:TextBox>

</td>

</tr>

<tr>

<td <asp:Label ID=”Label3″ runat=”server” Text=”EmployName”></asp:Label>

</td>

<td>

<asp:TextBox ID=”txtEmployeeName” runat=”server”></asp:TextBox>

</td>

</tr>

<tr>

<td >

<asp:Label ID=”Label2″ runat=”server” Text=”Designation”></asp:Label>

</td>

<td>

<asp:TextBox ID=”txtDesignation” runat=”server”></asp:TextBox>

</td>

</tr>

<tr>

<td valign=”top”>

<asp:Label ID=”Label4″ runat=”server” Text=”Address”></asp:Label>

</td>

<td>

<asp:TextBox ID=”txtAddress” runat=”server” Height=”67px” Width=”225px”></asp:TextBox>

</td>

</tr>

<tr>

<td >

&nbsp;</td>

<td>

&nbsp;</td>

</tr>

<tr>

<td >

&nbsp;</td>

<td>

<asp:Button ID=”Button1″ runat=”server” onclick=”Button1_Click” Text=”Submit” />

</td>

</tr>

</table>

Step3: Double click Submit Button then write below code to insert form data into MS-Access database

//ConnectionString write above page_load event

OleDbConnection objConn = new OleDbConnection(”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\\Employee.mdb;User Id=;Password=;”);

protected void Page_Load(object sender, EventArgs e)

{

}

protected void Button1_Click(object sender, EventArgs e)

{

string cmdString = “insert into TblEmployee(Employeecode,EmployeeName,Designation,Address) values(@Employeecode,@EmployeeName,@Designation,@Address)“;

OleDbCommand objCmd = new OleDbCommand(cmdString, objConn);

objCmd.Parameters.Add(”@Employeecode”, SqlDbType.Text).Value = txtEmployeecode.Text;

objCmd.Parameters.Add(”@EmployeeName”, SqlDbType.Text).Value = txtEmployeeName.Text;

objCmd.Parameters.Add(”@Designation”, SqlDbType.Text).Value = txtDesignation.Text;

objCmd.Parameters.Add(”@Address”, SqlDbType.Text).Value = txtAddress.Text;

objConn.Open();

objCmd.ExecuteNonQuery();

objConn.Close();

}