c# - Excel through OleDb shows numbers differently...depending on whether spreadsheet is open? -
i'm opening excel worksheet datatable using oledb this:
string select = string.format("select * [{0}$]", worksheetname); using (var con = new oledbconnection(connectionstring)) using (var adapter = new oledbdataadapter(select, con)) { con.open(); var dt = new datatable(); adapter.fill(dt); con.close(); return dt; }
then loop through rows of datatable reading various bits of data this:
decimal charge; bool ischargereadable = decimal.tryparse(row["charge"].tostring(), out charge);
i discovered code choking on cells dollar amounts such "$1100.00", can't parse decimal. not surprising...except code working before now.
further investigation revealed if run code while workbook open, sees 1 of cells "1100". if run while workbook closed, sees "$1100.00".
why happening? i'll have rework code function while workbook closed, why make difference? would've thought reading saved workbook.
the connection string i'm using this...
"provider=microsoft.jet.oledb.4.0; data source={0}; extended properties=""excel 8.0;hdr=no;imex=1"";"
...where {0} replaced excel file name, of course.
i've found oledb operations work better in excel without imex=number set. perhaps problem there?
Comments
Post a Comment