Reading Excel file in c#

public Object readExcel(string filepath)

 

{

static string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
                    Data Source=C:\EmployeeXLS.xls;
                    Extended Properties=Excel 5.0";


           string query = @"Select * From [Sheet1$]";

            System.Data.OleDb.OleDbConnection con =
              new System.Data.OleDb.OleDbConnection(
              ConnectionString);
            con.Open();
            OleDbDataAdapter da = new OleDbDataAdapter(query, con);
         //   DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            da.Fill(ds, "nyNameHere");

            //con.Close();
            StringBuilder sb = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            List<Employee> _empList = new List<Employee>();
            try
            {
                foreach (DataRow myDataRow in ds.Tables[0].Rows)
                {
                    Employee emp = new Employee();
                    emp.FName =   myDataRow["fname"].ToString();
                    emp.LName = myDataRow["lname"].ToString();
                    emp.Age = int.Parse( myDataRow["age"].ToString());
                    emp.Salary = Double.Parse(myDataRow["wage"].ToString());
                    _empList.Add(emp);
                    //Stores info in Datarow into an array
                    Object[] cells = myDataRow.ItemArray;
                    //Traverse through each array and put into object cellContent as type Object
                    //Using Object as for some reason the Dataset reads some blank value which
                    //causes a hissy fit when trying to read. By using object I can convert to
                    //String at a later point.
                    foreach (object cellContent in cells)
                    {
                        //Convert object cellContect into String to read whilst replacing Line Breaks with a defined character
                        string cellText = cellContent.ToString();
                        cellText = cellText.Replace("\n", "|");
                        sb.Append("\n" + cellText);
                        //Read the string and put into Array of characters chars
                       // richTextBox1.AppendText("\n" + cellText);
                    }
                  
                 return )empList;

} 

Reading an excel file in c#

//Create the Connection String
//You must specify the Provider as is here, I think
//Thre Data Source is the path to your file
//Extended Properties is something to do with excel, use as here

string ConnectionString=@"Provider=Microsoft.Jet.OLEDB.4.0;
                    Data Source=C:\Test Projects\Excel example\Excel - reading an excel file\ReadThis.xls;
                    Extended Properties=Excel 5.0";

//Create the connection

System.Data.OleDb.OleDbConnection ExcelConnection = 
new System.Data.OleDb.OleDbConnection
                 (ConnectionString);
            
//create a string for the query

string ExcelQuery;

//Sheet1 is the sheet name
//create the query:
//read column with heading A from the Excel file

ExcelQuery = "Select A from [Sheet1$]"; // from Sheet1";

//use "Select * ... " to select the entire sheet
//create the command

System.Data.OleDb.OleDbCommand ExcelCommand = new System.Data.OleDb.OleDbCommand(ExcelQuery,ExcelConnection);
            
//Open the connection

ExcelConnection.Open();

//Create a reader

System.Data.OleDb.OleDbDataReader ExcelReader;
ExcelReader = ExcelCommand.ExecuteReader();
            
//For each row after the first
//Message box the values in the first column i.e. column 0

while (ExcelReader.Read())
{
  MessageBox.Show((ExcelReader.GetValue(0)).ToString());
}
ExcelConnection.Close();

//Try update the file

ExcelQuery = "Update [Sheet1$] set B = 2 where B = 1"; // from Sheet1";

//Create the command to be executed

ExcelCommand = new System.Data.OleDb.OleDbCommand
(ExcelQuery,ExcelConnection);

//Open the connection to the file

ExcelConnection.Open();

//Execute the update

ExcelCommand.ExecuteNonQuery();

//Close the connection

ExcelConnection.Close();