Regular Expression RegEx to validate Custom Excel formats

I am creating Excel spreadsheets programmaticly (in C#/.Net using the fabulous EPPlus library) and am passing in an array of cell ranges (string-based) and the custom format string to apply to these ranges.

// Struct:
public struct ExcelColumnFormat
{
    public string CellRangeDefinition;
    public string ExcelFormatString;
}

-

// Usage
ExcelHelper.ExcelColumnFormat[] formats = new ExcelHelper.ExcelColumnFormat[1];
formats[0].ExcelFormatString = "$#,##0.00";
formats[0].CellRangeDefinition = "I:I";

-

// Consumption / application of those formats
foreach (ExcelColumnFormat format in formats)
{
    // Make sure that there are non-zero or null strings passed in
    if (!String.IsNullOrEmpty(format.ExcelFormatString) && !String.IsNullOrEmpty(format.CellRangeDefinition))
        ws.Cells[format.CellRangeDefinition].Style.Numberformat.Format = format.ExcelFormatString;
}

I am already checking the strings for IsNullOrEmpty, but I wanted to put another level of validation to test if the custom format strings are, in fact, valid Excel formats. Here is a link to the Office help file that describes the rules around Excel Formats.

The question is: given the many permutations available in custom Excel formats, is this a worthwhile endeavor and how might the RegEx look?

Thanks in advance!

Answers


Or you could let VBA figure it out:

On Error GoTo complain
junk = Format(dummyData,formatString)
...

The dummyData could be a reference to a cell in the given range if the type of data isn't known.


Need Your Help

Reducing Android Alternative Drawable Resources

android drawable android-resources

I have images that need to scale to the screen's size. The images will also have text in it that needs to be translated to a second language. So there will be two versions of each image to start, o...

CodeIgniter: Trouble removing “index.php” from URL

php codeigniter url url-rewriting

I have been trying to fix this for days and have tried the following tutorials: