将 Excel 表转换为 C# DataTable 会裁剪掉一些数据
我正在使用以下代码将工作表上的一组 Excel 数据转换为 C# 数据表：
var connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + _filename + ";Extended Properties=\"Excel 12.0;HDR=YES\";");
var adapter = new OleDbDataAdapter("SELECT * FROM [owssvr$]", connection);
connection.Open();
var ds = new DataSet();
adapter.Fill(ds);
connection.Close();
return ds.Tables[0];
这似乎工作正常，但是当我循环遍历数据表以提取值时，我有一个大约 400 个字符的字段，并且当我我从数据表中抓取它，我只看到前 255 个字符。
有什么办法可以从 Excel 导入它而没有这个字符限制吗？
i am using the following code to convert an excel set of data on a worksheet to a C# dataTable:
var connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + _filename + ";Extended Properties=\"Excel 12.0;HDR=YES\";");
var adapter = new OleDbDataAdapter("SELECT * FROM [owssvr$]", connection);
connection.Open();
var ds = new DataSet();
adapter.Fill(ds);
connection.Close();
return ds.Tables[0];
This seems to work fine but when i loop through the dataTable to extract value out, i have one field that is about 400 characters and i when i grab it from the datatable i only see the first 255 characters.
Is there any way i can import this from excel with out this character limit?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
好的。你坐下了吗？
我要告诉你一些你不会喜欢的事情。
您必须编辑 registry 来解决这个问题。根据我的请阅读，即使您使用的是较新的非 Jet 驱动程序，您仍然需要执行此操作。是的，这太可怕了。不，我不知道他们为什么这样设计。在过去的一些用例中，这让我很恼火。
IMO 唯一真正的解决方案是根本不使用此驱动程序，而是使用 XLSX 文件，这些文件是封装在 ZIP 包中并使用 XLSX 扩展名重命名的 XML 文件。当给定这些文件之一时，您可以使用 Office Open XML 来读取和操作它，或者你可以用 SharpZibLib 解压缩包，找到相关的数据文件，并将其读取为 XML，无论你相信与否，我有时发现这更简单。
OK. Are you sitting down?
I'm about to tell you something that you are not going to like.
You have to edit the registry in order to fix this. And from what I've read, you still need to do this even if you are using the newer non-Jet driver. Yes, it's terrible. No, I have no idea why they designed it this way. This has burned me before on a number of use cases in the past.
The only real solution IMO is to not use this driver at all and use XLSX files instead, which are XML files wrapped up in a ZIP package and renamed with the XLSX extension. When given one of these files, you could either use the Office Open XML to read and manipulate it, or you could just unzip the package with SharpZibLib, find the relevant data file, and read it as XML, which I sometimes find simpler, believe it or not.
嗯，你还坐着吗？所以请继续坐着！
不幸的是，Dave 是对的，但我会更进一步，放弃 OLEDB 并用强大的第 3 方组件替换它。我可以推荐Spire.XLS。经过各种场景的测试，发现它是最方便、最可靠的一种。
希望这有帮助
Well, are you still sitting down? So please keep sitting!
Unfortunately, Dave is right, but I would go further and would get rid off OLEDB and replace it with a robust 3rd party component. I can recommend Spire.XLS. It was tested in various scenarios and was found as the most convenient and reliable one.
Hope this helped