Date question [SOLVED]
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
totya · 2016-12-01 20:53

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 · 2016-12-02 00:14

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
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 · 2016-12-02 05:34

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).

QuoteDid you write the file yourself?
No, I usually never modified the excel file, I only read this.

Thanks!

----------------------------------------------------------------------
totya · 2016-12-02 17:33

Hi!

As I wrote, I tested it.

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        
with Workbook.FormatSettings do        begin         
ShortDateFormat:= 'yy.mm.dd';          LongDateFormat:= 'yy.mm.dd';  
    end;
Well, the ShortDateFormat setting is working, but the LongDateFormat
settings doesn't work. See the example file (third line).

----------------------------------------------------------------------
wp · 2016-12-02 18:52

BIFFExplorer and Spready's inspector tell me that this cell has a
custom format 'YYYY/\ MMMM/\ DD\' - this means
four-digit yearthe '/' is replaced by the DateSeparator of the
Workbook's FormatSettingsthe '\ ' means: add a spacelong date name'/'
--> Dateseparator as above'\ ' ---> space'DD' --> two-digit day
number'\' --> DateseparatorBeing 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 · 2016-12-02 19:59

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 · 2016-12-02 20:27

Quote from: totya on December 02, 2016, 08:59:06 pmbut 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 · 2016-12-02 21:13

Quote from: wp on December 02, 2016, 09:27:08 pmUgly? 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.

Quote from: wp on December 02, 2016, 09:27:08 pmFormats are very
complex, I gave up implementing all details, especially since many
features are not well-documented.
I understand.

Quote from: wp on December 02, 2016, 09:27:08 pmIf 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
DateFormatOverride:='YYYY.MM.DD';
then all date type fields format this. What do you think?

----------------------------------------------------------------------
wp · 2016-12-02 22:16

Quote from: totya on December 02, 2016, 10:13:34 pmUgly 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.

Quote from: totya on December 02, 2016, 10:13:34 pmYes, 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
FixDateFormat(ASheet: TsWorksheet);var  cell: PCell;  nf:
TsNumberFormat;  nfs: String;begin  for cell in ASheet.Cells do begin 
  ASheet.ReadNumFormat(cell, nf, nfs);    if SameText(nfs, 'DDDD\,\
MMMM\ DD\,\ YYYY') then      ASheet.WriteNumberFormat(cell, nfCustom,
'YYYY.MMMM.DD');  end;end; procedure TForm1.Button1Click(Sender:
TObject);var  book: TsWorkbook;  sheet: TsWorksheet;begin  book :=
TsWorkbook.Create;  book.ReadFromFile('..\date_example.xls');  sheet
:= book.GetFirstWorksheet;  FixDateFormat(sheet); 
sWorksheetGrid1.LoadfromWorkbook(book);end;

----------------------------------------------------------------------
wp · 2016-12-02 23:07

Quote from: totya on December 02, 2016, 11:59:36 pmSo, 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 · 2016-12-02 23:34

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

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
FixDateFormat(ASheet: TsWorksheet);  var    cell: PCell;  begin    for
cell in ASheet.Cells do begin      if cell^.ContentType = cctDateTime
then        ASheet.WriteNumberFormat(cell, nfCustom, 'YYYY.MM.DD');  
end;  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 · 2016-12-03 00:03

Quote from: totya on December 03, 2016, 12:34:07 amI 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.

Quote from: totya on December 03, 2016, 12:34:07 amBecause 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 · 2016-12-03 08:14

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1; {$mode objfpc}{$H+} interface uses  Classes, SysUtils,
FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls, 
fpspreadsheetgrid, FPSpreadsheet, FPSTypes,  LCLIntf; type   { TForm1
}   TForm1 = class(TForm)    Button1: TButton;    sWorksheetGrid1:
TsWorksheetGrid;    procedure Button1Click(Sender: TObject); 
end; var  Form1: TForm1; implementation {$R *.lfm} { TForm1
} procedure TForm1.Button1Click(Sender: TObject);   // Hungarian date
syntax...  procedure FixDateFormat(ASheet: TsWorksheet);  var    cell:
PCell;    nf: TsNumberFormat;    nfs: string;    NewDateFormat:
string;  begin    for cell in ASheet.Cells do    begin      if
cell^.ContentType = cctDateTime then      begin       
ASheet.ReadNumFormat(cell, nf, nfs);         // Ignore cells with time
value        if not ((Pos('h', nfs) >0) or (Pos('m', nfs) >0) or
(Pos('s', nfs) >0)) then        begin          NewDateFormat:= '';   
      if Pos('YY', nfs) >0 then            NewDateFormat:=
NewDateFormat+ 'YYYY.';           if Pos('MMMM', nfs) >0 then        
  NewDateFormat:= NewDateFormat+ '\ MMMM\ ' else              if
