✅ How can I read and edit .XLSX files in C# (WPF)
I am in need of creating a desktop application, where I can see and interact with excel data, cant use database, because it needs to run offline. What are the best libraries I can use? This is my pilot project, starting with C#, I have a good understanding of Excel VBA, if there is something similar to EVBA, that would be helpful. Thanks.
68 Replies
IME they all suck
Try closedxml but watch the memory usage, i've seen it use a ridiculous amount of GB to do simple edits to a 10mb file
I ended up using an old LGPL version of EP Plus which was much more memory efficient
doesn't closedxml only creates and writes? I would like to also read the file and be able to basically access any cell I desire, or at least read the whole sheet and store it in an Array, which I can parse through and work with it.
I wonder how commercial solutions compare, e.g., devexpress should have something for working with excel files
If it can write, it must be able to read?
You can read files with ClosedXML
My only issue with it is the insane size of the column and row objects in memory
they are absolutely huge
I will have to take a more thorough look.
There will be dedicated PC just for that, so It should be fine hopefully.
cant use database, because it needs to run offlineAnd why should that prevent you from using a database? SQLite and LiteDB exist
Also ms access 💀
Company policy, any kind of a third party software cant use and access server
No 🙂
Uh, Excel is third-party
unless...
Unless you mean SQLite being on the server'
There are a few exceptions, we have a white/black list
In which case... no
SQLIte is local
sqlite doesn't even need software installed
^
it's all contained in the library
Also the whole company is working with excel files, so migrating it all would be bigger pain than trying to figure how to parse the excel file
If you can use sqlite, definitely do. It will be a much nicer move than working with xlsx
My condolences
Maybe just try using CSV files, dunno
same but my job uses tons of spreadsheets with VBA macros that directly access our DB 
Jesus
EPPlus is the best of the ones I've used but the license isn't super friendly
Yep, could not even use things like Selenium to test a few things... I am very limited by what I can do.
Time to look for another company 
Is it a bank or smth?
Use LiteDB, then. It's an embedded database
Even at commerzbank we don't have such insane regulations
Ohh yeah, I work on these, right now everything is automated by excel files and I am super pissed, it is nightmare to work with, if I need to merge and fix any kind of mistake or added feature
A bank, a school, or a hospital
Or a government agency
yeah i'm going to try and move all the logic to an API and reduce the VBA stuff to simple things like requesting data and dumping into the right cells
Worse Corporate for Designing of Catalytic Convertors.
Basically Automotive, and the higher ups are totally clueless about any kind of programing.
They won't notice you using SQLite then 
Well, I want to stay there for atleast a year to learn how to code in peace, they gave me a lot of time to figure it out.
They wont, but our IT team will 😄
Join forces with the it team
Is your IT team just as clueless?
Sadly for me there is no other way but to go Desktop + XLSX as database.
I'd definitely present it as an option alongside using excel and explain why you think it will be better
then at least it's on them for shooting you down if it all goes to heck
Naah, they dont really have any power, they are just taking care of the computers etc..
The thing about corporate is, that it takes months and they will on 99% denie it anyway
Worth a shot at least
Higher ups understand money and budget aaand meetings. Setup a meeting, tell them that you need permission from the it team to move forward with the project, otherwise they will have to deepen their pockets in the long term
Especially since it doesn't need any whitelisting or anything
Since SQLite is more of a file standard than software
Tried it a few times, with some other things, like if I can use SAP GUI for creating a more user friendly SAP operations, but nope,
They care about production, if the project does not yield any added value they dont care, and this just helps maintenance and warehouse management, it is not important for them.
I think getting in touch with it is the first step forward
I am glad they atleast let me install Visual Studio 😅
I once got denied installing vs, because as a developer i pose a huge risk to the bank, as I can change 0's and 1's
Wonder why I got hired then...
even as a developer you shouldn't have access to anything that can do any damage
Yeah, that is kinda what is also happening around here, but after a few months there working as the only one who can do some basic sripting and VBA it got better, but not by much.
unless they give devs blanket access to all their systems or something
Exactly, pay me to spin on the shitty office chair
Well, I will try the ClosedXML and see, problem is, that I am talking about thousands of warehouse items, hope it will not be overwhelmed.
And what about... Export to csv, process, and export back to excel....? Does that solve anything?
Hope the thing it's running on has a lot of memory
If not you might want to cough up for a EPPlus license
I mean, in terms of posibillity, it is probably doable, but that memory issue might still prevail.
Fat chance, but I will see what can be done.
If it's an internal tool you can probably get away with using the old LGPL versions of EP Plus too
But that might break dependency rules
parsing the whole sheet into an multidimensional array might be even worse, but that would mean, that I dont have to be connected to that XLSX constantly, just read it once and work with the data
OpenXML is what we use at work, but I've nevr really done, like, any analysis on how good it is, compared to other solutions
I do know we can generate spreadsheets with roundabout 300,000 rows before we risk the process running out of memory
actually, I think we observed out-of-memory exceptions at like 500,000, and set a limit on ourselves for 300,000
I've benchmarked most of the popular .NET Excel libraries here:
https://github.com/MarkPflug/Benchmarks/blob/main/docs/ExcelReaderBenchmarks.md
I'm the author of the Sylvan library. If performance and memory efficiency is your main concern, you might give it a try. It provides a minimal feature set to read data out of Excel, but doesn't try to provide the full feature set of other libraries.
GitHub
Benchmarks/docs/ExcelReaderBenchmarks.md at main · MarkPflug/Benchm...
Benchmarks for various .NET libraries. Contribute to MarkPflug/Benchmarks development by creating an account on GitHub.
Nice work, that's really impressive
Thanks. The project repository is here: https://github.com/MarkPflug/sylvan.data.excel
GitHub
GitHub - MarkPflug/Sylvan.Data.Excel: The fastest .NET library for ...
The fastest .NET library for reading Excel data files. - GitHub - MarkPflug/Sylvan.Data.Excel: The fastest .NET library for reading Excel data files.
Thanks, will take a look. I am just trying to do basic stuff, I dont need no styles and fonts etc.. I just want to edit and read values in cells.
definitely gonna bookmark thos for when I get around to re-evaluating our Excel junk at work
Was this issue resolved? If so, run
/close - otherwise I will mark this as stale and this post will be archived until there is new activity.Here is a tutorial, that worked for me. Only problem is, that office needs to be installed for this to work. Also it is needed to close the workbook after being done with the worksheet or else the book will get locked. https://stardevstudio.com/csharp/how-to-read-excel-files-in-csharp/?mwg_rnd=8606258
Darren Grantham
StarDevStudio
How to read Excel files in CSharp - StarDevStudio
We are going to be reading data that is stored in Excel files using CSharp. Writing code to read Excel sheets in C# is really easy!
wb.close(); closes the workbook, it has the same logic and function as programming in excel with VBA.