What are the differences between the EPPlus and ClosedXML libraries for working with OpenXML? [closed]

Question

I'm trying to choose between ClosedXML, EPPlus, and possibly SpreadsheetLight. What reasons might I consider for picking one over the others?

Flagstaff

I know this is an old question, but are there any reasons in particular why you weren't happy with ClosedXML? Is it more of a political thing where it needs to have a major version number, or did you feel that ClosedXML wasn't stable enough? – Doi

My problem with ClosedXML is it just doesn't work in many cases. Referencing cells with array formulas, table formulas, SUMIF are some of the things that completely break down the library. – Obligation

Answer 1

I've been evaluating EPPlus, ClosedXML, and SpreadsheetLight over the past week. All three are really great libraries that make working with the extremely convoluted OpenXML a breeze. I've decided to go with EPPlus for the following reasons:

• Initially, I was going to go with ClosedXML. It has great documentation and the object structure made a lot of sense to me. I have some charting requirements though and as of right now, ClosedXML doesn't have any charting functions.

• Then I stumbled upon SpreadsheetLight which also has good docs and does support some charting. I also like the author's attitude and his commitment to his product. However, the project is built on top of the OpenXML 2.0 SDK, not 2.5, which is a deal breaker for me because I want to have the flexibility to work directly with OpenXML in the event that the library doesn't quite meet my needs. My project is targeting excel 2013 so I want to have the latest SDK to work with. ...Update! See ErrCode's comment below for additional info...

• Finally, I came across EPPlus which has okay documentation and supports charting. It is has been downloaded far more times than the other two which gives me some security in knowing that others are using it and the community seems to be active around the project. They are also currently on beta 2 of version 4 which sounds promising from what others are saying. I'm also getting the gist that EPPlus has good performance (not that the others are necessarily bad) and the upcoming version 4 seems to be improving on that further.

---

Update

Another very useful feature I've found included with EPPlus is how it exposes the XML for the various parts of the worksheet. If there's a feature that is not supported by EPPlus, you can still edit the XML (in some cases) to get the desired output. For example, if you have a pivot chart and want to enable multi-level labels, you can do the following:

    private void EnableMultiLevelLabels(ExcelChart chart)
    {   
        var element = chart.ChartXml.GetElementsByTagName("c:catAx")[0];
        if (element == null)
            return;

        var multiLevelLabelNode = element.AppendChildElementNode("c:noMultiLvlLbl");
        if (multiLevelLabelNode != null)
            multiLevelLabelNode.AppendNodeAttribute("val", "0");
    }

Remind

I agree. Wonderful answer! I wanted to use ClosedXML due to the object model, but this answer totally swayed me. I am wondering if EPPlus could adopt ClosedXML's object model somehow? What are your thoughts? – Sycosis

Thanks @JohnZabroski, glad I could help! I agree with you on the object structure; I think they could take things a little further with abstracting away some of the more complicated parts of OpenXML. On the other hand, I think they're also trying to find the right balance of flexibility between working with their library and OpenXML side-by-side which also makes sense to me. I'm hoping over time, as they take on more features, we'll start to see some of these improvements. – Remind

One other "advantage" of EPPLUS, in my opinion, is that it's nuget package has no dependencies, not even DocumentFormat.OpenXml (unlike ClosedXML). – Antineutrino

Small update: seems like since version 3.4.5, Spreadsheetlight is based on OpenXML 2.5 You will need to download and install the Open XML SDK 2.0 2.5 (version 3.4.5 onwards work with SDK 2.5) from Microsoft (it’s freely available) – Sindysine

Yet another example of question deemed a "shopping question" yet has a highly useful set of answers. – Bawcock