C# Excel“外部表不是预期的格式。”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1139390/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel "External table is not in the expected format."
提问by Sisiutl
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
我正在尝试使用下面显示的代码读取 Excel (xlsx) 文件。我收到“外部表不是预期的格式”。错误，除非我已经在 Excel 中打开了文件。换句话说，我必须先在 Excel 中打开文件，然后才能从 C# 程序中读取 if。xlsx 文件位于我们网络上的共享位置。如何在不必先打开文件的情况下读取文件？谢谢
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);
}
采纳答案by FAtBalloon
"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
“外部表不是预期的格式。” 通常在尝试使用带有以下连接字符串的 Excel 2007 文件时发生：Microsoft.Jet.OLEDB.4.0 和 Extended Properties=Excel 8.0
Using the following connection string seems to fix most problems.
使用以下连接字符串似乎可以解决大多数问题。
public static string path = @"C:\src\RedirectApplication\RedirectApplication1s.xlsx";
public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
回答by Nelson
Instead of OleDb, you could use the Excel Interop and open the worksheet as read-only.
您可以使用 Excel Interop 并以只读方式打开工作表，而不是 OleDb。
回答by Trex
Thanks for this code :) I really appreciate it. Works for me.
感谢您提供此代码 :) 我真的很感激。为我工作。
public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
So if you have diff version of Excel file, get the file name, if its extension is .xlsx, use this:
因此，如果您有不同版本的 Excel 文件，请获取文件名，如果其扩展名为.xlsx，请使用以下命令：
Private Const connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
and if it is .xls, use:
如果是.xls，请使用：
Private Const connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" + path + ";Extended Properties=""Excel 8.0;HDR=YES;"""
回答by cederlof
(I have too low reputation to comment, but this is comment on JoshCaba's entry, using the Ace-engine instead of Jet for Excel 2007)
（我的声誉太低无法发表评论，但这是对 JoshCaba 条目的评论，使用 Ace-engine 而不是 Jet for Excel 2007）
If you don't have Ace installed/registered on your machine, you can get it at: https://www.microsoft.com/en-US/download/details.aspx?id=13255
如果您的机器上没有安装/注册 Ace，您可以从以下网址获取：https: //www.microsoft.com/en-US/download/details.aspx?id=13255
It applies for Excel 2010 as well.
它也适用于 Excel 2010。
回答by John M. Black
I have also seen this error when trying to use complex INDIRECT() formulas on the sheet that is being imported. I noticed this because this was the only difference between two workbooks where one was importing and the other wasn't. Both were 2007+ .XLSX files, and the 12.0 engine was installed.
尝试在正在导入的工作表上使用复杂的 INDIRECT() 公式时，我也看到了此错误。我注意到这一点，因为这是两个工作簿之间的唯一区别，其中一个正在导入，另一个没有。两者都是 2007+ .XLSX 文件，并且安装了 12.0 引擎。
I confirmed this was the issue by:
我通过以下方式确认这是问题所在：
- Making a copy of the file (still had the issue, so it wasn't some save-as difference)
- Selecting all cells in the sheet with the Indirect formulas
- Pasting as Values only
- 制作文件的副本（仍然有问题，所以这不是一些另存为的区别）
- 使用间接公式选择工作表中的所有单元格
- 仅粘贴为值
and the error disappeared.
并且错误消失了。
回答by jordanhill123
I had this same issue(Using the ACE.OLEDB) and what resolved it for me was this link:
我遇到了同样的问题（使用 ACE.OLEDB），为我解决的是这个链接：
http://support.microsoft.com/kb/2459087
http://support.microsoft.com/kb/2459087
The gist of it is that installing multiple office versions and various office sdk's, assemblies, etc. had led to the ACEOleDB.dll reference in the registry pointing to the OFFICE12 folder instead of OFFICE14 in
其要点是安装多个 office 版本和各种 office sdk、程序集等导致注册表中的 ACEOleDB.dll 引用指向 OFFICE12 文件夹而不是 OFFICE14
C:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL
C:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL
From the link:
从链接：
Alternatively, you can modify the registry key changing the dll path to match that of your Access version.
Access 2007 should use OFFICE12, Access 2010 - OFFICE14 and Access 2013 - OFFICE15
(OS: 64bit Office: 64bit) or (OS: 32bit Office: 32bit)
Key: HKCR\CLSID{3BE786A0-0366-4F5C-9434-25CF162E475E}\InprocServer32\
Value Name: (Default)
Value Data: C:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL
(OS: 64bit Office: 32bit)
Key: HKCR\Wow6432Node\CLSID{3BE786A0-0366-4F5C-9434-25CF162E475E}\InprocServer32\
Value Name: (Default)
Value Data: C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL
或者，您可以修改注册表项，更改 dll 路径以匹配您的 Access 版本。
Access 2007 应使用 OFFICE12、Access 2010 - OFFICE14 和 Access 2013 - OFFICE15
（操作系统：64 位办公室：64 位）或（操作系统：32 位办公室：32 位）
密钥：HKCR\CLSID{3BE786A0-0366-4F5C-9434-25CF162E475E}\InprocServer32\
值名称：（默认）
数值数据：C:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL
（操作系统：64 位办公室：32 位）
密钥：HKCR\Wow6432Node\CLSID{3BE786A0-0366-4F5C-9434-25CF162E475E}\InprocServer32\
值名称：（默认）
数值数据：C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL
回答by user3140982
回答by John M
I was getting errors with third party and Oledb reading of a XLSX workbook. The issue appears to be a hidden worksheet that causes a error. Unhiding the worksheet enabled the workbook to import.
我在第三方和 Oledb 阅读 XLSX 工作簿时出错。该问题似乎是导致错误的隐藏工作表。取消隐藏工作表使工作簿能够导入。
回答by Andrey Belykh
This can also be a file that contains images or charts, see this: http://kb.tableausoftware.com/articles/knowledgebase/resolving-error-external-table-is-not-in-expected-format
这也可以是包含图像或图表的文件，请参见：http: //kb.tableausoftware.com/articles/knowledgebase/resolving-error-external-table-is-not-in-expected-format
The recommendation is to save as Excel 2003
建议另存为 Excel 2003
回答by Kieran Quinn
Just adding my solution to this issue. I was uploading a .xlsx file to the webserver, then reading from it and bulk inserting to SQL Server. Was getting this same error message, tried all the suggested answers but none worked. Eventually I saved the file as excel 97-2003 (.xls) which worked... only issue I have now is that the original file had 110,000+ rows.
只是将我的解决方案添加到这个问题。我正在将 .xlsx 文件上传到网络服务器，然后从中读取并批量插入到 SQL Server。收到同样的错误消息，尝试了所有建议的答案，但没有一个奏效。最终我将文件保存为 excel 97-2003 (.xls)，它起作用了……我现在唯一的问题是原始文件有 110,000+ 行。
