RSS 2.0
Sign In
# Friday, 11 December 2009

A client asked us to produce Excel reports in ASP.NET application. They've given an Excel templates, and also defined what they want to show.

What are our options?

  • Work with Office COM API;
  • Use Office Open XML SDK (which is a set of pure .NET API);
  • Try to apply xslt somehow;
  • Macro, other?

For us, biased to xslt, it's hard to make a fair choice. To judge, we've tried formalize client's request and to look into future support.

So, we have defined sql stored procedures to provide the data. This way data can be represented either as ADO.NET DataSet, a set of classes, as xml, or in other reasonable format. We do not predict any considerable problem with data representation if client will decide to modify reports in future.

It's not so easy when we think about Excel generation.

Due to ignorance we've thought that Excel is much like xslt in some regard, and that it's possible to provide a tabular data in some form and create Excel template, which will consume the data to form a final output. To some extent it's possible, indeed, but you should start creating macro or vb scripts to achieve acceptable results.

When we've mentioned macroses to the client, they immediately stated that such a solution won't work due to security reasons.

Comparing COM API and Open XML SDK we can see that both provide almost the same level of service for us, except that the later is much more lighter and supports only Open XML format, and the earlier is a heavy API exposing MS Office and supports earlier versions also.

Both solutions have a considerable drawback: it's not easy to create Excel report in C#, and it will be a pain to support such solution if client will ask, say in half a year, to modify something in Excel template or to create one more report.

Thus we've approached to xslt. There we've found two more directions:

  • generate data for Office Open XML;
  • generate xml in format of MS Office 2003.

It's turned out that it's rather untrivial task to generate data for Open XML, and it's not due to the format, which is not xml at all but a zipped folder containing xmls. The problem is in the complex schemas and in many complex relations between files constituting Open XML document. In contrast, MS Office 2003 format allows us to create a single xml file for the spreadsheet.

Selecting between standard and up to date format, and older proprietary one, the later looks more attractive for the development and support.

At present we're at position to use xslt and to generate files in MS Office 2003 format. Are there better options?

Friday, 11 December 2009 09:28:32 UTC  #    Comments [4] -
Tips and tricks | xslt
Saturday, 12 December 2009 00:04:16 UTC
I've done a good bit of work in this area and the easiest and cleanest solution i have found is to generate basic (and well formed) html tables on the web server from the results of your database queries (i usually go to xml direct from db and then use generic xslt stylesheets to transform to html/rss/atom/csv etc.). You can then use the web query capability in excel (http://articles.techrepublic.com.com/5100-10878_11-6115870.html?part=rss&tag=feed&subj=tr) to embed these html tables in an excel spreadsheet which can have all the styling/formulas etc that you want in it. all the user has to do is push a little button in excel to grab the latest data from the website. it's all going over plain old http or https so security shoudln't be an issue.

the actual spreadsheets are then just really templates that you can store on the server as files which people can download to their desktops whenever they need the latest version/layout.

if you want to discuss further, feel free to get in touch. enjoy your website btw.
Saturday, 12 December 2009 07:07:55 UTC
Hello Andrew and thank you for the comment.

You're right, creating html is one of options, and probably even more attractive than creating MS Office 2003 xml. More than that, we've found a post Convert your HTML Tables to Excel
, which states that it's enough to define ContentType of a response as "application/vnd.ms-excel" for Excel to pick html result up. This brings us into the well known world of the html.

Thank you for the hint!
Vladimir Nesterovsky
Saturday, 12 December 2009 18:47:49 UTC
Great. If you just want the raw data in an excel sheet, that sound like the best solution. Simple solutions are always nice!
Saturday, 12 December 2009 19:17:52 UTC
That won't be raw data but several tables with styling and captions. All this Excel consumes with no glitch.
Vladimir Nesterovsky
All comments require the approval of the site owner before being displayed.
Name
E-mail
Home page

Comment (Some html is allowed: a@href@title, b, blockquote@cite, em, i, strike, strong, sub, super, u) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

[Captcha]Enter the code shown (prevents robots):

Live Comment Preview
Archive
<2009 December>
SunMonTueWedThuFriSat
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789
Statistics
Total Posts: 364
This Year: 9
This Month: 0
This Week: 0
Comments: 223
Locations of visitors to this page
Disclaimer
The opinions expressed herein are our own personal opinions and do not represent our employer's view in anyway.

© 2018, Nesterovsky bros
All Content © 2018, Nesterovsky bros
DasBlog theme 'Business' created by Christoph De Baene (delarou)