How to Read the Data from Excel in C#

Here, You will see how to read the data from excel in C#.

Code to read the data from excel :

string columnHeader = "";

OleDbDataReader drexcel = cmdExcel.ExecuteReader();
strInsQuery = "Insert into " + drptable.SelectedValue + " (" + "Data_Month,Data_Year,"; 

for (intFldCnt = 0; intFldCnt <= (drexcel.FieldCount - 1); intFldCnt++)

{
        columnHeader = drexcel.GetName(intFldCnt);
        strInsQuery = strInsQuery + "[" + Strings.Replace(columnHeader, "#", "") + "]" + ",";
}
strInsQuery = Strings.Left(strInsQuery, Strings.Len(strInsQuery) - 1);
strInsQuery = strInsQuery + ") values ('" + File_month + "'" + "," + File_Year + ","; 
while ((drexcel.Read)) {
        //Assign values to access command parameters
        for (intFldCnt = 0; intFldCnt <= (drexcel.FieldCount - 1); intFldCnt++)
        {
               if (Information.IsDBNull(drexcel(intFldCnt))) {
                       strFldValues = strFldValues + "null,";
               } else {
                       strFldValues = strFldValues + "'" + Strings.Replace(Strings.Trim(drexcel(intFldCnt)), "'", "''") + "',";
               }
        } 
        strFldValues = Strings.Left(strFldValues, Strings.Len(strFldValues) - 1);
        strInsFinQuery = strInsQuery + strFldValues + ")";
        cmdAccess.CommandText = strInsFinQuery;
        flag = cmdAccess.ExecuteNonQuery();
        if (flag != 0) {
               updatedRec = updatedRec + 1;
               UploadSucessFlag = "TRUE";
        } else {
               UploadSucessFlag = "FALSE";
        }
        recCount = recCount + 1;
        strInsFinQuery = "";
        strFldValues = "";
} 

Code to read a particular column from the excel sheet :

connExcel = strExcel + strfullpath + ";";

//creating connection objects
_connection = new OleDbConnection(connExcel);
connAccess = new OleDbConnection(strConn); 

//creating command object for excel

OleDbCommand cmdExcel = new OleDbCommand();
cmdExcel = _connection.CreateCommand();
cmdExcel.CommandType = CommandType.Text;
cmdExcel.CommandText = "SELECT * from  [Sheet1$]"; 
//Command object for Access
OleDbCommand cmdAccess = new OleDbCommand();
cmdAccess = connAccess.CreateCommand();
cmdAccess.CommandType = CommandType.Text;
cmdAccess.CommandText = "Select * from UtilizationData";
//Dim drAccess As OleDbDataReader = cmdAccess.ExecuteReader() 

//Open connections

_connection.Open();
connAccess.Open(); 

DataSet dsExcel = new DataSet();

daExcel = new OleDbDataAdapter(cmdExcel);
daExcel.Fill(dsExcel, "ExcelTable");

//logic to get file date form the excel file

int colcount = dsExcel.Tables["ExcelTable"].Rows[0].ItemArray.Count;

int rowCount = dsExcel.Tables["ExcelTable"].Rows.Count;
System.DateTime File_Date = default(System.DateTime); 

for (iRowCount = 0; iRowCount <= rowCount - 1; iRowCount++)

{
        string DateRow = dsExcel.Tables["ExcelTable"].Columns[iRowCount].ToString();
        if (Strings.UCase(DateRow).Contains("START DATE")) {
               //DateRowindex = iRowCount
               File_Date = Strings.Trim(dsExcel.Tables["ExcelTable"].Rows[0][iRowCount].ToString());
               break; // TODO: might not be correct. Was : Exit For
        }
}