how to read excel file like this condition.

Posted by Jayakumars under ASP.NET on 11/18/2011 | Points: 10 | Views : 1541 | Status : [Member] [MVP] | Replies : 1
hi

How to read this Excel file in my application.check below i am attah excel
file i will input text result value only i need corresponding record show
my vb.net form or asp.net web page how will do this i need most urgent.I will mark
yellow color in my excel sheet

my text i will input like this Res1 only i need show corresponding record
only how will do this.

Mark as Answer if its helpful to you


Responses

Posted by: perfectchourasia-9163 on: 11/18/2011 [Member] Starter | Points: 25

Up
0
Down
I have created User Control Like this


<%@ Control Language="C#" AutoEventWireup="true" CodeFile="AboutusTip.ascx.cs" Inherits="AboutusTip" %>
<div class="tips">
<h2><img src="images/tipsimg2.gif" width="12" height="22" alt="" title="" />Tip of the day</h2>
<p><%=lblToolTip%></p>
</div>


Then In .Cs page

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 AboutusTip : System.Web.UI.UserControl
{
public string lblToolTip;
protected void Page_Load(object sender, EventArgs e)
{
BindRecordFromExcal();
}


In webconfig


<add key="ExcalDatabaseName" value="DataFile/MessageNew.xls"/>
private void BindRecordFromExcal()
{
string Dates = DateTime.Today.Date.ToShortDateString().Trim();
Dates = Dates + " 12:00:00 AM";
string Excalfilepath = Server.MapPath(ConfigurationManager.AppSettings["ExcalDatabaseName"].ToString().Trim());
DataSet RecordData = new DataSet();
RecordData = exceldata(Excalfilepath);
if (RecordData.Tables.Count > 0)
{
DataRow[] DR = RecordData.Tables["MessageRecord"].Select("MessageDate='" + Dates.ToString().Trim() + "'");
String TipMemssage = String.Empty;

if (DR.Length > 0)
{
TipMemssage = DR[0].ItemArray[0].ToString().ToString() + DR[0].ItemArray[2].ToString().ToString();
}
else
{
TipMemssage = "No Record!!!";
}
//int StrLength = TipMemssage.Length;
//if (StrLength > 147)
//{
TipMemssage = TipMemssage;
lblToolTip = TipMemssage.ToString().Trim();
//}
//else
//{
// lblToolTip = TipMemssage.ToString().Trim();
//}
}
else
{
lblToolTip = "Data Error!";

}
}
public static DataSet exceldata(string filelocation)
{ OleDbConnection excelConn = new OleDbConnection();
try
{
DataSet ds = new DataSet();
OleDbCommand excelCommand = new OleDbCommand();
OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter();
string excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filelocation + "; Extended Properties =Excel 8.0;";
//string excelConnStr = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;
excelConn = new OleDbConnection(excelConnStr);
excelConn.Open();
DataTable dtMessageRecord = new DataTable();
excelCommand = new OleDbCommand("SELECT Message as MessageData, Date as MessageDate, ExtendedMessage as EMessage FROM [Sheet1$]", excelConn);
excelDataAdapter.SelectCommand = excelCommand;
excelDataAdapter.Fill(dtMessageRecord);
dtMessageRecord.TableName = "MessageRecord";
ds.Tables.Add(dtMessageRecord);
excelConn.Close();
excelConn.Dispose();
excelCommand.Dispose();
return ds;
}
finally
{
excelConn.Close();
excelConn.Dispose();
}

}
}


ER sandeep chourasia
sandeepchrs@yahoo.com (on facebook)
http://www.aspnetcodes.com/

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

Login to post response