As part of almost every application I work on, I need to create Excel sheets one way or the other, usually from an ASP.net MVC application. During the years, I’ve tried several approaches, and they all sucked one way or the other:
- COM Interop: By far the worst option. Requires an installed Excel. Slow. Error prone. Resource-intensive. Not supported in a server environment.
- CSV or HTML Tables: Only supports a single worksheet, not much formatting, prone to break Excel’s heuristics (e.g., the string “00123” is interpreted as a number, stripping the leading zeroes. For big numbers, Excel loves to use scientific notation, which sucks for Barcodes which should just be interpreted as strings) and hard to create (CSV is a pain if you need to escape quotes or have newlines)
- Excel 2003 XML/SpreadsheetML: A crutch. Uncertain future, limited options, big files. But actually, not too bad.
- One of the many Excel 2007+ .xlsx libraries: I tried about 4 or 5, and they all sucked in a different way. No offense, but some library authors try to cover the entirety of Excel’s capabilities, leading to an awkward API. Many don’t catch specific Excel limitations that aren’t part of the standard (e.g., Sheet name length or invalid characters), which means I’m creating sheets that cause Excel to tell me there was invalid data, leaving me puzzled how to fix that.
- Going Low-Level with the OpenXML SDK 2.0. Believe me, you don’t want to go down that road. There is very little help creating the documents, and if you want certain features that seem obvious (e.g., setting the Author of a document, which requires adding the creator element to core.xml) you will find that there is actually no way to do it.
So, all solutions I tried sucked. Which means that I set out to create another solution that sucks slightly less. Armed with some spare time, the ECMA-376 standard and Excel 2007 and 2010 to actually test against, I created a library that has a limited set of features, but implements them well, handles errors properly and (hopefully) has a good API for you guys to work against.
- You can store numbers as numbers, so no more unwanted conversion to scientific notation on large numbers!
- You can store text that looks like a number as text, so no more truncation of leading zeroes because Excel thinks it’s a number
- You can have multiple Worksheets
- You have basic formatting: Font Name/Size, Bold/Underline/Italic, Color, Border around a cell
- You can specify the size of cells
- Workbooks can be saved compressed or uncompressed (CPU Usage vs. Network Traffic)
- You can specify repeating rows and columns (from the top and left respectively), useful when printing.
- Fully supported in ASP.net and Windows Services (The documentation contains an example ActionResult for ASP.net MVC)
You can get the Simplexcel Nuget Package for .net 4.0 Client Profile or higher – https://nuget.org/packages/simplexcel
Documentation can be found at http://mstum.github.com/Simplexcel/