Class structure/pattern for validating several different Excel templates?

Question

I'm trying to validate the contents of Excel sheets that follow several different templates. For each one, there are three possible validation actions for various cells:

• Regex (e.g. "XYZ-123" fails because it doesn't fit pattern "[A-Z]{4}-\d{3}")
• Date/Time format (e.g. "Jan 24, 2013" fails because it's not mm/dd/yyyy)
• Data type (e.g. "xyz" fails because it's not an integer)

My first thought was to use the following class:

public class Validator
{
 Excel.Worksheet vSheet;
 List<Tuple<string,string,string>> cellActions=new List<Tuple<string,string,string>>();

 //populates the list of cellActions based on the template type
 public Validator(Excel.Worksheet sht)
 {
  this.vSheet=sht;
  string templateType=templateCheck(sht);
  switch (templateType)
  {
   case "type1":
    cellActions.Add(new Tuple<string,string,string>("C5","regex","[A-Z\d]{6}");
    cellActions.Add(new Tuple<string,string,string>("D3","datatype","long");
    //and so on for another 30 list items
    break;
   case "type2"
    cellActions.Add(new Tuple<string,string,string>("A3","date_time","yyyy-MM-dd HH:mm");
    cellActions.Add(new Tuple<string,string,string>("A6","regex",".+\s[ACGT]{3}");
    //etc.
  }
 }

 private string templateCheck(Excel.Worksheet sht)
 {
  //return template type as string based on contents of worksheet
 }

 public void validate()
 {
  foreach(Tuple<string,string,string> cellAction in cellActions)
  {
   //check the contents of the cell in the vSheet property according to cellAction's rules
  }
 }
}

}

Is this a case where Strategy Pattern would be appropriate? There are several different types of sheets, each with a different list of cells and rules for those cells, so maybe it would be right to think about each template as a strategy to be implemented as a class. Or is this not a situation where the pattern applies, given that each of the 3 validation action types is always the same regardless of which template it's applied in?

Answer 1

Think about what problem you're trying to solve, and where it might change. If this is a case of using Excel for users to submit data, you really should consider XML data-binding and an XSD. (Or, at least, Excel's built-in rules for validation.)

For the specific question, no, I don't think a Strategy pattern is appropriate here. You're performing the same task, just with a different input each time, and having too-tight binding between your C# program code and the particular standards that happen to be.

Remove the standard rules to a data store of some kind (XML, SQL, flat-file, even an XSL), and load them as you would any other set of data to be tested. Unless you are using those values and data-structures in code, you shouldn't bind them so tightly. And with 30+ tests per type of input, it smells like something that will change more often that you suspect.

Answer 2

I would go with specific typing for your cells. C5 is an... identifier type cell? Go specific, as specific as your domain requires.

Then, describe each sheet in terms of what cells with what types it contains. Encapsulate that, put it in a class that represents the sheet.

Now you have a nice object structure representing your allowable Excel spreadsheets. Validation gets encapsulated in the type for each cell. Note how you achieve maximum reuse and capture the structure of your sheets.

As to whether this is strategy, the lines are blurry, but yes, I consider the validators thus embedded in the types as an incarnation of the Strategy design pattern.

Validation now becomes a simple matter of going through each sheet, and each cell of interest, and invoking the validator on the cell, which will delegate to the type validator.

class CellType {
    bool IsValid(string value);
    string DisplayName { get; }
}

class Cell {
    string Value { get; set }
    CellType Type { get; set; }
    boolean IsValid() { return Type.IsValid(Value); }
}

class CellPosition {
    ...
}

class Sheet {
    Cell CellAt(CellPosition position);
    IEnumerable<Cell> CellsOfInterest { get; }
}

void Validate() {
    foreach (Sheet sheet in sheets) {
        foreach (Cell cell in sheet.CellsOfInterest) {
            if (!cell.IsValid()) {
                // Whoops!
            }
        }
    }
}

Note: it is overkill to employ Visitor to validate the degenerate "composite" structure induced by Sheet and Cell, as the structure always predictably has exactly two layers. For that reason, I have not mentioned it. :)