ReadAsText and TFormatSettings
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
kjteng · 2019-10-25 13:20

I tried ReadAsText(ACell: PCell;  AFormatSettings: TFormatSettings)
No issue will float and integer but I can get it work for Date type
(Only date separator changed by not the position of d,m ,y).
Did I miss any thing in the following 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 procedure
TForm1.Button2Click(Sender: TObject);var fmt: TFormatSettings;begin 
fmt := DefaultFormatSettings;//  fmt.LongDateFormat:= 'yyyy-mm-dd'; 
fmt.ShortDateFormat:= 'yyyy-mm-dd';  fmt.DateSeparator:= '-';  with
wbSource1.Worksheet do   
showmessage(ReadAsText(GetCell(Activecellrow, ActiveCellCol),
fmt));end;

----------------------------------------------------------------------
wp · 2019-10-25 13:48

Ah I see. This does not work any more because the number formats are
stored in parsed format. So, when you request a particular
FormatSettings for dates here, the default formats should be parsed
again because the day-month-year order or the symbol count in the
date/time format parts can change. Since reparsing here would kill the
advantage due to the parsed formats I tend to deprecate this parameter
combination.

Why don't you set the workbook's FormatSettings and call ReadAsText
without the FormatSettings parameter? This is working. It affects the
entire workbook, though. Is this a problem?

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 program
Project1; uses  SysUtils,  fpspreadsheet, fpstypes; var  b:
TsWorkbook;  sh: TsWorksheet; begin  b := TsWorkbook.Create;  try   
b.FormatSettings.ShortDateFormat := 'yyyy-mm-dd';    sh :=
b.AddWorksheet('Test');    sh.WriteDateTime(0, 0, Now, nfShortDate); 
 WriteLn(sh.ReadAsText(0, 0));     finally    b.Free;  end;  
ReadLn;end.

----------------------------------------------------------------------
kjteng · 2019-10-25 16:16

Quote from: wp on October 25, 2019, 03:48:28 pmAh I see. This does not
work any more because the number formats are stored in parsed format.
So, when you request a particular FormatSettings for dates here, the
default formats should be parsed again because the day-month-year
order or the symbol count in the date/time format parts can change.
Since reparsing here would kill the advantage due to the parsed
formats I tend to deprecate this parameter combination.

Why don't you set the workbook's FormatSettings and call ReadAsText
without the FormatSettings parameter? This is working. It affects the
entire workbook, though. Is this a problem?


Not exactly. My probem: I have to read the excel worksheets from
various sources which contain date data in different format. Setting
workbook.FormatSettings will not help because (as I can see from
fpspread.pas) readAsText(rr,cc) eventually will call
readAsText(getCell(rr,cc), Workbook.FormatSettings).

----------------------------------------------------------------------
wp · 2019-10-25 17:34

Quote from: kjteng on October 25, 2019, 06:16:42 pmI have to read the
excel worksheets from various sources which contain date data in
different format. Does this mean that there are differently date-
formatted cells within the same workbook? How does Excel behave here?
I guess that such cells must be formatted individually so that the
day-month-year order is defined by the format string; here the
FormatSettings should not play a role except for the weekday and month
names. There are some locale-dependent formats in Excel (i.e. where
the day-month-year order may change from country to country), but I
never managed to see them explicitly because in all my tests I saw
them converted to specific format strings in which the order was
already defined. Therefore, when fps reads a file, it gets the format
string and parses it to a sequence of day-separator-month-separator-
year tokens (and more). When the cell text is displayed the tokens are
replaced by the numbers and/or the day/month and date separator
strings of the format settings. A built-in format such as nfLongDate
is defined at its first usage and this sets the order of day-month-
year parts. Later you cannot change this order again (hmmm... well
maybe in some hack). So, when you must read files with non-standard
date format you first must set the workbook's FormatSettings
(immediately after creating the workbook, before reading the file).
The AFormatSettings parameter of the ReadAsText function does not
change the day-month-year order! It only allows you to insert
different day and month names.

