X ) MS Excel


 MS Excel


:-  $ is data reader

:- Ms Excel Library :- Using System.Data.OLEDB;

Step :- 

 goto the MSEXCEL -- file -- new -- blanck workbook -- Create -- Cell(Id ,Name,City) -- File -- Save as -- file name (Student) -- select drive (C,D,E etc) -- save as type :- Excel 97-2003 workbook  


Q ) How to connect Excel to dotnet 

Step 1 :- Ceate a aspx page -- add Excel library (using System.Data.OleDb;)

Step 2 :- OleDbConnection cn;

    OleDbCommand cm;

    OleDbDataReader dr;

Step 3 :- if (!IsPostBack)

        { 

        

        }

Step 4 :- Source Code :- MsExcel.Aspx

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


<!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></title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

    

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

        <asp:Button ID="Button1" runat="server" Height="33px" onclick="Button1_Click" 

            Text="Show" Width="68px" />

        <br />

        <br />

        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 

            Height="235px" Width="362px">

            <Columns>

                <asp:BoundField DataField="name" HeaderText="student name" />

                <asp:BoundField DataField="city" HeaderText="student city" />

            </Columns>

        </asp:GridView>

   enter Name

        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>

   enter City

        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>

        <br />

        <br />

&nbsp;&nbsp;&nbsp;&nbsp;

        <asp:Button ID="Button2" runat="server" onclick="Button2_Click" Text="Submit" />

    </div>

    </form>

</body>

</html>

   ButtonCode :- MsExcel.Aspx.CS

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

using System.Data.OleDb;   // Excel library

public partial class ZZExreg : System.Web.UI.Page
{
    OleDbConnection cn;
    OleDbCommand cm;
    OleDbDataReader dr;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        { 
        
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string path = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/ExDb/clgstudent.xls") + ";" + "Extended Properties=Excel 8.0;";
        cn = new OleDbConnection(path);
        cn.Open();

        string k = "select * from [Sheet1$]";  //$ is data reader
        cm = new OleDbCommand(k, cn);
        dr = cm.ExecuteReader();
        GridView1.DataSource = dr;
        GridView1.DataBind();
        dr.Close();

    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        string path = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/ExDb/clgstudent.xls") + ";" + "Extended Properties=Excel 8.0;";
        cn = new OleDbConnection(path);
        cn.Open();

        //string k = "insert into [Sheet1$] values('"+TextBox1.Text+"','"+TextBox2.Text+"')";

        string k = "insert into [Sheet1$] ([name],[city]) values(@name1,@city1)";
        cm = new OleDbCommand(k, cn);
        cm.Parameters.AddWithValue("name1", TextBox1.Text);
        cm.Parameters.AddWithValue("city1", TextBox2.Text); 
        cm.ExecuteNonQuery();
        Response.Write("record save succesfully");
    }
}


   2 ) Source Code :- ExcelCrud.Aspx

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


<!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></title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

    

        <br />

        <br />

        Roll&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

        <asp:TextBox ID="txtroll" runat="server"></asp:TextBox>

        <br />

        Name&nbsp;&nbsp;&nbsp;&nbsp;

        <asp:TextBox ID="txtname" runat="server"></asp:TextBox>

        <br />

        City&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

        <asp:TextBox ID="txtcity" runat="server"></asp:TextBox>

    

        <br />

        <br />

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

        <asp:Button ID="Button1" runat="server" Height="33px" Text="Submit" 

            Width="97px" onclick="Button1_Click" />

        &nbsp;&nbsp;

        <asp:Button ID="Button2" runat="server" Height="33px" onclick="Button2_Click" 

            Text="Update" Width="80px" />

&nbsp;

        <asp:Button ID="Button3" runat="server" Height="32px" onclick="Button3_Click" 

            Text="View" Width="71px" />

        &nbsp;<br />

        <br />

&nbsp;&nbsp;

        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 

            Height="208px" Width="415px">

            <Columns>

                <asp:TemplateField HeaderText="Roll">

                <ItemTemplate>

                    <asp:TextBox ID="TextBox1" runat="server" Text='<%#Eval("roll") %>'></asp:TextBox>

                </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="Name">

                <ItemTemplate>

                    <asp:TextBox ID="TextBox2" runat="server" Text='<%#Eval("name") %>'></asp:TextBox>

                </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="City">

                <ItemTemplate>

                    <asp:TextBox ID="TextBox3" runat="server" Text='<%#Eval("city") %>'></asp:TextBox>

                </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="Submit">

                    <ItemTemplate>

                        <asp:Button ID="Button5" runat="server" Text="Submit" OnClick="submit1_click" />

                    </ItemTemplate>

                </asp:TemplateField>

            </Columns>

        </asp:GridView>

    </div>

    </form>

</body>

</html>

  
                                 Button Code :- ExcelCrud.Aspx.CS


using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.OleDb;

using System.Data;


public partial class ZZZexelupdate : System.Web.UI.Page

{

    OleDbConnection cn;

    OleDbCommand cm;

