ASP.NET manuplating Excel sheet

Question

I need to manuplate an excel sheet workbook (add sheets/ add data/ hide columns / protect cells with password / change fields contents/ etc etc)

should I use the COM objects provided by microsoft (but then i think they have few problems being used in asp.NET)

or is there a good (commurcial or free) library / plug in to be used for doing this kind of activity.

please help

Answer 1

The easiest way I've found is to use the Excel XML format. That way you can edit an Excel sheet like any other XML file. To see how Excel hides columns or protects them with a password, do those things manually and see how Excel saves them as XML. This is a breeze to code up, it's rock stable, and it's fast.

Manipulating native Excel files is harder: you can use the COM objects with the primary interop assemblies. In my experience this is very hard to get right. In the best case, Excel leaves old copies of itself around, which you can mitigate with a scheduled nightly server reset. In the worst case, Excel will randomly hang, making your web site unresponsive.

Another way to work with native Excel files is Visual Studio Tools for Office. It's certainly easier to use than COM interop, and works best from Visual Basic:

Application.Workbooks.Open("Workbook.xls")

C# lacks optional parameters, and opening a workbook becomes something of a bad joke:

Application.Workbooks.Open(@"Workbook.xls",
    missing, missing, missing, missing, missing, missing, missing,
    missing, missing, missing, missing, missing,missing, missing);

I didn't dive very far into VSTO because XML manipulation worked so well for me. Maybe other people can comment on how it works out in an ASP.NET website.

Answer 2

try spread Sheet Gear third party component

http://www.spreadsheetgear.com/

Answer 3

With SpreadsheetGear you have complete access to the Excel doc and all of the macro capability including the ability to protect and un-protect cells, columns, etc. We have the same thing you are describing except in WinForms.

Answer 4

We have used the COM objects and I cannot recommend it. On big problem is that the objects never get released...

We now use the component from Gembox software. Have used it for two years and works OK, it's a little cheaper that the one metioned above, which I didn't know before seeing it here.