View Issue Details

IDProjectCategoryView StatusLast Update
0036137Lazarus CCRFPSpreadsheetpublic2019-10-10 20:23
ReporterAlexey EmelyanovAssigned Towp 
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionfixed 
PlatformAppleOSOS Version
Summary0036137: Patch to fix incorrect date format interpretation in Numbers for iOS and Mac OS X
DescriptionDate values in cells of spreadsheet files, exported using FPSpreadsheet package, displayed incorrectly in Numbers for iOS and Mac OS X.
Date format string "DD.MM.YYYY" with upper case letters misinterpreted by Numbers and leads to display number of days since start of year in first field instead of number of days since start of month.
Presumably problem caused by implementation of spreadsheet import exactly according to current excel format specification which allows only lower case letters in date format string.
Steps To Reproduce1. Export sample spreadsheet file in Excel 8 format (.xls), Office Open XML (.xlsx) with date values in cells and date format string "DD.MM.YYYY"
2. Open spreadsheet file in Numbers for iOS and Mac OS X
Additional InformationBuildFormatStringFromSection function in fpsnumformat.pas receives a set of flags to specify the rules for format string creation.
That set of flags prepared by GetNumberFormatOptions method of TsWorkbook class according to existence of boStrictFormat flag in Options property of workbook.
TagsNo tags attached.
Widgetset
Attached Files
  • strict_num_fmt.diff (14,969 bytes)
    Index: source/common/fpsfunc.pas
    ===================================================================
    --- source/common/fpsfunc.pas	(revision 7151)
    +++ source/common/fpsfunc.pas	(working copy)
    @@ -2156,7 +2156,7 @@
             Result := FindPart(searchString, s) > 0
             // NOTE: FindPart currently supports only the wildcard '?'
           else
    -        Result := SameStr(s, searchString);
    +        Result := CompareStr(s, searchString) = 0;
         end else
         begin
           case ACell^.ContentType of
    Index: source/common/fpsnumformat.pas
    ===================================================================
    --- source/common/fpsnumformat.pas	(revision 7151)
    +++ source/common/fpsnumformat.pas	(working copy)
    @@ -154,6 +154,12 @@
       {@@ Array of parsed number format sections }
       TsNumFormatSections = array of TsNumFormatSection;
     
    +  {@@ Number format conversion options }
    +  TsNumFormatOption = (nfoDisableLocalizedAMPM, nfoStrictFormat);
    +
    +  {@@ Set of number format conversion options }
    +  TsNumFormatOptions = set of TsNumFormatOption;
    +
       { TsNumFormatParams }
     
       {@@ Describes a parsed number format and provides all the information to
    @@ -160,11 +166,12 @@
         convert a number to a number or date/time string. These data are created
         by the number format parser from a format string. }
       TsNumFormatParams = class(TObject)
    +  public
    +    function BuildNumFormatStr(AOptions: TsNumFormatOptions): String; virtual;
       private
    -    FAllowLocalizedAMPM: Boolean;
    +    function GetNumFormatStr: String;
       protected
         function GetNumFormat: TsNumberFormat; virtual;
    -    function GetNumFormatStr: String; virtual;
       public
         {@@ Array of the format sections }
         Sections: TsNumFormatSections;
    @@ -177,7 +184,6 @@
         procedure SetDecimals(AValue: Byte);
         procedure SetNegativeRed(AEnable: Boolean);
         procedure SetThousandSep(AEnable: Boolean);
    -    property AllowLocalizedAMPM: boolean read FAllowLocalizedAMPM write FAllowLocalizedAMPM;
         property NumFormat: TsNumberFormat read GetNumFormat;
         property NumFormatStr: String read GetNumFormatStr;
       end;
    @@ -271,9 +277,9 @@
         procedure CheckSections;
         procedure CheckSection(ASection: Integer);
         procedure FixMonthMinuteToken(var ASection: TsNumFormatSection);
    +  public
         // Format string
    -    function BuildFormatString: String; virtual;
    -
    +    function BuildFormatString(AOptions: TsNumFormatOptions): String; virtual;
       public
         constructor Create(const AFormatString: String;
           const AFormatSettings: TFormatSettings);
    @@ -319,7 +325,7 @@
       AMinIntDigits: Integer = 1): String;
     
     function BuildFormatStringFromSection(const ASection: TsNumFormatSection;
    -  AllowLocalizedAMPM: Boolean = true): String;
    +  AOptions: TsNumFormatOptions = []): String;
     
     function ApplyTextFormat(AText: String; AParams: TsNumFormatParams): String;
     function ConvertFloatToStr(AValue: Double; AParams: TsNumFormatParams;
    @@ -1476,7 +1482,7 @@
               @return Excel-compatible format string
     -------------------------------------------------------------------------------}
     function BuildFormatStringFromSection(const ASection: TsNumFormatSection;
    -  AllowLocalizedAMPM: Boolean = true): String;
    +  AOptions: TsNumFormatOptions = []): String;
     var
       element: TsNumFormatElement;
       i, n: Integer;
    @@ -1527,11 +1533,17 @@
           nftText:
             if element.TextValue <> '' then result := Result + '"' + element.TextValue + '"';
           nftYear:
    -        Result := Result + DupeString('Y', element.IntValue);
    +        if nfoStrictFormat in AOptions
    +          then Result := Result + DupeString('y', element.IntValue)
    +          else Result := Result + DupeString('Y', element.IntValue);
           nftMonth:
    -        Result := Result + DupeString('M', element.IntValue);
    +        if nfoStrictFormat in AOptions
    +          then Result := Result + DupeString('m', element.IntValue)
    +          else Result := Result + DupeString('M', element.IntValue);
           nftDay:
    -        Result := Result + DupeString('D', element.IntValue);
    +        if nfoStrictFormat in AOptions
    +          then Result := Result + DupeString('d', element.IntValue)
    +          else Result := Result + DupeString('D', element.IntValue);
           nftHour:
             if element.IntValue < 0
               then Result := Result + '[' + DupeString('h', -element.IntValue) + ']'
    @@ -2201,7 +2213,6 @@
     constructor TsNumFormatParams.Create;
     begin
       inherited;
    -  FAllowLocalizedAMPM := true;
     end;
     
     {@@ ----------------------------------------------------------------------------
    @@ -2257,19 +2268,30 @@
     
       @return  Excel-compatible number format string.
     -------------------------------------------------------------------------------}
    -function TsNumFormatParams.GetNumFormatStr: String;
    +function TsNumFormatParams.BuildNumFormatStr(AOptions: TsNumFormatOptions): String;
     var
       i: Integer;
     begin
       if Length(Sections) > 0 then begin
    -    Result := BuildFormatStringFromSection(Sections[0]);
    +    Result := BuildFormatStringFromSection(Sections[0], AOptions);
         for i := 1 to High(Sections) do
    -      Result := Result + ';' + BuildFormatStringFromSection(Sections[i], FAllowLocalizedAMPM);
    +      Result := Result + ';' + BuildFormatStringFromSection(Sections[i], AOptions);
       end else
         Result := '';
     end;
     
     {@@ ----------------------------------------------------------------------------
    +  Constructs the number format string from the parsed sections and elements.
    +  The format symbols are selected according to Excel syntax.
    +
    +  @return  Excel-compatible number format string.
    +-------------------------------------------------------------------------------}
    +function TsNumFormatParams.GetNumFormatStr: String;
    +begin
    +  Result := BuildNumFormatStr([]);
    +end;
    +
    +{@@ ----------------------------------------------------------------------------
       Inserts a parsed format token into the specified format section before the
       specified element.
     
    @@ -2812,14 +2834,14 @@
     { Creates a formatstring for all sections.
       Note: this implementation is only valid for the fpc and Excel dialects of
       format string. }
    -function TsNumFormatParser.BuildFormatString: String;
    +function TsNumFormatParser.BuildFormatString(AOptions: TsNumFormatOptions): String;
     var
       i: Integer;
     begin
       if Length(FSections) > 0 then begin
    -    Result := BuildFormatStringFromSection(FSections[0]);
    +    Result := BuildFormatStringFromSection(FSections[0], AOptions);
         for i:=1 to High(FSections) do
    -      Result := Result + ';' + BuildFormatStringFromSection(FSections[i]);
    +      Result := Result + ';' + BuildFormatStringFromSection(FSections[i], AOptions);
       end;
     end;
     
    @@ -3162,7 +3184,7 @@
     
     function TsNumFormatParser.GetFormatString: String;
     begin
    -  Result := BuildFormatString;
    +  Result := BuildFormatString([]);
     end;
     
     { Extracts the currency symbol form the formatting sections. It is assumed that
    Index: source/common/fpsopendocument.pas
    ===================================================================
    --- source/common/fpsopendocument.pas	(revision 7151)
    +++ source/common/fpsopendocument.pas	(working copy)
    @@ -5438,18 +5438,20 @@
       nfs: String;
       fmt: TsCellFormat;
       nfParams: TsNumFormatParams;
    +  book: TsWorkbook;
     begin
    -  for i := 0 to (FWorkbook as TsWorkbook).GetNumCellFormats - 1 do
    +  book := FWorkbook as TsWorkbook;
    +  for i := 0 to book.GetNumCellFormats - 1 do
       begin
    -    fmt := TsWorkbook(FWorkbook).GetCellFormat(i);
    +    fmt := book.GetCellFormat(i);
         nfs := '';
         nfidx := fmt.NumberFormatIndex;
         if nfidx <> -1 then
         begin
    -      nfParams := TsWorkbook(FWorkbook).GetNumberFormat(nfidx);
    +      nfParams := book.GetNumberFormat(nfidx);
           if nfParams <> nil then
           begin
    -        nfs := nfParams.NumFormatStr;
    +        nfs := nfParams.BuildNumFormatStr(book.GetNumberFormatOptions);
             for j:=0 to NumFormatList.Count-1 do
             begin
               s := NumFormatList[j];
    Index: source/common/fpspreadsheet.pas
    ===================================================================
    --- source/common/fpspreadsheet.pas	(revision 7151)
    +++ source/common/fpspreadsheet.pas	(working copy)
    @@ -840,6 +840,7 @@
         function AddNumberFormat(AFormatStr: String): Integer;
         function GetNumberFormat(AIndex: Integer): TsNumFormatParams;
         function GetNumberFormatCount: Integer;
    +    function GetNumberFormatOptions: TsNumFormatOptions;
         procedure RemoveAllNumberFormats;
     
         { Formulas }
    @@ -10164,6 +10165,17 @@
     end;
     
     {@@ ----------------------------------------------------------------------------
    +  Returns number format convertion options.
    +-------------------------------------------------------------------------------}
    +function TsWorkbook.GetNumberFormatOptions: TsNumFormatOptions;
    +begin
    +  if boStrictFormat in Options then
    +    Result := [nfoStrictFormat]
    +  else
    +    Result := [];
    +end;
    +
    +{@@ ----------------------------------------------------------------------------
       Removes all numberformats
       Use carefully!
     -------------------------------------------------------------------------------}
    Index: source/common/fpsreaderwriter.pas
    ===================================================================
    --- source/common/fpsreaderwriter.pas	(revision 7151)
    +++ source/common/fpsreaderwriter.pas	(working copy)
    @@ -689,14 +689,18 @@
     var
       i: Integer;
       numFmt: TsNumFormatParams;
    +  numFmtOptions: TsNumFormatOptions;
       numFmtStr: String;
    +  book: TsWorkbook;
     begin
    -  for i:=0 to TsWorkbook(Workbook).GetNumberFormatCount - 1 do
    +  book := TsWorkbook(Workbook);
    +  numFmtOptions := book.GetNumberFormatOptions;
    +  for i:=0 to book.GetNumberFormatCount - 1 do
       begin
    -    numFmt := TsWorkbook(Workbook).GetNumberFormat(i);
    +    numFmt := book.GetNumberFormat(i);
         if numFmt <> nil then
         begin
    -      numFmtStr := numFmt.NumFormatStr;
    +      numFmtStr := numFmt.BuildNumFormatStr(numFmtOptions);
           if FindNumFormatInList(numFmtStr) = -1 then
             FNumFormatList.Add(numFmtStr);
         end;
    Index: source/common/fpstypes.pas
    ===================================================================
    --- source/common/fpstypes.pas	(revision 7151)
    +++ source/common/fpstypes.pas	(working copy)
    @@ -944,7 +944,7 @@
                                    Cannot be used for biff formats. }
       TsWorkbookOption = (boVirtualMode, boBufStream, boFileStream,
         boAutoCalc, boCalcBeforeSaving, boReadFormulas, boWriteZoomFactor,
    -    boAbortReadOnFormulaError, boIgnoreFormulas);
    +    boAbortReadOnFormulaError, boIgnoreFormulas, boStrictFormat);
     
       {@@ Set of option flags for the workbook }
       TsWorkbookOptions = set of TsWorkbookOption;
    Index: source/common/xlsbiff8.pas
    ===================================================================
    --- source/common/xlsbiff8.pas	(revision 7151)
    +++ source/common/xlsbiff8.pas	(working copy)
    @@ -4806,7 +4806,9 @@
       w3: Word;
       nfParams: TsNumFormatParams;
       nfs: String;
    +  book: TsWorkbook;
     begin
    +  book := Workbook as TsWorkbook;
       { BIFF record header }
       rec.RecordID := WordToLE(INT_EXCEL_ID_XF);
       rec.RecordSize := WordToLE(SizeOf(TBIFF8_XFRecord) - SizeOf(TsBIFFHeader));
    @@ -4826,10 +4828,10 @@
       j := 0;
       if (AFormatRecord <> nil) and (uffNumberFormat in AFormatRecord^.UsedFormattingFields)
       then begin
    -    nfParams := (Workbook as TsWorkbook).GetNumberFormat(AFormatRecord^.NumberFormatIndex);
    +    nfParams := book.GetNumberFormat(AFormatRecord^.NumberFormatIndex);
         if nfParams <> nil then
         begin
    -      nfs := nfParams.NumFormatStr;
    +      nfs := nfParams.BuildNumFormatStr(book.GetNumberFormatOptions);
           j := NumFormatList.IndexOf(nfs);
           if j = -1 then j := 0;
         end;
    Index: source/common/xlscommon.pas
    ===================================================================
    --- source/common/xlscommon.pas	(revision 7151)
    +++ source/common/xlscommon.pas	(working copy)
    @@ -421,8 +421,8 @@
       end;
     
       TsExcelNumFormatParser = class(TsNumFormatParser)
    -  protected
    -    function BuildFormatString: String; override;
    +  public
    +    function BuildFormatString(AOptions: TsNumFormatOptions): String; override;
       end;
     
     
    @@ -1074,11 +1074,11 @@
     
     { FPS can use an "ampm" modifier in the time format string, Excel cannot.
       The function replaces is by "AM/PM". }
    -function TsExcelNumFormatParser.BuildFormatString: String;
    +function TsExcelNumFormatParser.BuildFormatString(AOptions: TsNumFormatOptions): String;
     var
       p: Integer;
     begin
    -  Result := inherited;
    +  Result := inherited BuildFormatString(AOptions);
       if IsTimeFormat or IsDateTimeFormat then begin
         p := pos('ampm', Lowercase(Result));
         if p > 0 then Result := Copy(Result, 1, p-1) + 'AM/PM';
    @@ -4455,15 +4455,17 @@
     var
       i: Integer;
       parser: TsNumFormatParser;
    +  fmtOptions: TsNumFormatOptions;
       fmtStr: String;
     begin
       ListAllNumFormats;
    +  fmtOptions := TsWorkbook(Workbook).GetNumberFormatOptions;
       for i:= FFirstNumFormatIndexInFile to NumFormatList.Count-1 do
       begin
         fmtStr := NumFormatList[i];
         parser := TsExcelNumFormatParser.Create(fmtStr, Workbook.FormatSettings);
         try
    -      fmtStr := parser.FormatString;
    +      fmtStr := parser.BuildFormatString(fmtOptions);
           WriteFORMAT(AStream, fmtStr, i);
         finally
           parser.Free;
    Index: source/common/xlsxml.pas
    ===================================================================
    --- source/common/xlsxml.pas	(revision 7151)
    +++ source/common/xlsxml.pas	(working copy)
    @@ -2492,9 +2492,9 @@
         if (uffNumberFormat in fmt^.UsedFormattingFields) then
         begin
           nfp := book.GetNumberFormat(fmt^.NumberFormatIndex);
    -      nfp.AllowLocalizedAMPM := false;    // Replace "AMPM" by "AM/PM"
           AppendToStream(AStream, Format(INDENT3 +
    -        '<NumberFormat ss:Format="%s"/>' + LF, [UTF8TextToXMLText(nfp.NumFormatStr)]));
    +        '<NumberFormat ss:Format="%s"/>' + LF, [
    +          UTF8TextToXMLText(nfp.BuildNumFormatStr(book.GetNumberFormatOptions + [nfoDisableLocalizedAMPM]))]));
         end;
     
         // Background
    Index: source/common/xlsxooxml.pas
    ===================================================================
    --- source/common/xlsxooxml.pas	(revision 7151)
    +++ source/common/xlsxooxml.pas	(working copy)
    @@ -3429,10 +3429,12 @@
     procedure TsSpreadOOXMLWriter.WriteNumFormatList(AStream: TStream);
     var
       i, n: Integer;
    +  numFmtOptions: TsNumFormatOptions;
       numFmtStr: String;
       xmlStr: String;
       parser: TsNumFormatParser;
     begin
    +  numFmtOptions := (FWorkbook as TsWorkbook).GetNumberFormatOptions;
       xmlStr := '';
       n := 0;
       for i:= FFirstNumFormatIndexInFile to NumFormatList.Count-1 do
    @@ -3440,7 +3442,7 @@
         numFmtStr := NumFormatList[i];
         parser := TsExcelNumFormatParser.Create(numFmtStr, Workbook.FormatSettings);
         try
    -      numFmtStr := UTF8TextToXMLText(parser.FormatString);
    +      numFmtStr := UTF8TextToXMLText(parser.BuildFormatString(numFmtOptions));
           xmlStr := xmlStr + Format('<numFmt numFmtId="%d" formatCode="%s" />',
             [i, numFmtStr]);
           inc(n);
    
    strict_num_fmt.diff (14,969 bytes)

