Recent

Author Topic: ReadAsText and TFormatSettings  (Read 3608 times)

kjteng

  • Sr. Member
  • ****
  • Posts: 259
ReadAsText and TFormatSettings
« on: October 25, 2019, 03:20:23 pm »
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][+][-]
  1. procedure TForm1.Button2Click(Sender: TObject);
  2. var fmt: TFormatSettings;
  3. begin
  4.   fmt := DefaultFormatSettings;
  5. //  fmt.LongDateFormat:= 'yyyy-mm-dd';
  6.   fmt.ShortDateFormat:= 'yyyy-mm-dd';
  7.   fmt.DateSeparator:= '-';
  8.   with wbSource1.Worksheet do
  9.     showmessage(ReadAsText(GetCell(Activecellrow, ActiveCellCol), fmt));
  10. end;


wp

  • Hero Member
  • *****
  • Posts: 11858
Re: ReadAsText and TFormatSettings
« Reply #1 on: October 25, 2019, 03:48:28 pm »
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][+][-]
  1. program Project1;
  2.  
  3. uses
  4.   SysUtils,
  5.   fpspreadsheet, fpstypes;
  6.  
  7. var
  8.   b: TsWorkbook;
  9.   sh: TsWorksheet;
  10.  
  11. begin
  12.   b := TsWorkbook.Create;
  13.   try
  14.     b.FormatSettings.ShortDateFormat := 'yyyy-mm-dd';
  15.     sh := b.AddWorksheet('Test');
  16.     sh.WriteDateTime(0, 0, Now, nfShortDate);
  17.     WriteLn(sh.ReadAsText(0, 0));  
  18.  
  19.   finally
  20.     b.Free;
  21.   end;
  22.  
  23.   ReadLn;
  24. end.

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: ReadAsText and TFormatSettings
« Reply #2 on: October 25, 2019, 06:16:42 pm »
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?


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

  • Hero Member
  • *****
  • Posts: 11858
Re: ReadAsText and TFormatSettings
« Reply #3 on: October 25, 2019, 07:34:54 pm »
I 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][+][-]
  1. program Project1;
  2.  
  3. uses
  4.   sysutils,
  5.   fpspreadsheet, fpstypes;
  6.  
  7. var
  8.   fs: TFormatSettings;
  9.   b: TsWorkbook;
  10.   sh: TsWorksheet;
  11.   t: TDateTime;
  12.  
  13. begin
  14.   fs := DefaultFormatSettings;
  15.   fs.LongMonthNames[10] := 'OCT';
  16.   fs.LongDayNames[1] := 'SO';
  17.   fs.LongDayNames[2] := 'MO';
  18.   fs.LongDayNames[3] := 'TH';
  19.   fs.LongDayNames[4] := 'WE';
  20.   fs.LongDayNames[5] := 'TH';
  21.   fs.LongDayNames[6] := 'FR';
  22.   fs.LongDayNames[7] := 'SA';
  23.   fs.LongDateFormat := 'dddd, yyyy-mm-dd';
  24.  
  25.   b := TsWorkbook.Create;
  26.   try
  27.     //b.FormatSettings := fs;   // <--- when this line is active the formatsettings are respected in all reads.
  28.  
  29.     sh := b.AddWorksheet('Test');
  30.     t := EncodeDate(2019, 10, 25);
  31.     sh.WriteDateTime(0, 0, t, nfLongDate);
  32.     sh.WriteDateTime(1, 0, t, nfLongDate);
  33.  
  34.     WriteLn('A: ', sh.ReadAsText(0, 0) );
  35.     WriteLn('B: ',  sh.ReadAsText(sh.Getcell(1, 0), fs));
  36.  
  37.     b.FormatSettings := fs;
  38.     WriteLn('C: ',  sh.ReadAsText(0, 0) );
  39.     WriteLn('D: ', sh.ReadAsText(sh.Getcell(1, 0), fs));
  40.  
  41.     sh.WriteDateTime(2, 0, t, nfLongDate);
  42.     sh.WriteDateTime(3, 0, t, nfLongDate);
  43.     WriteLn('E: ',  sh.ReadAsText(2, 0) );
  44.     WriteLn('F: ', sh.ReadAsText(sh.Getcell(3, 0), fs));
  45.  
  46.   finally
  47.     b.Free;
  48.   end;
  49.  
  50.   ReadLn;
  51.  
  52. end.

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: ReadAsText and TFormatSettings
« Reply #4 on: October 26, 2019, 11:49:24 am »
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][+][-]
  1.  
  2. function TSheetHelper.ReadAsStr(ARow, ACol: Cardinal;
  3.    DateFmt:string = 'yyyy-mm-dd'; NumFmt: string= '##0.00'): string;
  4. var
  5.   ACell: PCell;
  6.   hyperlink: PsHyperlink;
  7.  
  8. begin
  9.   Result := '';
  10.   ACell := GetCell(ARow, ACol);
  11.   with ACell^ do
  12.     case ContentType of
  13.       cctUTF8String:
  14.         Result := UTF8StringValue;
  15.  
  16.       cctNumber:
  17.           Result := FormatFloat(NumFmt, NumberValue);
  18.  
  19.       cctDateTime:
  20.         Result := FormatDateTime(DateFmt, DateTimeValue);
  21.  
  22.       cctBool:
  23.         Result := StrUtils.IfThen(BoolValue, STR_TRUE, STR_FALSE);
  24.  
  25.       cctError:
  26.         Result := GetErrorValueStr(TsErrorValue(ErrorValue));
  27.  
  28.       else   // blank --> display hyperlink target if available
  29.         Result := '';
  30.       if HasHyperlink(ACell) then
  31.         begin
  32.           hyperlink := FindHyperlink(ACell);
  33.           if hyperlink <> nil then Result := hyperlink^.Target;
  34.         end;
  35.     end;
  36. end;
  37.                                  
  38.  
  39.  
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

  • Hero Member
  • *****
  • Posts: 11858
Re: ReadAsText and TFormatSettings
« Reply #5 on: October 26, 2019, 01:26:03 pm »
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][+][-]
  1. program Project1;
  2.  
  3. uses
  4.   sysutils,
  5.   fpspreadsheet, fpstypes;
  6.  
  7. var
  8.   b: TsWorkbook;
  9.   sh: TsWorksheet;
  10.   t1, t2: TDateTime;
  11.  
  12. begin
  13.   b := TsWorkbook.Create;
  14.   try
  15.     sh := b.AddWorksheet('Test');
  16.     t1 := EncodeDate(2019, 10, 25);
  17.     WriteLn('Date written: ', DateToStr(t1));
  18.     sh.WriteDateTime(0, 0, t1, nfLongDate);
  19.  
  20.     if sh.ReadAsDateTime(0, 0, t2) then
  21.       WriteLn('Date read: ', FormatDateTime('yyyy-mm-dd', t2));
  22.  
  23.   finally
  24.     b.Free;
  25.   end;
  26.  
  27.   ReadLn;
  28.  
  29. end.

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: ReadAsText and TFormatSettings
« Reply #6 on: October 26, 2019, 02:22:38 pm »
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.
« Last Edit: October 27, 2019, 01:02:23 am by kjteng »

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: ReadAsText and TFormatSettings
« Reply #7 on: October 26, 2019, 03:38:08 pm »
Could you please edit your post and fix the code and/or quote tags? Your message is difficult to read.

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: ReadAsText and TFormatSettings
« Reply #8 on: October 27, 2019, 04:32:53 am »
Could 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 :-)

 

TinyPortal © 2005-2018