Excel 通过 OleDb 显示不同的数字...取决于电子表格是否打开？
我正在使用 OleDb 将 Excel 工作表打开到 DataTable 中，如下所示：
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;
}
然后我循环遍历 DataTable 的行，读取各种数据位，如下所示：
decimal charge;
bool isChargeReadable =
decimal.TryParse(row["Charge"].ToString(), out charge);
我刚刚发现我的代码在包含诸如“$1100.00”之类的美元金额的单元格上被阻塞。 "，它无法解析为十进制。并不奇怪……只不过这是之前运行良好的代码。
进一步的调查显示，如果我在工作簿打开时运行此代码，它会将其中一个单元格视为“1100”。如果我在工作簿关闭时运行它，它会看到“$1100.00”。
为什么会发生这种情况？显然，我必须在工作簿关闭时重新编写代码才能运行，但为什么会有所不同呢？我本以为它只是读取保存的工作簿。
我使用的连接字符串是这样的...
"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source={0};
Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"";"
...其中 {0} 当然被 Excel 文件名替换。
I'm opening an Excel worksheet into a DataTable using OleDb like this:
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;
}
Then I loop through the rows of the DataTable reading various bits of data like this:
decimal charge;
bool isChargeReadable =
decimal.TryParse(row["Charge"].ToString(), out charge);
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.
Further investigation revealed that if I run this code while the workbook is open, it sees one of those cells as "1100". If I run it while the workbook is closed, it sees "$1100.00".
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.
The connection string I'm using is this...
"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source={0};
Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"";"
...where {0} is replaced by the Excel file name, of course.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我发现我的 oledb 操作在 excel 中运行得更好，无需设置 IMEX=number 。或许你的问题就在那里？
I've found that my oledb operations work better in excel withOUT an IMEX=number set. Perhaps your problem is there?