Skip to main content

Latest Posts:

Comparing Excel generation techniques on ASP .net:
Entry Date: Nov 2007 keywords: ASP .net; GridView; SSAS; SSRS; Performance; XML; Open XML; .net framework 3.0; Web Services; Office; XSL; DataSet; HTML; Excel; Sharepoint; CSV; DHTML; Excel Services;
Short Description:

There are a lot of different approaches available for Excel Generation on ASP .net, and none of them seems to be a silver bullet… (some of them are limited, some others have an associated license cost, or pay a penaly on performance, ...)

This post tries to shed some light in this area and easy the process of selecting which approach to follow in your web application.

Entry:

Almost in every business web application we get as a requirement to have the ability of export / dump our data into Excel: gridviews, report generation…

The thing is… there are a lot of different approaches available, and none of them seems to be a silver bullet… in some cases you have to spend some money, in some other you pay a penalty on performance, or they are complex to develop and you don’t have such timeframe in your project…

This post tries to shed some light in this area and easy the process of selecting which approach to follow in your web application. We are going to compare/evaluate:

  • --> CSV generation.
  • --> XMLSS generation.
  • --> GridView HTML dump into Excel.
  • --> OWC
  • --> Reporting Services
  • --> Reporting Services + Office Writer
  • --> Sharepoint Excel Services.
  • --> (*)

(*) Note down that automation/VBA model is not included, that’s because is not recommended and not supported by Ms for Web Applications (read KB257757 for more information.).

CSV Generation

The oldest way to generate a file compatible with excel, it consists on having rows delimited by \r\n and columns separated by commas or semi-colon, e.g.: 

123,John, Werk\r\n
124,Peter, Tol\r\n
(…)

If you just need the data dumped into excel without any fancy formatting or cooking you can just use this format, quite easy to implement (using datareader + stream writer or xsl approach).

Factor Score Comments
No additional component install on server side You just create files or streams that are standard text files.
No additional component install on client side Any client can understand a csv file, even non Windows platform.
Easy / Fast to develop Just use a datareader and write each row, or use an XSL if you want to go for a more elegant way.
Performance / Speed of generation If you are using a datareader and a stream writer, it runs like a rocket.
License Cost Free
Issues with Office 2007 No
Office Backward Compatibilty Any version of Office supports this format
Keeps cell layout order Yes, no problems with column merging problems or others.
Support Formatting No, plain vanilla text
Support Formulaes No
Support Charts No
Support Macros No
Can be generated from OLAP Datasource Yes, you are the one responsible of feeding the CSV, and execute the datareader.
Can be combined with XSL technologies Yes, but if you have to generate huge CSV file you will suffer performance issues.

XMLSS Generation

XMLSS stands form “XML SpreadSheet” Format. It’s a quite popular format used in Web Apps, introduced in Office 2002, you can save a limited subset of the excel functionallity (things like formatting and formulaes are included, but not for instance macros or charts), and quite easy to understand.

Factor Score Comments
No additional component install on server side You just have an XML (could it be a dataset) and an XSL template, with .net framework you can apply all the transformation, so no headache for the IT guys :-).
No additional component install on client side You just flush to the client a 100 % valid Excel file (XMLSS format). No need at all to install anny software different than Excel.
Easy / Fast to develop There are available plenty of examples, if you just take an existing excel template and save it as XML format, it’s easy to transform it to an XSL template (well… for normal complexity excel files).
Performance / Speed of generation I have used this approach in different scenarios having good performance results (the excel is generated locally on the server, no need for web service call to report servers).
License Cost Free, you are just playing with XML/XSL
Issues with Office 2007 If you save the file with XLS extension instead of XML, Office 2007 will warn you and tell you that is not a valid Excel file, this issue is know by Ms, and it’s supossed that probably would be fix in a later service pack.
Office Backward Compatibilty It only runs on Office 2002 or higher (who in the hell is still running 2000 or 97 ?).
Keeps cell layout order You have a fine control about cell layout.
Support Formatting You can use font formatting, colors, …
Support Formulaes You can setup formulaes, if you can place your formula in a place that can be shifted just use relative positioning.
Support Charts No, no charts, no grouping, beware of promising things like that to your client, there is no workaround for this using XMLSS.
Support Macros No.
Can be generated from OLAP Datasource You are the one that performs the query and the transformation to XML (easy way ? DataSet to XML), as far as you do all the cooking you can just query MDX and port it to XML.
Can be combined with XSL technologies Of course, that’s the strong point of this approach.

DataGrid / GridView HTML dump into Excel

