Export to Excel limit in UI for ASP.NET AJAX Grid

Leo

9 posts

Member since:
Sep 2008

Posted 01 Sep 2008 Link to this post

Hi all,

I'm wondering if there is a way around excel's limit of 65000 rows. In the radgrid, the database is able to bind over 65000 rows and the users are trying to export this to excel. Due to the nature of excel (where a sheet cannot hold more than 65k rows), the application gives an error.

Can anyone suggest a way around this?
Is it possible to split the data source into seperate sheets within a workbook? Or would we have to split the data into 2 excel workbook files?

Princy

17421 posts

Member since:
Mar 2007

Posted 02 Sep 2008 Link to this post

Hello Leo,

You can upgrade to Excel 2007 which supports more than 65K records.Also check out this forum thread that i found which gives various solutions.

Princy.

Daniel

Admin

4949 posts

Posted 02 Sep 2008 Link to this post

Hello Leo,

Excel 2003 supports up to 65,536 rows, unlike Excel 2007 which can handle up to 1,048,576 rows. You can use the newer format using Microsoft Office Compatibility Pack. Otherwise you can split your data into different sheets.

Regards,
Daniel
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

Leo

9 posts

Member since:
Sep 2008

Posted 02 Sep 2008 Link to this post

Thanks for your replies, Princy and Daniel.

Actually, the first wall that I've hit while playing around with the exports (I attempted to export to csv as it was recommended in one of the various solutions) is the fact that the system cannot seem to process the export at such a large volume.

Here are some sites detailing the kind of issue I'm dealing with (if anyone needs a reference):
http://forums.asp.net/p/1281278/2449394.aspx
http://discuss.fogcreek.com/dotnetquestions/default.asp?cmd=show&ixPost=4787

In regards to office 2007, unfortunately I can't force my users to upgrade as the current standard is 2003.

With splitting the results, would that mean I would have to develop a function to do it? Or is there a way, using Rad tools, to bind a grid > export to a sheet, create a new sheet within the same work book > bind grid > export etc or would I have to create a new worksheet at each export?

Daniel

Admin

4949 posts

Posted 03 Sep 2008 Link to this post

Hello Leo,

You can try to achieve similar functionality by using page size = 60000 (for instance) and IgnorePaging = false. Indeed you should handle this type of functionality on your own.

Regards,
Daniel
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

JB

16 posts

Member since:
Jul 2008

Posted 26 Jun 2009 Link to this post

Hi,

I'm not sure why but I'm getting OutOfMemoryException when I try to export records. But it is not as large as 65k. The actual count is 32787. So I'm not sure why this is happening.

    
            [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, Int32 startIndex, Int32 charCount) +115 
        
               System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args) +235 
        
               Telerik.Web.UI.GridExcelBuilder.Abstract.ElementBase.AppendAttributes(StringBuilder sb) +396 
        
               Telerik.Web.UI.GridExcelBuilder.DataElement.AppendAttributes(StringBuilder sb) +62 
        
               Telerik.Web.UI.GridExcelBuilder.Abstract.ElementBase.Render(StringBuilder sb) +14 
        
               Telerik.Web.UI.GridExcelBuilder.CellElement.RenderChildElements(StringBuilder sb) +21 
        
               Telerik.Web.UI.GridExcelBuilder.Abstract.ElementBase.Render(StringBuilder sb) +23 
        
               Telerik.Web.UI.GridExcelBuilder.RowElement.RenderChildElements(StringBuilder sb) +99 
        
               Telerik.Web.UI.GridExcelBuilder.Abstract.ElementBase.Render(StringBuilder sb) +23 
        
               Telerik.Web.UI.GridExcelBuilder.Abstract.ElementBase.Telerik.Web.UI.GridExcelBuilder.Abstract.IElement.Render(StringBuilder sb) +4 
        
               Telerik.Web.UI.GridExcelBuilder.TableElement.RenderChildElements(StringBuilder sb) +549 
        
               Telerik.Web.UI.GridExcelBuilder.Abstract.ElementBase.Render(StringBuilder sb) +23 
        
               Telerik.Web.UI.GridExcelBuilder.WorksheetElement.RenderChildElements(StringBuilder sb) +28 
        
               Telerik.Web.UI.GridExcelBuilder.Abstract.ElementBase.Render(StringBuilder sb) +23 
        
               Telerik.Web.UI.GridExcelBuilder.WorkBook.RenderChildElements(StringBuilder sb) +295 
        
               Telerik.Web.UI.GridExcelBuilder.WorkBook.Render(StringBuilder sb) +32 
        
               Telerik.Web.UI.Grid.Export.TableViewExporter.ExcelExportRenderForm(HtmlTextWriter nullWriter, Control form) +2966 
        
               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 
        
               System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1896

I hope someone has a solution as I'm confused about what to do here. It works fine if I split them as 1000 records but the users do not want to do it through multiple excel files.

