CODEWITHSUNDEEP
c#excelexportexport-to-excelrdlc
memory of a dream
memory of a dream

Reputation: 1267

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

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?

Upvotes: 1

Views: 5739

Answers (3)

Shayne Ephraim
Shayne Ephraim

Reputation: 551

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.

Upvotes: 2

StevenWhite
StevenWhite

Reputation: 6034

I know this is an old question, but you can add page breaks to your .rdl file. These cause the export to start new sheets in Excel. You can use a formula in the .rdl to determine when it needs another page break dynamically.

Upvotes: 2

Zack Kay
Zack Kay

Reputation: 454

Not sure what version of Excel you're using, but 65,536 rows is the limit for .xls files. If you create an .xlsx file instead, you'll have a max of 1,048,576 rows (from here), which should satisfy your requirements.

Edit: Although I've used the native Excel objects myself recently, apparently EPPlus will allow you to create XLSX files much easier.

HTH, Z

As Requested:

xl.Application myExcelApp;
xl.Workbooks myExcelWorkbooks;
xl.Workbook myExcelWorkbook;
xl.Worksheet myExcelWorksheet;

myExcelApp = new xl.Application();
myExcelApp.DisplayAlerts = false;
myExcelApp.Visible = false;
myExcelWorkbooks = myExcelApp.Workbooks;

String fileName = @"G:/foo/bar/goleafsgo.xlsx"; // set this to the file you want
myExcelWorkbook = myExcelWorkbooks.Add(misValue);
myExcelWorksheet = (xl.Worksheet)myExcelWorkbook.Worksheets.get_Item(1);

Recordset rs = ConvertToRecordset(dt);

ws.get_Range("A3", System.Reflection.Missing.Value).CopyFromRecordset(rs);

myExcelWorkbook.SaveAs(fileName, xl.XlFileFormat.xlWorkbookDefault, misValue, misValue, false, false, xl.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue);
myExcelApp.Quit();

The ConvertToRecordset function can be found here

Upvotes: 1

Related Questions