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

        }

}


Ebook Download
View all
Learn
View all