How to evaluate Excel format string

Question

How to evaluate an Excel format string programmatically?

I am programmatically creating Excel cells based on data in a certain format, e.g xml in some of the the cells; there are format strings that need to be applied to them.

e.g "[Red] #,##0.00" for 987564.63635

before filling into the cell, I want to check (basically only colors) for the cell or text. In case it is won't display well to the user, e.g light yellow is very bad, a user can hardly see this color.

So it comes to before creating the cell, I need to know the color from the format string e.g "[Red] #,##0.00" or "[Color 32] dd/mm/yyyy" or an even more complex Excel format string.

I want to know. Is there some kind of library that can do this job? or does .net have this kind of function already? or i have to do it by myself?

Sample

e.g if format string is "[Red] #,##0,00", i want to know the corresponding Sysmte.draw.Color for the [Red] base on the string. or the corresponding web color, ff0000, for [Red] base on the string or corresponding RGB values

Please clarify what you mean by "evaluate", perhaps illustrating with an example what the input is and what the corresponding output should be. Is "[Red] #,##0.00" the input? Would "Red" then be the output? Or (255,0,0)? — Jean-François Corbett
– Jean-François Corbett, Commented Jun 29, 2011 at 6:21

NirMH · Accepted Answer · 2011-06-29 13:11:22Z

2

You can use the Excel range object to access all the cell's configuration setup.

for example:

Worksheet sheet = excelApp.ActiveWorkbook.ActiveSheet;
Range r = sheet.Cells[1, 1];
MessageBox.Show(r.Value2 + "\n" + r.Text + "\n" + r.NumberFormat);

r.Value2 holds the underline value of the cell (the formula).

r.Text holds the visible (user) value of the cell (the result).

r.NumberFormat holds the format string - in your example it will hold the "[Red] #,##0.00".

From there you'll need to use any string manipulation techniques on the NumberFormat member (i recommend a regular expression) to find the color you are looking for or any other thing.

EDIT, here is some usage of regExp and TypeCovertor to accomplish the string to Color object conversion. (with a continue to the above source)

string formatString = r.NumberFormat;
Regex colorRegExp = new Regex(@"^\[(?<color>\S+)\]", RegexOptions.Compiled);

Color resultColor;
System.ComponentModel.TypeConverter converter = System.ComponentModel.TypeDescriptor.GetConverter(new Color());

Match m = colorRegExp .Match(formatString);
if (true == m.Success)
{
   string colorString = m.Groups["color"].Value;
   resultColor = (Color)converter.ConvertFromString(colorString);
}

The regular expression object is compiled to the assembly for performance. I assume using the Color resultColor object is something you can figure yourself (it has ARGB and other members to examine).

And last comment, you can easily reduce the number of lines in this example, i just wanted to emphasize each step on the way.

edited Jun 29, 2011 at 13:11

answered Jun 29, 2011 at 6:02

NirMH

5,0255 gold badges54 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jean-François Corbett Over a year ago

"use any string manipulation techniques on the NumberFormat member" I think that's what the OP is asking how to do...

NirMH Over a year ago

@Corbett: thanks for pointing this, somehow i missed the original question ;-), see my update above

Collectives™ on Stack Overflow

How to evaluate Excel format string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related