but my simple code produce better result (almost perfect).
Of course. Too quick and too dirty...
if YearPart <> '' then
NewDateFormat := YearPart
else
NewDateFormat := '';
if MonthPart <> '' then begin
if NewDateFormat <> '' then NewDateFormat := NewDateFormat + ' ' + MonthPart else NewDateFormat := MonthPart;
if Length(MonthPart) <= 3 then NewDateformat := NewdateFormat + '.';
end;
if DayPart <> '' then begin
if NewDateFormat <> '' then NewDateFormat := NewDateFormat + ' ' + DayPart else NewDateFormat := Daypart;
if Length(DayPart) <= 2 then NewDateFormat := NewDateFormat + '.';
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.
// 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).