Date question [SOLVED]

[totya]

Hi!

For example, in excel file I have a cell, and this cell contain a date, for example: 1977.04.01, but if I open this excel file with your component, I see only: 77.04.01

How can I solve this situation? I want to see the full format of date YYYY.MM.DD (or in excel stored format).

Thanks!

[wp]

Did you write the file yourself? In this case you should specify the format requested in the writing command. There are several possibilities:

(1) If you want to have the format 'yyyy.mm.dd' always - even on systems with different language settings, for example where the year comes last:

Code: Pascal  [Select][+][-]

1.   MyWorksheet.WriteDateTime(row, col, Now(), 'yyyy.mm.dd');  

(2) If you want the long year format of every system  (on my computer, however, it uses an abbreviated month name instead of the two-digit number) use

Code: Pascal  [Select][+][-]

1.   Myworksheet.WriteDateTime(row, col, Now(), nfLongDate);

If the file was written by Excel or some other application you might be successful if you change the FormatSettings used by the workbook. Spready has a dialog for this (Settings / Number format settings).

Let me explain this:

In your sample file, the cell is formatted with format "14" which is a locale dependent format. FPSpreadsheet does not contain a table of pecularities of all countries regarding number formatting, and uses the ShortDateFormat for the workbook's formatSettings to format this date as a string. Therefore, you can open this dialog (before reading the file) and set the ShortDateFormat to 'yyyy.mm.dd'. Then, when the file is read the cell will be formatted with this format string.

Unfortunately this feature has stopped working some time ago. But in the new revision it is fixed.

How do you know that you have to modify the ShortDateFormat? Open the "Inspector" of Spready ("View" / "Inspector"), select page "cell properties". Click on the cell, and in the line "NumberFormat" of the Inspector you can read the date format symbol: if it is nfShortDate then you must adapt the FormatSettings.ShortDateFormat, if it is nfLongDate then you adapt FormatSettings.LongDateFormat.

[totya]

Hi!

Thanks for the quick answer and and the quick fix!

I see you modified the shortdateformat from yy to yyyy, this is okay for me, because now I see the date is 19xx or 20xx But I will see your instructions later (today or weekend).

Did you write the file yourself?

No, I usually never modified the excel file, I only read this.

Thanks!

[totya]

Hi!

As I wrote, I tested it.

Code: Pascal  [Select][+][-]

1.         with Workbook.FormatSettings do
2.         begin
3.           ShortDateFormat:= 'yy.mm.dd';
4.           LongDateFormat:= 'yy.mm.dd';
5.         end;

Well, the ShortDateFormat setting is working, but the LongDateFormat settings doesn't work. See the example file (third line).

[wp]

BIFFExplorer and Spready's inspector tell me that this cell has a custom format 'YYYY/\ MMMM/\ DD\' - this means

• four-digit year
• the '/' is replaced by the DateSeparator of the Workbook's FormatSettings
• the '\ ' means: add a space
• long date name
• '/' --> Dateseparator as above
• '\ ' ---> space
• 'DD' --> two-digit day number
• '\' --> Dateseparator

Being a custom (= user-defined) format the FormatSettings that you specify do not play any role here.

If you want this cell to be formatted as 'yy.mm.dd' you must either apply the corresponding format in the writing application, or you can change the format of the cell in Spready by using "Cell" > "Number format", or the "Calendar toolbar icon" > "Custom" where you can select one format from the template list (sorry, using this dialog is a bit inconvenient).

[totya]

Hi!

See attached excel file. I see correctly the third line in Excel, but in Spready, is ugly, because Spready say, this is custom format. I can't copy from Spready cell properties window, but about:
DDDD\,\ MMMM\ DD\,\ YYYY
But in excel I see this (about).
YYYY. MMMM. DD.

What's wrong in this case?

I think this isn't custom format really, it's OS country settings-depend general format, don't you think? I see * in excel/cell/number/date format, see attached picture.

[wp]

but in Spready, is ugly

