Building Excel Files with C# [closed]

Question

I need to create an excel file via C#. I have read a few places that creating an XML document is the easiest way to do this? I need to have multiple named tabs and be able to specify that particular cells are text, date time, numeric, etc... Any suggestions or good examples?

Answer 1

You need the System.IO.Packaging API - this will allow you to generate .xlsx documents as described in Inserting Values into Excel 2007 Cells. The Excel 2007 format can also be used by Excel 2003 and XP with the free Microsoft Office Compatibility Pack installed.

Answer 2

This is frustratingly hard to do, as Microsoft's recommended mechanism involves using Office, which is fine on the desktop but useless for the web.

The binary format of .xls files is propriety, so Microsoft introduced .xlsx OpenXML files in Office 2007.

The .xslx is supposedly simple - it's just a zip container full of XML files. You can open it with System.IO.Packaging and edit it with System.Xml. There's even a compatability pack for older versions of office.

Unfortunately it just isn't simple - the format of .xslx is horrible beyond words.

It looks like they've taken the 16 bit optimised binary .xls format (designed originally for Windows 3.1) and serialised it to XML instead of binary. Then they've added really stupid changes, like the cell comments are actually VML - a format supposedly dropped with IE5! They've also added a ton of magic numbers and meta data to the XML so you can't use any kind of transforms on it, so you're parsing that sucker by hand.

Finally they've made it a complete donkey to debug, and we regularly find .xslx files that the compatability pack reports as corrupt (no reason given) but that recent version of Office can open fine.

There is a really nice open source library out there for it: SpreadsheetLight. It's a very good library, but anything that requires you to dig in and extent the .xslx files yourself is going to be painful.