Activities

Alexey Emelyanov

2019-10-04 21:33

reporter  

strict_num_fmt.diff (14,969 bytes)
Index: source/common/fpsfunc.pas
===================================================================
--- source/common/fpsfunc.pas	(revision 7151)
+++ source/common/fpsfunc.pas	(working copy)
@@ -2156,7 +2156,7 @@
         Result := FindPart(searchString, s) > 0
         // NOTE: FindPart currently supports only the wildcard '?'
       else
-        Result := SameStr(s, searchString);
+        Result := CompareStr(s, searchString) = 0;
     end else
     begin
       case ACell^.ContentType of
Index: source/common/fpsnumformat.pas
===================================================================
--- source/common/fpsnumformat.pas	(revision 7151)
+++ source/common/fpsnumformat.pas	(working copy)
@@ -154,6 +154,12 @@
   {@@ Array of parsed number format sections }
   TsNumFormatSections = array of TsNumFormatSection;
 
+  {@@ Number format conversion options }
+  TsNumFormatOption = (nfoDisableLocalizedAMPM, nfoStrictFormat);
+
+  {@@ Set of number format conversion options }
+  TsNumFormatOptions = set of TsNumFormatOption;
+
   { TsNumFormatParams }
 
   {@@ Describes a parsed number format and provides all the information to
@@ -160,11 +166,12 @@
     convert a number to a number or date/time string. These data are created
     by the number format parser from a format string. }
   TsNumFormatParams = class(TObject)