Pos('MMM', nfs) >0 then                NewDateFormat:= NewDateFormat+
'\ MMM\ ' else                  if Pos('MM', nfs) >0 then            
      NewDateFormat:= NewDateFormat+ 'MM.' else                     
if Pos('M', nfs) >0 then                        NewDateFormat:=
NewDateFormat+ 'M.';           if Pos('DD', nfs) >0 then           
NewDateFormat:= NewDateFormat+ 'DD.' else              if Pos('D',
nfs) >0 then                NewDateFormat:= NewDateFormat+ 'D.';     
    ASheet.WriteNumberFormat(cell, nfCustom, NewDateFormat);       
end;      end;    end;  end; var  book: TsWorkbook;  sheet:
TsWorksheet;begin  book := TsWorkbook.Create; 
book.ReadFromFile('C:\tmp\date_example.xls');  sheet :=
book.GetFirstWorksheet;  FixDateFormat(sheet); 
sWorksheetGrid1.LoadfromWorkbook(book);end; end.

----------------------------------------------------------------------
wp · 2016-12-03 10:17

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i uses 
StrUtils, fpsNumFormat; { TForm1 } procedure FixDateFormat(ASheet:
TsWorksheet);var  cell: PCell;  fmt: TsCellFormat;  nfp:
TsNumFormatParams;  newDateFormat: String;  dayPart, monthPart,
yearPart: String;  elem: TsNumFormatElement;  i: Integer;begin  for
cell in ASheet.Cells do begin    fmt := ASheet.ReadCellFormat(cell); 
 nfp := ASheet.Workbook.GetNumberFormat(fmt.NumberFormatIndex);    if
nfp = nil then   // default number format      Continue;    if
IsDateFormat(nfp) then begin      dayPart := '';      monthPart :=
'';      yearPart := '';      for i:=0 to
High(nfp.Sections[0].Elements) do begin        elem :=
nfp.Sections[0].Elements[i];        case elem.Token of         
nftYear: YearPart := DupeString('Y', elem.IntValue);         
nftMonth: MonthPart := DupeString('M', elem.IntValue);         
nftDay: DayPart := DupeString('D', elem.IntValue);        end;     
end;      newDateFormat := YearPart + '.' + MonthPart + '.' +
DayPart;      ASheet.WriteNumberFormat(cell, nfCustom,
newDateFormat);    end;  end;end;

----------------------------------------------------------------------
totya · 2016-12-03 12:27

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)

----------------------------------------------------------------------
totya · 2016-12-03 12:37

... 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 · 2016-12-03 13:07

My improved code, use your instructions:

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

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   //
Hungarian date syntax...  procedure FixDateFormat(ASheet:
TsWorksheet);  var    cell: PCell;    nf: TsNumberFormat;    nfs:
string;    NewDateFormat: string;  begin    for cell in ASheet.Cells
do    begin      if cell^.ContentType = cctDateTime then      begin  
    ASheet.ReadNumFormat(cell, nf, nfs);         // Ignore cells with
time value        if (cell^.DateTimeValue > 0)            and        
 (cell^.DateTimeValue = Trunc(cell^.DateTimeValue)) then        begin 
        NewDateFormat:= '';           if Pos('YY', UpperCase(nfs)) >0
then            NewDateFormat:= NewDateFormat+ 'YYYY." "';          
if Pos('MMMM', UpperCase(nfs)) >0 then            NewDateFormat:=
NewDateFormat+ 'MMMM" "' else              if Pos('MMM',
UpperCase(nfs)) >0 then                NewDateFormat:= NewDateFormat+
'MMM" "' else                  if Pos('MM', UpperCase(nfs)) >0 then  
                NewDateFormat:= NewDateFormat+ 'MM." "' else         
           if Pos('M', UpperCase(nfs)) >0 then                       
NewDateFormat:= NewDateFormat+ 'M." "';           if Pos('DD',
UpperCase(nfs)) >0 then            NewDateFormat:= NewDateFormat+
'DD.' else              if Pos('D', UpperCase(nfs)) >0 then          
    NewDateFormat:= NewDateFormat+ 'D.';          
ASheet.WriteNumberFormat(cell, nfCustom, NewDateFormat);        end; 
   end;    end;  end;

----------------------------------------------------------------------
wp · 2016-12-03 13:57

Quote from: totya on December 03, 2016, 01:27:40 pmbut my simple code
produce better result (almost perfect).
Of course. Too quick and too dirty...
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   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).

Quote from: totya on December 03, 2016, 01:37:22 pmWhat 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.

Quote from: totya on December 03, 2016, 01:37:22 pmTime 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)) thenI
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 · 2016-12-03 17:16

Quote from: wp on December 03, 2016, 02:57:00 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 from: wp on December 03, 2016, 02:57:00 pmQuote        // Ignore
cells with time value
        if (cell^.DateTimeValue > 0)
            and
           (cell^.DateTimeValue = Trunc(cell^.DateTimeValue)) thenI
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...

Quote from: wp on December 03, 2016, 02:57:00 pmIf 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!