Not answered Export to Excel limit

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?

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.

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

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?

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

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.


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.

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

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

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

>  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

 

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

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.

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