Populating FarPoint Spread in C# with huge chunk of Data (64-bit Spreadsheet I

Populating FarPoint Spread in C# with huge chunk of Data (64-bit Spreadsheet I

Posted by: abhishek1991rana on 21 December 2018, 12:05 am EST

• 

  abhishek1991rana

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 21 December 2018, 12:05 am EST

  In C# 64-bit, i am trying to populate a FarPoint Spreadsheet with approx 70000 rows. The entire data gets loaded on spredsheet after taking 3-4 hours of time duration, which makes the entire process to have lot of performance issues.

  Currently i am populating the data to spreadsheet by individual cells.Is there anything i can do in order to increase the performance of this issue i am facing??

  Below is my code template to populate the spreadsheet by individual cells.

  Public void PopulateSpreadsheet()
  
  {
  
  FarPoint.Win.Spread.FpSpread SS;

          SS.SuspendLayout();
          int i = 0;
          int Rows = 70000;
  
          while( i < Rows)
              {
                SS.ActiveSheet.ActiveCell.Text = Data to populate;
              }
  
          SS.ResumeLayout();
  

  }
  
  Please guide me how to improve the performance. Any Help is appreciated!! Thank You in Advance

• 

  jitender.yadav

  

  • Post Options:
  • Link
    Copy

  Posted 23 December 2018, 5:42 pm EST

  Hi,

  I used your code to populate some random numbers, with 70000 row and 10 columns. The data was loaded in less than 15 seconds.

  Depending on the data you’re loading, you may need to disable AutoCalculation, AutoUpdateNotes, and FormulaParsing before setting values. Please see if using these properties/methods solves your issue.

  If this does not help, then please attach a small sample where we can observe this performance issue, and investigate it further.

  For your reference, I’ve also attached the sample I used. You can also modify this sample.

  Thanks,
  
  Jitender

  LargeSpreadsheet.zip

• 

  abhishek1991rana

  • Post Options:
  • Link
    Copy

  Posted 23 December 2018, 6:17 pm EST

  Hello Jitender,

  I saw your sample code attached in the above comment, You are basically trying to populate integer values which takes less significant time to populate. I am trying to populate data which consists of images, string as well as integer. Each Integer value can range upto 12 13 digits and each string can range upto 12 13 characters.

  Thanks,
  
  Abhishek

• 

  jitender.yadav

  

  • Post Options:
  • Link
    Copy

  Posted 23 December 2018, 7:51 pm EST

  Abhishek,

  If there are common images which are used in multiple cells then you should load them before settings values. I have modified my previous sample to include Images and String values. The Spread still takes around 35 seconds.

  It is very likely that you can resolve this performance issue if you can load your images once (for instance in an ImageList) and then use them, instead of loading them for every cell.

  Also, can you mention approximately how many distinct images you have, and what is their dimension?

  Let me know if you cannot resolve this even after these modifications.

  Regards,
  
  Jitender

  LargeSpreadsheet_Images_String.zip

• 

  abhishek1991rana

  • Post Options:
  • Link
    Copy

  Posted 25 December 2018, 10:03 pm EST

  Hello Jitender,

  I am trying to populate data from a struct. Each time, 1st record is fetched from the structure and then the data from structure is accessed and populated on the FP spreadaheet and then the 2nd record is fetched and populated and it goes on till last record (almost 70000 rows).

  I think this process also hit the performance issue. what’s your take over it?

  Thanks
  
  Abhishek

• 

  jitender.yadav

  

  • Post Options:
  • Link
    Copy

  Posted 25 December 2018, 11:27 pm EST

  Hi Abhishek,

  By struct do you mean that you’re fetching a single record from the database? If so, it would definitely affect the performance. Is there a specific reason you’re not fetching the records in bulk? You can load 1000 or so records at a time instead of fetching a single record.

  Can you test the performance without Spread? Just load the data records one by one, and benchmark it. This would tell us how much of the performance impact is due to record fetching.

  Regards,
  
  Jitender