Recent

Author Topic: Option to show value  (Read 1307 times)

kjteng

  • Full Member
  • ***
  • Posts: 149
Option to show value
« on: February 09, 2019, 04:11:47 am »
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

  • Hero Member
  • *****
  • Posts: 5652
Re: Option to show value
« Reply #1 on: February 09, 2019, 11:39:00 am »
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]
  1. program Project1;
  2.  
  3. uses
  4.   SysUtils,
  5.   fpspreadsheet, fpstypes, xlsxOOXML;
  6.  
  7. var
  8.   b: TsWorkbook;
  9.   sh: TsWorksheet;
  10.   r: Cardinal;
  11.   cell: PCell;
  12. begin
  13.   b := TsWorkbook.Create;
  14.   try
  15.     sh := b.AddWorksheet('Test');
  16.     for r :=0 to 4999 do begin
  17.       cell := sh.WriteNumber(r, 0, r);
  18.       sh.WriteCellProtection(cell, []);      // this cell is not protected
  19.       cell := sh.WriteFormula(r, 1, '=A'+IntToStr(r+1));
  20.       sh.WriteCellProtection(cell, [cpHideFormulas, cpLockCell]);  // protected
  21.     end;
  22.     sh.Protect(true);
  23.     b.WriteToFile('test.xlsx', sfOOXML, true);
  24.   finally
  25.     b.Free;
  26.   end;
  27. end.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 149
Re: Option to show value
« Reply #2 on: February 20, 2019, 08:15:41 am »
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

  • Hero Member
  • *****
  • Posts: 5652
Re: Option to show value
« Reply #3 on: February 20, 2019, 12:46:15 pm »
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.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 149
Re: Option to show value
« Reply #4 on: February 23, 2019, 02:27:22 am »
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.