Daniel

Admin

4949 posts

Posted 02 Jul 2009 Link to this post

Hello JB,

Depending on the number of columns in your RadGrid the generated output may become large enough to cause such error. For example I tried to export 60000 items in 4 columns - the resulting file was 8 MB and the memory, occupied by the worker process was 400+ MB. We don't recommend you export such amounts of data because you will run out of memory if several users try to use the same functionality in the same time.

Kind regards,
Daniel
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.

JB

16 posts

Member since:
Jul 2008

Posted 02 Jul 2009 Link to this post

Hi,

The number of users will be max 2-3 at a time. However, the columns count is 20 per record. But not all columns have data.

Isn't there any other way to work around this? Because they are finding it a problem that they cannot export all the records in one go and they are not technically wise to know what exactly the problem is. They would like a solution and not an explanation. So I'm just asking if it is possible or not in someway.

Regards,
JB

Daniel

Admin

4949 posts

Posted 08 Jul 2009 Link to this post

Hello JB,

I would like to clarify that 32000 items (and 20 columns) are way too much data regardless whether you are exporting or just displaying the data.

To get a better picture, I recommend you try to set the page size to 32000.

Let me know what your findings are.

Kind regards,
Daniel
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.

Jaroslav

3 posts

Member since:
Feb 2009

Posted 07 Dec 2009 Link to this post

> I'm not sure why but I'm getting OutOfMemoryException when
> I try to export records. But it is not as large as 65k.
> The actual count is 32787. So I'm not sure why this is happening.

I have same problem. For users I show in radgrid 25rows per page only, but "export" button must export all rows stored in sqldatasource
When I export about 50000rows with 30columns (invoice table), still get exception "out of memory".
Will telerik solve this bug? or we must export data otherwise?

thx
Jaroslav Kadlecek

Daniel

Admin

4949 posts

Posted 10 Dec 2009 Link to this post

Hello Jaroslav,

I believe there is no way to export such enormous amount of data.

Please try the following: Set IgnorePaging to false to display all the 50000 items in 30 columns in your browser. Then run the task manager and let me know how much memory is occupied by its process.

For testing purposes, I tried this with 50,000 items and just 3 columns - the result was 470MB footprint. Please notice, that I'm the only user exporting at this time - imagine what will happen if there are 5 users, each of them exporting 50000 items in 30 columns simultaneously.

I hope my explanation was helpful.

Best regards,
Daniel
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

Jaroslav

3 posts

Member since:
Feb 2009

Posted 11 Dec 2009 Link to this post

Yes. I understand. You load data from datasource to radgrid objects (all 50k rows/ 30collumns). But it isn't necessary on export event.

In my opinion,
When user click "export", you still load same data to radgrid with defined pagesize as usually (30rows/30collumns)
But export event could call datasource separatelly. Radgrid event could load data direct from datasource and push to exported xls file much more effectively.

Is it possible to solve this problem? Or that solution is too hard and we shoul'd do export on our own.

thx
Jaroslav K.

Daniel

Admin

4949 posts

Posted 15 Dec 2009 Link to this post

Hello Jaroslav,

Thank you for your suggestion - I passed it to our development team.

As I mentioned below, it is not possible to export such amounts of data. Please excuse us for the inconvenience.

Best regards,
Daniel
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

Jean

43 posts

Member since:
Feb 2010

Posted 25 Feb 2010 Link to this post

hello all !

I am using the excel export using telerik. Some of my users are working with "old" version of microsoft Excel (2000 for example), and can't open exported files.

I can't plan to tell them to upgrade their office version, so I wonder how I can do ?

So if you have some ideas, tell me please ;)

Jean

Jean

43 posts

Member since:
Feb 2010

Posted 25 Feb 2010 Link to this post

Ok guys I've juste see that in the telerik help : "Only MS Word and MS Excel 2003 or later are supported."

But I absolutely need a solution...

Some ideas !?

Daniel

Admin

4949 posts

Posted 25 Feb 2010 Link to this post

Hello Jean,

As already mentioned in the documentation, Microsoft Excel 2000 is not supported. It is already 10 years old, so I believe that it is understandable.

Excuse us for any inconvenience this may cause.

Regards,
Daniel
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

Jean

43 posts

Member since:
Feb 2010

Posted 26 Feb 2010 Link to this post

Hello Daniel !

No problem ! Indeed I understand :)

But do you have an idea how I could do for recalcitrant users ? :D

Thanks for you help.

Jean

Philippe GRACA

35 posts

Member since:
Nov 2003

Posted 05 Aug 2010 Link to this post

Hello
Is there a release plan to solve this issue?
Up to know, I was not facing the issue in our production server but since the amount of data is growing slowly (22k rows, 35 columns during export) the Out of memory exception is now occuring.
Please advice
Regards
Philippe

Daniel

Admin

4949 posts

