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?
36 Answers, 1 is accepted
Rank 2
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.
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.
Rank 1
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?
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.
Rank 2
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.
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.
Rank 2
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
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.
Rank 1
> 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
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.
Rank 1
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.
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.
Rank 1
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
Rank 1
But I absolutely need a solution...
Some ideas !?
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.
Rank 1
No problem ! Indeed I understand :)
But do you have an idea how I could do for recalcitrant users ? :D
Thanks for you help.
Jean
Rank 1
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
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
Rank 1
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
Rank 1
Is this something that might be supported in a future version?
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
Rank 2
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
Rank 1
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
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
Rank 1
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 ?
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
Rank 2
Rank 1
Rank 2
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
Rank 1
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.
Rank 2
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
Rank 1
Rank 1
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?
Rank 1
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
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
Rank 1
Iron
When grid contains more than 30k records then i am getting 500 Error.
This is my code
@(Html.ReportsGrid
<apexportal.WebService.Models.AuditReportResult>()
.HtmlAttributes(new { @style = "height: 500px;" })
.Name("AuditReportGrid")
.Excel(e => { e.FileName("AuditReport.xlsx").AllPages(true); })
.Events(e => { e.ExcelExport("excelExportCheck"); })
.AutoBind(false)
.DataSource(datasource => datasource
.Custom()
.Type("aspnetmvc-ajax")
.Transport(t => t.Read(r => r.Action("Read", "AuditReport").Data("getAdditionalData")))
.Schema(s => s
.Data("Data")
.Model(model =>
{
model.Id(itm => itm.VR_Audit_LogID); //("VR_Audit_LogID");
model.Field("VendorID", typeof(string));
model.Field("ActionTime", typeof(DateTime));
model.Field("ReviewedDate", typeof(DateTime));
})
.Total("Total")
)
.ServerSorting(true)
.ServerPaging(true)
.ServerFiltering(true)
.ServerGrouping(false)
.ServerAggregates(false)
.Events(events => events.Error("error_handler"))
)
.Columns(columns =>
{
columns.Bound(portal => portal.VR_ID).Title("VR ID".Translate())
.HeaderHtmlAttributes(new { @class = "NumericHeaderAlign" }).HtmlAttributes(new { @class = "NumericRowAlign" }).Width(180)
.Filterable(x => x.Cell(y => y.Template("NumericFilter")));
if (userACL.IsRegistrationEnabled &&!userACL.IsCustomerRegistrationEnabled)
{
columns.Bound(portal => portal.VendorID).Title("Vendor ID".Translate())
.HeaderHtmlAttributes(new { @class = "StringHeaderAlign" }).HtmlAttributes(new { @class = "StringRowAlign" }).Width(150);
}
if (userACL.IsCustomerRegistrationEnabled)
{
columns.Bound(portal => portal.VendorID).Title("Entity ID".Translate())
.HeaderHtmlAttributes(new { @class = "StringHeaderAlign" }).HtmlAttributes(new { @class = "StringRowAlign" }).Width(150);
}
columns.Bound(portal => portal.CompanyName).Title("Company Name".Translate())
.ClientTemplate("#=buildCompanyNameLink(CompanyName, '" + @Url.Action("GetCompanyUrl") + "')#")
.HeaderHtmlAttributes(new { @class = "StringHeaderAlign" }).HtmlAttributes(new { @class = "StringRowAlign" }).Width(200);
columns.Bound(portal => portal.ModifiedFieldName).Title("Field Name".Translate())
.HeaderHtmlAttributes(new { @class = "StringHeaderAlign" }).HtmlAttributes(new { @class = "StringRowAlign" }).Width(250);
columns.Bound(portal => portal.DecryptedOriginalValue).Title("Original Value".Translate())
.HeaderHtmlAttributes(new { @class = "StringHeaderAlign" }).HtmlAttributes(new { @class = "StringRowAlign" }).Width(150);
columns.Bound(portal => portal.DecryptedNewValue).Title("New Value".Translate())
.HeaderHtmlAttributes(new { @class = "StringHeaderAlign" }).HtmlAttributes(new { @class = "StringRowAlign" }).Width(150);
columns.Bound(portal => portal.ChangedBy).Title("Changed By".Translate())
.HeaderHtmlAttributes(new { @class = "StringHeaderAlign" }).HtmlAttributes(new { @class = "StringRowAlign" }).Width(150);
columns.Bound(portal => portal.ChangedByFirstName).Title("Changed By First Name".Translate())
.HeaderHtmlAttributes(new { @class = "StringHeaderAlign" }).HtmlAttributes(new { @class = "StringRowAlign" }).Width(150);
columns.Bound(portal => portal.ChangedByLastName).Title("Changed By Last Name".Translate())
.HeaderHtmlAttributes(new { @class = "StringHeaderAlign" }).HtmlAttributes(new { @class = "StringRowAlign" }).Width(150);
columns.Bound(portal => portal.RegistrationType).ClientTemplate("#= RegistrationType == null ? '' : TranslateText(RegistrationType) #").Title("Registration Type".Translate())
.HeaderHtmlAttributes(new { @class = "StringHeaderAlign" }).HtmlAttributes(new { @class = "StringRowAlign" }).Width(150);
if (userACL.IsCustomerRegistrationEnabled && userACL.IsRegistrationEnabled)
{
columns.Bound(portal => portal.ProfileType).ClientTemplate("#= ProfileType == null ? '' : TranslateText(ProfileType) #").Title("Profile Type".Translate())
.HeaderHtmlAttributes(new { @class = "StringHeaderAlign" }).HtmlAttributes(new { @class = "StringRowAlign" }).Width(150);
}
columns.Bound(portal => portal.Action).ClientTemplate("#= Action == null ? '' : TranslateText(Action) #").Title("Action".Translate())
.HeaderHtmlAttributes(new { @class = "StringHeaderAlign" }).HtmlAttributes(new { @class = "StringRowAlign" }).Width(110);
//columns.Bound(portal => portal.PRIMARY_KEY).Title("Primary Key".Translate()).HtmlAttributes(new { @style = "white-space: nowrap;" }).Width(110);
columns.Bound(portal => portal.DataSource).Title("Data Source".Translate())
.HeaderHtmlAttributes(new { @class = "StringHeaderAlign" }).HtmlAttributes(new { @class = "StringRowAlign" }).Width(110);
columns.Bound(portal => portal.ActionTime).Title("Action Time".Translate()).Format("{0:G}")
.HeaderHtmlAttributes(new { @class = "DateHeaderAlign" }).HtmlAttributes(new { @class = "DateRowAlign" }).Width(150);
columns.Bound(portal => portal.ReviewMode).ClientTemplate("#= ReviewMode == null ? '' : TranslateText(ReviewMode) #").Title("Review Action".Translate())
.HeaderHtmlAttributes(new { @class = "StringHeaderAlign" }).HtmlAttributes(new { @class = "StringRowAlign" }).Width(110);
columns.Bound(portal => portal.ReviewedBy).Title("Reviewed By".Translate())
.HeaderHtmlAttributes(new { @class = "StringHeaderAlign" }).HtmlAttributes(new { @class = "StringRowAlign" }).Width(110);
columns.Bound(portal => portal.ReviewedDate).Title("Reviewed Date".Translate()).Format("{0:G}")
.HeaderHtmlAttributes(new { @class = "DateHeaderAlign" }).HtmlAttributes(new { @class = "DateRowAlign" }).Width(150);
}
)
.Filterable(filterable => filterable
.Extra(false)
.Operators(operators => operators
.ForDate(str => str.Clear()
.IsNotEqualTo("Is not equal to".Translate())
.IsGreaterThanOrEqualTo("Is after or equal to".Translate())
.IsGreaterThan("Is after".Translate())
.IsLessThanOrEqualTo("Is before or equal to".Translate())
.IsLessThan("Is before".Translate())
.IsNull("Is Null".Translate())
.IsNotNull("Is Not Null".Translate()))))
)
}
@Html.GridErrorHandler()
@Html.GridResizeHandler("AuditReportGrid")
@CustomHtml.GridContextMenu(Html, "AuditReportGrid")
@CustomHtml.EnableFormValidation("SearchForm", "searchbtn")
Hi Sajid, it looks like you're asking about an issue with a Telerik MVC grid displaying more than 30k records and receiving a 500 error. However, this forum is specifically for RadGrid in ASP.NET AJAX.
I recommend posting your question in the Telerik MVC forums, where the community and support team can provide more relevant assistance. You can find the Telerik MVC forums here: Telerik UI for ASP.NET MVC Forum.
This will ensure you get the best possible help for your issue!
Rank 1
Iron
Hi Rumen,
I am asking about the downloading more than 50000 records into excel. then i am getting this error.
Hi Sajid,
I understand that you're facing an issue while downloading more than 50,000 records into Excel. However, as mentioned earlier, this forum is specifically for RadGrid in ASP.NET AJAX, while your issue relates to Telerik UI for ASP.NET MVC Grid which is a different component.
If you want to export large amount of data with RadGrid follow the instructions in this KB article: Export large amount of data to PDF, XLSX and CSV using the Telerik Document Processing libraries.
For exporting large data with the Kendo UI Grid check this article: Export Grid to Excel with the RadSpreadStreamProcessing Library.
To get the most relevant assistance, I recommend posting your question in the Telerik UI for ASP.NET MVC Forum.
This will ensure you receive help from the right community and support team.