Using Attributes in Excel Add-in Framework for Validating and Exporting Data

• Download source code

Summary

Attributes can be used to improve maintainability. I converted this application to use Attributes to specify information with each property in the class, making maintenance a lot easier and reduce the chances of bugs. These properties of each property are used to modify the properties before use, and to determine whether the value of the property is valid. Previously there was a method in the class that returned a collection of classes that contained this information.

Introduction

I previously posted an article Excel Add-in Framework for Validating and Exporting Data on Codeproject. Before I posted it I made a number of improvements over my original implementation, many of which improved reuse. Eventually the design required only one customized class to use outside of some UI stuff. I also wanted to improve the way that validation was specified. I originally designed the specification of the validation to be done with anonymous methods, but this was not as maintainable as I would have liked. Part of the problem was that I not only had to have the properties defined in a class, but I also had to specify the properties in a method that included the name of the property as a string, and the validation. This meant that there were two places had to define a property, with all the maintenance issues associated with this.

I suspected that I could do better by using the Attribute class, but there were more important issues to clean up and I had never programmed attributes. Then there were a number of design issues that would have to be resolved, and the possibility of doing serious damage to the application.

Research

I started out searching for articles on the Codeproject site. The first article I read was A Programmer's Introduction to C# - Chapter 21. The article is Ok, but was a little simplistic, probably because it was targeted at a class and not a class’s properties and methods. The second article was much more useful: An attribute based approach to business object validation was more useful since it was somewhat similar to what I was doing, but I did not think he used his attributes as well as he could have.

Attribute Design

All the classes for the attributes for validation should inherit from an interface so that it would be easy to filter. Only a method required does do the actual validation having a single argument which is of type ValueValidator. This works great since it has all the information needed for validation, and encapsulates the Value string so when the Value property is updated, the code does not have to worry about strings being immutable. I want to be able to update the Value property for several reasons:

• Convert all the characters to a specific case,
• Allow that formatting can be applied such as displaying only the date from DateTime fields.
• So substitution can be done.

interface IValueValidationAttribute
{
  string Validate(ValueValidator valueValidator);
}

An example of an Attribute that inherits from this interface that validates that a value is a valid date:

[AttributeUsage(AttributeTargets.Property)]
public class IsDateValueValidationAttribute : Attribute, IValueValidationAttribute
{
  private readonly string _returnFormat;

  public IsDateValueValidationAttribute(string returnFormat = null)
  {
    _returnFormat = returnFormat;
  }

  public string Validate(ValueValidator valueValidator)
  {
    Contract.Requires(valueValidator != null, "value argument is null");
    DateTime date;
    if (!DateTime.TryParse(valueValidator.Value, out date))
      return "The value must be a valid date";
    if (_returnFormat != null)
      valueValidator.Value = date.ToString(_returnFormat);
    return null;
  }
}

As can be seen, the constructor takes an optional argument that is the format to return if Value is a valid DataTime format. The Validate method then is responsible for checking that the Value matches the requirements. If it does, a null is returned, otherwise a string describing the error is returned. Also, the argument form the constructor is used to update the ValueValidator Value property so it will be a string of the value in the specified format. In use in the example the format is specified to be short date so that when displayed in the DataGridView control, only the date will displayed, and not the time.

AbstractValidationObject Class

To use these attributes, a class inherits from the abstract AbstractValidationObject class. The class that inherits is used to define the properties that will be exported as properties and provide attributes for these properties that define the validation and if the title (which is the name of the property) must be in the header row for validation. There are two methods in this class. One of the properties returns a collection of ValueValidator class instances, one for each property that is to be read from the excel sheet:

public IEnumerable<ValueValidator> GetValidators()
{
  return GetType().GetProperties().Select(
    propertyInfo => new ValueValidator(propertyInfo, this));
}

Notice that a new instance of the ValueValidator class is created each time the GetValidators method is called. This is because each ValueValidator represents a cell in the Excel spreadsheet and thus a single value of the property.

The second method does the validation on a single ValueValidator instance:

public string Validate(ValueValidator valueValidator)
{
  var propertyInfo = GetType().GetProperty(valueValidator.HeaderText);
  return propertyInfo.GetCustomAttributes(
      typeof(IValueValidationAttribute), false).
    Select(customAttribute => ((IValueValidationAttribute)customAttribute).
        Validate(valueValidator)).
        FirstOrDefault(returnResult => returnResult != null);
}

