StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POExcel through OleDb shows numbers differently...depending on whether spreadsheet is open?
text
Body
copied!<p>I'm opening an Excel worksheet into a DataTable using OleDb like this:</p> <pre><code>string select = string.Format("SELECT * FROM [{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; } </code></pre> <p>Then I loop through the rows of the DataTable reading various bits of data like this:</p> <pre><code>decimal charge; bool isChargeReadable = decimal.TryParse(row["Charge"].ToString(), out charge); </code></pre> <p>I discovered just now that my code was choking on cells with dollar amounts such as "$1100.00", which it can't parse to decimal. Not so surprising...except that this is code that was working perfectly before just now.</p> <p>Further investigation revealed that if I run this code <strong>while the workbook is open</strong>, it sees one of those cells as "1100". If I run it while the workbook is closed, it sees "$1100.00".</p> <p>Why is this happening? Obviously I'll have to rework my code to function while the workbook is closed, but why would it make a difference? I would've thought it would just be reading the saved workbook.</p> <p>The connection string I'm using is this...</p> <pre><code>"Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}; Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"";" </code></pre> <p>...where {0} is replaced by the Excel file name, of course.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload