Exporting RDLC Report In Excel When Record Count Is Above 65536 In Report Viewer 10.0.0.0

Theme

Solution for Excel Rendering Extension: Number of rows exceeds the maximum possible rows per sheet in this format; Rows Requested: XXXXX, Max Rows: 65536

Introduction

As a default, RDLC reports can be viewed in a tabular format or a chart format. In the tabular format, the users can download the data in Excel, PDF and Word formats (see Screenshot 1) but when the users try to export a report into an Excel format with more than 65536 rows, it throws an error (see the screenshot 2).

Screen Shot 1



Screen Shot 2



Reason for this issue

In Report view 10.0 or lesser, the versions supports only MS excel (97-2003). Due to the limitation of an older version of Excel, the sheet cannot display more than 65536 records in a single sheet.

Solutions

There are two possible solutions to overcome this problem. They are.

1. Migrate the report viewer to the latest versions like 11.0.
2. Setting Page breaks for the RDLC report.

Migrate the report viewer to latest versions like 11.0

Report viewer version 11.0 uses the latest version of MS Excel (i.e. XLSX format). By default, this type of an extension supports 1,048,576 records per sheet. With this large data support, the users don’t get the maximum rows conversion issue while exporting into an Excel sheet.

We have a few challenges for implementing this migration to the latest version. This can be the rework of the all the reports and report pages. This can be achievable with the small projects or fewer number of reports but for big projects, this solution is costly and requires extra man hours. In order to avoid this, the developers can choose the second option, as shown below.

Setting Page breaks for the RDLC

To overcome the limitation error, RDLC provides an option to break the pages into multiple sheets.

Example

Create a sample RDLC report with Report Viewer 10.0, using Adventure Works database, which is displaying above 66000 records.



By applying the steps, mentioned below in RDLC report, the large data can be easily exportable into multiple sheets.

Step 1

Open RDLC and select the data row of the table. Right-click on it, select Add Group and then select Parent Group.


Step 2

In this step, you should get a Tablix group Window. Enter the function, mentioned below in the Group by: Text box and click OK.

=Int((RowNumber(Nothing))/65520)


Step 3

In the next step, at the bottom of the RDLC report, you will find “Row Groups” pane. Here, right-click on the group name and select Group Properties.



You will get a Group Properties Window. We can give a proper group name here.



Check the “Between each instance of a group” option in Page Breaks from the left pane.



Next, in the Sorting section, delete the sort by function.



If you observe the report after performing the steps mentioned above, it adds Group1 column, as shown below. Select add delete the Column.



Now, save & run the report and export to Excel format. If everything works, the data should display in the multiple sheets.



If we observe in the sheet 2, the report headers won’t display. In order to display the headers, add the code, mentioned below.

Open the RDLC report in XML editor. Add the nodes, mentioned below at the first TablixMember node of TablixMembers node.

<KeepWithGroup>After</KeepWithGroup> <RepeatOnNewPage>true</RepeatOnNewPage>



Save, rerun the report and export to Excel. You can observe the data in multiple sheets with the headers in all the sheets.