This is a migrated thread and some comments may be shown as answers.

Problem in Exporting RadGrid data to Excel

5 Answers 340 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Omkar
Top achievements
Rank 1
Omkar asked on 19 Apr 2011, 07:35 AM
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");

        }


5 Answers, 1 is accepted

Sort by
0
Princy
Top achievements
Rank 2
answered on 19 Apr 2011, 07:56 AM
Hello Omkar,

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

Thanks,
Princy.
0
Omkar
Top achievements
Rank 1
answered on 19 Apr 2011, 08:03 AM
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
0
Iana Tsolova
Telerik team
answered on 19 Apr 2011, 02:43 PM
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.

0
Omkar
Top achievements
Rank 1
answered on 20 Apr 2011, 05:11 AM
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

 
0
James
Top achievements
Rank 2
answered on 09 Feb 2015, 06:54 PM
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...
Tags
Grid
Asked by
Omkar
Top achievements
Rank 1
Answers by
Princy
Top achievements
Rank 2
Omkar
Top achievements
Rank 1
Iana Tsolova
Telerik team
James
Top achievements
Rank 2
Share this question
or