Ugly? Do you mean the '########' displayed in the cell? This is because the column width is too small. Go to the column header and drag the column wider. This is the same behavior as in Excel.

The format written in the file is '[$-F800]dddd\,\ mmmm\ dd\,\ yyyy'. The '[$F-800]' means that the LongDateFormat of your System's DefaultFormatSettings shouild be used for formatting. Without digging into the code. Since Spready dops this symbol I think this is not correctly handled by FPSpreadsheet - as the format code number is in the range of custom formats I think that FPSpreadsheet takes the format literally instead of replacing it by the system's LongDateFormat.

Formats are very complex, I gave up implementing all details, especially since many features are not well-documented.

If particular cell formats are important to you I'd recommand to stay away of those formats proposed by Excel which begin with a leading * like the one you show in your screenshot.

[totya]

Ugly? Do you mean the '########' displayed in the cell? This is because the column width is too small. Go to the column header and drag the column wider. This is the same behavior as in Excel.

Thanks for this joke. Yes, I know the ##### what is it mean long-long-long time ago

Ugly and unreadable this (as I wrote):
DDDD\,\ MMMM\ DD\,\ YYYY
excel show this:
YYYY. MMMM. DD.

Formats are very complex, I gave up implementing all details, especially since many features are not well-documented.

I understand.

If particular cell formats are important to you I'd recommand to stay away of those formats proposed by Excel which begin with a leading * like the one you show in your screenshot.

Yes, but I said, I only get (simple) excel file, I must can read this, and dates too.

Thanks for these informations, but I need readable dates without I need to modify excel file. And you wrote, fps can't handle these custom formats correctly, the result is ugly, unreadable etc... My idea, if not to complex (I think it doesn't) for example:
DateFormatOverride property. This is mean, read and show all date type cells this way, if this property isn't empty. And thats all

So, if I set

Code: Pascal  [Select][+][-]

1. DateFormatOverride:='YYYY.MM.DD';

then all date type fields format this. What do you think?

[wp]

Ugly and unreadable this (as I wrote):
DDDD\,\ MMMM\ DD\,\ YYYY
excel show this:
YYYY. MMMM. DD.

As I said, Excel uses a locale specific format here (the [$F-800] that I mentioned above). But FPSpreadsheet ignores the fact that this format is locale-specific and takes the format literally. Unfortunately, these special symbols are not well-documented, and would require a lot of reverse engineering to get the clue.

Yes, but I said, I only get (simple) excel file, I must can read this, and dates too. [...] Thanks for these informations, but I need readable dates without I need to modify excel file. And you wrote, fps can't handle these custom formats correctly, the result is ugly, unreadable etc... My idea, if not to complex (I think it doesn't) for example:
DateFormatOverride property. This is mean, read and show all date type cells this way, if this property isn't empty. And thats all

So, if I set

Code: Pascal  [Select][+][-]

1. DateFormatOverride:='YYYY.MM.DD';

then all date type fields format this. What do you think?

You seem to forget that you work inside a programming environment and you can add code by yourself. Try this (just a form with a WorksheetGrid and a button):

Code: Pascal  [Select][+][-]

1. procedure FixDateFormat(ASheet: TsWorksheet);
2. var
3.   cell: PCell;
4.   nf: TsNumberFormat;
5.   nfs: String;
6. begin
7.   for cell in ASheet.Cells do begin
8.     ASheet.ReadNumFormat(cell, nf, nfs);
9.     if SameText(nfs, 'DDDD\,\ MMMM\ DD\,\ YYYY') then
10.       ASheet.WriteNumberFormat(cell, nfCustom, 'YYYY.MMMM.DD');
11.   end;
12. end;
13.  
14. procedure TForm1.Button1Click(Sender: TObject);
15. var
16.   book: TsWorkbook;
17.   sheet: TsWorksheet;
18. begin
19.   book := TsWorkbook.Create;
20.   book.ReadFromFile('..\date_example.xls');
21.   sheet := book.GetFirstWorksheet;
22.   FixDateFormat(sheet);
23.   sWorksheetGrid1.LoadfromWorkbook(book);
24. end;  

