Retriev a row from excel and extract data in strings

Question

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();

Leon D · Answer

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;

Yogesh Jadhav · Answer

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.

Retriev a row from excel and extract data in strings

Answers (2)