+  public
+    function BuildNumFormatStr(AOptions: TsNumFormatOptions): String; virtual;
   private
-    FAllowLocalizedAMPM: Boolean;
+    function GetNumFormatStr: String;
   protected
     function GetNumFormat: TsNumberFormat; virtual;
-    function GetNumFormatStr: String; virtual;
   public
     {@@ Array of the format sections }
     Sections: TsNumFormatSections;
@@ -177,7 +184,6 @@
     procedure SetDecimals(AValue: Byte);
     procedure SetNegativeRed(AEnable: Boolean);
     procedure SetThousandSep(AEnable: Boolean);
-    property AllowLocalizedAMPM: boolean read FAllowLocalizedAMPM write FAllowLocalizedAMPM;
     property NumFormat: TsNumberFormat read GetNumFormat;
     property NumFormatStr: String read GetNumFormatStr;
   end;
@@ -271,9 +277,9 @@
     procedure CheckSections;
     procedure CheckSection(ASection: Integer);
     procedure FixMonthMinuteToken(var ASection: TsNumFormatSection);
+  public
     // Format string
-    function BuildFormatString: String; virtual;
-
+    function BuildFormatString(AOptions: TsNumFormatOptions): String; virtual;
   public
     constructor Create(const AFormatString: String;
       const AFormatSettings: TFormatSettings);
