Option to show value
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
kjteng · 2019-02-09 03:11

Currently fpspread would show formula of cell when workbookSource is
not on boAutoCalc.
Is it possible to have an option to use the old style i.e. show
calculated values, despite that the cell value may not be updated ?  I
am requesting for this because many users are not confortable when
they see the formula appear (especially when they know most of the
cell values are not affected by small change that they keyed in).

----------------------------------------------------------------------
wp · 2019-02-09 10:39

You mean the display of the formula in the TsCellEdit?

I don't know whether it is a good idea to display the calculated value
only because there's nothing to protect the users from typing some
values where there used to be a formula. Did you try to use cell and
worksheet protection?

The following code protects the entire worksheet except for the cells
which already contain a number. Formula cells in addition have the
protection flag to hide the formula:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
Project1; uses  SysUtils,  fpspreadsheet, fpstypes, xlsxOOXML; var  b:
TsWorkbook;  sh: TsWorksheet;  r: Cardinal;  cell: PCell;begin  b :=
TsWorkbook.Create;  try    sh := b.AddWorksheet('Test');    for r :=0
to 4999 do begin      cell := sh.WriteNumber(r, 0, r);     
sh.WriteCellProtection(cell, []);      // this cell is not protected 
   cell := sh.WriteFormula(r, 1, '=A'+IntToStr(r+1));     
sh.WriteCellProtection(cell, [cpHideFormulas, cpLockCell]);  //
protected    end;    sh.Protect(true);    b.WriteToFile('test.xlsx',
sfOOXML, true);  finally    b.Free;  end;end.

----------------------------------------------------------------------
kjteng · 2019-02-20 07:15

Nope. I mean the display of cell contentw in the worksheetGrid. 
TsWorksheetGrid was recently changed recently to show cell formulal
(instead of showing the previous calculated value of each cell as in
Excel/open office).

Most of the end users are more used to the Excel/open office. Most
users are confused by when the formulas appears in the workgrid and
assume it is to be a program bug.

Can we have an option to choose 'show formulas' or 'show values' for
the wsGrid component?

----------------------------------------------------------------------
wp · 2019-02-20 11:46

The trunk version of fpsworksheetgrid now has a property
"ShowFormulas" which shows/hides formulas in the grid cells when
AutoCalc is off. The property name is a bit misleading because it
suggests that formulas can be shown also when AutoCalc is ON, which is
not true. But let's see what happens.

----------------------------------------------------------------------
kjteng · 2019-02-23 01:27

TQVM.  ShowFormula works.
Slight difference from Excel (e.g. cell display blank when new formula
is keyed in to a cell) but it is ok for me.