Recent

Author Topic: Date question [SOLVED]  (Read 12263 times)

totya

  • Hero Member
  • *****
  • Posts: 720
Re: Date question [SOLVED]
« Reply #15 on: December 03, 2016, 01:37:22 pm »
... and here is the sample(s).

Thanks for the "mm" tip, my first code use uppercase everywhere, but I  have seen in Spready, time formats always use lowercase format chars. What context use different case chars?
Time format doesn't use always ":" char, see time example xls.

Thanks for your job, and your many help!

totya

  • Hero Member
  • *****
  • Posts: 720
Re: Date question [SOLVED]
« Reply #16 on: December 03, 2016, 02:07:32 pm »
My improved code, use your instructions:

- fixed time detection
- use " " for space
- use uppercase
- better language rules usage (space)

Code: Pascal  [Select][+][-]
  1.   // Hungarian date syntax...
  2.   procedure FixDateFormat(ASheet: TsWorksheet);
  3.   var
  4.     cell: PCell;
  5.     nf: TsNumberFormat;
  6.     nfs: string;
  7.     NewDateFormat: string;
  8.   begin
  9.     for cell in ASheet.Cells do
  10.     begin
  11.       if cell^.ContentType = cctDateTime then
  12.       begin
  13.         ASheet.ReadNumFormat(cell, nf, nfs);
  14.  
  15.         // Ignore cells with time value
  16.         if (cell^.DateTimeValue > 0)
  17.             and
  18.            (cell^.DateTimeValue = Trunc(cell^.DateTimeValue)) then
  19.         begin
  20.           NewDateFormat:= '';
  21.  
  22.           if Pos('YY', UpperCase(nfs)) >0 then
  23.             NewDateFormat:= NewDateFormat+ 'YYYY." "';
  24.  
  25.           if Pos('MMMM', UpperCase(nfs)) >0 then
  26.             NewDateFormat:= NewDateFormat+ 'MMMM" "' else
  27.               if Pos('MMM', UpperCase(nfs)) >0 then
  28.                 NewDateFormat:= NewDateFormat+ 'MMM" "' else
  29.                   if Pos('MM', UpperCase(nfs)) >0 then
  30.                     NewDateFormat:= NewDateFormat+ 'MM." "' else
  31.                       if Pos('M', UpperCase(nfs)) >0 then
  32.                         NewDateFormat:= NewDateFormat+ 'M." "';
  33.  
  34.           if Pos('DD', UpperCase(nfs)) >0 then
  35.             NewDateFormat:= NewDateFormat+ 'DD.' else
  36.               if Pos('D', UpperCase(nfs)) >0 then
  37.                 NewDateFormat:= NewDateFormat+ 'D.';
  38.  
  39.           ASheet.WriteNumberFormat(cell, nfCustom, NewDateFormat);
  40.         end;
  41.       end;
  42.     end;
  43.   end;
« Last Edit: December 03, 2016, 02:15:00 pm by totya »

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: Date question [SOLVED]
« Reply #17 on: December 03, 2016, 02:57:00 pm »
but my simple code produce better result (almost perfect).
Of course. Too quick and too dirty...
Code: Pascal  [Select][+][-]
  1.   if YearPart <> '' then
  2.     NewDateFormat := YearPart
  3.   else
  4.     NewDateFormat := '';
  5.   if MonthPart <> '' then begin
  6.     if NewDateFormat <> '' then NewDateFormat := NewDateFormat + ' ' + MonthPart else NewDateFormat := MonthPart;
  7.     if Length(MonthPart) <= 3 then NewDateformat := NewdateFormat + '.';
  8.   end;
  9.   if DayPart <> '' then begin
  10.     if NewDateFormat <> '' then NewDateFormat := NewDateFormat + '  ' + DayPart else NewDateFormat  := Daypart;
  11.     if Length(DayPart) <= 2 then NewDateFormat := NewDateFormat + '.';
  12.   end;
(Maybe still buggy, but I only want to show the idea).

What context use different case chars?
Char case simply is ignored. Only the "m" is a problem: If adjacent to "h" or "s" it means "minutes", if adjacent to "y" or "d" it is "months". FPS also, like FPC, accepts 'n' for minutes.

Time format doesn't use always ":" char, see time example xls.
Correct. To be on the safe side you should not make any assumptions when dealing with date/time formats, in particular in foreign languages. The ':' is a symbol which is replaced by the TimeSeparator of the FormatSettings. So, if a user is aware of other cultures opening his xls file he should use the ':' in the time format. Likewise, the '/' in a date format is replaced by the DateSeparator of the FormatSettings. If you rearrange date parts the year-month-day order and hard-code a '.' as a separator, this is not correct from this point of view because users having a '-' or '/' as date separator will see the '.' although they would prefer othe other characters... If you use '/' you will be on the safe side.

Quote
        // Ignore cells with time value
        if (cell^.DateTimeValue > 0)
            and
           (cell^.DateTimeValue = Trunc(cell^.DateTimeValue)) then
I would not rely on the cell values to exclude time formats. A measurement, for example, could write full date/time values (integer + fractional part) into the xls file, but still format the number as time, because, for example, all values happen within the same day, and the column width should be short. Your condition would not detect this case...

Again: If you use the results of the fpspreadsheet format parser (like I did in my previous example) you have a good chance to detect a time format because the escaped parts of the format have been removed (but again: 100% not guaranteed).

totya

  • Hero Member
  • *****
  • Posts: 720
Re: Date question [SOLVED]
« Reply #18 on: December 03, 2016, 06:16:51 pm »
...Likewise, the '/' in a date format is replaced by the DateSeparator of the FormatSettings. If you rearrange date parts the year-month-day order and hard-code a '.' as a separator, this is not correct from this point of view because users having a '-' or '/' as date separator will see the '.' although they would prefer othe other characters... If you use '/' you will be on the safe side.

Thanks, but I want to use my country format, regardless of regional settings. But I tired it, and "/" works. I'm thinking on it, but then I have probems with spaces...

Quote
        // Ignore cells with time value
        if (cell^.DateTimeValue > 0)
            and
           (cell^.DateTimeValue = Trunc(cell^.DateTimeValue)) then
I would not rely on the cell values to exclude time formats. A measurement, for example, could write full date/time values (integer + fractional part) into the xls file, but still format the number as time, because, for example, all values happen within the same day, and the column width should be short. Your condition would not detect this case...

I think time value lost only, if day >0 and if the time is 00:00.

Edit:
if Column witdth sorter than needed, ### appear...

If you use the results of the fpspreadsheet format parser (like I did in my previous example) you have a good chance to detect a time format because the escaped parts of the format have been removed (but again: 100% not guaranteed).

Thank you for many help!
« Last Edit: December 03, 2016, 06:23:49 pm by totya »

 

TinyPortal © 2005-2018