How to get OleDb for reading excel in asp.net core

Question

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?

Answer 1

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/) is one such library. It's well documented and allows to both read and write Excel files with custom cell style formatting.

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:

1. You will need to install MS ACE OLEDB provider which sometimes is hard to configure.
2. You will have to read Excel files in passes while with ClosedXML you can access rows/cells randomly by id/address.
3. OleDB uses a Windows registry configurable setting to check the number of rows (default is 8) to determine the data type for the whole column and sometimes there are issues with it because the data type is determined incorrectly. ClosedXML allows you to set a specific data type for any cell.

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.

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.

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.

Answer 2

As indicated by andrews, it is recommended to use a third-party library, especially when working with formatting. An alternative to ClosedXML is EPPlus. For most of my project I have used ClosedXML, but I had to switch to EPPlus because it has support for charts.

For ASP.NET Core, you can use EPPlus.Core. I have performed a quick test within an ASP.NET Core 2.0 console app and it seems to work fine:

var newFile = new FileInfo("some file.xlsx");
using (ExcelPackage xlPackage = new ExcelPackage(newFile))
{
   var ws = xlPackage.Workbook.Worksheets.Add("etc");
   ws.Cells[1, 1].Value = "test";

   // do work here 
   xlPackage.Save();
}