This is a migrated thread and some comments may be shown as answers.
Background Processing
3 Answers 13 Views
This is a migrated thread and some comments may be shown as answers.
steve
Top achievements
Rank 1
steve asked on 12 Feb 2019, 02:57 AM
Before I start down the path of using RadSpreadsheet I want to verify that it will do what I want.  
I would like to use the spreadsheet to do some custom background calculations in a SaaS environment.  The end user would create their own spreadsheets with links to data in their database (maybe using Custom Functions).  The spreadsheet would be calculated in the backend every few minutes, pulling in updated data and solving the spreadsheet.  The output cells (again using Custom Functions) would update the customer's data tables.
Is that a viable use-case for RadSpreadsheet?  I realize the input and output connections to the external data is not part of RadSpreadsheet but it seems like Custom Functions might get me there.
Is there any information on the order of solving calculations in formulas cells?   

3 Answers, 1 is accepted

Sort by
0
Boby
Telerik team
answered on 14 Feb 2019, 02:37 PM
Hi Steve,

The scenario seems feasible. The cells that contain formulas are automatically updated when the referenced cell values are updated. You can read more in the Cell References help article. For custom functions, the Custom Functions documentation article or  CustomFunctions SDK example may help.

Regards,
Boby
Progress Telerik
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
Dmitry
Top achievements
Rank 1
answered on 21 Jan 2021, 07:36 AM

Sorry for bumping an old thread, but it seems like a valid place for my question.

 

I've kinda successfully implemented background processing in my console app. I've implemented custom functions, and in a background thread I use those formulas to get and set the data from our server. The code sample is attached below.

 

My only problem is that I can't find a way (if it even exists) to force recalculate the formulas in fields. If I just call the GetValue() method, nothing happens, because no cells have their value changed before the formulas are calculated, and nothing is calculated before the values change...

I've used a hack to reserve "A1" cell as a seed cell, outputting a random number there, and using the cell as a reference to all my input cells, but it's not so user-friendly to our customers to work with.

Maybe you can give me a clue, how to make formulas be force recalculated (be it in a cell, or a whole worksheet)?

 

Thanks in advance. Here's how I update the values so far, maybe will be a help to someone in a future.

for (var row = usedCellRange.FromIndex.RowIndex; row <= usedCellRange.ToIndex.RowIndex; row++)
{
  for (var column = usedCellRange.FromIndex.ColumnIndex; column <= usedCellRange.ToIndex.ColumnIndex; column++)
  {
    var cellValue = worksheet.Cells[row, column].GetValue().Value;
    if (cellValue.ValueType == CellValueType.Formula)
    {
      cellValue.GetResultValueAsString(CellValueFormat.GeneralFormat);
    }
  }
}
0
Dimitar
Telerik team
answered on 25 Jan 2021, 10:21 AM

Hi Dmitry,

I have tested this and it works on my side (see the attached project). Perhaps this is caused by the way the cells are updated in your code. 

In addition, we have a feature request for an API that will force the recalculation: SpreadProcessing: Provide an API used to recalculate all formulas in the document.

I am looking forward to your reply.

Regards,
Dimitar
Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

Asked by
steve
Top achievements
Rank 1
Answers by
Boby
Telerik team
Dmitry
Top achievements
Rank 1
Dimitar
Telerik team
Share this question
or