Changing Row Height in Excel after Rendering by ASP.Net SSRS ReportViewer

Question

I have an SSRS report that has a Comments field where there's enough text to span multiple rows per record. It renders fine in the ASP.Net ReportViewer control, but when it's exported the formatting can be different. Exporting to Word it looks fantastic. But exporting to Excel and PDF, the row height for each record is never increased beyond the default, so the text just gets truncated. In Excel I can go in and double-click on individual or multiple row heights to fix them, but this is an extra step we don't want.

Since there is no "exported" event coming from the ReportViewer, how can I fix it so that it exports/formats correctly in Excel? Is there a way to do it inside the ReportViewer at render-time, or do I need to think about producing the Excel file and then going into it to change it with VBA code after-the-fact? Any ideas?

Answer 1

Unfortunately, there is not many tweaking you can do to the built-in reportViewer renderers. You can tweak some of the renderer's output via the DeviceInfo string, however, nothing for what you are attempting to do. (Information on DeviceInfo)

There are some 3rd party renderers that you can purchase, which will give you a lot more control. I have never come across a free one.

You could start to write your own renderer, but I highly suggest you don't, as it would be overkill for this situation (would take you a very long time to pull this off).

Aside from that, you could write some code to open the Excel file via COM, and alter the file. You could actually code that right after the export is completed, streamlining it. Or, you could also write some VBA macros for Excel, like you mentioned.

Answer 2

It's true you can't tell when the user hits the export button if they are using Report Manager. But you can control the process if you provide your own web interface for generating reports. You can get the reports from SSRS using web services. Then you can open and post-process the spreadsheet in your code using a server-side Excel manipulation tool like SoftArtisans OfficeWriter and stream it to the user.

Disclaimer: I work for SoftArtisans