Compartir a través de

SQL Server Reporting Services (SSRS): Reporting Services in SQL Server 2012 (codename "Denali") will support XLSX, DOCX formats. Bye bye 65536 rows limit in XLS files ;)

  • Artículo

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.

Comments

  • Anonymous
    August 08, 2012
    Nice article....

  • Anonymous
    July 15, 2013
    Do have any workarounds for SQL2008 for escaping that error? i tried in grouping for page break 35000 rows but same error coming.

  • Anonymous
    July 15, 2013
    If you are getting the exact same error, it is for sure that the number of rows "needed" in XLS file exceeds 65536 rows limit. Don't forget that every empty row needed to be added is also counted as a row. If you want to see that it Works until 65536 limit, create a very basic/simple report with a table only (no grouping etc.) and retrieve more than 35000 rows. Then export it to Excel. You will see that it will work. For your original report, you may enable the Verbose Logging for SQL Server Reporting Services by referring to support.microsoft.com/.../2146315 and technet.microsoft.com/.../ms156500(v=sql.100).aspx . Verbose Logging will give you much more information about the rendering extension you use ("Excel" rendered for your case). If you need immediate help on this issue, you can raise a Support request to Microsoft Support from http://support.microosoft.com