DataTable.Rows returns second row in c#

614 views Asked by At

I'm reading Excel sheet using OleDbDataReader and convert it to a Datatable. The first few columns row in the Excel sheet tells information about the details of excel.

Ex:

Month ---- First Row
Year --- Second Row
Payment ---THird row

Balance rows in that Excel sheet have salary details of all employees.

I coded like below:

OleDbCommand ocmd = new OleDbCommand(query, conn);

OleDbDataReader odr = ocmd.ExecuteReader();    

DataTable dtable = new DataTable(); 
dtable.Load(odr);

if (dtable.Rows.Count > 0)
{                             
    DataRow row = dtable.Rows[0];
    sMonth = row[2].ToString();
    row = dtable.Rows[1];
    sYear = row[2].ToString();
    row = dtable.Rows[2];
    sPayDate = row[2].ToString();         

    ///Salary details
    for (int i = 6; i < dtable.Rows.Count; i++)
    {        
        row = dtable.Rows[i];
        colName = odr[1].ToString();
        colBasic = row[9].ToString();
        colHRA = row[10].ToString();
        ......
    }
}

But

DataRow row = dtable.Rows[0]; 

returns the second row. (i.e) Year Details

I could not get month details in first row.

1

There are 1 answers

0
aspiring On BEST ANSWER

Please change your connection string's HDR=YES property to NO as per my comment.

conn.ConnectionString = 
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source = '" + path + "'" + 
@";Extended Properties=""Excel 8.0;HDR=NO;IMEX=1;ImportMixedTypes=Text;
TypeGuessRows=0""";

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite..