dagjo

Hi,

we make a web application (ASP.NET) which among other things generate many reports we export to Excel. We must support Excel 2000 and newer versions.

A few years back I read on MSDN about Office Development and noted that MS discouraged the use of the Office applications on the server side, since they were designed for desktop use and consequently expect an interactive user and offer limited scalability.

We chose to generate Excel 2003 XML files and use the spreadsheet component (from Office Web Components, it's an ActiveX), to export to Excel 2000 clients. Alas, the component exports by generating an HTML representation that it then brings into Excel via the clipboard (so a cut and paste, basically). This means we had to use automation (instantiate Excel on the client in script, as an ActiveX, and interact with the instance via scripting) to do things like export multiple-sheet workbooks, define named ranges, and more.

We are now at a point where we plan to implement a lot of new report generation capabilities, and I want to re-investigate our options regarding how to best support different versions of Excel. I am certain many have faced the same challenges as us, and hope to get some advice. First of all, Microsoft may have done something to the office applications themselves to faciliate server-side use, or MS or someone else might offer one or more APIs that offer a similar programming model and the ability to generate the files. We have no quibbles with putting Excel on the server if it now supports such use (and the license allows it!) properly.

What I would ideally like to achieve:

  1. Completely avoid using automation. This would allow clients to work with our application without any special configuration. 
  2. Export should be achieved by generating a response with a mime type indicating that the content is an Excel spreadsheet. AFAIK this is the "web" way to do it, and the only possibility besides automation.
  3. The programming model should be independent of the file format utlimately chosen to save a spreadsheet to, or at least the same for Excel 2000 XLS files and newer formats.

 

Please let me know if Office can now be used server-side in this manner. If it cannot, are there any (MS or third party) components on the market that are easy to use in an ASP.NET context and that facilitate server-side creation of Excel spreadsheets

 

Thank you in advance for your valued feedback! Happy coding,

 Dag Johansen



Re: Visual Studio Tools for Office Best practices for server-side generation of spreadsheets

Cindy Meister

Hi Dag

The situation for automating Office applications server-side hasn't changed. MS has chosen a different route: Open Office XML file format for Office 2007 applications, which allows you to construct the files as XML. The big difference for you is that the Office team has provided a converter that will allow earlier versions of Office to also open/read these files (in Word it can also write to the new file format). The converter is available to Office 2002/2003 as part of the automatic updates. It can also be downloaded and installed for all these versions, plus 2000. Note that performance/results may be poorer in this latter scenario.






Re: Visual Studio Tools for Office Best practices for server-side generation of spreadsheets

karfast

I think you could also programatically generate the spreadsheets using SSRS as a Web Service and a Visual Studio client. I am planning to try this shortly. Please let me know if you find more on this.

Thanx

Kar





Re: Visual Studio Tools for Office Best practices for server-side generation of spreadsheets

karfast

My needs are similar to yours. i.e.

1. I want to deliver reports as excel files and

2. I dont want any changes on the client Excel app (no plug-ins, automation)

I have tried out two options, both using SQL 2005:

A. I used SSRS and a Data Driven subscription to create multiple Excel files. This worked ok when all I had to send out was Data (not charts etc). Only porb was performance and bloated file sizes.

B. I used SSIS, and a sample excel with some formulae in it. I just looped through a list of subscriptions, and for each subscription, I copied the sampleexcel as the target file, and made the target file my Excel Destination, and wrote the report to that excel. This is much faster, though it needs more development work. I havent tried out stuff like charting, which, I suspect, will need Automation.

I am now thinking of building a dotnet DLL that SSIS can call, to do stuff inside the Excel, like generate charts etc.





Re: Visual Studio Tools for Office Best practices for server-side generation of spreadsheets

dagjo

Hi,

Thank you for your input. I'm afraid I don't know what SSRS is. And I have to say I find it a little bit incredible, given the ubiquity of Office, that no Microsoft or third-party component exists for this. Perhaps it is a good idea for a startup; now the file formats are open and documented and all there should be plenty of possibility for doing this. It would require basically a DOM for each document type (Word, Excel, Powerpoint,...) and logic to "render" and parse the XML...

I haven't looked into this, and I suspect the license constraints prevent us from using it, but one idea is to take a trip to SourceForge and take a look at the OpenOffice source code. Internally, the application should use something like a DOM anyway, and saving and loading files obviously corresponds to parsing and rendering. With luck, there will already be a clean separation between code that interacts with the user and "pure model code" - if so, it is just what we need.

I don't know what license it is under though, and in my scenario it is certainly not an option to disclose our source code, give away the application for free, or allow anyone to modify it....





Re: Visual Studio Tools for Office Best practices for server-side generation of spreadsheets

Misha Shneerson - MSFT

I believe Cindy's answer explains the current situation with server-side generation of Open Office XML file formats. I will mark this reply as an answer and close this thread.