    OleDbDataReader dr;

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            show();

        }

    }

    protected void show()

    {

        string path = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/ExDb/board.xls") + ";" + "Extended Properties=Excel 8.0;";

        cn = new OleDbConnection(path);

        cn.Open();


        string k = "select * from [Sheet1$]";

        cm = new OleDbCommand(k, cn);

        dr = cm.ExecuteReader();

        GridView1.DataSource = dr;

        GridView1.DataBind();

        dr.Close();

    }

    protected void Button1_Click(object sender, EventArgs e)

    {

        string path = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/ExDb/board.xls") + ";" + "Extended Properties=Excel 8.0;";

        cn = new OleDbConnection(path);

        cn.Open();


        string k = "insert into [Sheet1$](roll,name,city)values(@roll1,@name1,@city1)";

        cm = new OleDbCommand(k, cn);

        cm.Parameters.AddWithValue("roll1", txtroll.Text);

        cm.Parameters.AddWithValue("name1", txtname.Text);

        cm.Parameters.AddWithValue("city1", txtcity.Text);

        cm.ExecuteNonQuery();

        Response.Write("data submited succesfully");

    }

    protected void Button2_Click(object sender, EventArgs e)

    {

        string path = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/ExDb/board.xls") + ";" + "Extended Properties=Excel 8.0;";

        cn = new OleDbConnection(path);

        cn.Open();


        string k = "update [Sheet1$] set name=@name1 where roll=@roll1";

        cm = new OleDbCommand(k, cn);

        cm.Parameters.AddWithValue("name1", txtname.Text);

        cm.Parameters.AddWithValue("roll1", txtroll.Text);

        cm.ExecuteNonQuery();

        Response.Write("data updated succesfully");

    }

    protected void Button3_Click(object sender, EventArgs e)

    {

        string path = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/ExDb/board.xls") + ";" + "Extended Properties=Excel 8.0;";

        cn = new OleDbConnection(path);

        cn.Open();


        string k = "select * from [Sheet1$] where roll=@roll1";

        cm = new OleDbCommand(k, cn);

        cm.Parameters.AddWithValue("roll1", txtroll.Text);

        dr = cm.ExecuteReader();


        if (dr.Read())

        {

            txtname.Text = dr["name"].ToString();

            txtcity.Text=dr["city"].ToString();

        }    

    }

    protected void submit1_click(object sender,EventArgs e)

    {

        string path = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/ExDb/board.xls") + ";" + "Extended Properties=Excel 8.0;";

        cn = new OleDbConnection(path);

        cn.Open();


        GridViewRow r1 = ((Button)sender).NamingContainer as GridViewRow;

        TextBox txtro = (TextBox)r1.FindControl("TextBox1");

        TextBox txtna = (TextBox)r1.FindControl("TextBox2");

        TextBox txtci = (TextBox)r1.FindControl("TextBox3");


        string k = "insert into [Sheet1$](roll,name,city)values(@roll1,@name1,@city1)";

        cm = new OleDbCommand(k, cn);

        cm.Parameters.AddWithValue("roll1",txtro.Text);

        cm.Parameters.AddWithValue("name1",txtna.Text);

        cm.Parameters.AddWithValue("city1",txtci.Text);

        cm.ExecuteNonQuery();

        Response.Write("data submited succesfully");

    }

} 

Comments

Post a Comment

Popular posts from this blog

DATA CONTROL ( Gridview , Repeater , Formview , DataList , Detailsview , Listview )

  GRIDVIEW SOURCE CODE :      <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"              Height="279px" Width="610px">             <Columns>                 <asp:TemplateField HeaderText="roll">                 <ItemTemplate>                     <asp:TextBox ID="txtroll" runat="server" Text='<%# Eval("roll") %>'></asp:TextBox>                 </ItemTemplate>                 </asp:TemplateField>                 <asp:TemplateField HeaderText="name">                 <ItemTemplate>               ...
Read more

Z 5 ) MVC (Model view Controller)

Image
  MVC (Model view Controller)    Model View Controller   The first version of  MVC is called MVED(Model View Editor) MVC is to multi layer architecture  Model         (class and variables) View          (.cshtml) Controller   (Logic building) MVC is directly operate html page in this reason it is called light weight environment. MVC Block Diagrame   .NET Framework  ASP.NET MVC ASP.Net Webform Benefits of MVC application It is manage complex application Direct control use in html It provide test driven development testing to (url http/data)
Read more

Z 8 ) MVC HTMLHELPER METHOD AND ACTIONLINK (how to use css ,atrributes,style in css)

Image
  HTML HELPER METHODS IN ASP.NET MVC 5 In ASP.NET MVC Framework, helper methods: Ø  Are extension methods to the Html Helper class, can be called only from views. Ø  An HTML helper is a method that is used to render html content in a view. Ø  Simplifies the process of creating a view. Ø  Allows generating HTML markup that you can reuse across the Web application. Some of the commonly used helper methods while developing an MVC application are as follows: Ø  Html.ActionLink() Ø  Html.BeginForm() and Html.EndForm() Ø  Html.Label() Ø  Html.TextBox() Ø  Html.TextArea() Ø  Html.Password() Ø  Html.CheckBox()
Read more