SetValue performance issue

SetValue performance issue

Posted by: petri.rasanen on 19 March 2019, 1:43 am EST

• 

  petri.rasanen

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 19 March 2019, 1:43 am EST

  Hi,

  When setting values to spread sheet, the performance is really slow when cells have reference to formula (in this case IF formula). Setting value to 500 cells takes about 16 seconds. Attached is a small sample program to test the issue. Spread version is 12.45.20181.0.

  Br,
  
  Petri

  Spread12.zip

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 19 March 2019, 10:06 pm EST

  Hello Petri,

  This issue happens when you are setting value in cells after opening Excel file.

  However, if you are just setting the values in cell without opening the Excel file then it takes 0.0048 seconds only to set values in cell.

  But we are still escalating it to our concerned team for further investigation. We will get back to you as soon as we hear anything from them.

  Tracking id for your issue is #270880

  Thanks,
  
  Reeva

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 21 March 2019, 3:07 pm EST

  Hello,

  Our developers have confirmed that this bug happens because logic get editor and check editor.Isvalid of sheetview when set value. When get Editor, spread should recalculate for Conditional formatting (CF) to compose style. With the large CF range ($B$8:$Q$3000), spread should re-calculate for multi cell from ($B$8:$Q$3000) so this issue happens. Hence, you should use logic set value from cell like below.
  
  Hence, please change the code in the button3_click with this code to resolve the issue:-

  private void button3_Click(object sender, EventArgs e)
  {
  ............
                  //sheet.SetValue(i, column, 10.0);
                   sheet.Cells[i, column].Value= 10.0;
  ............
  }
  
  

  Hope it helps.

  Thanks,
  
  Reeva