string dtaFile = "d:\\d1\\d2\\s1.xls"; string cmdSQL = "select * from [Sheet 1$] where F16='NP'"; System.Data.OleDb.OleDbConnection cnXL; System.Data.OleDb.OleDbDataAdapter cmXL; System.Data.DataSet DtSet; cnXL = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + dtaFile + "';Extended Properties=Excel 8.0;"); cmXL = new System.Data.OleDb.OleDbDataAdapter(cmdSQL, cnXL); DtSet = new System.Data.DataSet(); cmXL.Fill(DtSet); cnXL.Close(); cnXL.Dispose();
The problem lies in F14. As output by the software that creates the workbook: Row(2) contains a date, Row(3) contains a time, and Row(4) contains "Page: ..". DtSet.GetXml() shows that F14's type is String.
cmdSQL selects data beginning with Row(9); beginning with that row, F14 contains an integer - but the resulting DataGrid shows an empty column for F14!
To test the return values, I created a method to iterate through the rows in the DataSet, and found that F14, (DataRow.ItemArray.GetValue(13).ToString()), was being returned as an empty string.
I have found a work-around, (move any two, or all three, of rows(2..4) to F15), but I don't understand the cause for this situation.
Any enlightenment would be greatly appreciated.