Excel Conditional Numeric Formats in .Net Format

Question

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

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

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

Before I go ahead and create my own custom string parser, does anyone know if there is something like this in .Net that I could use? I know that there is: -a separator for formatted strings, but it does not seem to be able to accept the conditions, with the exception of "negative / positive / zero", as far as I can tell.

Answer 1

You will need to do the formatting yourself, but you can connect to the existing .NET formatting platform using the IFormatProvider and ICustomFormatter interfaces.

Here is one example of how to do this. A ConditionalFormatter class is created, consisting of several ConditionalFormat objects. The ConditionalFormat class has Predicate and a Format . ConditionalFormatter will look for all ConditionalFormat objects, sequentially defining the first where Predicate 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 need to create an instance that matches the type you want to format. The following is an example of 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); 

Answer 2

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

The syntax for your example is:

 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 want to support the exact Excel syntax mentioned above, you can download the SmartFormat source and modify the ConditionalFormatter to support your syntax. It uses a regular expression to parse each (condition) , so it's easy to change it.

Answer 3

you need to write if / else if statements. No direct way

 string numberToFormat = //state your number here string FormattedString = null; if(numberToFormat >2) { //Format 1 //eg 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 conditional formatter.