Excel macro run from the web?

Question

I have been handed a critical macro that takes an old school file full of invoices which thankfully is quite consistent. The macro reads this file, moves the data around to make it consistant and then generates a three tab speadsheet which is pretty much three CSV's. It then generates off these three CSV's another speadsheet which has a tab for each invoice. The amount of invoices can really vary.

It works, everyone is happy. We would like to put this out on the web with some security. For now, have it so that the user:

1) Logs in, uploads the old school file and presses process which will then spit out the same speadsheet with each tab being an invoice.

2) Store the data in a database for future growth and use of this data, as well as reporting.

I'm teaching myself ASP.NET and C# and think this would be a great learning project. Before I jump into it, can this realistically be done and what would others recommend in this case? Should I simply re-write based off the logic in the macro or is there a way to port over existing VBA code?

Answer 1

Using the Excel COM API from a web application is difficult. There are security issues which are non-trivial to address. If you wanted to retain the excel processing then you could build some sort of an out of band process which monitors an upload directory and, when it detects a new file, kicks off a process of transforming the excel file as the old macro use to.

There is no easy transition from VBA to C# since all the VBA code assume the existence of excel which may not be the case. However you can call macros in workbooks using the COM API.

Answer 2

You can do it with an Excel COM API. But this tends to lead to memory leaks, I would not recomend it.

Microsoft has Excel Services which allow you to run Excel Spreadsheets on the server. But it is very expensive and may not support Macros.

SpreadSheetGear may be able to do it. But I have not tested it myself.

I would recommend that you rewrite the application in C#, you would get a better solution, and it may not take you any longer than getting the spreadsheet running on the server.

Answer 3

Driving Excel from C# is surprisingly hard to get 100% right. Conversely, driving Excel from a VB6 application is surprisingly easy. But, calling this from a web application makes it harder, since you need to deal both with security and concurrency (2 users at once will trip over each other).

Microsoft don't support the use of Excel on the server (apart from Excel Services), so don't expect any help there. SpreadsheetGear is suited to this, but you'd have to pay for it.

You say this would make a good learning project - I'd disagree; it's likely to put you off programming altogether. This particular mix doesn't have a "nice" solution - it's a case of finding the least-unpleasant hack. If you want to learn ASP.NET & C#, I'd say find another pet project.