Problem in Exporting RadGrid data to Excel

6 posts, 0 answers
  1. Omkar
    Omkar avatar
    3 posts
    Member since:
    Nov 2010

    Posted 19 Apr 2011 Link to this post

    we are having an application with large number of data.

    while exporting the data from rad grid to excel format we are getting the error :
    Exception type: OutOfMemoryException
    the Rad Grid contains the 25 columns and nearly 35,000 rows

    there is one aspx page on that page load the data will fetch into the RadGrid and then the data will export to Excel or CSV
    i pasted the HTML and cs code

    can you please assist us on this
    <telerik:RadGrid ID="radGrid" runat="server" Visible="false" AutoGenerateColumns="false" OnGridExporting="radGrid_GridExporting"
        OnExcelExportCellFormatting="RadGrid1_ExcelExportCellFormatting">
         <ExportSettings HideStructureColumns="true" />

           </telerik:RadGrid>


    the .CS  Code :

    protected void Page_Load(object sender, EventArgs e)
    {
            if (Request.QueryString["downloadoption"] != null)
             {
                  LoadData();
              }
    }


     private void LoadData()
            {
                Logger.LogInformation("Start", "btnSaveAs_Click", "DownloadFormat");
                if (Request.QueryString["ReportPagerName"] != null)
                {
                    ReportPager = Request.QueryString["ReportPagerName"];
                }
                if (Session[ReportPager] != null)
                {
                    int reportIntId = 0;
                    Int32.TryParse(Request.QueryString[ReportID], out reportIntId);
                    //string reportID = Request.QueryString[ReportID];
                    this.CheckDataAccess(DataAccessType.Report, null, null, null, null, null, null, null, reportIntId);
                    if (!IsTransactionDetailsPage(reportIntId))
                    {
                        reportPager = (ReportPager)Session[ReportPager];
                        AddColumnsToGrid();
                        SetExportSettings();
                        BindDataTable();
                    }
                    else
                    {
                        if (Session["TransactionDetails"] != null)
                        {
                            DataTable dt = (DataTable)Session["TransactionDetails"];
                            this.radGrid.MasterTableView.Columns.Clear();
                            foreach (DataColumn dc in dt.Columns)
                            {

                                GridBoundColumn gbc1 = new GridBoundColumn();
                                gbc1.DataField = dc.ColumnName;
                                gbc1.HeaderText = string.Empty;
                                this.radGrid.MasterTableView.Columns.Add(gbc1);
                            }

                            this.radGrid.DataSource = dt;
                            this.radGrid.DataBind();
                        }
                    }

                    DownloadDataGrid();
                }
                Logger.LogInformation("End", "btnSaveAs_Click", "DownloadFormat");
            }

    private void AddColumnsToGrid()
            {
                Logger.LogInformation("Start", "AddColumnsToGrid", "DownloadFormat");

                this.radGrid.MasterTableView.Columns.Clear();
                foreach (GridField entry in reportPager.ColumnDictionary.Values)
                {
                    if (entry.IsDisplayField)
                    {
                        GridBoundColumn gbc1 = new GridBoundColumn();
                        gbc1.DataField = entry.DataField;
                        gbc1.HeaderText = entry.HeaderText;
                        this.radGrid.MasterTableView.Columns.Add(gbc1);
                    }
                }

                Logger.LogInformation("End", "AddColumnsToGrid", "DownloadFormat");
            }


    private void SetExportSettings()
            {
                Logger.LogInformation("Start", "SetExportSettings", "DownloadFormat");

                this.radGrid.ExportSettings.ExportOnlyData = true;
                this.radGrid.ExportSettings.IgnorePaging = true;
                this.radGrid.ExportSettings.OpenInNewWindow = true;
                this.radGrid.ExportSettings.FileName = ConfigurationManager.AppSettings["DownloadDataFileName"];

                Logger.LogInformation("End", "SetExportSettings", "DownloadFormat");
            }



    private void BindDataTable()
            {
                Logger.LogInformation("Start", "BindDataTable", "DownloadFormat");

                this.reportPager.StartRow = 1;
                this.reportPager.EndRow = this.reportPager.GetRowCount();
                DataTable dt;
                //Excel
                if (Request.QueryString["downloadoption"].ToString() == "EXCEL")
                    dt = this.reportPager.GetDataTable(false, false);
                else
                    dt = this.reportPager.GetDataTable(false);

                this.radGrid.DataSource = dt;
                this.radGrid.DataBind();

                Logger.LogInformation("End", "BindDataTable", "DownloadFormat");
            }



    private void DownloadDataGrid()
            {
                Logger.LogInformation("Start", "DownloadData", "DownloadFormat");
                this.radGrid.Visible = true;
                //CSV
                if (Request.QueryString["downloadoption"].ToString() == "CSV")
                {
                    //if (SelRadioButtonList1.SelectedItem.Text == SelRadioButtonList1.Items[2].Text){

                    this.SetCSVDelimiter();
                    this.radGrid.MasterTableView.ExportToCSV();
                }
                //Excel
                else if (Request.QueryString["downloadoption"].ToString() == "EXCEL")
                {
                    //else if (SelRadioButtonList1.SelectedItem.Text == SelRadioButtonList1.Items[0].Text)
                    //{
                    this.radGrid.MasterTableView.ExportToExcel();
                }
    }


    protected void radGrid_GridExporting(object source, GridExportingArgs e)
            {
                Logger.LogInformation("Start", "radGrid_GridExporting", "DownloadFormat");
                Response.ClearContent();
                Response.ClearHeaders();
                Response.Clear();
                Response.BufferOutput = true;
                if (e.ExportType == ExportType.Excel || e.ExportType == ExportType.Csv)
                {
                    string filename = radGrid.ExportSettings.FileName;
                    string fileExtension = radGrid.ExportSettings.Excel.FileExtension;
                    if (e.ExportType == ExportType.Csv)
                        fileExtension = radGrid.ExportSettings.Csv.FileExtension;

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

                    if (e.ExportType == ExportType.Excel)
                    {

                        //specify the number decimal separator and group separator being used.
                        string css = "<style type='text/css'><!--table {mso-displayed-decimal-separator:'\\" + Thread.CurrentThread.CurrentUICulture.NumberFormat.NumberDecimalSeparator;
                        css += "'; mso-displayed-thousand-separator:'\\" + Thread.CurrentThread.CurrentUICulture.NumberFormat.NumberGroupSeparator + "';} ";
                        css += this.AddDateFormatCSSClass();
                        css += "--> </style>";
                        //css += @"<script type=""text/javascript"" language=""javascript"">window.opener.Invoked();</script>";
                        e.ExportOutput = e.ExportOutput.Replace("</head>", css + "</head>");
                    }
                }
                if (Request.QueryString["downloadoption"].ToString() == "CSV")
                {

                    Response.ContentType = "application/octet-stream";
                    Response.AddHeader("Content-Type", "text/csv");
                    Response.BinaryWrite(new UnicodeEncoding().GetBytes(e.ExportOutput));
                    Response.Flush();
                    Response.End();
                }
                Logger.LogInformation("End", "radGrid_GridExporting", "DownloadFormat");

            }


  2. Princy
    Princy avatar
    17421 posts
    Member since:
    Mar 2007

    Posted 19 Apr 2011 Link to this post

    Hello Omkar,

    Please take a look at the following forum post which discussed the similar scenario.
    Export to Excel Question

    Thanks,
    Princy.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Omkar
    Omkar avatar
    3 posts
    Member since:
    Nov 2010

    Posted 19 Apr 2011 Link to this post

    we are already used the RadGrid.MasterDataTable. ExportToExcel()
    still we are getting with this error
    and in second option you suggested for paging but that will affect the functionality
    so is there any other alternative
  5. Iana Tsolova
    Admin
    Iana Tsolova avatar
    3388 posts

    Posted 19 Apr 2011 Link to this post

    Hi Omkar,

    The more the grid items and columns are, the more memory is used while generating the exported file. As you already read in the forum threads provided by Princy, in such scenarios is it likely to receive OutOfMemoryException, especially in the cases where a few users are exporting the grid at a time.
    Therefore, the alternative is to export the data per page. You can increase the PageSize for the grid in this case to a reasonable number so users have more records in the resulting file and export all data easier.

    Best wishes,
    Iana
    the Telerik team

    Browse the vast support resources we have to jump start your development with RadControls for ASP.NET AJAX. See how to integrate our AJAX controls seamlessly in SharePoint 2007/2010 visiting our common SharePoint portal.

  6. Omkar
    Omkar avatar
    3 posts
    Member since:
    Nov 2010

    Posted 20 Apr 2011 Link to this post

    enable paging will hamper my functionality.,
    lets see,
    in future,if you get any solution on this please let me know
    thank you lana for your help.
    --
    Omkar

     
  7. James
    James avatar
    1 posts
    Member since:
    Apr 2012

    Posted 09 Feb 2015 in reply to Omkar Link to this post

    It's been a while... But for the sake of helping others looking at this thread...
    We had OutOfMemoryException errors while exporting to Excel.  By exporting to csv, all OutOfMemoryException errors went away and Excel recognizes csv as an Excel format.  What you lose, of course, is some fancy formatting...
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017