Timeformat-Cells are in TsWorksheetGrid not editable, bug or feature?
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
Soner · 2018-08-04 11:43

Timeformat-Cells are in TsWorksheetGrid not editable, bug, feature or
do I have to do something extra?

To reproduce the problem follow the next steps or try it with spready
example application.

1. Create new project and place on the form one TsWorksheetGrid and
TButton.

2. Make Grid editable.(--> sWorksheetGrid1.Options.goEditing:=true)

3. Write in Buttons onclick handler this:
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 uses
fpsTypes;procedure TForm1.Button1Click(Sender: TObject);begin 
sWorksheetGrid1.Worksheet.WriteNumberFormat(0,0,nfGeneral);end;  4.
Run the example and enter in the cell A1 an hour i.e.:
10:30

5. Now try to change the cell A1, you can't.

6. Click on Button1 to change Cell-A1 format to default number.

7. Now you can edit the cell A1, but every time, when you enter a time
the cell-format changes again to time-format and you cant edit
anymore.

----------------------------------------------------------------------
Soner · 2018-08-04 13:08

It seems to be a bug.
You can not change time entry to other time entry but you can change
time to a number i.E:
Changing 3:30 to 4:30 is not allowed, but you can change in two steps.
First to 4 then to 4:30 .

----------------------------------------------------------------------
wp · 2018-08-04 15:51

Seems to be a left-over of some "optimization"...

Since I am expecting side-effects could you please try this:
Open fpspreadsheet.pasFind procedure
TsWorksheet.WriteCellValueAsStringThere are several "if
TryStrTo<Something>" calls.Find the block with "if TryStrToDateTime"
and replace it by thisCode: Pascal  [Select][+][-]window.onload =
function(){var x1 = document.getElementById("main_content_section");
if (x1) { var x = document.getElementsByClassName("geshi");for (var i
= 0; i   if TryStrToDateTime(AValue, number, AFormatSettings) then 
begin    if (number < 1.0) or IsTimeFormat(numFmtParams) then        
 // this is a time alone    begin      ucValue := Uppercase(AValue); 
   isAMPM := (pos('AM', ucValue) > 0) or (pos('PM', ucValue) > 0);   
 isLongTime := IsLongTimeFormat(AValue,
AFormatSettings.TimeSeparator);      WriteDateTime(ACell, number,
TIME_FMT[isAMPM, isLongTime]);    end else    if frac(number) = 0.0
then  // this is a date alone    begin      if pos(' ', AValue) > 0
then        WriteDateTime(ACell, number, nfShortDateTime)      else  
    WriteDateTime(ACell, number, nfShortDate);    end else    if not
IsDateTimeFormat(fmt.NumberFormat) then      WriteDateTime(ACell,
number, nfShortDateTime)    else      WriteDateTime(ACell, number);  
if IsTextFormat(numFmtParams) then    begin     
WriteNumberFormat(ACell, nfText);      WriteText(ACell, AValue);   
end;    exit;  end;

----------------------------------------------------------------------
Soner · 2018-08-04 16:35

I did it in:
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 procedure
TsWorksheet.WriteCellValueAsString(ACell: PCell; AValue: String;
 const AFormatSettings: TFormatSettings);//...  // Check for a
date/time value:  // Must be after float detection because
StrToDateTime will accept a string  // "1" as a valid date/time.   //
^after this comment, from line ~#5209 I used trunk version 6568 from
lazarus-ccr (today ~12:00 O'Clock. )

It works.
But I can not say something about side effects because I don't use
this component. Today, I took closer look to this component, because I
am thinking to use it. Maybe I take KGrid and do calculations myself.


 There is second issue. I explain it with example.
When you have a formula in a3 like this:
=a1+a2
And the cells a1 have the value 1 and a2 2 then the result is 3 and
that is right.
Now when you change the value a1 to some string like this 'hello' then
the result of formula 2, because fpspreadsheet calculates the string
cells as 0.
That is wrong. It should something like "NaN", "Not a Number" or
something like this.
The user have to see that something is wrong with entries and correct
it.

----------------------------------------------------------------------
wp · 2018-08-05 15:51

Fixed in r6570.

----------------------------------------------------------------------
Soner · 2018-08-06 07:58

Thanks.