[wp]

So, this code isn't a perfect solution, because slow (need to read all of cells), and identify only one date format. My (porperty) idea is better/faster I think.

Did you measure the speed? If you did you should present the results. I am convinced that you won't see any difference if the files have some "normal" size.

[totya]

I'm sorry, I delete my prevous post, because the modified code (which match my request) doesn't work correctly:

Code: Pascal  [Select][+][-]

1.  procedure FixDateFormat(ASheet: TsWorksheet);
2.   var
3.     cell: PCell;
4.   begin
5.     for cell in ASheet.Cells do begin
6.       if cell^.ContentType = cctDateTime then
7.         ASheet.WriteNumberFormat(cell, nfCustom, 'YYYY.MM.DD');
8.     end;
9.   end;

Because cell with time value (15:25 for example) isn't a date. I need a check, the cell is DATE really, or not. I think need pos 'YY' in NumberFormatStr or similar... but how can I get the NumberFormatStr?

[wp]

I need a check, the cell is DATE really, or not. I think need pos 'YY' in NumberFormatStr or similar... but how can I get the NumberFormatStr?

Look at my example above: it is the variable nfs.

But note that seeking for the YY in the format string may be in error as well. There are date formats which do not contain the year symbol at all ('dd/mm' --> 02.12). Or even 'dddd' or 'mmmm' to display the day or month name.

I'd check the formatstring and replace it only if the date is not formatted in the way you need it. Again: look at my example.

Because cell with time value (15:25 for example) isn't a date.

Are you sure. For Excel, date and time are the same thing, they are just floating point numbers - the integer part counting the days after some reference date, the fractional part corresponding to the fraction of the day past midnight. So, any date can be formatted as a time. If a cell contains the number 42707.041683796 it will display the date '2016.12.03' if formatted as 'yyyy.mm.dd', or the time '01:00' if formatted as 'hh:nn'

[totya]

I'm sorry, I wrote my previous message about few minutes before I go to sleep Certainly, nfs contain the custom format string. This is my quick code:

Code: Pascal  [Select][+][-]

1. unit Unit1;
2.  
3. {$mode objfpc}{$H+}
4.  
5. interface
6.  
7. uses
8.   Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls,
9.   fpspreadsheetgrid, FPSpreadsheet, FPSTypes,
10.   LCLIntf;
11.  
12. type
13.  
14.   { TForm1 }
15.  
16.   TForm1 = class(TForm)
17.     Button1: TButton;
18.     sWorksheetGrid1: TsWorksheetGrid;
19.     procedure Button1Click(Sender: TObject);
20.   end;
21.  
22. var
23.   Form1: TForm1;
24.  
25. implementation
26.  
27. {$R *.lfm}
28.  
29. { TForm1 }
30.  
31. procedure TForm1.Button1Click(Sender: TObject);
32.  
33.   // Hungarian date syntax...
34.   procedure FixDateFormat(ASheet: TsWorksheet);
35.   var
36.     cell: PCell;
37.     nf: TsNumberFormat;
38.     nfs: string;
39.     NewDateFormat: string;
40.   begin
41.     for cell in ASheet.Cells do
42.     begin
43.       if cell^.ContentType = cctDateTime then
44.       begin
45.         ASheet.ReadNumFormat(cell, nf, nfs);
46.  
47.         // Ignore cells with time value
48.         if not ((Pos('h', nfs) >0) or (Pos('m', nfs) >0) or (Pos('s', nfs) >0)) then
49.         begin
50.           NewDateFormat:= '';
51.  
52.           if Pos('YY', nfs) >0 then
53.             NewDateFormat:= NewDateFormat+ 'YYYY.';
54.  
55.           if Pos('MMMM', nfs) >0 then
56.             NewDateFormat:= NewDateFormat+ '\ MMMM\ ' else
57.               if Pos('MMM', nfs) >0 then
58.                 NewDateFormat:= NewDateFormat+ '\ MMM\ ' else
59.                   if Pos('MM', nfs) >0 then
60.                     NewDateFormat:= NewDateFormat+ 'MM.' else
61.                       if Pos('M', nfs) >0 then
62.                         NewDateFormat:= NewDateFormat+ 'M.';
63.  
64.           if Pos('DD', nfs) >0 then
65.             NewDateFormat:= NewDateFormat+ 'DD.' else
66.               if Pos('D', nfs) >0 then
67.                 NewDateFormat:= NewDateFormat+ 'D.';
68.  
69.           ASheet.WriteNumberFormat(cell, nfCustom, NewDateFormat);
70.         end;
71.       end;
72.     end;
73.   end;
74.  
75. var
76.   book: TsWorkbook;
77.   sheet: TsWorksheet;
78. begin
79.   book := TsWorkbook.Create;
80.   book.ReadFromFile('C:\tmp\date_example.xls');
81.   sheet := book.GetFirstWorksheet;
82.   FixDateFormat(sheet);
83.   sWorksheetGrid1.LoadfromWorkbook(book);
84. end;
85.  
86. end.

