使用OLEDB读取Excel工作表数据 [英] Reading Excel Sheet data using OLEDB

问题描述

我开发了一个应用程序，该应用程序使用OLEDB从excel表中读取数据.
在我使用Excel 2007连接字符串的PC上，它可以正常工作.但是，当我将输出文件复制到另一台计算机上时，它就无法工作并出现错误.

最终，我认为这是因为该计算机已安装Excel 2003
然后我尝试在Excel 2003的安装位置使用连接字符串，如下所示-

I have developed an application which reads data from excel sheet using OLEDB.
It''s working fine on my PC where I am using connection string for Excel 2007. But when I copy output files to some another machine then its not working and trowing an error.

Eventually I thought that it was because that machine has Excel 2003 installed
then I tried where Excel 2003 is installed I used connection string as follow -

string sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=" + filePath + ";Extended Properties=Excel 8.0;HDR=YES;";
OleDbConnection dbCon = new OleDbConnection(sConnection);
dbCon.Open();

但是也发生了错误.当我尝试在该机器上调试代码时，在打开OLEDB连接时，它抛出了类似"ServerVersion =" dbCon.ServerVersion"的错误，并引发了"System.InvalidOperationException"类型的异常.

我没有得到错误背后的原因.请帮忙.告诉我是否需要更多信息.

but there also error is occurring. When I tried to debug my code on that machine then it threw an error like "ServerVersion = ''dbCon.ServerVersion'' threw an exception of type ''System.InvalidOperationException''" at the time of opening OLEDB Connection.

I am not getting the reason behind the error. Please help. Tell me if you need more information.

推荐答案

您可以尝试执行以下操作(请注意，我喜欢使用一组后备连接字符串，您可能想要在其中添加与您相关的字符串)；

You could try something like this (note that I like to use a set of fall back connection strings, you might want to add strings that are relevant to you there);

public IEnumerable<sometypeofyours> Load(string filename, string sheetName)
{

    foreach (string connectionStringBase in new[]
        {
            "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;",
            "Provider=Microsoft.Jet.OLEDB.4.0;Data source={0};Extended Properties=Excel 8.0;"
        })
    {
        try
        {
            string connectionString = String.Format(connectionStringBase, filename);
            OleDbDataAdapter adapter = new OleDbDataAdapter(String.Format("select * from [{0}

"，sheetName)，connectionString)中选择*； DataSet数据集= 新 DataSet(); adapter.Fill(数据集，" ); DataTable表=数据集.Tables[" ]; IList< sometypeofyours>结果= 新 List< sometypeofyours>(); foreach (表中的System.Data.DataRow行.rows). { SomeTypeOfYours item = 新 SomeTypeOfYours {SomeProperty = row.Get( 0 )}; result.Add(item); } 返回结果； } 捕获(异常) { // 试试下一个 } } 抛出 新 ArgumentOutOfRangeException(" 文件名"， 文件不包含已知格式的导入数据. "); }

", sheetName), connectionString); DataSet dataset = new DataSet(); adapter.Fill(dataset, "dummy"); DataTable table = dataset.Tables["dummy"]; IList<sometypeofyours> result = new List<sometypeofyours>(); foreach (System.Data.DataRow row in table.Rows) { SomeTypeOfYours item = new SomeTypeOfYours { SomeProperty = row.Get(0) }; result.Add(item); } return result; } catch (Exception) { // Try next } } throw new ArgumentOutOfRangeException("filename", "File does not contain import data in a known format."); }

希望这会有所帮助，
弗雷德里克(Fredrik)

Hope this helps,
Fredrik

这篇关于使用OLEDB读取Excel工作表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！