@@ -319,7 +325,7 @@
   AMinIntDigits: Integer = 1): String;
 
 function BuildFormatStringFromSection(const ASection: TsNumFormatSection;
-  AllowLocalizedAMPM: Boolean = true): String;
+  AOptions: TsNumFormatOptions = []): String;
 
 function ApplyTextFormat(AText: String; AParams: TsNumFormatParams): String;
 function ConvertFloatToStr(AValue: Double; AParams: TsNumFormatParams;
@@ -1476,7 +1482,7 @@
           @return Excel-compatible format string
 -------------------------------------------------------------------------------}
 function BuildFormatStringFromSection(const ASection: TsNumFormatSection;
-  AllowLocalizedAMPM: Boolean = true): String;
+  AOptions: TsNumFormatOptions = []): String;
 var
   element: TsNumFormatElement;
   i, n: Integer;
@@ -1527,11 +1533,17 @@
       nftText:
         if element.TextValue <> '' then result := Result + '"' + element.TextValue + '"';
       nftYear:
-        Result := Result + DupeString('Y', element.IntValue);
+        if nfoStrictFormat in AOptions
+          then Result := Result + DupeString('y', element.IntValue)
+          else Result := Result + DupeString('Y', element.IntValue);
       nftMonth:
-        Result := Result + DupeString('M', element.IntValue);
+        if nfoStrictFormat in AOptions
+          then Result := Result + DupeString('m', element.IntValue)
+          else Result := Result + DupeString('M', element.IntValue);
       nftDay:
-        Result := Result + DupeString('D', element.IntValue);
+        if nfoStrictFormat in AOptions
+          then Result := Result + DupeString('d', element.IntValue)
+          else Result := Result + DupeString('D', element.IntValue);
       nftHour:
         if element.IntValue < 0
           then Result := Result + '[' + DupeString('h', -element.IntValue) + ']'
@@ -2201,7 +2213,6 @@
 constructor TsNumFormatParams.Create;
 begin
   inherited;
-  FAllowLocalizedAMPM := true;
 end;
 
 {@@ ----------------------------------------------------------------------------
@@ -2257,19 +2268,30 @@
 
   @return  Excel-compatible number format string.
 -------------------------------------------------------------------------------}
-function TsNumFormatParams.GetNumFormatStr: String;
+function TsNumFormatParams.BuildNumFormatStr(AOptions: TsNumFormatOptions): String;
 var
   i: Integer;
 begin
   if Length(Sections) > 0 then begin
-    Result := BuildFormatStringFromSection(Sections[0]);
+    Result := BuildFormatStringFromSection(Sections[0], AOptions);
     for i := 1 to High(Sections) do
-      Result := Result + ';' + BuildFormatStringFromSection(Sections[i], FAllowLocalizedAMPM);
+      Result := Result + ';' + BuildFormatStringFromSection(Sections[i], AOptions);
   end else
     Result := '';
 end;
 
 {@@ ----------------------------------------------------------------------------
+  Constructs the number format string from the parsed sections and elements.
+  The format symbols are selected according to Excel syntax.
+
+  @return  Excel-compatible number format string.
+-------------------------------------------------------------------------------}
+function TsNumFormatParams.GetNumFormatStr: String;
+begin
+  Result := BuildNumFormatStr([]);
+end;
+
+{@@ ----------------------------------------------------------------------------
   Inserts a parsed format token into the specified format section before the
   specified element.
 
@@ -2812,14 +2834,14 @@
 { Creates a formatstring for all sections.
   Note: this implementation is only valid for the fpc and Excel dialects of
   format string. }
-function TsNumFormatParser.BuildFormatString: String;
+function TsNumFormatParser.BuildFormatString(AOptions: TsNumFormatOptions): String;
 var
   i: Integer;
 begin
   if Length(FSections) > 0 then begin
-    Result := BuildFormatStringFromSection(FSections[0]);
+    Result := BuildFormatStringFromSection(FSections[0], AOptions);
     for i:=1 to High(FSections) do
-      Result := Result + ';' + BuildFormatStringFromSection(FSections[i]);
+      Result := Result + ';' + BuildFormatStringFromSection(FSections[i], AOptions);
   end;
 end;
 
@@ -3162,7 +3184,7 @@
 
 function TsNumFormatParser.GetFormatString: String;
 begin
-  Result := BuildFormatString;
+  Result := BuildFormatString([]);
 end;
 
 { Extracts the currency symbol form the formatting sections. It is assumed that
Index: source/common/fpsopendocument.pas
===================================================================
--- source/common/fpsopendocument.pas	(revision 7151)
+++ source/common/fpsopendocument.pas	(working copy)
@@ -5438,18 +5438,20 @@
   nfs: String;
   fmt: TsCellFormat;
   nfParams: TsNumFormatParams;
+  book: TsWorkbook;
 begin
-  for i := 0 to (FWorkbook as TsWorkbook).GetNumCellFormats - 1 do
+  book := FWorkbook as TsWorkbook;
+  for i := 0 to book.GetNumCellFormats - 1 do
   begin
-    fmt := TsWorkbook(FWorkbook).GetCellFormat(i);
+    fmt := book.GetCellFormat(i);
     nfs := '';
     nfidx := fmt.NumberFormatIndex;
     if nfidx <> -1 then
     begin
-      nfParams := TsWorkbook(FWorkbook).GetNumberFormat(nfidx);
+      nfParams := book.GetNumberFormat(nfidx);
       if nfParams <> nil then
       begin
-        nfs := nfParams.NumFormatStr;
+        nfs := nfParams.BuildNumFormatStr(book.GetNumberFormatOptions);
         for j:=0 to NumFormatList.Count-1 do
         begin
           s := NumFormatList[j];
Index: source/common/fpspreadsheet.pas
===================================================================
--- source/common/fpspreadsheet.pas	(revision 7151)
+++ source/common/fpspreadsheet.pas	(working copy)
@@ -840,6 +840,7 @@
     function AddNumberFormat(AFormatStr: String): Integer;
     function GetNumberFormat(AIndex: Integer): TsNumFormatParams;
     function GetNumberFormatCount: Integer;
+    function GetNumberFormatOptions: TsNumFormatOptions;
     procedure RemoveAllNumberFormats;
 
     { Formulas }
@@ -10164,6 +10165,17 @@
 end;
 
 {@@ ----------------------------------------------------------------------------
+  Returns number format convertion options.
+-------------------------------------------------------------------------------}
+function TsWorkbook.GetNumberFormatOptions: TsNumFormatOptions;
+begin
+  if boStrictFormat in Options then
+    Result := [nfoStrictFormat]
+  else
+    Result := [];
+end;
+
+{@@ ----------------------------------------------------------------------------
   Removes all numberformats
   Use carefully!
 -------------------------------------------------------------------------------}
Index: source/common/fpsreaderwriter.pas
===================================================================
--- source/common/fpsreaderwriter.pas	(revision 7151)
+++ source/common/fpsreaderwriter.pas	(working copy)
@@ -689,14 +689,18 @@
 var
   i: Integer;
   numFmt: TsNumFormatParams;
+  numFmtOptions: TsNumFormatOptions;
   numFmtStr: String;
+  book: TsWorkbook;
 begin
-  for i:=0 to TsWorkbook(Workbook).GetNumberFormatCount - 1 do
+  book := TsWorkbook(Workbook);
+  numFmtOptions := book.GetNumberFormatOptions;
+  for i:=0 to book.GetNumberFormatCount - 1 do
   begin
-    numFmt := TsWorkbook(Workbook).GetNumberFormat(i);
+    numFmt := book.GetNumberFormat(i);
     if numFmt <> nil then
     begin
-      numFmtStr := numFmt.NumFormatStr;
+      numFmtStr := numFmt.BuildNumFormatStr(numFmtOptions);
       if FindNumFormatInList(numFmtStr) = -1 then
         FNumFormatList.Add(numFmtStr);
     end;
Index: source/common/fpstypes.pas
===================================================================
--- source/common/fpstypes.pas	(revision 7151)
+++ source/common/fpstypes.pas	(working copy)
@@ -944,7 +944,7 @@
                                Cannot be used for biff formats. }
   TsWorkbookOption = (boVirtualMode, boBufStream, boFileStream,
     boAutoCalc, boCalcBeforeSaving, boReadFormulas, boWriteZoomFactor,
-    boAbortReadOnFormulaError, boIgnoreFormulas);
+    boAbortReadOnFormulaError, boIgnoreFormulas, boStrictFormat);
 
   {@@ Set of option flags for the workbook }
   TsWorkbookOptions = set of TsWorkbookOption;