Posted 10 Aug 2010 Link to this post

Hello Philippe,

At this point there is little we could do to optimize the speed and memory consumption but I passed this question as a feature request to our developers. For the time being, you could:
- decrease the number of rows/columns
- try the CSV format

Regards,
Daniel
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items

Philippe GRACA

35 posts

Member since:
Nov 2003

Posted 10 Aug 2010 Link to this post

Hi Daniel
I understand your issues since you've to consider all the potential grid formatting during the output
What I've done is that I moved to the CarlosArg.xmlwriter component to create the excel document since what i do really need is the raw data (the output file is around 40 Mb in excel-ml format!!)

Best regards
Philippe

Kurt

17 posts

Member since:
Aug 2006

Posted 22 Oct 2010 Link to this post

I am hitting the out of memory issue as well and agree that in the case of exporting all rows to a downloadable file, it would probably be better to use a file backing store instead of doing it all in memory.

Is this something that might be supported in a future version?

Daniel

Admin

4949 posts

Posted 28 Oct 2010 Link to this post

Hello Kurt,

RadGrid is capable to export limited quantities of data. Should you need to export large datasources it would be better to use a separate exporting component, reporting control, or your own exporting library (you could use NPOI to generate the file) that fetches the data directly from the data source.

Even if we theoretically improve the speed and memory footprint three times (just an example) you will get the same exception if you have three users that export the same data in the same time. This is why we do not recommend to use RadGrid in such scenarios.

Best regards,
Daniel
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items

JB

16 posts

Member since:
Jul 2008

Posted 21 Dec 2010 Link to this post

Hi,

I implemented the paging technique and allowed them to download the report partially. They tried it until now but now the database has grown too big to do it partially. They are asking me for a solution or else they will take the project to someone else.

The excel sheet is about 60MB when generated. And Microsoft Excel 2007 comes with 1,048,576 rows by 16,384 columns. So why is it still generating in the old 2003 version? Why can't we generate it in xlsx format? That should fix this problem shouldn't it?

Also, someone told me to try serliazing the object before putting it in the stream to convert it. I'm wondering if it is being serialized here internally before convert.

I would really appreciate it if you can get back to me ASAP. Look forward to your response.

Kind Regards,
JB

Jaroslav

3 posts

Member since:
Feb 2009

Posted 22 Dec 2010 Link to this post

Hi.

You can use ExcelML export (this is xml format)

http://www.telerik.com/help/aspnet-ajax/grid-excelml-export.html
http://demos.telerik.com/aspnet-ajax/grid/examples/generalfeatures/excelmlexport/defaultcs.aspx

Daniel

Admin

4949 posts

Posted 24 Dec 2010 Link to this post

Hello JB,

As explained in the online documentation (and in my previous answers), RadGrid is not suitable for exporting such amounts of data.
Export overview (Exporting a large amount of data section)

Please excuse us for any inconvenience caused.

Regards,
Daniel
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.

april

4 posts

Member since:
Oct 2010

Posted 09 Nov 2011 Link to this post

Hello Telerik Team,

I am not sure exporting issue is constrained to Excel 2003 or 2007.
I am trying to export out a huge amount of data to a PDF file from the Rad Grid.
But i end up with the

Exception of type 'System.OutOfMemoryException' was thrown.

My Server has about 4 GB of available RAM, the w3wp.exe process only consumes 1.4 Gb (1,040,708 K) when i got this error.

Hence I notice that, although the server has a lot more memory to consume, the export crashes after consuming about 1.4 GB.
i prioritised w3wp.exe instance to use Real Time Memory through Task Manager, but that doesn't help either.

What can I do to for this.
I am trying to export about 500K rows with 5 columns of data. What would you suggest I do for this ?

Daniel

Admin

4949 posts

Posted 14 Nov 2011 Link to this post

Hello April,

As you can see in my previous answer, I'm talking in general, so yes, the limitation is valid for all supported export formats. I'm afraid that it is not possible to export such amount of items when using the current version of RadGrid. Our developers are aware of this limitation and are doing their best to find a suitable solution.

Regards,
Daniel
the Telerik team

If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now

Jayesh Goyani

2733 posts

Member since:
May 2010

Posted 14 Nov 2011 Link to this post

Hello april,

Please also check this link.

Thanks,
Jayesh Goyani

Adam

8 posts

Member since:
Feb 2012

Posted 15 May 2012 Link to this post

Just fix the control so that it writes to the response output stream rather than load it all into RAM. I don't really understand why this is not possible for you guys.

Jayesh Goyani

2733 posts

Member since:
May 2010

Posted 15 May 2012 Link to this post

Hello Adam,

Because if there is not such type of validation in RadGrid.
If we export large data then it will used large amount of memories then it raise server down issue.

Note : You can also check, how much memories it allocated.
for that you can check :TASK MANAGER -> PROCESS ->

Thanks,
Jayesh Goyani