使用OLEDB数据提供程序读取Excel文件

Question

我正在使用 OLEDB 数据提供程序 读取 Excel 文件，但问题在于 Excel 表格中某些列具有无效值，例如，某些单元格中出现了字符串而不是数字。

对于上面的截图，当我读取“John”这个值时，会得到一个空字符串。

那么有没有办法读取这个无效值？

非常感谢任何帮助。

以下是读取 Excel 文件的代码：

private DataTable ReadExcelFile(string sheetName, string path)
{

    using (OleDbConnection conn = new OleDbConnection())
    {
        DataTable dt = new DataTable();
        string Import_FileName = path;
        string fileExtension = Path.GetExtension(Import_FileName);
        if (fileExtension == ".xls")
            conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
        if (fileExtension == ".xlsx")
            conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'";
        using (OleDbCommand comm = new OleDbCommand())
        {
            comm.CommandText = "Select * from [" + sheetName + "$]";

            comm.Connection = conn;

            using (OleDbDataAdapter da = new OleDbDataAdapter())
            {
                da.SelectCommand = comm;
                da.Fill(dt);
                return dt;
            }

        }
    }
}

- Gajendra

Answer 1

这对我起作用了

        using (OleDbConnection conn = new OleDbConnection())
        {
            DataTable dt = new DataTable();
            conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path
            + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";
            using (OleDbCommand comm = new OleDbCommand())
            {
                comm.CommandText = "Select * from [" + sheetName + "$]";
                comm.Connection = conn;
                using (OleDbDataAdapter da = new OleDbDataAdapter())
                {
                    da.SelectCommand = comm;
                    da.Fill(dt);
                    return dt;
                }
            }
        }

MAXSCANROWS=0会覆盖注册表默认值，在确定类型之前扫描所有行。仍需要包括IMEX=1。

例如，给定此表：

Header | Header
------ | ------
Cell1  | 2456354
Cell2  | 2456354
Cell3  | 2456354
Cell4  | 2456354
Cell5  | 2456354
Cell6  | 2456354
Cell7  | 2456354
Cell8  | 2456354
Cell9  | A5341

以下连接字符串将失去A5341。

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path 
   + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;'"

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path
   + ";Extended Properties='Excel 12.0 Xml;HDR=YES;MAXSCANROWS=0'"

当两者同时拥有时，它就能够起作用。

- Adam

Answer 2

您需要将TypeGuessRows注册表键的值设置为0，这样驱动程序将基于所有列值而不是前8个值来设置数据类型（默认情况下）。

该键的位置因驱动程序的版本而异，您可以根据您使用的特定版本轻松地在Google上找到它。例如，对于Access Connectivity Engine 2007，它将位于：

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

顺便说一句，您不需要Jet就能读取XLS文件，ACE同样可以完美胜任。

- Yuriy Galanter

---

在HDR=YES之后的连接字符串中加入;IMEX=1，可以实现同样的功能。 - Doug Glancy

据我记得，这种方法存在问题，但肯定值得一试，如果奏效的话，就比注册表编辑更好了。 - Yuriy Galanter

IMEX=1对此没有影响。请参阅此主题https://dev59.com/jmkw5IYBdhLWcg3wJXMh - Adam