Excel style conditional numeric formats in .Net

I need to use Excel conditional format strings to format numbers in a .Net application. For those unfamiliar with them, the Excel format strings look like this:

[>=2]#,##0.0;[<=-2]-#,##0.0;#,##0.00

...which should be interpreted as "use the first开发者_StackOverflow format for numbers greater than two, the second for numbers lower than -2 and the third format for everything else".

Before I go ahead and build my own custom format string parser, does anyone know if there is anything similar to this in .Net that I could use? I'm aware there's the ;-separator for format strings, but it doesn't seem be able to take conditions beyond "is negative/positive/zero" into account as far as I can tell.

You will have to implement the formatting yourself but you can hook into .NET's existing formatting framework by using the interfaces IFormatProvider and ICustomFormatter.

Here is one example on how to do that. A ConditionalFormatter class is created that consists of several ConditionalFormat objects. The ConditionalFormat class has a Predicate and a Format. The ConditionalFormatter will search all ConditionalFormat objects in sequence finding the first where Predicate is true and use the associated Format. The formatter uses the letter 'Z' as the format string.

class ConditionalFormat<T> where T : IFormattable {
  public Func<T, Boolean> Predicate { get; set; }
  public String Format { get; set; }
  public static readonly Func<T, Boolean> Tautology = _ => true;
}

class ConditionalFormatter<T> : Collection<ConditionalFormat<T>>, IFormatProvider, ICustomFormatter
  where T : IFormattable {

  public const String FormatString = "Z";

  readonly CultureInfo cultureInfo;

  public ConditionalFormatter(IEnumerable<ConditionalFormat<T>> conditionalFormats)
    : this(conditionalFormats, null) { }

  public ConditionalFormatter(IEnumerable<ConditionalFormat<T>> conditionalFormats, CultureInfo cultureInfo)
    : base(conditionalFormats.ToList()) {
    this.cultureInfo = cultureInfo;
  }

  public Object GetFormat(Type formatType) {
    return formatType == typeof(ICustomFormatter) ? this : null;
  }

  public String Format(String format, Object arg, IFormatProvider formatProvider) {
    if (arg.GetType() != typeof(T))
      return HandleOtherFormats(format, arg);
    var formatUpperCase = format.ToUpperInvariant();
    if (formatUpperCase != FormatString)
      return HandleOtherFormats(format, arg);

    var value = (T) arg;
    foreach (var conditionalFormat in this)
      if (conditionalFormat.Predicate(value))
        return ((IFormattable) value).ToString(conditionalFormat.Format, cultureInfo);

    throw new InvalidOperationException(String.Format("No format matching value {0}.", value));
  }

  String HandleOtherFormats(String format, Object arg) {
    var formattable = arg as IFormattable;
    if (formattable != null)
      return formattable.ToString(format, this.cultureInfo);
    else if (arg != null)
      return arg.ToString();
    else
      return String.Empty;
  }

}

The class is generic and you will have to create an instance that matches the type you want to format. Here is an example using Double:

var conditionalFormatter = new ConditionalFormatter<Double>(
  new[] {
    new ConditionalFormat<Double> {
      Predicate = d => -2 < d && d < 2,
      Format = "#,##0.00"
    },
    new ConditionalFormat<Double> {
      Predicate = ConditionalFormat<Double>.Tautology,
      Format = "#,##0.0"
    },
  }
);
var value = 1234.5678;
var formattedValue = String.Format(conditionalFormatter, "Value is {0:Z}", value);

Another interesting solution: there is a library called SmartFormat that includes a conditional formatter.

The syntax for your example would be:

var output = Smart.Format("{0:>=2?{0:#,##0.0}|<=-2?{0:-#,##0.0}|{0:#,##0.00}}", value);

For an explanation of the syntax, see ConditionalFormatter.

However, if you wanted to support the exact Excel syntax you mentioned above, you could download the SmartFormat source and modify the ConditionalFormatter to support your syntax. It uses a Regex to parse each (condition), so that would be easy to modify.

you need to write if/else if statements. There is no direct way

string numberToFormat = //state your number here
string FormattedString = null;

if(numberToFormat >2)
{
    //Format 1
    //e.g. FormattedString = String.Format("{0:0.00}", numberToFormat);

}

else if(numberToFormat < -2)
{
     //Format 2
}

else 
{
     // Format Default     
}

This is the shortest way. But of course you can get your own conditional formatter.