Export to Excel limit

33 posts, 0 answers
  1. Leo
    Leo avatar
    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?

  2. Princy
    Princy avatar
    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.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Daniel
    Admin
    Daniel avatar
    4943 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.
  5. Leo
    Leo avatar
    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?
  6. Daniel
    Admin
    Daniel avatar
    4943 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.
  7. JB
    JB avatar
    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.


  8. Daniel
    Admin
    Daniel avatar
    4943 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.
  9. JB
    JB avatar
    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
  10. Daniel
    Admin
    Daniel avatar
    4943 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.
  11. Jaroslav
    Jaroslav avatar
    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

     

  12. Daniel
    Admin
    Daniel avatar
    4943 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.
  13. Jaroslav
    Jaroslav avatar
    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.
  14. Daniel
    Admin
    Daniel avatar
    4943 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.
  15. Jean
    Jean avatar
    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
  16. Jean
    Jean avatar
    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 !?
  17. Daniel
    Admin
    Daniel avatar
    4943 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.
  18. Jean
    Jean avatar
    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
  19. Philippe GRACA
    Philippe GRACA avatar
    29 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
  20. Daniel
    Admin
    Daniel avatar
    4943 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
  21. Philippe GRACA
    Philippe GRACA avatar
    29 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
  22. Kurt
    Kurt avatar
    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?
  23. Daniel
    Admin
    Daniel avatar
    4943 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
  24. JB
    JB avatar
    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
  25. Jaroslav
    Jaroslav avatar
    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
  26. Daniel
    Admin
    Daniel avatar
    4943 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.
  27. april
    april avatar
    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 ?

  28. Daniel
    Admin
    Daniel avatar
    4943 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
  29. Jayesh Goyani
    Jayesh Goyani avatar
    2732 posts
    Member since:
    May 2010

    Posted 14 Nov 2011 Link to this post

    Hello april,

    Please also check this link.

    Thanks,
    Jayesh Goyani
  30. Adam
    Adam avatar
    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.
  31. Jayesh Goyani
    Jayesh Goyani avatar
    2732 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
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017