Background Processing

[steve]

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?   

[Boby]

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

[Dmitry]

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);
    }
  }
}

[Dimitar]

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