The return value is a string that describes the error if there is an issue with validation. If the value within the ValueValidator is valid, the return value is null. The ValueValidator provides both the name of the property and the value. The name is used to look up the PropertyInfo for the property using reflection, and then reflection is used to look the custom attributes using the GetCustomAttributes method of the PropertyInfo class. The GetCustomAttributes argument is used to filter only those Attributes that inherit from IValueValidationAttribute. Fortunately the order is maintain, so the instances of IValueValidationAttribute can be processed in the order they are enumerated. This is done by executing the Validate method on the class returned. This Validate method determines if the Value in the ValueValidator passes a single test. Process continues until first instance of the Attribute instance returns a no-null string, or all instances have been processed.

ValueValidator Class

The ValueValidator class is used to contain information about the property, and specific information about the cell containing the specific information. There will be a ValueValidator instance for each property for each Excel row. Thus the ValueValidator maintains the following information

• The Header text and or the property name (could change the design to have optionally a different text for the header). This is used for looking up the PropertyInfo in the AbstractValidationObject class and searching for the cell that represents the header for the column. There is also information about if the header is required to successfully process the worksheet.
• The cell associated with the value, which is used to reset the formatting if necessary. Resetting the formats needs to also be saved. Information on how to reset the Excel cell formatting uses ISaveFormats interface.
• The Value that user wants associated with property that will be validated.
• An instance of the AbstractValidationObject that is used for validating. In the design, all ValueValidator instances are using the same instance of the AbstractValidationObject class for validation.

In the constructor, the ValueValidator takes two arguments: the PropertyInfo argument which is used to find attributes associated with the property such as the Header Text, and the AbstractValidationObject instance, which is used for validation.

public ValueValidator(PropertyInfo propertyInfo,
    AbstractValidationObject abstractValidationObject)
{
  Name = propertyInfo.Name;
  var alternateTitleAttribute = (AlternateTitleAttribute)propertyInfo.
    GetCustomAttributes(typeof(AlternateTitleAttribute), false).FirstOrDefault();
  HeaderText = alternateTitleAttribute == null ? Name :
    alternateTitleAttribute.AlternateTitle;
  Required = propertyInfo.GetCustomAttributes
    (typeof(RequiredTitleAttribute), false).Any();
  _abstractValidationObject = abstractValidationObject;
  Validate(null, false);
}

The most important method in the ValueValidator is the Validate method which takes and argument of the Excel Range which is the worksheet cell, and a flag that indicates if an error is to be thrown when validation fails.

public void Validate(Range cell, bool throwException = true)
{
  _cell = cell;
  Value = cell == null ? string.Empty : cell.Value == null ?
    string.Empty : cell.Value.ToString();
  IsValid = true;
  _errorMessage = _abstractValidationObject.Validate(this);
  if (!string.IsNullOrEmpty(_errorMessage))
  {
    _errorMessage = string.Format(_errorMessage, HeaderText);
    IsValid = false;
    if (throwException)
      throw new ValidatorException(_errorMessage);
  }

}

The functions that this method is responsible for are:

• Saving an instance of the Range object which represents the cell containing the value. This is required so that the cell can be formatted to give feedback to the user about validation errors, and later to reset the formatting.
• Updating the Value property to the value contained in the cell.
• Running the AbstractValidationObject Validate method, saving the return value which represents the error message. Also does
• Formatting on the error message if there is a validation header (adding the Header text to the message if so formatted) and throwing a ValidatorException if the return from the validation is not null.
• Setting or resetting the IsValid property so that it can be determined if a row passed all validation.

Additional Advantage of New Design

If you look at the original project you will see that I wrap a string in a class to make it mutable. This was used in the validators so that the Value could be updated. Using the attribute design the Value property of the ValueValidator instance is updated when value is changed.

Conclusion

Attributes make the code for customizing this Excel code to support the generic code used to export data a lot cleaner and easier to maintain. I particularly like that it is really easy to add an attribute, but this does not increase the complexity of customizing up the export. The flexibility of the approach was shown when I added the ability to have a title with spaces. This just required adding a new attribute, and some changes in the initialization of the ValueValidator constructor. I also added a new property because I needed the property name readily available.