Export to Excel limit

[Leo]

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]

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]

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]

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]

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]

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]

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]

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]

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]

>  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]

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]

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]

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]

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]

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]

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]

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]

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]

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

[Philippe GRACA]

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]

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]

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

[JB]

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]

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]

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

[april]

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]

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

[Jayesh Goyani]

Hello april,

Please also check this link.

Thanks,
Jayesh Goyani

[Adam]

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]

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

[Adam]

The control should allow you to write directly to the response stream rather than building the file in RAM and then streaming the entire file through the response stream.

This should actually be pretty simple -- I'd imagine that currently the file is written to a memory stream.  Just change that to the response stream, problem solved.

If the data set itself is too large, then you simply load chunks of it.  At this point we are probably over the limit of what should be done by a web server - but the limitations that you're describing here are created by the constraints of your controls.

[Jayesh Goyani]

Hello,

Sorry but i have no idea why they use this method.
But i am sure that there must be some reason to do this.

Note : But to avoid this issue You can export large amount of record by using Telerik Report,RDLC report, Crystal Report, RDL report.

Thanks,
Jayesh Goyani

[Mohamed Salah Al-Din]

actually even if the grid successfully exported 100K you will face another problem which is network error....., so i think we have to accept the limitation of working in WEB DEVELOPMENT, and it's better to come with a new ideas not just stop, we should look out of the box, and i prefer Telerik to support Export Paging with the Max Allowed records in a separate sub-control like the pager control or the ToolBar control

[Abhijit]

When perform ExportToExcel() function, excel sheet is downloading with size 18 MB and but actual size of file is 9 MB only.

Why it is downloading with double size, do not have idea. Can some body help on this?

[Kalai]

Hi Team,

I am trying to export RadGrid data to excel. My Radgrid dont have any nested grid or controls and length of character occurred in each cell having maximum 35 char.  I can export 42000 rows and 5 columns of data in 14mins. While i am trying to export 50000, getting "out of memory exception". My Excel version  is 2013. Any update regarding this issue? How to improve performance?

I referred below link

https://docs.telerik.com/devtools/aspnet-ajax/controls/grid/functionality/exporting/overview#export-large-amount-of-data

 

[Eyup]

Hi Kalai,

RadGrid provides various excel formats:
https://demos.telerik.com/aspnet-ajax/grid/examples/functionality/exporting/excel-export/defaultcs.aspx
https://demos.telerik.com/aspnet-ajax/grid/examples/functionality/exporting/pdf-export/defaultcs.aspx

But I'm afraid it is not suitable for exporting large amount of data:
http://docs.telerik.com/devtools/aspnet-ajax/controls/grid/functionality/exporting/overview#export-large-amount-of-data
http://www.telerik.com/forums/system-outofmemory-exception-while-exporting-telerik-grid-data#6viUZtAzT0SLWwX3EfTauQ

If you want to reduce the exporting time, the only viable option would be to export only a portion of the items by removing or disabling the IgnorePaging property.

You can also check RadSpreadStreamProcessing for building the data in the exported file manually, but please bear in mind this would be a custom endeavor and beyond our support scope:
https://demos.telerik.com/aspnet-ajax/spreadstreamprocessing/large-document-export/defaultcs.aspx
https://docs.telerik.com/devtools/aspnet-ajax/controls/spreadstreamprocessing/overview.html

I really want to to offer you something feasiable, but I'm afraid it won't be possible in this case. The count of records is too much and this kind of massive exporting is just not supported with RadGrid.

Regards,
Eyup
Progress Telerik