Try this demo:
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 program
Project1; uses  sysutils,  fpspreadsheet, fpstypes; var  fs:
TFormatSettings;  b: TsWorkbook;  sh: TsWorksheet;  t:
TDateTime; begin  fs := DefaultFormatSettings;  fs.LongMonthNames[10]
:= 'OCT';  fs.LongDayNames[1] := 'SO';  fs.LongDayNames[2] := 'MO'; 
fs.LongDayNames[3] := 'TH';  fs.LongDayNames[4] := 'WE'; 
fs.LongDayNames[5] := 'TH';  fs.LongDayNames[6] := 'FR'; 
fs.LongDayNames[7] := 'SA';  fs.LongDateFormat := 'dddd, yyyy-mm-
dd';   b := TsWorkbook.Create;  try    //b.FormatSettings := fs;   //
<--- when this line is active the formatsettings are respected in all
reads.     sh := b.AddWorksheet('Test');    t := EncodeDate(2019, 10,
25);    sh.WriteDateTime(0, 0, t, nfLongDate);    sh.WriteDateTime(1,
0, t, nfLongDate);     WriteLn('A: ', sh.ReadAsText(0, 0) );   
WriteLn('B: ',  sh.ReadAsText(sh.Getcell(1, 0), fs));    
b.FormatSettings := fs;    WriteLn('C: ',  sh.ReadAsText(0, 0) );   
WriteLn('D: ', sh.ReadAsText(sh.Getcell(1, 0), fs));    
sh.WriteDateTime(2, 0, t, nfLongDate);    sh.WriteDateTime(3, 0, t,
nfLongDate);    WriteLn('E: ',  sh.ReadAsText(2, 0) );    WriteLn('F:
', sh.ReadAsText(sh.Getcell(3, 0), fs));   finally    b.Free;  end;  
ReadLn; end.

----------------------------------------------------------------------
kjteng · 2019-10-26 09:49

Yeah you are right, the order of yy mm dd cannot be changed unless we
remove the number format of each cell.
TQ for the code sample but I think it cannot solve my problem because
the worksheets are not created by me and
and the user might set the date/number format in different way.
After spending some time to study this issue, I think it would be
better for me to modify the readasText function in
a classhelper for my own purpose. As follows:
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  function
TSheetHelper.ReadAsStr(ARow, ACol: Cardinal;   DateFmt:string = 'yyyy-
mm-dd'; NumFmt: string= '##0.00'): string;var  ACell: PCell; 
hyperlink: PsHyperlink; begin  Result := '';  ACell := GetCell(ARow,
ACol);  with ACell^ do    case ContentType of      cctUTF8String:    
  Result := UTF8StringValue;       cctNumber:          Result :=
FormatFloat(NumFmt, NumberValue);       cctDateTime:        Result :=
FormatDateTime(DateFmt, DateTimeValue);       cctBool:        Result
:= StrUtils.IfThen(BoolValue, STR_TRUE, STR_FALSE);       cctError:  
    Result := GetErrorValueStr(TsErrorValue(ErrorValue));       else 
// blank --> display hyperlink target if available        Result :=
'';      if HasHyperlink(ACell) then        begin          hyperlink
:= FindHyperlink(ACell);          if hyperlink <> nil then Result :=
hyperlink^.Target;        end;    end;end;                           
      The above code is largely taken from fpspreadsheet.pas and it
works so far. Only thing I wish to ask is about the 'else' case (line
2873 fpspreadsheet.pas):
(i) why do we have to set result to '' again? and (ii) shoud I change
this part to
 

Code: [Select]  ...
    else   // blank --> display hyperlink target if available
        if HasHyperlink(ACell) then
          begin
            hyperlink := FindHyperlink(ACell);
            if hyperlink <> nil then Result := hyperlink^.Target;
          end;
  ...

----------------------------------------------------------------------
wp · 2019-10-26 11:26

This is probably a left-over from adding the hyperlink feature. I'd
remove the "Result := ''" from the "else" part of the instruction
because the default function result has already been set in the
beginning, and I'd put the hyperlink stuff completely outside the
"case" instruction" because it could happen that a cell stores an
empty string but still contains a hyperlink so that the hyperlink
target should be displayed instead of the empty cell. (however, this
will not happen in practice because Excel and fps always write the
hyperlink target into the cell text field even if the user did not
assign a particular text to it). That's what I did in the current
commit.

Your ReadAsStr helper duplicates a lot of code. Why don't you just
handle the cctDateTime case and return the fps result otherwise?
Moveover this function kills any specific number formatting applied to
cells.

Alternatively (without introducing this particular helper function)
you could also read the date/time value with the corresponding
worksheet method and apply your own format to 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 program
Project1; uses  sysutils,  fpspreadsheet, fpstypes; var  b:
TsWorkbook;  sh: TsWorksheet;  t1, t2: TDateTime; begin  b :=
TsWorkbook.Create;  try    sh := b.AddWorksheet('Test');    t1 :=
EncodeDate(2019, 10, 25);    WriteLn('Date written: ',
DateToStr(t1));    sh.WriteDateTime(0, 0, t1, nfLongDate);     if
sh.ReadAsDateTime(0, 0, t2) then      WriteLn('Date read: ',
FormatDateTime('yyyy-mm-dd', t2));   finally    b.Free;  end;  
ReadLn; end.

----------------------------------------------------------------------
kjteng · 2019-10-26 12:22

Quote author=wp link=topic=47194.msg337436#msg337436 date=1572089163]
This is probably a left-over from adding the hyperlink feature. I'd
remove the "Result := ''" from the "else" part of the instruction
because the default function result has already been set in the
beginning, and I'd put the hyperlink stuff completely outside the
"case" instruction" because it could happen that a cell stores an
empty string but still contains a hyperlink so that the hyperlink
target should be displayed instead of the empty cell. (however, this
will not happen in practice because Excel and fps always write the
hyperlink target into the cell text field even if the user did not
assign a particular text to it). That's what I did in the current
commit.

Your ReadAsStr helper duplicates a lot of code. Why don't you just
handle the cctDateTime case and return the fps result otherwise?
Moveover this function kills any specific number formatting applied to
cells.

Alternatively (without introducing this particular helper function)
you could also read the date/time value with the corresponding
worksheet method and apply your own format to it:

Thanks for the explaination and  sample code. The code definitely help
me to better understand the workings of fpspread. However in this
particlar case here, I  need to have the unformatted string (eg no
thousand separator etc) because all the data would be assigned to
fieldbyname('xx').asString. It is not a general purpose function but
it is called frequently in the particular application. As such I've
saved it in the  same folder with the related  lpi  (project) file.

----------------------------------------------------------------------
wp · 2019-10-26 13:38

Could you please edit your post and fix the code and/or quote tags?
Your message is difficult to read.

----------------------------------------------------------------------
kjteng · 2019-10-27 03:32

Quote from: wp on October 26, 2019, 03:38:08 pmCould you please edit
your post and fix the code and/or quote tags? Your message is
difficult to read.
Sorry for the creless mistake. ... old man's problem :-)