Index: source/common/xlsbiff8.pas
===================================================================
--- source/common/xlsbiff8.pas	(revision 7151)
+++ source/common/xlsbiff8.pas	(working copy)
@@ -4806,7 +4806,9 @@
   w3: Word;
   nfParams: TsNumFormatParams;
   nfs: String;
+  book: TsWorkbook;
 begin
+  book := Workbook as TsWorkbook;
   { BIFF record header }
   rec.RecordID := WordToLE(INT_EXCEL_ID_XF);
   rec.RecordSize := WordToLE(SizeOf(TBIFF8_XFRecord) - SizeOf(TsBIFFHeader));
@@ -4826,10 +4828,10 @@
   j := 0;
   if (AFormatRecord <> nil) and (uffNumberFormat in AFormatRecord^.UsedFormattingFields)
   then begin
-    nfParams := (Workbook as TsWorkbook).GetNumberFormat(AFormatRecord^.NumberFormatIndex);
+    nfParams := book.GetNumberFormat(AFormatRecord^.NumberFormatIndex);
     if nfParams <> nil then
     begin
-      nfs := nfParams.NumFormatStr;
+      nfs := nfParams.BuildNumFormatStr(book.GetNumberFormatOptions);
       j := NumFormatList.IndexOf(nfs);
       if j = -1 then j := 0;
     end;
