Is there a better way I can fill out a spread sheet on a web server (using asp.net) than using interop?
EDIT: I wasn't very clear as to what I require:
I have a template that I must use that is provided by our customer. In the template are some macros that are password protected that I do not have access to so I cannot generate the excel file. I am stuck updating a 2003 excel file.
Definitely, could you have spreadsheet as a simple csv and update it so. This would be more performant in many regards. Interop with office can be heavy and if there are many simultaneous instances there could be real trouble.
Office 2007 documents (Word, PowerPoint and Excel) are based on the OpenXML Formats. They are just zip files with a bunch of XML and binary parts (think files) inside. You can open them with the Packaging API (System.IO.Packaging in WindowsBase.dll) and manipulate them with any of the XML classes in the Framework.
Check out OpenXMLDeveloper.org for details.
YES.
If you have an existing spread sheet, and you want to add data to it based on user input where either one sheet is shared by every user or even one user periodically updates his own sheet, then you are going about it the wrong way. Excel can't handle concurrency or the interop load in a web situation. The only place you need interop is when you have to open existing spreadsheets that are out of your control.
Instead, save the user input to a real database and only generate an excel document as needed. See the question linked below for help generating the excel document: there are several options that require no interop at all, including html tables, xml, openxml csv, or 3rd party component, and most of those support formatting, formulas, etc.
http://stackoverflow.com/questions/150339/generating-an-excel-file-in-aspnet
Update
If you need to execute macros, then you likely need excel interop. However, this just won't work well in web site. I suggest you go back to the customer and ask them to be more open with you about the requirements for the sheet.
Check this project:
ExcelPackage: Office Open XML Format file creation.
It's great for creating Excel 2007 format spreadsheets.
Perhaps you could plit the operation into two parts.
Store the results in a database for later workings.
Run a process that updates the spreadsheets.
This won't work if the web site has to return the results of the spreadsheet though.
You can use the Excel XML specification.
I've got a demo on my blog of how to do it in C# 3.0 and also link to an example on VB 9. It uses LINQ to XML.
The concept can be modified to .NET 2.0 easily.
http://www.aaron-powell.com/blog/september-2008/linq-to-xml-to-excel.aspx
Check out SpreadsheetGear. This component will allow you to manipulate Excel 2007 spreadsheets, execute macros, and update spreadsheets from ADO.Net. It does not require Excel to be installed on the server in the interop.
There site has good example code for you to peruse. We deployed an app internally with it and it runs great.
This is the solution I ended up going with. I saved my template as an xls file, each time I need to create the sheet I copy it to a directory and give it a name. Then I am using OleDb and UPDATE statements to fill in the cells that I need to add my data to.