Strategy in exporting to Excel with formatting from ASP.NET?

Question

So this is another exporting to Excel question.

I have a page that has a table with formatting by stylesheet. When I export the page by setting the ContentType to application/excel and Content-Disposition to attachment, I can export the table to Excel (not CSV). However, it loses all formatting. I think it's because Excel does not load CSS and I guess that's reasonable.

So, in a scenario where I have to show the table on the web and also export to Excel, both with similar (even if not exact) formatting, what would be the best approach without using something like NPOI?

I am trying to minimize the work and keep the single template if possible. Is it necessary for me to create two separate templates: one with stylesheet, the other with embedded style in the table itself for Excel?

Having a single template with conditional formatting inside would be very messy.

Any ideas?

Answer 1

there are several aproach you could instantiate a excel object on your server using VSTO, and then write the document on memory and write to response the native file, but this aproach could be a litle expensive if you create a excel object per request, so you could try to do a singleton object that wraps the excel object instance

Answer 2

You can create a report (rdlc file) with a similar look to the grid. Then, you can have an action where you instantiate a LocalReport, pass the data you want to it and call its Render method. You then return the byte array returned by the Render method.

Answer 3

If you not yet solve the problem I'll recommend you to use Open XML SDK 2.0 for Microsoft Office (see http://www.microsoft.com/downloads/details.aspx?FamilyID=c6e744e5-36e9-45f5-8d8c-331df206e0d0&displaylang=en). With this way you will be able create an XLSX file without installing Excel on the server. XLSX file is compressed (like ZIP file) collection of xml files. Open XML SDK 2.0 helps you create and change XLSX file as pure xml files. At the first time if you look at Open XML SDK a lot of things look like strange, but it's only at the beginning. There are so named "Open XML SDK 2.0 Productivity Tool" (a part of Open XML SDK 2.0) which can generate a lot of useful code for you. Moreover you can create a nice Excel document which you can use as a prototype (template) of the document which you will create. So you can solve the problem of complex formating without writing of a lot of code.

Look at http://msdn.microsoft.com/en-us/library/cc850837(v=office.14).aspx for some examples and on http://openxmldeveloper.org/default.aspx. See also http://stackoverflow.com/questions/1012547/creating-excel-document-with-openxml-sdk-2-0 as a start example. You can find also a lot of good stuffs on http://www.codeplex.com about Open XML SDK