Not answered Export to Excel limit

Feed from this thread

Leo

Posted on Sep 1, 2008 (permalink)

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?

Reply

Princy MVP

Posted on Sep 2, 2008 (permalink)

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.

Reply

Daniel Daniel

Posted on Sep 2, 2008 (permalink)

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.

Reply

Leo

Posted on Sep 2, 2008 (permalink)

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?

Reply

Daniel Daniel

Posted on Sep 3, 2008 (permalink)

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.

Reply

Posted on Jun 26, 2009 (permalink)

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 Daniel

Posted on Jul 2, 2009 (permalink)

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.

Reply

JB

Posted on Jul 2, 2009 (permalink)

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

Reply

Daniel Daniel

Posted on Jul 8, 2009 (permalink)

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.

Reply

Jaroslav

Posted on Dec 7, 2009 (permalink)

> 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

Reply

Daniel Daniel

Posted on Dec 10, 2009 (permalink)

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.

Reply

Jaroslav

Posted on Dec 11, 2009 (permalink)

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.

Reply

Daniel Daniel

Posted on Dec 15, 2009 (permalink)

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.

Reply

Jean

Posted on Feb 25, 2010 (permalink)

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

Reply

Jean

Posted on Feb 25, 2010 (permalink)

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 !?

Reply

Daniel Daniel

Posted on Feb 25, 2010 (permalink)

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.

Reply

Jean

Posted on Feb 26, 2010 (permalink)

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

Reply

Philippe GRACA

Posted on Aug 5, 2010 (permalink)

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

Reply

Daniel Daniel

Posted on Aug 10, 2010 (permalink)

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

Reply

Philippe GRACA

Posted on Aug 10, 2010 (permalink)

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

Reply

Kurt

Posted on Oct 22, 2010 (permalink)

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?

Reply

Daniel Daniel

Posted on Oct 28, 2010 (permalink)

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

Reply

JB

Posted on Dec 21, 2010 (permalink)

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

Reply

Jaroslav

Posted on Dec 22, 2010 (permalink)

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

Reply

Daniel Daniel

Posted on Dec 24, 2010 (permalink)

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.

Reply

april

Posted on Nov 9, 2011 (permalink)

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 ?

Reply

Daniel Daniel

Posted on Nov 14, 2011 (permalink)

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

Reply