Excel的＆QUOT;外部表不是预期的格式＆QUOT。 [英] Excel "External table is not in the expected format."

问题描述

我试图读取使用如下所示的code一个Excel（XLSX）文件。我得到一个外部表不是预期的格式。错误，除非我有该Excel文件已经打开。换句话说，我要在Excel中打开文件之前首先我可以读，如果从我的C＃程序。该xlsx档案是在我们的网络上的共享。我怎样才能读取文件而无需首先打开它？
谢谢

  SQL字符串=SELECT * FROM [工作表Sheet1 $];
字符串excelConnection =供应商= Microsoft.Jet.OLEDB.4.0;数据源=+路径+;扩展属性= \\Excel中8.0; HDR = YES; IMEX = 1; \\;使用（OleDbDataAdapter的适配器=新OleDbDataAdapter的（SQL，excelConnection））{
    DataSet的DS =新的DataSet（）;
    adaptor.Fill（DS）;
}
 

解决方案

外部表不是预期的格式。通常发生时试图使用Excel 2007文件与使用连接字符串：Microsoft.Jet.OLEDB.4.0和扩展属性= Excel的8.0

使用下面的连接字符串似乎解决大部分问题。

 公共静态字符串路径= @C：\\ SRC \\ RedirectApplication \\ RedirectApplication \\ 301s.xlsx
公共静态字符串connStr =供应商= Microsoft.ACE.OLEDB.12.0;数据源=+路径+;扩展属性= Excel的12.0;;
 

I'm trying to read an Excel (xlsx) file using the code shown below. I get an "External table is not in the expected format." error unless I have the file already open in Excel. In other words, I have to open the file in Excel first before I can read if from my C# program. The xlsx file is on a share on our network. How can I read the file without having to open it first? Thanks

string sql = "SELECT * FROM [Sheet1$]";
string excelConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathname + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";

using (OleDbDataAdapter adaptor = new OleDbDataAdapter(sql, excelConnection)) {
    DataSet ds = new DataSet();
    adaptor.Fill(ds);
}

解决方案

"External table is not in the expected format." typically occurs when trying to use an Excel 2007 file with a connection string that uses: Microsoft.Jet.OLEDB.4.0 and Extended Properties=Excel 8.0

Using the following connection string seems to fix most problems.

public static string path = @"C:\src\RedirectApplication\RedirectApplication\301s.xlsx";
public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

这篇关于Excel的＆QUOT;外部表不是预期的格式＆QUOT。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！