If you hit to the 65.536 rows limitation in Excel (97-2000) XLS file format, we have good news 🙂
As you know the 65.536 rows limit in an Excel sheet has been removed with Office 2007/2010 with the new Excel format XLSX. But SQL Server Reporting Services was lack of XLSX file export support. With SQL Server 2012 we will be able to use XLSX files also.
That means you won't receive the error messages like below :
"Microsoft.ReportingServices.ReportProcessing.HandledReportRenderingException: Excel Rendering Extension: Number of rows exceeds the maximum possible rows per sheet in this format; Rows Requested: NNNNNN, Max Rows: 65536"
(As you know/can guess where NNNNN in the error message above is a number of rows you wanted to to be rendered into Excel file which is bigger than 65536)
But I want to warn you that you should not try to generate an XLSX file with millions of rows. Reason is simple :
Who will open this Excel sheet with millions of rows in it ? Even if you answer this question as "I will reach this XLSX file programatically not directly opening with Excel". I will strongly underline again that Excel files are not intended to be used as a database! Even the new XLSX format does not have rows limit.