I\'m trying to validate the contents of Excel sheets that follow several differe
ID: 643742 • Letter: I
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-Zd]{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?
Explanation / 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.