Index: source/common/xlscommon.pas
===================================================================
--- source/common/xlscommon.pas	(revision 7151)
+++ source/common/xlscommon.pas	(working copy)
@@ -421,8 +421,8 @@
   end;
 
   TsExcelNumFormatParser = class(TsNumFormatParser)
-  protected
-    function BuildFormatString: String; override;
+  public
+    function BuildFormatString(AOptions: TsNumFormatOptions): String; override;
   end;
 
 
@@ -1074,11 +1074,11 @@
 
 { FPS can use an "ampm" modifier in the time format string, Excel cannot.
   The function replaces is by "AM/PM". }
-function TsExcelNumFormatParser.BuildFormatString: String;
+function TsExcelNumFormatParser.BuildFormatString(AOptions: TsNumFormatOptions): String;
 var
   p: Integer;
 begin
-  Result := inherited;
+  Result := inherited BuildFormatString(AOptions);
   if IsTimeFormat or IsDateTimeFormat then begin
     p := pos('ampm', Lowercase(Result));
     if p > 0 then Result := Copy(Result, 1, p-1) + 'AM/PM';
@@ -4455,15 +4455,17 @@
 var
   i: Integer;
   parser: TsNumFormatParser;
+  fmtOptions: TsNumFormatOptions;
   fmtStr: String;
 begin
   ListAllNumFormats;
+  fmtOptions := TsWorkbook(Workbook).GetNumberFormatOptions;
   for i:= FFirstNumFormatIndexInFile to NumFormatList.Count-1 do
   begin
     fmtStr := NumFormatList[i];
     parser := TsExcelNumFormatParser.Create(fmtStr, Workbook.FormatSettings);
     try
-      fmtStr := parser.FormatString;
+      fmtStr := parser.BuildFormatString(fmtOptions);
       WriteFORMAT(AStream, fmtStr, i);
     finally
       parser.Free;
