CODEWITHSUNDEEP
delphidelphi-xe8
boggy
boggy

Reputation: 4027

How do I set a dataset's DisplayFormat to match the Windows currency format?

I am looking for a way to read the default currency related formatting information stored in Windows and build a format string, including the formatting for negative numbers, that I can use as the DisplayFormat of a dataset field.

For instance, In C# there is the format "C" for currency (see Standard Numeric Format Strings).

For example (I ran it in linqpad):

String.Format("{0:C}", -120)

It prints ($120.00), which matches my Windows settings.

In Delphi I would need a function that looks at the TFormatSettings properties and builds the currency format string, something like this: $,0.00;($,0.00) (I am using English-US default settings).

I'm trying to work around an issue in the DevExpress quantum grid where the SUM footer aggregate for a column with currency values doesn't format negative numbers as defined by the OS settings.

Upvotes: 1

Views: 1684

Answers (1)

boggy
boggy

Reputation: 4027

I ended up using the code below. The first two functions are from data\Data.FMTBcd.pas.

One thing I noticed doing some testing, if I change the Decimal Symbol or the Digit grouping symbol in Control Panel -> Region and Language -> Additional Settings -> Currency tab, TFormatSettings doesn't read those new values, it always returns the dot as the decimal separator and the comma as the thousands separator. Instead, it uses these settings from the Numbers tab. 

function AddCurrencySymbol(const Value, CurrSymbol: string; const CurrFormat: Byte): string;
begin
  case CurrFormat of
    0: Result := Format('%s%s', [CurrSymbol, Value]);
    1: Result := Format('%s%s', [Value, CurrSymbol]);
    2: Result := Format('%s %s', [CurrSymbol, Value]);
    3: Result := Format('%s %s', [Value, CurrSymbol]);
  end;
end;

{   0 = '($1)'      4 = '(1$)'      8 = '-1 $'      12 = '$ -1'
    1 = '-$1'       5 = '-1$'       9 = '-$ 1'      13 = '1- $'
    2 = '$-1'       6 = '1-$'      10 = '1 $-'      14 = '($ 1)'
    3 = '$1-'       7 = '1$-'      11 = '$ 1-'      15 = '(1 $)'  }
function AddNegCurrencySymbol(const Value, CurrSymbol: string; const CurrFormat: Byte): string;
begin
  case CurrFormat of
    0: Result := Format('(%s%s)', [CurrSymbol, Value]);
    1: Result := Format('-%s%s', [CurrSymbol, Value]);
    2: Result := Format('%s-%s', [CurrSymbol, Value]);
    3: Result := Format('%s%s-', [CurrSymbol, Value]);
    4: Result := Format('(%s%s)', [Value, CurrSymbol]);
    5: Result := Format('-%s%s', [Value, CurrSymbol]);
    6: Result := Format('%s-%s', [Value, CurrSymbol]);
    7: Result := Format('%s%s-', [Value, CurrSymbol]);
    8: Result := Format('-%s %s', [Value, CurrSymbol]);
    9: Result := Format('-%s %s', [CurrSymbol, Value]);
   10: Result := Format('%s %s-', [Value, CurrSymbol]);
   11: Result := Format('%s %s-', [CurrSymbol, Value]);
   12: Result := Format('%s %s', [CurrSymbol, Value]);
   13: Result := Format('%s -%s', [Value, CurrSymbol]);
   14: Result := Format('(%s- %s)', [CurrSymbol, Value]);
   15: Result := Format('(%s %s)', [Value, CurrSymbol]);
  end;
end;

function DefaultCurrencyFormat(formatSettings: TFormatSettings): String;
begin

 if formatSettings.CurrencyDecimals > 0 then
   Result := '0' + formatSettings.DecimalSeparator + StringOfChar('0', formatSettings.CurrencyDecimals)
 else
   Result := '0';

 Result := formatSettings.ThousandSeparator + Result;

 Result := AddCurrencySymbol(Result, formatSettings.CurrencyString, formatSettings.CurrencyFormat)
        + ';' + AddNegCurrencySymbol(Result, formatSettings.CurrencyString, formatSettings.NegCurrFormat)

end;

Upvotes: 1

Related Questions