Does Flexcel support updating calculated fields?

FLX TMS FlexCel for VCL & FMX
1

Hello,


Does Flexcel support updating calculated fields?  I'm just getting started with Flexcel and am looking for the ability to update values in my spreadsheet and have the calculated values update.

To start with I used a TFlexCelGrid, FlexCelImport and XLSAdapter.  I can open my spreadsheet just fine and see everything.  Double clicking on a cell will show me the underlying formula.  However once I click off to a different cell the cell with the formula just shows a blank.  Double clicking on the cell still shows me the formula so I know it is still there.  Changing any of the input fields does update the value displayed there, but none of the calculated fields on the sheet update.

I'm getting the impression that the tools are geared more for Generate and View and less toward editing.  Is that a correct view?

Thanks,

Mark Elder
2

Hi,

You are right that indeed FlexCelGrid is designed more to view than to edit, and also that FlexCel 3 doesn't recalculate formulas, so when you change any value in the grid, formulas are invalidated (because FlexCel can't know if the value you changed invalidated the formula).


Now, FlexCel 5 comes with a full recalculation engine, and you can use the FlexCelGrid in v3 with an XlsxAdapter, (not XlsAdapter), and have the grid recalculate. FlexCel 5 has a very complete recalculation engine, supporting more than 300 functions, formulas linked to other files, array formulas, circular formulas, etc.

To make the XlsViewer recalculate, you would need to:
1)Open the XlsViewer demo in Delphi.

2)Replace the XlsAdapter by an XlsxAdapter   (in the form, right click->view as text, seach for TXlsAdapter and change it to TXlsxAdapter, right click->view as form->Ok in the warnings, save)

3)Change the code:
procedure TFXlsViewer.UpdateEdCell(const ARow, ACol: integer);
var
  AColor: integer;
  Fmt: TFlxFormat;
begin
  (FlexCelImport.GetWorkbook as TXlsxFile).GetTWorkbook.Recalc;
  FlexCelImport.GetCellFormatDef(ARow, ACol, Fmt);
  if FlexCelImport.IsFormula[ARow, ACol] then EdCell.Text:= FlexCelImport.CellFormula[ARow, ACol]
  else EdCell.Text:=XlsFormatValue1904(FlexCelImport.CellValue[ARow, ACol], Fmt.Format, FlexCelImport.Options1904Dates, AColor);
end;

This line will tell FlexCel to recalculate when you change a cell, because by default FlexCel only recalculates when saving.

If you have Delphi XE or newer, this is it.

If you have an older Delphi version, sadly FlexCel 5 won't run on them. While we are working in a dll to allow older Delphi versions use the xlsx and recalculation support, this isn't ready yet. 

There is an ugly workaround if you are in dlephi < XE and can't wait for the dll, and it implies using a "recalcer.exe" (from http://tmssoftware.net/public/flexcel/tools/Recalcer.zip )

This "recalcer" app is compiled with FlexCel 5 and it just opens the file, recalculates it and resaves it. So you can use code like this in UpdateEdCell:
FlexCelImport.Save(filename)
ShellExecute('open', 'recalcer.exe', filename ....);
FlexCelImport.Open(filename);

This does work (and I know some customers use it) but I wouldn't use unless you really need it.

3

Thanks for the complete answer.  I am on Delphi 2007 right now.  This may be reason enough to push up our Delphi XE conversion.