Index: source/common/xlsxml.pas
===================================================================
--- source/common/xlsxml.pas	(revision 7151)
+++ source/common/xlsxml.pas	(working copy)
@@ -2492,9 +2492,9 @@
     if (uffNumberFormat in fmt^.UsedFormattingFields) then
     begin
       nfp := book.GetNumberFormat(fmt^.NumberFormatIndex);
-      nfp.AllowLocalizedAMPM := false;    // Replace "AMPM" by "AM/PM"
       AppendToStream(AStream, Format(INDENT3 +
-        '<NumberFormat ss:Format="%s"/>' + LF, [UTF8TextToXMLText(nfp.NumFormatStr)]));
+        '<NumberFormat ss:Format="%s"/>' + LF, [
+          UTF8TextToXMLText(nfp.BuildNumFormatStr(book.GetNumberFormatOptions + [nfoDisableLocalizedAMPM]))]));
     end;
 
     // Background
Index: source/common/xlsxooxml.pas
===================================================================
--- source/common/xlsxooxml.pas	(revision 7151)
+++ source/common/xlsxooxml.pas	(working copy)
@@ -3429,10 +3429,12 @@
 procedure TsSpreadOOXMLWriter.WriteNumFormatList(AStream: TStream);
 var
   i, n: Integer;
+  numFmtOptions: TsNumFormatOptions;
   numFmtStr: String;
   xmlStr: String;
   parser: TsNumFormatParser;
 begin
+  numFmtOptions := (FWorkbook as TsWorkbook).GetNumberFormatOptions;
   xmlStr := '';
   n := 0;
   for i:= FFirstNumFormatIndexInFile to NumFormatList.Count-1 do
@@ -3440,7 +3442,7 @@
     numFmtStr := NumFormatList[i];
     parser := TsExcelNumFormatParser.Create(numFmtStr, Workbook.FormatSettings);
     try
-      numFmtStr := UTF8TextToXMLText(parser.FormatString);
+      numFmtStr := UTF8TextToXMLText(parser.BuildFormatString(numFmtOptions));
       xmlStr := xmlStr + Format('<numFmt numFmtId="%d" formatCode="%s" />',
         [i, numFmtStr]);
       inc(n);
strict_num_fmt.diff (14,969 bytes)

wp

2019-10-04 23:01

developer   ~0118337

I don't have a Mac, and Numbers is a program which I do not know. So I cannot check.

It is not a problem to write lower-case day/month/year format place holders to the file; Excel and Calc do not care. I don't know why I had selected upper-case characters...

Just let me rephrase the issue to make sure that I understand correctly: When a date is written to an Excel file formatted with capital symbols (DD.MM.YYYY) then the days are displayed as days since Jan 1, not as days of the specific month?
Please confirm, and I'll change fpspreadsheet to lower-case place holders. I think it is not necessary to introduce a specific option for this.

Alexey Emelyanov

2019-10-06 18:52

reporter   ~0118384

That's right, you have got the situation right.

The decision to do without an additional option also seems reasonable to me.
At least files created using fpspreadsheet with lowercase placeholders in date format strings displayed correctly in Excel 2003, Libre Office 4.4 & 5.4 (checked for Excel 8 format (.xls), Office Open XML (.xlsx)).

By the way, if we are talking about simplification of solutions, please take a look at the AllowLocalizedAMPM property in the TsNumFormatParams class.
It is not used in revision 7151 in the BuildFormatStringFromSection function.
Is that correct?

wp

2019-10-06 19:51

developer   ~0118385

Changed to lower-case symbols. Please test and close if ok (or re-open if not).

As for the AllowLocalizedAMPM, I have to investigate. I guess this is half-finished work, got distracted and forgot about it...

Alexey Emelyanov

2019-10-10 20:23

reporter   ~0118474

Thank you.

Tested in Numbers for Mac OS X, Excel 2003, Libre Office 4.4 & 5.4.
Everything works correctly.

Issue History

Date Modified Username Field Change
2019-10-04 21:33 Alexey Emelyanov New Issue
2019-10-04 21:33 Alexey Emelyanov File Added: strict_num_fmt.diff
2019-10-04 23:01 wp Note Added: 0118337
2019-10-04 23:01 wp Assigned To => wp
2019-10-04 23:01 wp Status new => assigned
2019-10-04 23:01 wp Status assigned => feedback
2019-10-06 18:52 Alexey Emelyanov Note Added: 0118384
2019-10-06 18:52 Alexey Emelyanov Status feedback => assigned
2019-10-06 19:51 wp Status assigned => resolved
2019-10-06 19:51 wp Resolution open => fixed
2019-10-06 19:51 wp Note Added: 0118385
2019-10-10 20:23 Alexey Emelyanov Status resolved => closed
2019-10-10 20:23 Alexey Emelyanov Note Added: 0118474