Retriev a row from excel and extract data in strings

apoorv srivastava

i am tring to use excel sheet for a small login form however i am unable to extract data from reader object.

This the table

User_Id	Login_Email	Login_Password	Last_Login	Access_type
xyzabc	[email protected]	Password		c

CODE

string ConStr = "";

string path = Server.MapPath("Login.xlsx");
ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;ReadOnly=False;HDR=Yes;\"";
string query = "Select from [Sheet1$] where [Login_Email] ='"+TextBox1.Text+"'";
OleDbConnection conn = new OleDbConnection(ConStr);
conn.Open();
OleDbCommand cmd = new OleDbCommand(query, conn);
OleDbDataReader reader = cmd.ExecuteReader();
String UEmail = reader["Login_Email"].ToString();
String Pwd = reader["Login_Password"].ToString();
conn.Close();

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Leon D8y ago
Another alternative is to use a 3rd party library. At first, reference Spire.Xls.dll (You can get it by searching free Spire.XLS on NuGet). Then refer the following code:
//Load Excel
Workbook workbook = new Workbook();
workbook.LoadFromFile("1.xlsx");

//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];

//Find the cell which contains the specific login email
CellRange cell = sheet.FindString(textBox1.Text, true, true);

//Get the text of the 3rd cell in the row where the above cell is located
string Pwd = sheet.Rows[cell.Row - 1].Cells[2].Text;
0
Yogesh Jadhav8y ago
string path = Server.MapPath("Login.xlsx");
ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;ReadOnly=False;HDR=Yes;\"";
OleDbConnection oledbConn = new OleDbConnection(ConStr);
oledbConn.Open();
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter oleda = new OleDbDataAdapter();
DataSet ds = new DataSet();
DataTable dt = oledbConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string workSheetName = (string)dt.Rows[0]["TABLE_NAME"];
cmd.Connection = oledbConn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Select from [Sheet1$] where [Login_Email] ='"+TextBox1.Text+"'";
oleda = new OleDbDataAdapter(cmd);
oleda.Fill(ds, "xyz");
oledbConn.Close();
// Data will appear in datatable.
//Manage your query as per your need.
0