If you have to create Excel spreadsheets for your users, don’t use Excel on the server. You will have problems. Instead, use one of the alternatives below:
I have gotten a lot of mileage out of this free library. This generates Excel 2003 compatible XML Workbooks. You have complete programmatic control over every attribute of every cell—you can set appearance, formatting, formulas, etc. If you have a workbook template to start from, Carlos has a code generator tool that will reverse engineer your template into the classes you need. I have made some very complicated multi page workbooks with this library. It can take some effort if your workbook is very complicated, but the end results have been great. Did I mention it’s free?
SpreadsheetML
All of Office 2007 files are natively a new form of XML, which conforms to the OpenXML standard. Excel’s implementation is called SpreadsheetML. There is a new set of libraries to create Office 2007 documents called the Open XML SDK. Download the free SDK and have a look at Brian Jones’ example for creating an Excel workbook with a chart. Double bonus here—OpenOffice is also natively compatible with OpenXML.
Aspose makes commercial libraries for Office automation and so much more. I have recently acquired their Total package for .NET, so I don’t have a lot of experience with it yet, but it is awesome. If the free tools don’t get you where you need to go, look here. Aspose is a supporter of the .NET community, which carries a lot of weight when I make purchasing decisions (in fact, I won this package at DevTeach via the Montreal .NET UG).
Another commercial component. I don’t have any experience with this library, but they advertise like crazy and support the .NET community, so they are worth checking out.
Great post. keep it up.