Which library to generate Excel in C#? OpenXmlSdk or ClosedXml?
I need to generate Excel files in my recent work. The file is not huge and it’s around 5000 lines.
I have tried two open source libraries to generate them.
They are:
- OpenXmlSdk
- ClosedXml
There is very good library EPPlus which is very popular. It has become a commercial product since the version 5, so a license is required for commercial use. I work for a French bank, and I prefer to use an open source library because of the long purchase procedure in the bank and the cost.
So here, I just compare the OpenXmlSdk and ClosedXml.
1. OpenXmlSdk
OpenXmlSdk is a SDK developed by Microsoft SDK to manipulate different types of file like Excel, PowerPoint etc.
You can download its nuget package: DocumentFormat.OpenXml
The Open XML SDK provides tools for working with Office Word, Excel, and PowerPoint documents. It supports scenarios such as:
- High-performance generation of word-processing documents, spreadsheets, and presentations.
- Populating content in Word files from an XML data source.
- Splitting up (shredding) a Word or PowerPoint file into multiple files, and combining multiple Word/PowerPoint files into a single file.
- Extraction of data from Excel documents.
- Searching and replacing content in Word/PowerPoint using regular expressions.
- Updating cached data and embedded spreadsheets for charts in Word/PowerPoint.
- Document modification, such as adding, updating, and removing content and metadata.
You can create an Excel document with the following sample code:
It seems ok to initialize a Excel file with a worksheet with the above code. But it becomes a little complicated when you want to insert data into it.
You need to insert each row and then each cell into the worksheet before write any data.
Here is some sample code to initialize a row and a cell.
Then to write data to the cell, you can use the following code:
But when you write data in this way, you have to create all cells before. It’s quite time consuming and you could create wrong types of cells which could cause Format error.
I would say I’ve met some problems in my try of this library, and the integration process was not smooth. There are not so much documentation and sample code about this library.
I wanted to find another library which is easier to use and understand.
Then, I tried ClosedXml, and the result is satisfying.
2. ClosedXML
ClosedXML is an open source library created based on OpenXmlSdk. It’s more user friendly.
You can download its nuget package: ClosedXMl
ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. It aims to provide an intuitive and user-friendly interface to dealing with the underlying OpenXML API.
It’s very easy to create an Excel.
There are some sample code to insert data in Excel from a string list, array list, object list or DataTable.
You don’t need to initialize each row and cell anymore. ClosedXML has done all that for you behind the scenes. And this could save you a lot of time and energy.
For more examples, you can have a look at its GitHub WIKI page.
Finally, I’ve used ClosedXML after having tried the two libraries.
Conclusion:
- OpenXmlSdk is truly powerful, but need more profound knowledge about the framework to start with.
- ClosedXML is a library based on OpenXmlSdk, which is easier to use with a lot of sample codes.
