up vote 1 down vote favorite

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?

share|improve this question

1 Answer

up vote 0 down vote

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. :)

share|improve this answer

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.