[wp]

I see several problems in your code:

• The "m" can be lower or uppercase and means "minutes" or "months", depending on context. So, your initial check "if pos('m', nfs) > 0" is not safe to detect a time format.  Usually time formats contain the ':', I would check this first (or use the output of the built-in parser - see below).
• Escaping the space character by means of a backslash is only one of several possibilities. It may also be enclosed by double quotes ("), or be inserted directly.
• Even the "m", "h", or "s" can belong to a date format in an escaped part (e.g., format string = '"The date is "DD/MM/YYYY'.

These cases are considered by the fpspreadsheet format string parser. The parsed results are available in the workbook's lists of TsCellFormat and TsNumFormatParams and can be queried knowing the format index stored in the cell. Here is some simple code which reorders the day/month/year parts to year-month-day, replaces the date separator by '.', and ignores escaped characters (note that formats such as 'DDDD, DD/MM/YYYY' will drop the leading day name this way). The count of digits (YYYY vs YY, MMMM vs MMM vs MM vs M,etc) is preserved:

Code: Pascal  [Select][+][-]

1. uses
2.   StrUtils, fpsNumFormat;
3.  
4. { TForm1 }
5.  
6. procedure FixDateFormat(ASheet: TsWorksheet);
7. var
8.   cell: PCell;
9.   fmt: TsCellFormat;
10.   nfp: TsNumFormatParams;
11.   newDateFormat: String;
12.   dayPart, monthPart, yearPart: String;
13.   elem: TsNumFormatElement;
14.   i: Integer;
15. begin
16.   for cell in ASheet.Cells do begin
17.     fmt := ASheet.ReadCellFormat(cell);
18.     nfp := ASheet.Workbook.GetNumberFormat(fmt.NumberFormatIndex);
19.     if nfp = nil then   // default number format
20.       Continue;
21.     if IsDateFormat(nfp) then begin
22.       dayPart := '';
23.       monthPart := '';
24.       yearPart := '';
25.       for i:=0 to High(nfp.Sections[0].Elements) do begin
26.         elem := nfp.Sections[0].Elements[i];
27.         case elem.Token of
28.           nftYear: YearPart := DupeString('Y', elem.IntValue);
29.           nftMonth: MonthPart := DupeString('M', elem.IntValue);
30.           nftDay: DayPart := DupeString('D', elem.IntValue);
31.         end;
32.       end;
33.       newDateFormat := YearPart + '.' + MonthPart + '.' + DayPart;
34.       ASheet.WriteNumberFormat(cell, nfCustom, newDateFormat);
35.     end;
36.   end;
37. end;  

[totya]

Hi!

Thanks for this (hard) code, but my simple code produce better result (almost perfect).

See the excel output, my code out, and your code out, and the last picture is the rules for my country. And see attached sample excel file (in the next post, beause attachment count is limited)