Click here to Skip to main content
15,867,568 members
Articles / Productivity Apps and Services / Microsoft Office

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

Rate me:
Please Sign up or sign in to vote.
4.95/5 (12 votes)
21 Sep 2012CPOL7 min read 41.9K   260   15   23
Demonstrates how to use attributes during run time in a serious application to improve maintainability.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Clifford Nelson Consulting
United States United States
Has been working as a C# developer on contract for the last several years, including 3 years at Microsoft. Previously worked with Visual Basic and Microsoft Access VBA, and have developed code for Word, Excel and Outlook. Started working with WPF in 2007 when part of the Microsoft WPF team. For the last eight years has been working primarily as a senior WPF/C# and Silverlight/C# developer. Currently working as WPF developer with BioNano Genomics in San Diego, CA redesigning their UI for their camera system. he can be reached at qck1@hotmail.com.

Comments and Discussions

 
QuestionMy vote 5 Pin
SOHAM_GANDHI6-Apr-14 20:27
SOHAM_GANDHI6-Apr-14 20:27 
AnswerRe: My vote 5 Pin
Clifford Nelson15-Apr-14 13:09
Clifford Nelson15-Apr-14 13:09 
AnswerRe: My vote 5 Pin
Clifford Nelson15-Apr-14 14:18
Clifford Nelson15-Apr-14 14:18 
GeneralWell done Pin
Espen Harlinn29-Aug-13 20:11
professionalEspen Harlinn29-Aug-13 20:11 
AnswerRe: Well done Pin
Clifford Nelson30-Aug-13 6:38
Clifford Nelson30-Aug-13 6:38 
QuestionMy vote of 5 Pin
Maciej Los3-Oct-12 10:37
mveMaciej Los3-Oct-12 10:37 
AnswerRe: My vote of 5 Pin
Clifford Nelson3-Oct-12 10:56
Clifford Nelson3-Oct-12 10:56 
GeneralMy vote of 5 Pin
fredatcodeproject2-Oct-12 21:01
professionalfredatcodeproject2-Oct-12 21:01 
AnswerRe: My vote of 5 Pin
Clifford Nelson3-Oct-12 10:56
Clifford Nelson3-Oct-12 10:56 
GeneralMy vote of 5 Pin
Sergey Alexandrovich Kryukov2-Oct-12 17:12
mvaSergey Alexandrovich Kryukov2-Oct-12 17:12 
GeneralRe: My vote of 5 Pin
Clifford Nelson2-Oct-12 17:37
Clifford Nelson2-Oct-12 17:37 
GeneralMy vote of 5 Pin
fredatcodeproject25-Sep-12 10:01
professionalfredatcodeproject25-Sep-12 10:01 
AnswerRe: My vote of 5 Pin
Clifford Nelson25-Sep-12 10:36
Clifford Nelson25-Sep-12 10:36 
AnswerRe: My vote of 5 Pin
Clifford Nelson2-Oct-12 15:22
Clifford Nelson2-Oct-12 15:22 
GeneralMy vote of 4 Pin
D-Kishore23-Sep-12 19:19
D-Kishore23-Sep-12 19:19 
AnswerRe: My vote of 4 Pin
Clifford Nelson24-Sep-12 6:23
Clifford Nelson24-Sep-12 6:23 
Any suggestions on improvement?
GeneralMy vote of 3 Pin
fredatcodeproject21-Sep-12 11:56
professionalfredatcodeproject21-Sep-12 11:56 
AnswerMessage Closed Pin
21-Sep-12 13:52
Clifford Nelson21-Sep-12 13:52 
GeneralRe: My vote of 3 Pin
fredatcodeproject22-Sep-12 7:39
professionalfredatcodeproject22-Sep-12 7:39 
AnswerMessage Closed Pin
24-Sep-12 6:24
Clifford Nelson24-Sep-12 6:24 
GeneralRe: My vote of 3 Pin
fredatcodeproject25-Sep-12 10:02
professionalfredatcodeproject25-Sep-12 10:02 
GeneralRe: My vote of 3 Pin
Sergey Alexandrovich Kryukov2-Oct-12 17:13
mvaSergey Alexandrovich Kryukov2-Oct-12 17:13 
GeneralRe: My vote of 3 Pin
fredatcodeproject2-Oct-12 21:02
professionalfredatcodeproject2-Oct-12 21:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.