在ASP.NET核心项目中如何获取OleDb以读取Excel [英] How to get OleDb for reading excel in asp.net core project
问题描述
有什么方法可以读取ASP.NET Core(基于.Net Core构建)中的excel数据吗?我无法在.net核心项目的project.json中引用OleDB.还有其他方法吗?
Is there any way of reading excel data in ASP.NET Core (built over .Net Core)? I am not able to refer OleDB in project.json of my .net core project. Is there any other way of doing this?
推荐答案
您今天真的需要OleDB来读取Excel吗?我认为，OleDB有点过时了.有一些开放源代码库可用于Excel文件，这些库更易于使用并且具有很大的灵活性.
Do you really need OleDB to read Excel today? To my opinion, OleDB is a bit outdated. There are opensource libraries to work with Excel files which are much easier to use and provide a lot of flexibility.
ClosedXML( https://closedxml.codeplex.com/)是这样的一个库.它有据可查，可以使用自定义单元格样式格式读取和写入Excel文件.
ClosedXML (https://closedxml.codeplex.com/) is one such library. It's well documented and allows to both read and write Excel files with custom cell style formatting.
我也使用OleDB读取非常大的Excel文件，它可以工作，但是有一些问题，这其中有一些是我无法想到的:
I have used OleDB for reading very large Excel files too, it works but there are certain issues with it, here are a few of them off the top of my head:
- 您将需要安装有时难以配置的MS ACE OLEDB提供程序.
- 您必须分批读取Excel文件，而使用ClosedXML则可以按ID/地址随机访问行/单元格.
- OleDB使用Windows注册表可配置设置来检查行数(默认为8)，以确定整个列的数据类型，有时会出现问题，因为数据类型确定不正确. ClosedXML允许您为任何单元格设置特定的数据类型.
我不是ClosedXML开发人员，因此这不是广告.如前所述，我在项目中使用了OleDB和ClosedXML(并继续使用).使用OleDB，我可以逐行或使用SQL Server"SELECT * FROM OPENROWSET(...)"读取非常大的Excel文件(例如，行数为400-800K +). SQL Server也可以使用相同的ACE提供程序直接写入Excel文件，并且它也适用于非常大的文件.
I'm not a ClosedXML developer, so this is not an ad. As I mentioned, I have used (and continue to use) both OleDB and ClosedXML in my projects. With OleDB I was able to read very large Excel files (400-800K+ of rows for example) either row by row or using SQL Server "SELECT * FROM OPENROWSET(...)". Also SQL Server can directly write to Excel files using same ACE provider and it worked for very large files too.
但是，ClosedXML我曾用于读取/写入相对较小的文件，但它们使用了许多自定义格式.因此，如果您开始一个新项目，我建议您不要使用OleDB.
However, ClosedXML I have used for reading/writing relatively small files but they used a lot of custom formatting. So if you start a new project I would recommend going away from OleDB.
ClosedXML的唯一限制是它仅支持压缩的XML Excel文件，即 Excel 2007+版本.您可以在上述ClosedXML网站上找到许多示例，这些示例将帮助您入门.
The only limitation of ClosedXML is that it support only zipped XML Excel files, i.e. Excel version 2007+. You can find many examples on the ClosedXML site mentioned above which would help you to get started.
让我知道这是否有帮助.谢谢.
Let me know if this was helpful. Thanks.
这篇关于在ASP.NET核心项目中如何获取OleDb以读取Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
