What is the limit of export to excel for Report Control?

Question

Hi,

I am trying to export to excel.

Report Viewer seems to be rendering ok for small row count (<30K - rows) but when I try to export larger size (>=50K - rows)
it times out on me.

The sql statement only takes 15 sec to get this data. But the rendering seems to be timming out after 10 mins

Any suggesion will be highly appreciated.



Thanks,

Anthony Desa

Answer 1

you sure it's the reportviewer that's dying on you?  I think excel has a limit of 65k rows per sheet.

And depending on how your report is designed, 50k rows from the DB doesn't always equal 50k rows to excel.

To test this, throw a few page breaks in there. I believe the reportviewer generates a new sheet in excel if there is a pagebreak.

---

Living my life at 123mph in 11.15 seconds

Answer 2

 Hi there,


Thanks for taking time to reply to my request.

I have tried exporting upto 30K and it works fine.
Note: 30k or SQL Rows translates to 30K rows of Excel

If I export 50K of rows it times out on me.

When I was trying to export 118K it gave me the error that Excel has a limitation of 65K.

This Report is very simple. It does not have any graphics or any other features. Its a export that will
be used by other application as an import. We want all the data to be in one sheet.

When this export runs we have notifced that the memory usage goes very high (99%) before it times out.

Our Reporting Server have 2GB of RAM.

Thanks,

Anthony Desa

Answer 3

Anthony Desa said:

 Hi there,


Thanks for taking time to reply to my request.

I have tried exporting upto 30K and it works fine.
Note: 30k or SQL Rows translates to 30K rows of Excel

If I export 50K of rows it times out on me.

When I was trying to export 118K it gave me the error that Excel has a limitation of 65K.

This Report is very simple. It does not have any graphics or any other features. Its a export that will
be used by other application as an import. We want all the data to be in one sheet.

When this export runs we have notifced that the memory usage goes very high (99%) before it times out.

Our Reporting Server have 2GB of RAM.

Thanks,

Anthony Desa

Best way to tackle the present is to expect the unexpected...

Answer 4

Hi,

SQL server export to excel doesn't have any limitation. If the excel sheet exceeds more than 65K rows automatically remaining data will continue in next sheet......

SQL Server reporting server rendering will take the time as per your data size. To solve your problem increase your time out(try to make it infinite). Then you won't get timeout issue.........


Hanu

---

Hanu

Answer 5

Hanu,

Thanks for taking time to reply to my request.

I have increased the time out on Web server and on Reporting Server to 72000 sec.

It looks like it runs out of memory. The memory usage reaches to 99% before it times out on me.

Thanks,

Anthony Desa

Answer 6

  Ok, lets try this.  Take the reportviewer out of the equation, and just send it straight to an excel format.  See if we have the same problem.


Here is a VB code example of how to do it with a dataset.  I'd put it in a proper code block, but looks like this forums webconfig is fruity at the moment.

''' <summary>

''' creates an unformatted excel file

