CopyRange performance issue with formulas

CopyRange performance issue with formulas

Posted by: petri.rasanen on 27 November 2018, 4:46 am EST

• 

  petri.rasanen

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 27 November 2018, 4:46 am EST

  Hi,

  There is a big performance issue with Spread 11.45.20183.
  
  CopyRange method took about 20 seconds when there are formulas on sheet. With version 9.35 same method took under 1 second.
  
  I have attached sample program for both versions with exactly same code. With sample program press “Generate Rows” button and wait about 20 seconds. If you first press “Remove formulas” and after that “Generate Rows” it took about 1 seconds which is ok.
  
  This performance issue is preventing us updating to latest Spread version in our solution.

  Br, Petri

  Spread11.zipSpread9.zip

• 

  jitender.yadav

  

  • Post Options:
  • Link
    Copy

  Posted 28 November 2018, 7:14 am EST

  Hi,

  We can observe the performance issue at our end, so we’ve escalated it to the concerned team. I’ll let you know as soon as we have more information.

  Regards,
  
  Jitender

• 

  jitender.yadav

  

  • Post Options:
  • Link
    Copy

  Posted 3 December 2018, 6:49 am EST

  Hi,

  Spread 11 and 12 use new Calculation Engine which combines continuous cells if they have same formula. So, in your sample all cells in a column are assigned same formula. In each iteration, a new cell is combined to previous cells. Before combining a new cell, Spread evaluates all the previous cell formulae to get the correct value.

  This is why it takes a long time to copy the cells with formula (when these cells are continuous).

  To avoid this problem, you can copy a cell’s value, and formula to a complete range instead of copying them one by one:

  for (int col = 0; col < colcount; ++col)
  {
       sheet.Cells[firstRow + 1, col, count - 1, col].Value = sheet.Cells[firstRow, col].Value;
       sheet.Cells[firstRow + 1, col, count - 1, col].Formula = sheet.Cells[firstRow, col].Formula;
  }
  

  I’ve modified your attached sample so you can see the change. Now, the cells are copied in less than half a second.

  Thanks,
  
  Jitender

• 

  petri.rasanen

  • Post Options:
  • Link
    Copy

  Posted 4 December 2018, 9:10 am EST

  Hi,

  This should solve our problem. Thank you!

  Br,
  
  Petri