Hello. I’ve been searching for various posts on Excel and ASP.Net, and wanted to try and figure out if what I’m attempting has any chance of working.
I have a special Excel workbook with a worksheet that has a bunch of calculations and such for various fields that creates an order for his crew. My client uses this Excel worksheet for everything.
He’s asking me to be able to see online orders (which I have), click a link and have the worksheet be passed down to the client all populated and formatted exactly as is (just adding/editing data).
Is this even possible? I’d have to load this specific .xls file, then open it on the server, save data to various cells, and then do a Save As and then binarystream it down to the client.
I’m quite certain there is no version of Excel loaded on the server. So I’m perplexed as to how to accomplish this.
Has anyone encountered this situation before and been able to accomplish what’s being asked?
Excel should never be installed much less run on a server. You’ll need some very expensive libraries if you need to be Excel 2003 (.xls) compatible or to do a bit of leg work if you can use excel 2007+ (.xslx).
If the excel is truly the complex side of the equation, I would build something in excel to load the data from a web service. Actually alot easier than generating complex excel on the server and lets you play to excel’s stronger suits.
You can write SpreadsheetXML files. To make a template and see how it works, load up a blank sheet in Excel, populate some fields, add some formatting, formulas, etc and save as SpreadsheetXML (it may be called XML Spreadsheet 2003 in Excel2007), load it up in a text editor, VS works well, and figure out how it works. Then take one of your existing spreadsheets and save it in this format and decipher how it saves your data. Strip the data out of it and then you can use that XML file as a template - read in the template and manupulate the XML with the new data and output it to the browser.
You can write your own quite-capable spreadsheets with formatting, formulas, frozen panes, and proper types (i.e numbers as numbers, text as text, etc). No charts, and other features, however.
I’ve used this format for both importing existing sheets (in well-defined formats with strict data-checking!) and for exporting “reports”.