''' </summary>

Private Shared Sub ExportDataSetToExcel(ByVal ds As DataSet)

Dim response As HttpResponse = HttpContext.Current.Response

' first let's clean up the response.object

response.Clear()

response.Charset = ""

' set the response mime type for excel

response.ContentType = "application/vnd.ms-excel"

response.AddHeader("Content-Disposition", "attachment;filename=" + "ReportDescription" + ".xls")

' create a string writer

Using sw As New StringWriter

Using htw As New HtmlTextWriter(sw)

' instantiate a datagrid

Dim dg As New DataGrid()

dg.DataSource = ds.Tables(0)

dg.DataBind()

dg.RenderControl(htw)

response.Write(sw.ToString())

response.[End]()

End Using

End Using

End Sub

---

Living my life at 123mph in 11.15 seconds

Answer 7

Hi Anthony,

1. Hanu mentions above the report will continue on to next sheets. That is not correct. Unless your report expressely sets a page break after a certain number of rows, all data will appear on one page\sheet and not auto-split sheets.

2. Rendering to Excel is limited to ~65k rows per sheet.

3. Rendering to Excel is limited to 256 columns.

4. During rendering, the entire data set and the report is loaded into memory on the server. If the machine runs out of memory, you will see the issues you are encountering. Recommendation would be to add more memory if you have the requirement to export that much data.

Hope that helps.

---

Brad Syputa, Microsoft Reporting Services This posting is provided "AS IS" with no warranties.

Answer 8

Hi Brad,

Thanks for your feedback. I highly appreciate! your quick response.

I have upgraded my web/reporing server to have 4GB of RAM. I am still experiancing the same issue. Server now takes a longer time to
time out.

One good thing that I have noticed is that server releases memory after it times out. Previoiusly I have to do iisreset to release memory.

Following is the code that I am using to export to excel 

Please note that the code works good for rows <= 30K. 

Your help in this respect is highly appreciated.

try

{

BLCRDLUtility l_blcRDLUtility = new BLCRDLUtility();

BERDLDataSources l_beRDLDataSources = new BERDLDataSources();

string l_strReportServerURL;

l_strReportServerURL = ConfigurationManager.AppSettings["ReportServicesURL"].ToString();

rvwExport.ServerReport.ReportServerUrl = new Uri(l_strReportServerURL);

BEUsage l_beUsage = new BEUsage();

l_beUsage.ReportID = m_lngReportID;

l_beUsage.LogInUserID = m_objBEUserSession.LoginID;

l_beUsage.UserID = m_objBEUserSession.UserID;

l_beUsage.SessionCD = m_objBEUserSession.SessionCD;

l_beUsage.ReportViewTypeCD = QTUtility.RDLReportViewType.EXPORT;

l_beUsage.ReportCategoryTypeCD = RDLCategoryTypeCD.MAIN_REPORT;

BLCRDL l_blcRDL = new BLCRDL();

BERDL l_beRDL = l_blcRDL.GetRDL(l_beUsage);

string l_strRDL = l_beRDL.ExportRDL;

if (l_strRDL.Length == 0)

throw new Exception("Length Of RDL is null");

l_beRDLDataSources = l_blcRDLUtility.GetDataSources();

l_strRDL = l_blcRDLUtility.UpdateRDLDataSource(l_strRDL, l_beRDLDataSources);

// no timeout :)

rvwExport.ServerReport.Timeout = -1;

StringReader l_stringReader = new StringReader(l_strRDL);

rvwExport.ServerReport.LoadReportDefinition(l_stringReader);

List<ReportParameter> l_reportParameters = l_beRDL.SQLParameters.ReportParameters(m_objBEUserSession.PreferredLanguageCD);//GetReportParameters(l_beRDL.SQLParameters);

if (l_reportParameters.Count > 0)

{

rvwExport.ServerReport.SetParameters(l_reportParameters);

}

Warning[] warnings;

string[] streamids;

byte[] bytes;

string mimeType;

string encoding;

string extension;

string filename;

bytes = rvwExport.ServerReport.Render(

"Excel", null, out mimeType, out encoding, out extension, out streamids, out warnings);

filename = string.Format("{0}.{1}", "ExportToCMM", "xls");

Response.ClearHeaders();

Response.Clear();

Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);

Response.ContentType = mimeType;

Response.BinaryWrite(bytes);

Response.Flush();

Response.End();

}

catch (Exception Excp)

{

if (Excp.Message != "Thread was being aborted.")

{

l_strCurrentOpt = QTUtility.UTLUtility.GetFullQualifyingName(MethodBase.GetCurrentMethod()) + "::" + l_strCurrentOpt;

Exception l_objException = new Exception(l_strCurrentOpt, Excp);

if (UTLAppConfig.UILogging)

m_l4nLog.Error(l_strCurrentOpt, l_objException);

if (Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.ExceptionPolicy.HandleException(l_objException, ExceptionCategory.USER_INTERFACE_EXCEPTION_POLICY))

throw;

}

}


Thanks,

Anthony Desa

Answer 9

My experience is it depends on the amount of RAM in the client doing the export.  It spools and spools and if it's timing out, it's not finished or not able to finish in time before the timeout occurs.