Home » C# scripts » Read data from an excel file using c#

Read data from an excel file using c#

This method is used to read data from excel file. It accepts path, where the excel file is located in the local drive and the sheet name inside that excel.

Let us assume excel has headers like FirstName,LastName,EmailAdress,Country etc. and there are around 10 such records in rows.

public class GeneriMethods
{
public List<userInfo> readExcelFile(string filePath, string excelSheetName)
 {
    List<userInfo> myUserList = new List<userInfo>();
 
 using (OleDboledbConnection oledbConn = new OleDboledbConnection())
 {
    string excelFileExtension = System.IO.Path.GetExtension(filePath);
 if (excelFileExtension == ".xls")
    oledbConn.oledbConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
 
 if (excelFileExtension == ".xlsx")
    oledbConn.oledbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'";
using (OleDboledbCommand oledbComm = new OleDboledbCommand())
 {
    oledbConn.Open();
    oledbComm.oledbCommandText = String.Format("select * from [{0}${1}]", excelSheetName,"A1:IV");;

oledbComm.oledbConnection = oledbConn;

using (OleDbDataAdapter oledbDataAdapt = new OleDbDataAdapter())
 {
    oledbDataAdapt.SelectoledbCommand = oledbComm;
    OleDbDataReader oDR = oledbComm.ExecuteReader();
 
 while(oDR.Read())
 {
    userInfo userin = new userInfo();   create object of userInfo class
    userin.FirstName=oDR["First name"].ToString();
    userin.LastName=oDR["Last name"].ToString();
    userin.Email=oDR["Email"].ToString();
    userin.Street=oDR["Street"].ToString();
    userin.City=oDR["City"].ToString();
    userin.Postalcode=oDR["Postalcode"].ToString();
    userin.Region=oDR["Region"].ToString();
    myUserList.Add(userin);
 } 
 }
 }
 }
  return myUserList;
 }
 }
 
 public class userInfo
 {
    public string FirstName{get;set;}
    public string LastName{get;set;}
    public string DOB{get;set;}
    public string Email{get;set;}
    public string Street{get;set;}
    public string City{get;set;}
  }

Now, we will see how to access in the main method.

List<userInfo> userDetails = new List<userInfo>();  // create a list of userInfo
 userDetails = readExcelFile("YourFileLocationPath","YourSheetName"); // as readExcelFile return list of userInfo

foreach( userInfo a in userDetails )
 {
    Console.Writeline(a.FirstName);
    Console.Writeline(a.LastName);
    Console.Writeline(a.Email);  // this way you can access all the data from excel
 }

1 Comment

Leave a comment

Your email address will not be published. Required fields are marked *