使用OLEDB读取Excel文件，返回错误的值

发布于 2025-02-08 12:46:11 字数 1109 浏览 1 评论 0 原文

我正在尝试读取一个Excel文件，其中包含列中的以下值。

当我在公式栏中查找这些列时，它的A datetime 。类似于下面的东西 -

我正在尝试使用以下代码读取这些列。

            var sqlBuilder = new OleDbConnectionStringBuilder
            {
                DataSource = FileName,
                Provider = "Microsoft.ACE.OLEDB.12.0"
            };
            sqlBuilder.Add("Extended Properties", "Excel 12.0;characterset=65001;HDR=No;IMEX=1;FMT=Delimited;");
            cn = new OleDbConnection(sqlBuilder.ConnectionString);
            cn.Open();

但是，问题在于这些值的读数不完全相同。他们被读 -

一些随机数字值为00。

原文

I am trying to read an Excel File which contains the following kind of value in a column.

Its a datetime while I am looking these column in the formula bar. Some what like below -

I am trying to read these column using the following code.

            var sqlBuilder = new OleDbConnectionStringBuilder
            {
                DataSource = FileName,
                Provider = "Microsoft.ACE.OLEDB.12.0"
            };
            sqlBuilder.Add("Extended Properties", "Excel 12.0;characterset=65001;HDR=No;IMEX=1;FMT=Delimited;");
            cn = new OleDbConnection(sqlBuilder.ConnectionString);
            cn.Open();

But, The problem is that these values are not read exactly the same. They are read like-

Some random numeric value takes places instead of 00. Tried to use Imex=0 or HDR=Yes, but wasn't helpful.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

得不到的就毁灭 2025-02-15 12:46:11

这是因为OLEDB ODBC驱动程序在猜测列数据格式方面非常可怕。最好告诉它如何读取列数据类型。我更喜欢在字符串中读取所有列值，并在我的代码中转换它们。

在您的连接字符串构建器中使用此信息：

sqlBuilder.Add("Extended Properties", "Excel 12.0 Xml;characterset=65001;HDR=YES;IMEX=1;");

请参阅

您可能还会获得绝对值，而不是在Excel中看到的格式化值正常工作，您必须弄清楚如何处理差异。

如果其他所有其他使用OLEDB方法都失败了，请切换到 epplus 我在我的成功方面取得了更好的成功准确读取XLSX文件。

That's because the OleDB ODBC driver is notoriously terrible at guessing column data formats. It's better to tell it exactly how to read the column data type. I prefer to read all the column values in as strings and convert them in my code.

Use this in your connection string builder:

sqlBuilder.Add("Extended Properties", "Excel 12.0 Xml;characterset=65001;HDR=YES;IMEX=1;");

See https://www.connectionstrings.com/ace-oledb-12-0/treating-data-as-text/

You might also be getting the absolute value and not the formatted values you see in Excel in which case your code is working perfectly and you'll have to figure out how to deal with the discrepancies.

If all else fails with using the OleDB method, switch to using EPPlus which I have had much better success with reading XLSX files accurately.

回复收藏 0 原文

~没有更多了~