Techniques for uploading and working on large excel files

Techniques for uploading and working on large excel files

Posted by: realdanielbyrne on 8 September 2017, 10:09 am EST

• 

  realdanielbyrne

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 8 September 2017, 10:09 am EST

  We have a need to upload, download and edit large excel files (14MB). What techniques are recommended for working with large files? For instance should we use the server side excel file upload or the client side version? How do we prevent the browser from issuing a wait or kill message box while uploading a large file? Can we use a Skip/Take paradigm for fetching only the data necessary for the current edit window?

• 

  reeva

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 10:09 am EST

  HEllo,

  Most SpreadJS APIs are designed to automatically invalidate and update the relevant parts of the control when called.
  
  In some cases, particularly when using loops to iterate many cells or ranges to set some change, or just applying many sequential changes in a function, this can cause performance issues when each change is updated immediately.
  
  So you can use suspendPaint() to temporarily suspend the automatic invalidate and update for each change and optimize the performance of loops or large blocks of code.

  InvalidateLayout deletes the current cached layout and forces the layout to be recalculated when the sheet or workbook is painted again; in code you can use invalidateLayout together with repaint to force the layout to be recalculated after some operation which will affect the layout. For example, if you implement code to filter rows or collapse a range group, then the layout of the cells will change; to query the cell rectangle using getCellRect for some cell affected by the change would require using invalidateLayout and repaint methods between the logic which affects the layout and the code calling getCellRect.
  
  Refresh method will make the control resize if necessary (can recompute layout in that case) and repaint its contents; repaint method will use current layout to repaint the content.
  
  Reset method will remove all settings in the sheet (values, formulas, styles, etc.). You can use this method to clear everything from the sheet and recycle an instance of Sheet instead of creating a new instance.

  If you are using a lot of formulas, then the method suspendCalcService can be used to optimize recalculation for cases where many formulas are set in code at once. Normally formulas are calculated immediately when set, but using suspendCalcService you can temporarily disable that until after all your formulas are set, then calculate all at once:
  
  http://sphelp.grapecity.com/webhelp/SpreadSheets10/webframe.html#JavascriptLibrary~GC.Spread.Sheets.Workbook~suspendCalcService.html
  
  http://sphelp.grapecity.com/webhelp/SpreadSheets10/webframe.html#JavascriptLibrary~GC.Spread.Sheets.Workbook~resumePaint.html

  You can try using suspendPaint( ) method for that matter as given in links below. And also there are two pair of functions which may be used to improve bunch value operations performance and they are:- suspendCalcService(), resumeCalcService(), suspendEvent(), resumeEvent()
  
  http://sphelp.grapecity.com/forums/topic/sheet-suspendcalcservice-and-resume-copy-problem/

  Hope it helps.

  Thanks,
  
  Reeva

• 

  realdanielbyrne

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 10:09 am EST

  Thank you for your response. However, uploading a large file 14MB currently is possible, and so suspending painting, etc doesn’t apply as the excelIo.open method never returns before crashing the browser. Do you have any workarounds to this file size limitation?

• 

  reeva

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 10:09 am EST

  Hello,

  Our support Engineer’s are already working on your issue at this forum link:-
  
  http://sphelp.grapecity.com/forums/topic/import-excel-file-error-out-of-memory-exception/

  And they have already posted a bug for the same and our developers are working on your bug too. Our support Engineer will notify you as soon as they will hear anything from the development team about the bug.

  In addition, please do not create multiple cases for the same question, as it creates redundancy in our system and takes longer to get a reply as we have to filter through the duplicate cases and it also creates a lot of confusion as well between the cases and takes a longer time to track the complete thread.

  However, further communication related to this thread will be done on original post here:-
  
  http://sphelp.grapecity.com/forums/topic/import-excel-file-error-out-of-memory-exception/
  
  Or on your original email thread with id #235511.

  Thanks,
  
  Reeva