Excel is able to read some kind of HTML, taking into consideration that the GridView and the DataGrid both generates HTML, with some hacking you can just output that HTML and change the content file to Excel. Quick and Dirty solution, use it if you ran out of time (last minute wish from your client), you only have to export gridview results, and your client want ask for refining layout / formatting or adding formulas.

Factor Score Comments
No additional component install on server side It’s just a hack, all that you have in .net framework is enough.
No additional component install on client side You are sending an stream telling that the content type is Excel, no need to install anything on the client.
Easy / Fast to develop That’s the strong point of this solutions, if you use an standard gridview / datagrid it’s quite fast to implement.
Performance / Speed of generation No issues found here so far.
License Cost Free hack :-).
Issues with Office 2007 Office 2007 doesn’t like that your generated doc has the XLS extension, and will tell you that is not a valid file (anyway if you click on continue it will open).
Office Backward Compatibilty No issues found here so far (maybe on 97/98 this solution doesn’t work).
Keeps cell layout order You don’t have fine granulated control over this, if your client wants an special layout you will have problems, if it’s just a tabular layour exactly the same as the GridView it wion’t do any strange manipulation / merge of the cells.
Support Formatting Limited, changing formatting can become a nightmare.
Support Formulaes Not 100 % sure if there is a way to insert a formula here, but you will have to fight with the rendering on Excel, if you need formulaes just go for another solution.
Support Charts No.
Support Macros No.
Can be generated from OLAP Datasource If you are able to load the GridView from an MDX query, you can apply the hack in order to get the GridView exported to Excel.
Can be combined with XSL technologies No, it’s just a quick and dirty hack, just works, no fireworks… :-)


OWC

Office web components has been shipped with Office 2002 and 2003, to cover the need of “having excel” on the web, basically it’s divided into two main products:

  • Server side chart generation engine: Quite good and powerful, it just generates the chart on the server side, converts it to an image (jpg, …) and sends it as a picture to the client, so the chart can be consumed from any internet browser, the look and feel of the chart is exactly the same as the ones generated with Office 2002 / 2003. You can use this approach in both intranet and extranet environments.
  • A web ActiveX Excel Sheet / Pivot table component: With this component you can have a simplified excel sheet or pivot table embedded on a web page, BUT this component is not quite popular, it’s suitable for using it on an intranet/extranet environment, even on an intranet environment you have to install an ActiveX component on the client side and if you need to read from a given datasource allow cross domain scripting, a bit dangerous if it’s not well configured ?.
    On the other hand it’s a simple way to browse OLAP cubes using pivot tables.

Please note down that although OWC will be supported by MS until 2011, no new versions will come up, this product is going to be discontinued.

Factor Score Comments
No additional component install on server side You don’t need to install Office, but some libraries yes.
No additional component install on client side The charting side doesn’t need anything to be installed on the client side (it just send an image with the chart).

The web excel sheet part needs to install an ActiveX control, and depending on what you need to make some extra component and IE configuration setting. If you are using pivot tables need to exploit and OLAP cube maybe you will have to talk with the IT guys to let you access (if it’s fire walled probably the won’t grant direct access from your client computer to the server).
Easy / Fast to develop For the charting side you will need to perform some coding (it’s a bit tedious).

For the web excel, you will need to perform some configuration (not straight forward and dangerous, a bad configuration could lead to a security hole).
Performance / Speed of generation No issues found so far on the charting solution.

On the Pivot table solution if you are accesing to OLAP cubes, it depends where the OLAP server is located.
License Cost No license cost (well you would need to have an Office License).
Issues with Office 2007 The charting side can generate exactly the same charts as Office 2002 and 2003, but not the new ones or styles that you can generate with Office 2007.
Office Backward Compatibilty N/A
Keeps cell layout order N/A
Support Formatting Charts support all the Excel 2002 / 2003 formatting, only note down that they are generated as static images.
Support Formulaes The excel ActiveX client supports formulas.
Support Charts As images (on the charting side).
Support Macros No.
Can be generated from OLAP Datasource Pivot tables support OLAP cubes as datasource.
Can be combined with XSL technologies For the charting technology you could implement an XML model, and a consumer engine (lot of work involved), but is possible.
Reporting Services (SSRS)

SQL Server ships a reporting server, you can use Visual Studio or Report builder to create reports and then integrate that reports via web service in your web application, in different formats (HTML, PDF, Excel, …).

Factor Score Comments
No additional component install on server side Depends if the reporting server is on a separate instance server, if you have to install all in the same machine, then require additional installation in your server environment.

