Thursday, October 27, 2011

Exporting a report to multiple Excel WorkSheets

To render a report to Excel on multiple sheets, be sure to use page breaks after the different sections of the report. If a section doesn't specifically allow page breaks, then you'll need to wrap the controls inside a rectangle and set the page break property on the rectangle.Let's say you have a report with two table regions, as shown in Figure 1.

Figure 1: A report with multiple table regions
When you export the report to Excel, you'll find that both the table regions display on the same worksheet, as shown in Figure 2. This makes it hard to make modifications to the Excel file.

Figure 2: Two table regions rendered to the same Excel worksheet
To make the table regions display on different worksheets, you can check Add a page break after check box, as shown in Figure 3.

Figure 3: Setting the PageBreakAtEnd property
When the report is exported to Excel, two worksheets will now be created, as shown in Figure 4.

Figure 4: Two table regions exported to two worksheets

No comments: