Programming => Packages and Libraries => FPSpreadsheet => Topic started by: Phoenix on July 18, 2019, 11:45:54 am

Title: [SOLVED] Problem with date without day
Post by: Phoenix on July 18, 2019, 11:45:54 am

I have a problem assigning the cells (via grid editor) with string such as '07/2019 '(I don't want to use DateTime type with format 'mm/yyyy' because I get the same basic problem).
Through internal functions there is no problem but manually typing on the grid is not taken as a string field but as a number (division)  :(.

I looked at the code in
TsCustomWorksheetGrid.SetCellValue () and I have no way to solve.
I thought that having an event that bypasses automatic control could be useful.

maybe something like this:
function (ACol, ARow: Integer; AValue: Variant): TCellContentType of object;

Maybe there is a way to "lock" a cell only to receive a specific TCellContentType without ever losing its memory (for example if it is momentarily emptied).

Also with Worksheet.OnChangeCell () I can't do anything because it would need something before the actual modification.

Do I have any other way?

any help is welcome
Title: Re: Problem with date without day
Post by: wp on July 20, 2019, 12:56:53 pm
This is tough...

I added an option soAutoDetectCellType to the worksheet Options and a property AutoDetectCellType to the worksheet grid which reflects the corresponding worksheet option. When this option is removed the cell's content type is no longer changed when data are entered. This means: you first set the format, then enter the value.

BUT: This will not work for dates. Assume the date format is 'mm/yyyy' and you enter the text '07/2019'. In spite of the predefined and non-changing format, you will get an exception instead because the string converter needs the full date, like in '1/7/2019'. Internally this is done by TryStrToDateTime which works only with full dates; ScanDateTime is probably more appropriate, but needs to be rewritten, not only because the current version raises exceptions, but mainly because it is not clear by which the missing value should be replaced. So I fear, there will not be much improvement.
Title: Re: Problem with date without day
Post by: Phoenix on July 20, 2019, 09:55:31 pm
I just tried the new addition ..
The soAutoDetectCellType option solves the biggest problem concerning memory loss of the contained type. This problem was both if I used UTF8String and DateTime.

This will not work for dates

In fact, if I continue to use UTF8String even with the modification it will not work. If I insert '07 / 2019 'it changes it to '12 / 1899'

But now I can go back to the DateTime type:
this shows the desired result (even if in editing mode you see the full date)

So the result is acceptable.  :)

I did another test (modifying the source).

Code: Pascal  [Select][+][-]
  2. TOnWriteCellValueAsString =
  3.    procedure (ACell: PCell; AValue: String; var IsPureText: Boolean) of object;
  5. procedure TsWorksheet.WriteCellValueAsString()
  6. ..
  7. begin
  8.   if ACell = nil then
  9.     exit;
  11.   DeleteFormula(ACell);
  13.   if AValue = '' then
  14.   begin
  15.     WriteText(ACell, '');
  16.     exit;
  17.   end;
  19.   if Assigned(FOnWriteCellValueAsString) then
  20.   begin
  21.    IsPureText := False;
  22.    FOnWriteCellValueAsString(ACell,AValue, IsPureText);
  23.    if IsPureText then
  24.    begin
  25.      WriteText(ACell, AValue);
  26.      exit;
  27.    end;
  28.   end;
  29. ..

using this I could continue to use UTF8String without problems (I only did a quick but positive test). This would be the optimal result but not knowing the code well I don't know if the addition is perfect. But perhaps as an idea can be considered?

  Thank's for your job!!  :)
Title: Re: Problem with date without day
Post by: wp on July 20, 2019, 11:01:17 pm
I think an event is not needed. There is already the possibility to format cells as text which avoids all detection routines and inserts the text literally:
Code: Pascal  [Select][+][-]
  1.    worksheet.WriteNumberFormat(0, 0, nfText);

In addition you can always bypass detection routines by preceding text with an apostrophe, i.e. when you type '7/2000 the text will not be interpreted as a fraction and be inserted as "7/2000".

These features did not work correctly so far, they should be better now.

As I noticed in Spready there is an issue when applying formats to cell ranges (format applied only to the first cell). I try to fix it...
Title: Re: Problem with date without day
Post by: Phoenix on July 21, 2019, 12:07:09 am
ok i tried and it works perfectly

Thank you!!  :D
TinyPortal © 2005-2018