Sunday, 6 May 2012

Connect to Excel using ODBC in C#

Step1 )
                  Create Excel  file with this


2)  Select Entire List and name it as "MyProgrammingList"
3)  Save to some folder for ex: c:\excelone\progstats.xls.

Step 2)   Create  Data Source Name(DSN)

      Control Panel\All Control Panel Items\Administrative Tools\DataSources(ODBC)















Click FINISH button to add DSN


Step 3)   Create   ASP.NET   Web Site.
             in Default.aspx  page    add Grid View Control

<body>
    <form id="form1" runat="server">
    <div>
    <asp:gridview ID="Gridview1" runat="server"></asp:gridview>
    </div>
    </form>
</body>

In  Code Page
add name spaces


using System.Data.Odbc;
using System.Data;



        protected void Page_Load(object sender, EventArgs e)
        {
            System.Data.Odbc.OdbcConnection conn =
                new OdbcConnection("DSN=myExcelone");  //DSN created in Step 2

            OdbcDataAdapter ad = new OdbcDataAdapter("select * from MyProgrammingList", conn);
            //MyProgrammingList  named for group of cloumns in Excel Sheet.
            DataSet ds = new DataSet();
            ad.Fill(ds);
            Gridview1.DataSource = ds;
            Gridview1.DataBind();
        }



Output: