Saturday, 3 August 2013

Search Data in Excel by asp.net C#

protected void GetDataByFolioNo(string strFolioNo)
    {
        string sqlQuery = string.Empty;

        string file = Server.MapPath("~/App_Data/TestExcel.xls");

        string connStr = string.Format(ConfigurationManager.AppSettings["Excel2003OleDBConnection"], file);

        DataTable table = new DataTable();

        sqlQuery = "SELECT * FROM [Sheet1$] where [Folio Number of Securities]='" + strFolioNo+"'";

        using (OleDbConnection conn = new OleDbConnection(connStr))
        {
            string sheet = @sqlQuery;

            using (OleDbCommand cmd = new OleDbCommand(sheet, conn))
            {

                conn.Open();



                using (OleDbDataAdapter ad = new OleDbDataAdapter(cmd))
                {

                    ad.Fill(table);

                }

                conn.Close();

            };

        }



        gvList.DataSource = table;
        gvList.DataBind();
    }


Add in config
===========

<appSettings>
        <add key="Excel2003OleDBConnection" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=&quot;Excel 8.0;HDR=YES;IMEX=1&quot;"/>
        <add key="Excel2007OleDBConnection" value="Provider=Microsoft.ACE.     OLEDB.12.0;Data Source={0};Extended Properties=&quot;Excel 12.0 Xml;HDR=YES;IMEX=1&quot;"/>
    </appSettings>