Problem to export to Excel

[Francisco]

Hi

Some of my users need to export to excel a huge quantity of information, 40.000 records or more.

i have two problems

1) The operation takes a lot of time (6 minutes or even more). How can i develop a timeout?

2) In my production server an exception is being triggered. But i cannot reproduce that exception in my development environment, so i don't know where to catch it. Below is a copy of the error i'm getting in the production server.:

========

Exception of type 'System.OutOfMemoryException' was thrown.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 

Stack Trace:

[OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.]
   System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity) +34
   System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength) +73
   System.Text.StringBuilder.Append(Char value) +97
   System.IO.StringWriter.Write(Char value) +28
   System.Web.UI.HtmlTextWriter.RenderBeginTag(HtmlTextWriterTag tagKey) +534
   System.Web.UI.WebControls.WebControl.RenderBeginTag(HtmlTextWriter writer) +47
   System.Web.UI.WebControls.WebControl.Render(HtmlTextWriter writer) +17
   System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +25
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +121
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +22
   System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +130
   System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +24
   System.Web.UI.WebControls.WebControl.RenderContents(HtmlTextWriter writer) +7
   System.Web.UI.WebControls.WebControl.Render(HtmlTextWriter writer) +29
   System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +25
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +121
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +22
   Telerik.Web.UI.GridTable.RenderContents(HtmlTextWriter writer) +1962
   System.Web.UI.WebControls.WebControl.Render(HtmlTextWriter writer) +29
   Telerik.Web.UI.GridTable.Render(HtmlTextWriter writer) +1802
   System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +25
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +121
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +22
   System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +130
   System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +24
   System.Web.UI.WebControls.WebControl.RenderContents(HtmlTextWriter writer) +7
   Telerik.Web.UI.GridTableViewBase.Render(HtmlTextWriter writer) +27
   System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +25
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +121
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +22
   Telerik.Web.UI.Grid.Export.TableViewExporter.ExcelExportRenderForm(HtmlTextWriter nullWriter, Control form) +1045
   System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +2065835
   System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +24
   System.Web.UI.HtmlControls.HtmlForm.RenderChildren(HtmlTextWriter writer) +59
   System.Web.UI.HtmlControls.HtmlForm.Render(HtmlTextWriter output) +68
   System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +25
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +121
   System.Web.UI.HtmlControls.HtmlForm.RenderControl(HtmlTextWriter writer) +37
   Telerik.Web.UI.Grid.Export.TableViewExporter.ExcelExportRenderPage(HtmlTextWriter nullWriter, Control page) +102
   System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +2065835
   System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +24
   System.Web.UI.Page.Render(HtmlTextWriter writer) +26
   System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +25
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +121
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +22
   Telerik.Web.UI.RadAjaxControl.RenderPageInAjaxMode(HtmlTextWriter writer, Control page) +619
   System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +2065835
   System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +24
   System.Web.UI.Page.Render(HtmlTextWriter writer) +26
   System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +25
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +121
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +22
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1896
===================

I use the method "RadGrid.MasterTableView.ExportToExcel()" , Visual Studio 2005 and RadControls for ASPNET AJAX Q2 2008.

Could you help me with this?

[Pavlina]

Hi Francisco,

You get this error because you try to export large records at once.
Unfortunately there is little that can be done when trying to export large amounts of data. You should have in mind that when IgnorePaging is set to true, RadGrid instance will be bound to all of the 40.000 records or more which is quite memory consuming. Exporting and storing such large amount of data in session variable will cause scalability problem. I can suggest you either to consider decreasing the amount of data or export only the current page by setting the IgnorePaging option to 'false'.

Kind regards,
Pavlina
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Francisco]

Some of my users need to export to excel a huge quantity of information, they don't want to split information in multiple sheets,  do you have any idea about where to catch this error?

[Pavlina]

Hello Francisco,

Please review the following forum discussion Exporting large data. RadGrid can support much larger DataSets than 60K. The problem is related to the excel. Excel 2003 supports up to 65,536 records whereas Excel 2007 can handle up to 1,048,576 rows. So, you can probably upgrade to Excel 2007.

Kind regards,
Pavlina
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[varun]

Hi Pavlina,
I am facing the same problem(System.OutOfMemoryException)inspite of using Excel 2007.
I donot have the option of turning off ignore paging as this slows the page drastically.

I need all the records in one excel file as this is being used as an input to another ETL process.
Is there anything I can do either on server or code to reolve this issue?
I am presently using telerik controls of Q1 2009 release.

[Martin]

Hello Varun,

In theory you can export as much items as the version of excel you use supports (if the browser ever gets to render all items). However exporting large grids requires a lot of available memory on the server. In addition the performance (and the memory consumption) depends not only on items count but on the columns count too. So if you have a 70k grid with one or two BoundColumns you might be able to export it. But if you have a large number of columns in the (especially if you have a hierarchy) then you might get exception even if the grid has 10k items.

Another thing that should be considered is that in a live website if you have several users trying to export such a grid at the same time then server memory exception is very likely.

You can review this forum post discussing such topic.

Hope this helps

Regards,
Martin
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Daniel]

We have several users on the system and when these users try to export to excel, they are usually exporting somewhere around 1500 records at the most. Perhaps 3 users a time. When this happens, and I am watching the task manager on the server, memory usage starts crawling up and eventually maxes out. In addition, the cpu stat for the Asp.Net worker process jumps way up and remains at around 94%-100%. These values never seem to recover until we recycle the app pool.

What is going on and why doesn't the system recover?

[David]

Maybe a little bit late but for anyone with this issue I suggest to implement the following memory flush method in the gridexporting event (https://www.codeguru.com/dotnet/making-a-ram-cleaner-in-net/)

[DllImport("KERNEL32.DLL", EntryPoint =
         "SetProcessWorkingSetSize", SetLastError = true,
         CallingConvention = CallingConvention.StdCall)]
      internal static extern bool SetProcessWorkingSetSize32Bit
         (IntPtr pProcess, int dwMinimumWorkingSetSize,
         int dwMaximumWorkingSetSize);

      [DllImport("KERNEL32.DLL", EntryPoint =
         "SetProcessWorkingSetSize", SetLastError = true,
         CallingConvention = CallingConvention.StdCall)]
      internal static extern bool SetProcessWorkingSetSize64Bit
         (IntPtr pProcess, long dwMinimumWorkingSetSize,
         long dwMaximumWorkingSetSize);
 
public void FlushMem()
      {
         GC.Collect();

         GC.WaitForPendingFinalizers();

         if (Environment.OSVersion.Platform == PlatformID.Win32NT)
         {

            SetProcessWorkingSetSize32Bit(System.Diagnostics
               .Process.GetCurrentProcess().Handle, -1, -1);

         }
      }