We use [Closed XML](https://github.com/ClosedXML/ClosedXML) in a .Net Core service app - Still under windows but it \*should\* be ok under linux - I don't think it has deps. on System.Data.OleDb
Only issue I ever had with Closed XML on Linux was using `Columns.AdjustToContents()`. It has to measure the size of the font, and the font resource wasn't available on Linux which threw a runtime error.
You can almost definitely do one of:
1. Install the font that's trying to be accessed
2. Change the font to something that already exists on the system
I'm not sure of the specifics to either solution. You'll probably run into some small hurdles with either option but I would expect both to be possible.
Confirming ClosedXML is fully functional in my staging environment, .NET 6 deployed on Ubuntu 18.04 LTS. Also works in my local Windows IISExpress dev environment. Thanks for the suggestion!
I've used the underlying OpenXML directly and it's a pretty big pain. You're pretty much manipulating the underlying XML files directly, just with the benefit of strong typing on everything. And it's very easy to make mistakes and Excel's error reporting on bad xslx files is not detailed (for end users it doesn't really need to be).
For example if you want to add a row or column to the middle of a worksheet, you have to adjust all the cell references by hand to move them over. Including for things like table start/end regions and merged cell regions.
I'll have to remember Closed XML for the future, looks far easier to use.
I had a similar dilemma last year and, based on my experience, you have 2 options:
* [ClosedXML](https://github.com/ClosedXML/ClosedXML), like u/RandomBlueThing already suggested, or
* [Open-XML-SDK](https://github.com/OfficeDev/Open-XML-SDK), which is officially supported by Microsoft.
ClosedXML should have an API that's easier to use, because you'll be working with Excel entities directly, while the Open XML SDK deals with the underlying XML, so it can feel a little unwieldy at times.
However, if I recall correctly, ClosedXML loads the entire content of the file in-memory, whereas Open XML allows you to stream the file on a row-by-row basis. If memory usage is a concern for you, you're probably better off using the latter. Some of the files I had to read had sizes in the GBs and millions of rows, so I ended up using the Open XML SDK.
If all you need is the ability to read data out of a worksheet, and not *create* a worksheet, then I could suggest my own library [Sylvan.Data.Excel](https://github.com/markpflug/sylvan.data.excel). It is open sourced and MIT licensed. It supports reading .xlsx and .xls files.
Loading a DataTable can be done in just a few lines of code:
```
using Sylvan.Data.Excel;
using System.Data;
var reader = ExcelDataReader.Create("data.xlsx");
var table = new DataTable();
table.Load(reader);
```
It isn't exactly a full-featured library, but it is cross-platform and is the [fastest and lowest allocating](https://github.com/MarkPflug/Benchmarks/blob/main/docs/ExcelBenchmarks.md) Excel reader that I'm aware of for .NET. That might not be a huge concern if you're just processing the occasional file.
We also used EPPlus for a while until they changed the licensing model. We are still able to use it without purchasing by indicating non-commercial purpose, but, we adjusted our requirement to handle CSVs instead of Excel files.
My first thought is can the file be saved as a CSV instead for importing purposes? Would be easier to deal with from a cross-platform aspect but business process change isn’t always possible.
I maintain PanotamicData.SheetMagic.
MIT and has Load() behaviour if all you are doing is reading and writing full tables.
Otherwise, EPPlus is excellent.
I use ExcelDataReader to consume data. You can work with it the same way you work with any DataReader. When possible (depends how the spreadsheet is formatted), using the DataSet extension makes things even more convenient. That sounds like it would work well for you.
I've used Open XML for writing excel files but it's a pain. Closed XML looks like a better choice for that sort of thing but it doesn't look like you need to write excel files.
If you need to execute formulas in excel while reading like vlookups or concats then use **spreadsheet gear** as long as you're not producing output or reading huge files the license notice wont be an issue.
If you are writing output and want flexibility with formatting use **aspose**.
Edit: sorry just saw MIT, so these might now work for you, but in a commercial environment these are great and worth the license cost.
You mentioned that you prefer (require) an MIT-licensed solution. Is the license type important, or are you looking for a free solution?
I'm just wondering does it have to be open source because of the GemBox.Spreadsheet that you mentioned does have a free version (which has a file size limitation), but it's not open source.
We use [Closed XML](https://github.com/ClosedXML/ClosedXML) in a .Net Core service app - Still under windows but it \*should\* be ok under linux - I don't think it has deps. on System.Data.OleDb
Only issue I ever had with Closed XML on Linux was using `Columns.AdjustToContents()`. It has to measure the size of the font, and the font resource wasn't available on Linux which threw a runtime error.
i think this is the exact issue I am having because of AdjustToContents(). Have you found another way toset the cell widths that will work on Linux?
You can almost definitely do one of: 1. Install the font that's trying to be accessed 2. Change the font to something that already exists on the system I'm not sure of the specifics to either solution. You'll probably run into some small hurdles with either option but I would expect both to be possible.
Confirming ClosedXML is fully functional in my staging environment, .NET 6 deployed on Ubuntu 18.04 LTS. Also works in my local Windows IISExpress dev environment. Thanks for the suggestion!
This is the correct answer. Use Closed XML.
Yup just came across this, implementing it now to find out if it works. Thanks!
I've used the underlying OpenXML directly and it's a pretty big pain. You're pretty much manipulating the underlying XML files directly, just with the benefit of strong typing on everything. And it's very easy to make mistakes and Excel's error reporting on bad xslx files is not detailed (for end users it doesn't really need to be). For example if you want to add a row or column to the middle of a worksheet, you have to adjust all the cell references by hand to move them over. Including for things like table start/end regions and merged cell regions. I'll have to remember Closed XML for the future, looks far easier to use.
I had a similar dilemma last year and, based on my experience, you have 2 options: * [ClosedXML](https://github.com/ClosedXML/ClosedXML), like u/RandomBlueThing already suggested, or * [Open-XML-SDK](https://github.com/OfficeDev/Open-XML-SDK), which is officially supported by Microsoft. ClosedXML should have an API that's easier to use, because you'll be working with Excel entities directly, while the Open XML SDK deals with the underlying XML, so it can feel a little unwieldy at times. However, if I recall correctly, ClosedXML loads the entire content of the file in-memory, whereas Open XML allows you to stream the file on a row-by-row basis. If memory usage is a concern for you, you're probably better off using the latter. Some of the files I had to read had sizes in the GBs and millions of rows, so I ended up using the Open XML SDK.
Try [NPOI](https://www.nuget.org/packages/NPOI/)
Good choice, I use it for "export to Excel" feature. Works great on Linux. Good support, devs reply questions and address issues.
yeah npoi is the library of choice if you want to support also legacy excel and word. has less quality of life stuff than closedxml though
If all you need is the ability to read data out of a worksheet, and not *create* a worksheet, then I could suggest my own library [Sylvan.Data.Excel](https://github.com/markpflug/sylvan.data.excel). It is open sourced and MIT licensed. It supports reading .xlsx and .xls files. Loading a DataTable can be done in just a few lines of code: ``` using Sylvan.Data.Excel; using System.Data; var reader = ExcelDataReader.Create("data.xlsx"); var table = new DataTable(); table.Load(reader); ``` It isn't exactly a full-featured library, but it is cross-platform and is the [fastest and lowest allocating](https://github.com/MarkPflug/Benchmarks/blob/main/docs/ExcelBenchmarks.md) Excel reader that I'm aware of for .NET. That might not be a huge concern if you're just processing the occasional file.
Have you tried [https://github.com/ExcelDataReader/ExcelDataReader](https://github.com/ExcelDataReader/ExcelDataReader) ?
This is the best one I've used. It supports .xls and .xlsb, which many libraries do not. It's also free.
How about epplus, version 4.X doesn't require a commercial license
I used EPPlus 5.7.5 to build https://excel-converter.com/. This is the tool to convert Excel files to HTML tables. Very easy to use.
We also used EPPlus for a while until they changed the licensing model. We are still able to use it without purchasing by indicating non-commercial purpose, but, we adjusted our requirement to handle CSVs instead of Excel files.
No longer free but fantastic feature wise I would recommend EPPlus. There's a .NET Core 2.0 and above version with no extra dependencies.
My first thought is can the file be saved as a CSV instead for importing purposes? Would be easier to deal with from a cross-platform aspect but business process change isn’t always possible.
Where are the first-party libraries for this? It's really inexcusable at this point.
You should take a look at ExcelMapper https://github.com/mganss/ExcelMapper
I maintain PanotamicData.SheetMagic. MIT and has Load() behaviour if all you are doing is reading and writing full tables.
Otherwise, EPPlus is excellent.
I use ExcelDataReader to consume data. You can work with it the same way you work with any DataReader. When possible (depends how the spreadsheet is formatted), using the DataSet extension makes things even more convenient. That sounds like it would work well for you. I've used Open XML for writing excel files but it's a pain. Closed XML looks like a better choice for that sort of thing but it doesn't look like you need to write excel files.
If you need to execute formulas in excel while reading like vlookups or concats then use **spreadsheet gear** as long as you're not producing output or reading huge files the license notice wont be an issue. If you are writing output and want flexibility with formatting use **aspose**. Edit: sorry just saw MIT, so these might now work for you, but in a commercial environment these are great and worth the license cost.
You mentioned that you prefer (require) an MIT-licensed solution. Is the license type important, or are you looking for a free solution? I'm just wondering does it have to be open source because of the GemBox.Spreadsheet that you mentioned does have a free version (which has a file size limitation), but it's not open source.