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

  • 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
    Monday, July 28, 2008 7:07 PM

All replies

  • 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
    Monday, July 28, 2008 9:14 PM
  •  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
    Monday, July 28, 2008 9:38 PM
  • 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...
    Monday, July 28, 2008 9:39 PM
  • 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
    Tuesday, July 29, 2008 6:49 AM
  • 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
    Tuesday, July 29, 2008 1:22 PM
  •   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
    Tuesday, July 29, 2008 1:46 PM
  • 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.
    Tuesday, July 29, 2008 6:33 PM
  • 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

    Tuesday, August 5, 2008 5:28 PM
  • 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.
    Friday, October 30, 2009 9:15 PM