Technical Blog: Sample Code to insert form data into Excel using C#.net

In this article I will illustrate how to insert form data into Excel using OleDBConnection.

For example , we need to capture Product Information through a form and insert this data into Excel ,
where Product Information consists of ItemCode , ItemName and Price.

Design:

Open new project and create three textboxes to capture ItemCode , ItemName and Price information.
Add a save button to form . Onclick of this button data will be inserted into Excel.
Next time when user enters another record , it will be appended to the data in the excel sheet.

Here is the sample code to insert data into Excel using C#.net

Note:
Create an Excel file at the location mentioned i.e "C:/Users/Sony/Desktop/test.xlsx"
and name the columns as ItemCode, ItemName , Price.

string con = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/Sony/Desktop/test.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES'";

Aspx Code:

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

<!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>Saving form data to excel</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
    <tr>
        <td colspan="2" style="color: #FF0000">
             <asp:Label ID="lblMsg" runat="server"></asp:Label>        
        </td>
    </tr>
    <tr>
    <td colspan="2"></td>
    </tr>
    <tr>
        <td>
             ENTER ITEM CODE:
        </td>
        <td>
        <asp:textbox ID="txtItemCode" runat="server"></asp:textbox>    
        </td>
    </tr>
     <tr>
        <td>
             ENTER ITEM NAME:
        </td>
        <td>
        <asp:textbox ID="txtItemName" runat="server"></asp:textbox>
        </td>
    </tr>
    <tr>
        <td>
             ENTER PRICE:
        </td>
        <td>
        <asp:textbox ID="txtPrice" runat="server"></asp:textbox>    
        </td>
    </tr>
   <tr>
        <td colspan="2" align="center">
            <asp:Button ID="btnSave" runat="server" Text="Save to Excel" OnClick="btnSave_Click" />        
        </td>
   </tr>
    </table>
           
        
        <br />

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

Code behind(.cs file)

using System;
using System.Collections;
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.Data.OleDb;

public partial class Forum_Topics_EditSaveLinksinGridView : System.Web.UI.Page
{

    protected void Page_Load(object sender, EventArgs e)
    {
        lblMsg.Text = "";
   }
    protected DataTable MakeDataTable()
    {
        DataTable Dt = new DataTable();
        Dt.Columns.Add("ItemCode", typeof(string));
        Dt.Columns.Add("ItemName", typeof(string));
        Dt.Columns.Add("Price", typeof(string));
        return Dt;
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        string con = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/Sony/Desktop/test.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES'";
        OleDbConnection obj = new OleDbConnection(con);
        obj.Open();
        OleDbCommand cmd = new OleDbCommand("Insert into [Sheet1$](ItemCode , ItemName , Price) values('" + txtItemCode.Text + "','" + txtItemName.Text + "','" + txtPrice.Text + "')", obj);
        cmd.ExecuteNonQuery();
        lblMsg.Text = "RECORD IS INSERTED SUCCESSFULLY!!";
        txtItemCode.Text = txtItemName.Text = txtPrice.Text = string.Empty;
        obj.Close();
    }
}

Technical Blog

Monday, September 17, 2012

Sample Code to insert form data into Excel using C#.net

4 comments:

Labels

About Me

Total Pageviews