C# – Class Structure for Validating Different Excel Templates

cdesign-patternsvalidation

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?

Best Answer

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.

Related Topic