ASP.NET WebService to return an excel spreadsheet

Having a bit of trouble with logic and hoping someone has a creative idea... or maybe by posting this I'll think of something clever.

Background: We run some web apps that basically just report information from the database. We have a (ghettofied) feature to export the data to excel after the query has run. It currently just sets the header as an excel document but puts it into an HTML table - it works but breaks a lot of excel functionality. This is currently in classic ASP.

Within two months we plan on *finally* updating to ASP.NET. My plan is to write a web service that can work currently and with the future version with as little change as possible.

So, when you're creating an actual XML excel file you have three parts - workbook boilerplate (IE, statements saying this is an excel document, last author, etc.), the styles (number formatting, borders, font, colors, etc.) and then the actual worksheet data.

The first is simple enough, but how in the F should I determine the styles?

• I could have them as a seperate XML file, and each report page has a corresponding XML file to read styles from - that's somewhat plastic, though.
• I could make the app declare the styles when they're calling the web service, but for 15+ pages it's a lot of repetitive dumb code for something that should be easy to implement.

So then I run into the problem of how do I determine which columns get which styles? If it's in a separate XML file I can just name the style the column name - easy enough. The file will become bloated with extra stuff though.

And then there comes the issue of calculated columns. If they're just going to be at the bottom of the spreadsheet that's fine, but intermixed I'm not really sure.

In a little over my head with the lack of planning behind this one.

I'm not a .NET developer, so my suggestions will be a bit broad, but...

If I'm understanding this right, and style data doesn't have to be different for each spreadsheet - Just keep the style declarations in a separate file and include/import it into the XML file before the data is exported to excel.

It doesn't seem like an extraneous amount of space to add a name/class to the columns for styling, provided it's just the columns and not each individual cell. That sounds like negligible load-time difference.

As for your problem with calculated columns, it's a little to specific for someone with no .NET knowledge, but good luck figuring it out.