使用C#读取Excel文件会引发OleDbException [英] Reading an Excel file with C# causes OleDbException to be thrown

问题描述

消息说

Microsoft Access数据库引擎找不到对象"Sheet1 $".确保该对象存在，并拼写其名称，然后正确的路径名.如果"Sheet1 $"不是本地对象，请检查您的网络连接或与服务器管理员联系.

The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator.

工作表中工作表的名称为"Sheet1"

The name of the sheet in the Worksheet is "Sheet1"

        string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES"";", fileName);
        string query = String.Format("SELECT [columnName1],[columnName2],[columnName3] from [{0}]", "Sheet1$");
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
        DataSet dataSet = new DataSet();
        dataAdapter.Fill(dataSet);
        DataTable YourTable = dataSet.Tables[0];
        listBox1.DataSource = YourTable.Columns["ColumnName1"];

推荐答案

这对我有用:

string filename = @"C:\Book1.xlsm";

        string connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\";", filename);
        string query = String.Format("SELECT * from [{0}$]", "Sheet1");
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
        DataSet dataSet = new DataSet();
        dataAdapter.Fill(dataSet);
        DataTable YourTable = dataSet.Tables[0];

* 注意:* 如果您的数据没有标头，以使 HDR = NO

*NOTE: * If your data does not have headers to make HDR=NO

还注意到您在问题中使用了

Also noticed that in your question you used

[columnName1],[columnName2],[columnName3]

供您选择的列.请记住，这些值应该是您要获取的列中第一个单元格的值.

for your columns to select. Please remember these should be the value(s) of the first cell in the column(s) that you would like to grab.

要获取E列，请使用:

        string connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=NO\";", filename);
        string query = String.Format("SELECT [F5] from [{0}$]", "Sheet1");

用您需要的其他任何列号替换

Replace 5 With any other column number you need so

F1 = A
F2 = B
F3 = C 

，依此类推.

您遇到的错误可能是因为文件已打开并处于活动状态.

The Error you are getting could be because you have the file open and active.

或者您指向的是错误的文件(请记住，您必须在 filename 字符串中包含完整的文件路径.并确保工作表正确.Alos请注意以下事实:我包括了 $ 在我的字符串中，而不是在我的参数中，因此请记住，仅输入您要获取的工作表的名称.如果仍然无法为我提供工作表的完整文件名，使用 C:\ Book1.xlsm 和您尝试从中获取数据的表.

OR you are pointing at the wrong file (Remember you have to include full file path in the filename string. and make sure the sheet is correct. Alos take notice to the fact the i include the $ in my string not in my parameter so rememer to only put just the name of the sheet you are trying to get. If you are still having trouble supply me with the FULL file name for the worksheet you are using i.e. C:\Book1.xlsm and the sheet you are trying to get data from.

这篇关于使用C#读取Excel文件会引发OleDbException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！