Reports: Excel Export Formatting

When exporting an SSRS report to Excel all kinds of interesting things can happen from a formatting point of view. The report which might otherwise render quite nicely in SSRS may appear to have been attacked by a bunch of gremlins in its exported Excel format. Several things might contribute to this haphazard effect:

1. The cells of data on the spreadsheet might be scattered all over the place
2. Some elements of data may occupy several excel cells having been auto-merged in the export
3. The report might export into multiple different sheets within the spreadsheet with no apparent rhyme or reason

The report below illustrates this showing cells of data being spread way out and multiple sheets being created from a single export file.

The main reason for this is that Excel is a highly structured document and it needs to fit the results into neat rows and columns. If the cells or rows are not aligned then Excel makes automatic decisions in terms or rendering the export. For example, merge cells may bring 2 sets of non-aligned data elements into alignment; expanding a column width or height may similarly bring a non-aligned element into alignment with other report elements.

Therefore it goes without saying that one of the keys to ensuring that a report renders well when exporting to Excel is to ensure alignment. This might seem obvious and is fairly easy when it comes to grid controls which are fairly structured elements to begin with. However if you look at the report above, you will notice that many of the data elements are text fields and you will need to ensure that these all align quite nicely. It might be difficult to discern with the eye - for example, a location of a cell might be 2.51322 vs. 2.5 and that small discrepancy can throw things off in the export to Excel. The following guidelines should therefore be used when creating SSRS reports (in general in terms of overall quality but obviously with extra emphasis when you know the report will be exported to Excel):

• Ensure alignment - you should use the text box properties to ensure that cells or aligned exactly
• Use rectangles extensively for grouping. It is quite common for different parts of a report to have different formatting structures. Using rectangles for grouping helps excel cope with changes to the format.
• If you are using sub-reports in your main report - ensure the page size of your sub-report adheres to the size of the parent report and once again - I'd recommend using a rectangle to enclose the sub-report
• Be careful with page breaks before or after sections. When excel encounters a page break it will create a new sheet.

One other important feature to be aware of is that you can also control the name that Excel assigns when creating a new sheet. As illustrated in the screenshot above - the export created default Sheet1, Sheet2 etc. sheet names. You can refer to the following post for a detailed write up of this feature (only available in RS 2008 R2). In my case, I just entered the page details as follows:

The end result is much cleaner looking export: