how to insert data into excelsheet

Posted by Parthibansk under ASP.NET on 3/7/2013 | Points: 10 | Views : 1772 | Status : [Member] | Replies : 4
i got a user registration form.. while users insert values data should be saved to excelsheet not to table..

how to code for the above..




Responses

Posted by: Satyapriyanayak on: 3/7/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace Insert_data_into_excel
{
public partial class Form1 : Form
{
OleDbConnection con;
OleDbCommand com;
string str;

public Form1()
{
InitializeComponent();
}

private void btn_add_Click(object sender, EventArgs e)
{
try
{

com = new OleDbCommand();
con = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='d:\\test.xls';Extended Properties=Excel 8.0;");
con.Open();
com.Connection = con;
str = "Insert into [sheet1$] (EID,EName,Designation) values('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "')";
com.CommandText = str;
com.ExecuteNonQuery();
con.Close();
MessageBox.Show("Records Added Successfully");
textBox1.Text = "";
textBox1.Focus();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}

}
}
}


If this post helps you mark it as answer
Thanks

Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rimi1289 on: 3/7/2013 [Member] Starter | Points: 25

Up
0
Down
Hi,

It will difficult to explain every thing here.

Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 3/8/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
parthi check this code

http://www.dotnetfunda.com/codes/code4247-handled-back-end-like-excel.aspx

Mark as Answer if its helpful to you

Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sailajareddy on: 3/8/2013 [Member] Starter | Points: 25

Up
0
Down
Sample code which stores data from form to Excel sheet.
Aspx Code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SavingdatatoExcel.aspx.cs" Inherits="Forum_Topics_SavingdatatoExcel" %>


<!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>
</head>
<body>
<form id="form1" runat="server">
<div>
ENTER NAME:<asp:textbox ID="txtName" runat="server"></asp:textbox>
<br />
ENTER AGE:<asp:textbox ID="txtAge" runat="server"></asp:textbox>
<br />
ENTER ADDRESS<asp:textbox ID="txtAddr" runat="server"></asp:textbox>
<br />
<asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" />
<br />
<asp:Label ID="lblMsg" runat="server"></asp:Label>

</div>
</form>
</body>
</html>


Code Behind:

using System;

using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;

public partial class Forum_Topics_SavingdatatoExcel : System.Web.UI.Page
{
private DataTable _dt;
public DataTable dt
{
get
{
return _dt;
}
set
{
_dt = value;
}
}

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataTable dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Age", typeof(string));
dt.Columns.Add("Addr", typeof(string));
Session["dt"] = dt;
}
_dt = (DataTable)Session["dt"];

}
protected void btnSave_Click(object sender, EventArgs e)
{
string con = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/Sony/Desktop/SaveDtls.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES'";
OleDbConnection obj = new OleDbConnection(con);
obj.Open();
OleDbCommand cmd = new OleDbCommand("Insert into [Sheet1$](Name,Age,Address) values('" + txtName.Text + "','" + txtAge.Text + "','" + txtAddr.Text + "')", obj);
cmd.ExecuteNonQuery();
lblMsg.Text = "INSERTED";
txtName.Text = txtAge.Text = txtAddr.Text = string.Empty;
obj.Close();
}
}



Make sure that Excel file is created in the location mentioned in save function.

SailajaReddy

Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response