If you are going to use a dedicated SSRS server, you need to ensure that you web application server can reach the reporting server.
No additional component install on client side No need, Reporting Services default navigator is pure HTML, if you access via web services you will get an stream with the excel file.
Easy / Fast to develop The report design experience is similar to creating reports using Crystal reports, and implementing the calls to retrieve a report as excel stream via web service is easy and it’s well documented.
Performance / Speed of generation If you have to generate huge excel files, it’s slow, rather use Office Writer extension for SSRS
License Cost If you already have an SQL Server license you have Reporting Services, if not you have to buy that product (or use the free version, SQL Server Express).
Issues with Office 2007 It generates Excel in Binary format, no way (at least until 2005 version) to generate in Open XML 2007 format (but the export are read without any problem by Excel 2007).
Office Backward Compatibilty Fully compatible (generates binary format).
Keeps cell layout order Not always, for tabular reports normally yes, for matrix reports if you try to use headers in the rows region it can become a nightmare (cells that merge, …), if your client want to perform further analysis using your exported excel file first do a proof of concept version.
Support Formatting Fonts, colors, separate in different sheets (maybe some advanced excel formatting not supported).
Support Formulaes Some calculated fields are exported as formulas.
Support Charts Charts are exported as static images (no excel diagram object).
Support Macros No.
Can be generated from OLAP Datasource Yes, that’s one of the strong points of this solution, fully OLAP support.
Can be combined with XSL technologies N/A


Reporting Services + Office Writer

Softartisans offers a custom extensions for reporting services that allows generating reports directly from Excel with a fine grade of granularity, in binary format, and performing like a rocket. Limitations: currently doesn’t support reporting based on Cubes/OLAP.

Factor Score Comments
No additional component install on server side You need to install the rendering extension on the reporting server, and on the development machines the designer for excel.
No additional component install on client side The excel is send as an stream (binary excel file), no need to install anything on the client side.
Easy / Fast to develop It’s easy to create reports using the Excel plugin.
Performance / Speed of generation Very good performance for generating huge files.
License Cost Microsoft has bought this product but it won’t include it on the SQL Server release until (maybe) june 2008.

Check out prices in SoftArtisans page, there is an standard version and an Enterprise version.
Issues with Office 2007 It generates Excel in Binary format, no way to generate in Open XML 2007 format (but the export are read without any problem by Excel 2007).
Office Backward Compatibilty Fully compatible (generates binary format).
Keeps cell layout order Yes, you generate the reports directly on Excel.
Support Formatting Full excel formatting.
Support Formulaes Yes.
Support Charts Yes, native Excel chart objects.
Support Macros Yes.
Can be generated from OLAP Datasource No, that’s a limitation, Ms is working on this.
Can be combined with XSL technologies N/A
Sharepoint Excel Services

This platform lets you view / edit an Excel File on a pure web environment (HTML). It even let's you use an Excel worksheet as a calculation engine and expose it via web services.

Factor Score Comments
Requires additional component install on server side Yes, sharepoint installation…
No additional component install on client side Pure HTML client.
Easy / Fast to develop Yes if you are good at excel :-).
Performance / Speed of generation Yes.
License Cost Check prices for licensing.
Issues with Office 2007 Supports a wide subset of Open XML (e.g.: it doesn’t support macros).
Office Backward Compatibilty If the user want to download the Excel work book it works with Open XML (users need to have installed Office 2007 or need to install backward compatibility pack).
Keeps cell layout order Yes.
Support Formatting Yes.
Support Formulaes Yes.
Support Charts Yes.
Support Macros No.
Can be generated from OLAP Datasource Yes.
Can be combined with XSL technologies N/A
Open XML

Open XML is the standard format for Office 2007, basically it’s a zip file that contains XML and bin parts. Very powerful, but quite complex to manage (it’s supossed that Ms will ship in some near future a VBA/Automation model/library to generate this kind of files), currently Ms offers an SDK but it’s a bit limited.

Factor Score Comments
Requires additional component install on server side It’s easier to develop using .net fx 3.0 and the Ms SDK, but you will need to deploy some dll's in your web app.
No additional component install on client side You need Office 2007 or the compatibility pack for previous versions.
Easy / Fast to develop No, quite complex, until we get some libraries that wrap all this complexity.
Performance / Speed of generation Depends on the implementation that you make, but it can be quite fast.
License Cost No additional license cost, open format.
Issues with Office 2007 No, it has been developed for Office 2007.
Office Backward Compatibilty User will have to install Office compatibility pack
Keeps cell layout order Yes.
Support Formatting Yes.
Support Formulaes Yes.
Support Charts Yes.
Support Macros Yes.
Can be generated from OLAP Datasource Yes.
Can be combined with XSL technologies Yes but it’s not easy.

Have you decided which technology to use? To start coding…you can find a selection of articles about these technologies in: Excel Generation Article Selection



Page (1) Of 1