I am loading an Excel spreadsheet which contains many formulas into a dxSpreadsheet control, assigning data to certain cells, and then I am saving the resulting spreadsheet into a BLOB field of a database from which I will later load that spreadsheet back into a dxSpreadsheet, permitting the user to edit this data.
After loading the initial data into the spreadsheet, and before saving it to the BLOB field, I want to sort the spreadsheet. The problem is that one or more of the columns on which I want to sort are based on a formula, and I need a way to instruct the spreadsheet to recalculate these cells before attempting the sort.
I have tried the following statements, both alone and in combination with each other, before calling my sorting code, but the sorting still treats the columns based on formulas as if they were empty.
DelphiSpreadsheet.FormulaController.Calculate; Spreadsheet.Update;
By comparison, when a user loads a previously imported spreadsheet, they can invoke my sorting code and the sorting works correctly on the results of the formulas. However, I want to apply the sort programmatically, as part of the importation process.
How can I instruct the spreadsheet to update all of its calculations so that I can then subsequently apply the sort, prior to saving the spreadsheet to the BLOB field?
Thank you for your input…
Hello Cary,
After loading a file to SpreadSheet, all formulas should be recalculated automatically as well as after sorting. It is difficult to determine the cause of the issue without a sample project. So, would you please provide us with a small text example that we will be able to run on our side and replicate the issue you faced?
For whatever reason, sorting on a column that is based on a formula simply does not work when I am initially importing a spreadsheet and loading it with data. Due to the complexity of the data, it would be very difficult to produce a demonstration of this effect. As a result, I have opted to provide an alternative solution, where I apply the sort each time the user loads the spreadsheet. I don't like this approach, but it works.
Thank you for your feedback.