Export rdl report to a multiple sheets excel file problematically from c#

Question

I've been searching online for a solution to this but so far no luck.

In the current application I'm developing, I have the need to export potentially large rdlc reports to excel files (among other formats)

This is the code I'm using to achieve this, and it works very well on normal sized reports:

private static void ExportToExcel(LocalReport localReport, string filename)
{
  if (File.Exists(filename))
  { 
    File.Delete(filename);
  }

  var bytes = localReport.Render("Excel", string.Empty);
  using (var stream = File.Create(filename))
  {
    stream.Write(bytes, 0, bytes.Length);
  }
}

Pretty straightforward, however when I try to export large reports I get an exception similar to the following: "Excel Rendering Extension: Number of rows exceeds the maximum possible in a worksheet of this version. Rows requested: 152190. Maximum rows: 65536."

So here's my question: Is there a way of telling the exporter to create a new worksheet when the maximum number of lines is achieved? and if so what is that way?

Answer 1

I ran into a similar issue and thought I would add my experience here. Since the version of SSRS was not mentioned and the question remained unanswered, I thought this was as good of place as any. I wrote a PowerShell script to export SSRS 2012 rdl reports to various formats, one being Excel. I was also getting the following error:

Exception calling "Render" with "7" argument(s): "Excel Rendering Extension: Number of rows exceeds the maximum possible in a worksheet of this version. Rows requested: 65587. Maximum rows: 65536. (rrRenderingError)"

SSRS 2012 does remove the 65536 row limit from Excel exports, but I had a hard time finding how to get past this row limit programmatically. After a bit of searching, I ran across a Microsoft Support Article that helped me resolve it. The parameter value "Excel" needs to be changed to "EXCELOPENXML"

var bytes = localReport.Render("EXCELOPENXML", string.Empty);

Again, this is no help for earlier versions of SSRS, but I wanted to provide more visibility to this for SSRS 2012.