用C＃解析XLS文件的问题 [英] Problem parsing an XLS file with C#

问题描述

好吧，让我们看看能否让这个有意义。

我有一个编写的程序，解析Excel文件，它的工作原理很好。我使用以下内容进入文件：

  string FileToConvert = Server.MapPath（。）+\\\ \\App_Data\\CP-ARFJN-FLAG.XLS; 
 string connectionString =Provider = Microsoft.Jet.OLEDB.4.0; Data Source =+ FileToConvert +;扩展属性= Excel 8.0;; 
 OleDbConnection connection = new OleDbConnection（connectionString）; 
 connection.Open（）; 
 //下一行假定该文件是默认Excel格式，Sheet1作为第一个工作表名称，相应调整
 OleDbDataAdapter adapter = new OleDbDataAdapter（SELECT * FROM [CP-ARFJN-FLAG $] ，连接）; 
  

这个工作很好。但是当我尝试它的实际文件（由另一个程序提供给我）我得到这个错误：

 系统。 Data.OleDb.OleDbException：外部表不是预期的格式。在System.Data.PropertiesInd System.Data.ProviderBase.DbConnectionClosed.OpenConnection（DbConnection outerConnection，DbConnectionFactory connectionFactory）的System.Data.ProviderBase.DbConnectionFactory.GetConnection（DbConnection拥有连接）中的CreateNonPooledConnection（DbConnection owningConnection，DbConnectionPoolGroup poolGroup）在System.Data.OleDb.OleDbConnection.Open（ ）在wetglobe.Page_Load（对象发件人，EventArgs e）
  

但是，这是我认为问题的地方所在。如果我拿这个文件，并用我的本地Excel保存，首先我得到这个弹出窗口：

CP-ARFJN-FLAG.XLS可能包含与文本（Tab
分隔符）不兼容的功能
。你要保留这个格式的
工作簿吗？

• 不兼容的功能，单击
  是。


• 要保留功能，请单击否。
  十保存最新的Excel
  格式的副本。 >
  
• 要查看可能丢失的内容，请单击
  帮助。

如果我单击否，然后将其保存为当前的Excel格式，程序将会正常工作。

所以我假设这是保存在一些疯狂的老Excel格式？

我想我的问题是：

• 告诉什么Excel版本
  保存这个？


• 如何解析它当前的
  状态？


• -or-我可以编程保存为较新版本吗？

我希望很清楚...谢谢。

解决方案

这听起来像是你生成的XLS文件r第三方应用程序可能不是真正的Excel格式 - 它可能实际上是一个制表符分隔的文本文件.xls扩展名。

尝试用文本打开它编辑看。

如果是制表符分隔符，可以将OleDB适配器拖放并作为标准文本文件打开/解析。

Ok, let's see if I can make this make sense.

I have a program written that parses an Excel file and it works just fine. I use the following to get into the file:

string FileToConvert = Server.MapPath(".") + "\\App_Data\\CP-ARFJN-FLAG.XLS";
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileToConvert + ";Extended Properties=Excel 8.0;";
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
//this next line assumes that the file is in default Excel format with Sheet1 as the first sheet name, adjust accordingly
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [CP-ARFJN-FLAG$]", connection);

and this works just fine. But when I try it on the actual file (it is supplied to me by another program) I get this error:

System.Data.OleDb.OleDbException: External table is not in the expected format. at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at wetglobe.Page_Load(Object sender, EventArgs e)

BUT, this is where I think the problem lies. If I take that file, and save it with my local Excel, first I get this popup:

CP-ARFJN-FLAG.XLS may contain features that are not compatible with Text (Tab delimited). Do you want to keep the workbook in this format?

• To Keep this format, which leaves out any incompatible features, click Yes.
• To preserve the features, click No. Ten save a copy in the latest Excel format.
• To see what might be lost, click Help.

If I click No and then save it as the current Excel format, the program will then work fine.

So I am assuming this is saved in some crazy old Excel format?

I suppose my questions would be:

• How can I tell what Excel version saved this?
• How can I parse it in its current state?
• -or- Can I programatically save it as a newer version?

I hope that is clear... Thank you.

解决方案

It sounds like the XLS file generated by your third-party app may not really be in Excel format - it might actually be a tab-delimited text file with an .xls extension.

Try opening it with a text editor and see.

If it is tab delimited, you can ditch the OleDB adapter and open/parse it as a standard text file.

这篇关于用C＃解析XLS文件的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！