[C#/Excel] Cannot read cells' value

[michael_hk]

Hi,

I use the following code to read data from excel file

private void btnProcess_Click(object sender, System.EventArgs e)
{
	... // variable declarations

	OleDbConnection conn = new OleDbConnection(); // connect to the file
	conn.ConnectionString = strConn; 
	conn.Open();

	OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM Course", conn); // get courses info
	DataSet ds = new DataSet(); 
	adapter.Fill(ds, "course");

	OleDbDataAdapter adapter2 = new OleDbDataAdapter("SELECT * FROM Student", conn); // get students info
	adapter2.Fill(ds, "student");

	foreach(DataRow oRow in ds.Tables["course"].Rows) // store courses info in array
	{
		i = 6; // skip first 6 elements, match the index below

		foreach (DataColumn oColumn in ds.Tables["course"].Columns)
		{
			subject[i] = oRow[oColumn.ColumnName].ToString();
			i++;
		}
	}

	foreach(DataRow oRow in ds.Tables["student"].Rows) 
	{
		i = 0;

		foreach (DataColumn oColumn in ds.Tables["student"].Columns)
		{
			if ( i>=6 ) // course info start from Cell7
			{
				if ( oRow[oColumn.ColumnName].ToString() == "1") // [COLOR=DarkRed]PROBLEM HERE[/COLOR]
				{
					subSelected += subject[i] + Environment.NewLine; // subSelected stores the subjects selected
				}
			}

			i++;
		}
	}
	
	...
}

I have defined two areas (Course and Student) in excel and want to loop throught the datatable student to check what subjects he/she has enrolled. But in the second foreach loop, oRow[oColumn.ColumnName].ToString() sometimes gives me empty string (I am sure the value is 1 in the excel file) and sometimes gives 1 (correct value). The programme output is totally unpredictable...

Is there anything I am doing wrong here?

Thanks.
Michael

 

[Joe Mamma]

is it possible that the data in the excel file is actually a 1 and not perhaps a 1.0 formatted as 1 or even a formula???

You might have better luck interop-ing the excel file and grabbing the named range as a range object where you have Cell.Text and Cell.Value available to you.

In short I have found it unpredictable interfacing excel via db drivers (ADO or ODBC) unless I have absolute control over what is in the file, meaning I am not getting the file from another user who may have applied various formats or other gunk to the data.

Excel is not a database and should not be used as one, in my opinion.

 

[michael_hk]

is it possible that the data in the excel file is actually a 1 and not perhaps a 1.0 formatted as 1 or even a formula???

I am 100% sure the value is a digit 1 (I have checked the format of the cells as well).

You might have better luck interop-ing the excel file and grabbing the named range as a range object where you have Cell.Text and Cell.Value available to you.

In short I have found it unpredictable interfacing excel via db drivers (ADO or ODBC)

Yes, I understand there are other ways to interface with the excel file but now I just want to find out WHY the output is unpredictable.

unless I have absolute control over what is in the file, meaning I am not getting the file from another user who may have applied various formats or other gunk to the data.

Excel is not a database and should not be used as one, in my opinion.

Unfortunately, the excel file is from my colleagu........

Yes, excel is not a database, but, unfortunuately again, my colleague is just a IT dummy.