在 C# 中解析 Excel 文件时，单元格似乎在 255 个字符处被截断...我该如何阻止这种情况？
我正在使用 c# 解析 asp.net 中上传的 excel 文件（xlsx）。 我正在使用以下代码（简化）：
string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";");
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connString);
DataSet ds = new DataSet();
adapter.Fill(ds);
adapter.Dispose();
DataTable dt = ds.Tables[0];
var rows = from p in dt.AsEnumerable() select new { desc = p[2] };
这工作得很好，但是如果单元格中的字符长度超过 255 个字符，它将被截断。 知道我做错了什么吗？ 谢谢。
编辑：当查看 Excel 工作表时，它显示的字符远远超过 255 个字符，所以我不相信工作表本身是有限的。
I am parsing through an uploaded excel files (xlsx) in asp.net with c#. I am using the following code (simplified):
string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";");
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connString);
DataSet ds = new DataSet();
adapter.Fill(ds);
adapter.Dispose();
DataTable dt = ds.Tables[0];
var rows = from p in dt.AsEnumerable() select new { desc = p[2] };
This works perfectly, but if there is anything longer than 255 characters in the cell, it will get cut off. Any idea what I am doing wrong? Thank you.
EDIT: When viewing the excel sheet, it shows much more than 255 characters, so I don't believe the sheet itself is limited.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
解决方案！
我今天也一直在与这个斗争。 我最终通过在解析 Excel 电子表格之前修改一些注册表项来使其工作。
您必须在解析 Excel 电子表格之前更新此注册表项：
将在此下的
TypeGuessRows更改为0，并将ImportMixedTypes更改为Text钥匙。 您还需要更新连接字符串以在扩展属性中包含IMEX=1：参考文献
http://blogs.vertigo.com/personal/aanttila/Blog/archive/2008/03/28/excel-and -csv-reference.aspx
http://msdn.microsoft.com /en-us/library/ms141683.aspx
The Solution!
I've been battling this today as well. I finally got it to work by modifying some registry keys before parsing the Excel spreadsheet.
You must update this registry key before parsing the Excel spreadsheet:
Change
TypeGuessRowsto0andImportMixedTypestoTextunder this key. You'll also need to update your connection string to includeIMEX=1in the extended properties:References
http://blogs.vertigo.com/personal/aanttila/Blog/archive/2008/03/28/excel-and-csv-reference.aspx
http://msdn.microsoft.com/en-us/library/ms141683.aspx
我遇到过这个问题，对我有用的解决方案是将带有长文本的单元格移动到电子表格的顶部。
我在描述该问题的论坛中找到了此评论
希望有所帮助其他人！
I have came across this, and the solution that worked for me was to move the cells with long text to the top of the spreadsheet.
I found this comment in a forum describing the issue
Hope this help someone else!
您是否尝试过将电子表格中的列数据类型设置为文本？ 我相信这样做将使单元格包含超过 255 个字符。
[编辑]
对于与 MS 的此对话框的价值- 《Excel 团队》读起来很有趣。 在底部的评论部分，他们对 255 的截止点进行了一些讨论。 他们说 Excel 12 可以支持每个单元格 32k 字符。
如果这是真的，那么一定有办法获取这些数据。 这里有两件事需要考虑。
过去，我在连接字符串中使用了“IMEX=1”选项来处理包含显示为空的混合数据的列。 虽然可能性不大，但你可以尝试一下。
您能否将文件导出为制表符分隔的平面文件？ 恕我直言，这是处理 Excel 数据的最可靠方法，因为 Excel 确实有很多问题。
Have you tried setting the columns datatype to text within the spreadsheet? I believe doing this will allow the cells to contain much more than 255 characters.
[Edit]
For what it's worth this dialog with the MS-Excel team is an interesting read. In the comments section at the bottom they get into some discussions about that 255 cutoff. They say Excel 12 can support 32k characters per cell.
If that is true there must be a way to get at this data. Here is two things to consider.
In the past I have used the "IMEX=1" option in my connection string to deal with columns containing mixed data showing up as empty. It's a longshot, but you might give that a try.
Could you export the file to a tab delimited flat file? IMHO this is the most reliable way of dealing with Excel data, since Excel does have so many gotchas.
仅从对该主题的快速谷歌搜索来看，这似乎是 Excel 的限制。
编辑：可能的解决方法（不幸的是在VB中）
Just from a quick Googling of the subject, it appears that that's a limit of Excel.
EDIT: Possible workaround (unfortunately in VB)
SpreadsheetGear for .NET 可以读取和写入（以及更多）xls 和 xlsx 工作簿，并支持与 Excel 相同的限制对于文本 - 换句话说它会起作用。 如果您想尝试一下，可以免费评估。
免责声明：我拥有 SpreadsheetGear LLC
SpreadsheetGear for .NET can read and write (and more) xls and xlsx workbooks and supports the same limitations as Excel for text - in other words it will just work. There is a free evaluation if you want to give it a try.
Disclaimer: I own SpreadsheetGear LLC
关于上一篇文章，我还使用了 SpreadsheetGear，发现从旧的 XLS（不是 XLSX）格式读取时，它也受到每个单元格 255 个字符的限制。
Regarding the last post, I also use SpreadsheetGear and find that it also suffers from the 255 characters per cell limitation when reading from the older XLS (not XLSX) format.