c# - ODBC 连接到 Excel 错误

Question

我在使用 ODBC 连接时遇到问题，该连接应该连接到 Excel 表格并对其进行处理。我已经在互联网上阅读了很多关于它的资料，但没有任何解决方案对我有帮助(包括 stackoverflow)。

所以基本上我正处于尝试打开与表的连接的位置。

private static SortedList<string, School> generateSchoolListExcel(string listFilePath)
{
    StringBuilder con = new StringBuilder();

    OdbcConnectionStringBuilder.AppendKeyValuePair(con, "Data Source", listFilePath);
    OdbcConnectionStringBuilder.AppendKeyValuePair(con, "HDR", "yes");
    OdbcConnectionStringBuilder.AppendKeyValuePair(con, "Format", "xlsx");
    OdbcConnectionStringBuilder.AppendKeyValuePair(con, "Driver", "{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}");
   //I have tried to specify driver without parentheses {} but it's still the same

    List<School> schoolList = new List<School>();

    using (OdbcConnection excel = new OdbcConnection(con.ToString()))
    {
        excel.Open();
        //doing actuall stuff
    }

        return schoolList;
}

当我调用 excel.Open() 方法时，我得到 OdbcException 消息:

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified", which is odd because I have those specified in the string named con.

还值得一提的是，在 ODBC 数据源管理器中，我可以清楚地看到我已经安装并运行了那些驱动程序。

还有一个奇怪的部分。当我调用我在 stackoverflow 上找到的以下方法时，它会返回以下驱动程序列表:

• "Driver da Microsoft para arquivos texto (*.txt; *.csv)"
• "Driver do Microsoft Access (*.mdb)"
• "Driver do Microsoft dBase (*.dbf)"
• "Driver do Microsoft Excel(*.xls)"
• "Driver do Microsoft Paradox (*.db )"
• "Microsoft Access Driver (*.mdb)"
• "Microsoft Access-Treiber (*.mdb)"
• "Microsoft dBase Driver (*.dbf)"
• "Microsoft dBase-Treiber (*.dbf)"
• "Microsoft Excel Driver (*.xls)"
• "Microsoft Excel-Treiber (*.xls)"
• "Microsoft ODBC for Oracle"
• "Microsoft Paradox Driver (*.db )"
• "Microsoft Paradox-Treiber (*.db )"
• "Microsoft Text Driver (.txt;.csv)"
• "Microsoft Text-Treiber (*.txt; *.csv)"
• "SQL Server"
• "SQL Server Native Client 11.0"

这些都没有“*.xlsx”，这是我正在尝试读取的文件的格式。

方法如下:

public static List<String> GetSystemDriverList()
{
    List<string> names = new List<string>();
    // get system dsn's
    Microsoft.Win32.RegistryKey reg = (Microsoft.Win32.Registry.LocalMachine).OpenSubKey("Software");
    if (reg != null)
    {
        reg = reg.OpenSubKey("ODBC");
        if (reg != null)
        {
            reg = reg.OpenSubKey("ODBCINST.INI");
            if (reg != null)
            {

                reg = reg.OpenSubKey("ODBC Drivers");
                if (reg != null)
                {
                    // Get all DSN entries defined in DSN_LOC_IN_REGISTRY.
                    foreach (string sName in reg.GetValueNames())
                    {
                        names.Add(sName);
                    }
                }
                try
                {
                    reg.Close();
                }
                catch { /* ignore this exception if we couldn't close */ }
            }
        }
    }

    return names;
}

应该注意的是，当我实际转到 regedit 并找到我清楚地看到的那些值时:

"Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)".

我是否遗漏了一些明显的东西？请帮助我:)

顺便说一下，我对 .NET 这方面还很陌生，所以请让您的回答保持简单，这样我才能真正理解发生了什么。谢谢!

编辑:一位 friend 指出我应该提供更多信息，所以这里是注册表编辑器的屏幕截图，ODBC 数据源管理器以及 ACEODBC.DLL 确实存在于我的硬盘上的证据:

此外，con.ToString() 提供以下内容:

Data Source="G:\POS\odabrane_skole novo_mod.xlsx";Driver="{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}"

Answer 1

使用OleDbConnection怎么样，首先得安装Microsoft Access Database Engine 2010

string path = @"c:\sample.xlsx";
string strCon = " Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source = " + path + ";Extended Properties='Excel 12.0;'";
OleDbConnection objConn = new OleDbConnection(strCon);

string strCom = " SELECT * FROM [a$] ";
objConn.Open();