Data validation

xlsgen > overview > Data validation

Data validation rules can be :

created, edited, deleted
enforced

Creating data validation rules

Data validation rules is a comprehensive set of rules that are added to a spreadsheet to ensure its integrity. Those rules include picklists, among others. Let's begin with picklists.

Picklists allow a client code to assign a special dropdown to a range of cells so that users can select a value among the ones available from the dropdown. xlsgen supports all Excel data validation rules, including pick lists.

Pick lists in action in Excel (in menu Data / Validation / List)

Creating a pick list involves the following ingredients :

a worksheet where that pick list should show
a range to limit the area where the pick list should show. A special case of range is a single cell.
a number of values that a user can select from (*).

(*) The values that users see in the pick lists are provided either explicitely or by defining a range which in turn stores the values in cells. Therefore there are two methods : AddItem to be used to add values one at a time. And AddItemSource to add a whole range of values at once.

Programmatically speaking, the interface is straight forward.

Explicit strings

The point of using explicit strings is to avoid having them stored in a worksheet. Note that explicit strings have a limitation, which is a limitation in Excel : the sum of the length of all explicit strings must not be greater than 255 characters. To alleviate this limitation, you probably want to define an item source instead of explicit strings.

VB code


Sub generate()

  Dim engine As CoXlsEngine
  Set engine = CreateObject("ExcelGenerator.ARsTDesign")

  Dim wbk As IXlsWorkbook
  Set wbk = engine.New("myfile.xls")

  Dim wksht001 As IXlsWorksheet
  Set wksht001 = wbk.AddWorksheet("Sheet1")

  ' create a pick list in area (1,1)-(5,5) with explicit strings

  Dim list As IXlsDataValidation
  Set list = wksht001.NewDataValidation(1,1, 5,5)

  list.AddItem "January"
  list.AddItem "February"
  list.AddItem "March"
  list.AddItem "April"
  list.AddItem "March"
  list.AddItem "May"
  list.AddItem "June"
  list.AddItem "July"
  list.AddItem "August"
  list.AddItem "September"
  list.AddItem "October"
  list.AddItem "November"
  list.AddItem "December"

  wbk.Close

  Set engine = Nothing

End Sub

C# code


  xlsgen.CoXlsEngine excel = new xlsgen.CoXlsEngine();
  IXlsWorkbook wbk = excel.New( @"myfile.xls" );
  IXlsWorksheet wksht001 = wbk.AddWorksheet( "Sheet1" );

  // create a pick list in area (1,1)-(5,5) with explicit strings

  IXlsDataValidation list = wksht001.NewDataValidation(1,1, 5,5);

  list.AddItem("January");
  list.AddItem("February");
  list.AddItem("March");
  list.AddItem("April");
  list.AddItem("March");
  list.AddItem("May");
  list.AddItem("June");
  list.AddItem("July");
  list.AddItem("August");
  list.AddItem("September");
  list.AddItem("October");
  list.AddItem("November");
  list.AddItem("December");

  wbk.Close();

C++ code

{
  xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

  xlsgen::IXlsWorkbookPtr wbk;
  wbk = engine->New( L"myfile.xls" );

  xlsgen::IXlsWorksheetPtr wksht001;
  wksht001 = wbk->AddWorksheet( L"Sheet1" );

  // create a pick list in area (1,1)-(5,5) with explicit strings

  xlsgen::IXlsDataValidationPtr list;
  list = wksht001->NewDataValidation(1,1, 5,5);

  list->AddItem("January");
  list->AddItem("February");
  list->AddItem("March");
  list->AddItem("April");
  list->AddItem("March");
  list->AddItem("May");
  list->AddItem("June");
  list->AddItem("July");
  list->AddItem("August");
  list->AddItem("September");
  list->AddItem("October");
  list->AddItem("November");
  list->AddItem("December");

  wbk->Close();

}

Item source

Item source defines a range where to pick values from. That's a convenient way to define pick lists. Also, because the values in the item source may change, this enables dynamic pick lists.

VB code

Dim wksht As IXlsWorksheet 
Set wksht = wbk.AddWorksheet( "Sheet1" )

' create a pick list that applies in range [A1:B2]

Dim list As IXlsDataValidation 
Set list = wksht.NewDataValidation(1,1, 2,2)

' create values to pick from

wksht.Number(5,6) = 50
wksht.Number(6,6) = 70
wksht.Number(7,6) = 100
wksht.Number(8,6) = 60
wksht.Number(9,6) = 120
wksht.Number(10,6) = 150

' associate the values and the pick list

data.AddItemSource( "R5C6:R10C6" )

C# code

IXlsWorksheet wksht = wbk.AddWorksheet( "Sheet1" );

// create a pick list that applies in range [A1:B2]

IXlsDataValidation list = wksht.NewDataValidation(1,1, 2,2);

// create values to pick from

wksht.set_Number(5,6, 50);
wksht.set_Number(6,6, 70);
wksht.set_Number(7,6, 100);
wksht.set_Number(8,6, 60);
wksht.set_Number(9,6, 120);
wksht.set_Number(10,6, 150);

// associate the values and the pick list

data.AddItemSource( "R5C6:R10C6" );

C++ code

xlsgen::IXlsWorksheetPtr wksht;
wksht = wbk->AddWorksheet( L"sheet1" );

// create a pick list that applies in range [A1:B2]

xlsgen::IXlsDataValidationPtr data = wksht->NewDataValidation(1, // row1
                                                                 1, // col1
                                                                 2, // row2
                                                                 2); // col2

// create values to pick from

wksht->Number[5][6] = 50;
wksht->Number[6][6] = 70;
wksht->Number[7][6] = 100;
wksht->Number[8][6] = 60;
wksht->Number[9][6] = 120;
wksht->Number[10][6] = 150;

// associate the values and the pick list

data->AddItemSource( L"R5C6:R10C6" );

Validation options

A number of options can be set, among which :

IgnoreErrorIfEmpty : ignore the error even if the cell is empty
ShowDropDown : hide the list of values to select from
ShowInformation : show an information message
InformationMessageTitle : title of the message (works in conjunction with ShowInformation)
InformationMessageBody : body of the message (works in conjunction with ShowInformation)
ShowError : show an error message whenever applicable
ErrorMessageTitle : title of the error (works in conjunction with ShowError)
ErrorMessageBody : body of the error (works in conjunction with ShowError)
ErrorMessageType : error type (see enumValidationErrorType)
Areas : returns the areas where the data validation rule applies. The areas is a semi-colon separated list of rectangular areas of the form RxxxCxxx:RyyyCyyy.

Other Validation conditions

xlsgen support all other ways to enforce data validation. It includes :

the ability to set a condition based on either a number, a floating-point number, a date, a time, a text length, or a pick-list (see above). The condition is defined using enumValidationType. When this is done, the cell condition interface lets you use of the 7 operators (between, not between, equal, not equal, greater than, less than, greater or equal than, less or equal then).
the ability to set a condition based on a general purpose formula.

Here is an example :

VB code


' create a data validation based on whether the value
' entered is greater than 3.5 or not

Dim wksht As IXlsWorksheet 
Set wksht = wbk.AddWorksheet( "Sheet1" )

Dim data1 As IXlsDataValidation
' row1, col1, row2, col2
Set data1 = wksht.NewDataValidation(1,  _
                                    1,  _
                                    2,  _
                                    1)   

data1.ValidationType = enumValidationType.validationtype_float
data1.ValidationCondition.GreaterThan("3.5")

C# code


// create a data validation based on whether the value
// entered is greater than 3.5 or not

IXlsWorksheet wksht = wbk.AddWorksheet( "Sheet1" );

IXlsDataValidation data1 = wksht.NewDataValidation(1, // row1
                                                   1,  // col1
                                                   2,  // row2
                                                   1); // col2

data1.ValidationType = enumValidationType.validationtype_float;
IXlsCellCondition cc = data1.ValidationCondition;
cc.GreaterThan("3.5");

C++ code


// create a data validation based on whether the value
// entered is greater than 3.5 or not

xlsgen::IXlsWorksheetPtr wksht;
wksht = wbk->AddWorksheet( L"Sheet1" );

xlsgen::IXlsDataValidationPtr data1 = wksht->NewDataValidation(1, // row1
                                                                 1,  // col1
                                                                 2,  // row2
                                                                 1); // col2

data1->ValidationType = xlsgen::validationtype_float;
xlsgen::IXlsCellConditionPtr cc = data1->ValidationCondition;
cc->GreaterThan(L"3.5");

Editing and deleting data validation rules

xlsgen has two introspection interfaces when it comes to data validation rules. In either case, you can obtain information such as the areas where a given data validation rule applies.

Introspecting data validation rules : the cell metadata type

Any cell exposes metadata which can be easily obtained with the CellMetadataType property of the current worksheet. It returns a value of type enumMetadataType which tells whether this cell's value is subject to one or more data validation rules. More information here.

Introspecting data validation rules : the data validation rules collection

Another way to introspect data validation rules is simply to list them. The index-based collection of data validation rules is exposed by the DataValidations property of the current worksheet. It returns the IXlsDataValidations interface.

Deleting data validation rules

Once you have a data validation rule object, you can call the Delete() method on it to delete it from the Excel spreadsheet.

Enforcing data validation rules

xlsgen can make sure, as data is added to a workbook, that the data is compatible with the data validation rules.

By default, data validation rules are not enforced. To enable it, the workbook interface has a EnforceDataValidation property. When the property is set to TRUE, it applies to all worksheets.

In the following example, we have set up a data validation rule in the area in orange defined as : decimal must be between 100 and 200. We try to store a value which won't validate, and then one which will.

Enforcing data validation rules, an example

VB code


' create a data validation rule based on whether the value
' entered is between 100 and 200

Dim wksht As IXlsWorksheet 
Set wksht = workbook.AddWorksheet( "Sheet1" )

Dim datarule As IXlsDataValidation 
' row1, col1, row2, col2
Set datarule = wksht.NewDataValidation(3, _
                                       5, _
                                       5, _
                                       8)  

datarule.ValidationType = enumValidationType.validationtype_decimal
datarule.ValidationCondition.Between("100", "200")

' enable rules enforcement

workbook.EnforceDataValidation = True

wksht.Float(5,6) = 50 ' returns an error since the rule is "between 100 and 200"

wksht.Float(5,6) = 150 ' returns OK since the rule is "between 100 and 200"

workbook.Close

C# code


// create a data validation rule based on whether the value
// entered is between 100 and 200

IXlsWorksheet wksht = workbook.AddWorksheet( "Sheet1" );

IXlsDataValidation datarule = wksht.NewDataValidation(3,  // row1
                                                      5,  // col1
                                                      5,  // row2
                                                      8); // col2

datarule.ValidationType = enumValidationType.validationtype_decimal;
datarule.ValidationCondition.Between("100", "200");

// enable rules enforcement

workbook.EnforceDataValidation = true;

wksht.set_Float(5,6, 50); // returns an error since the rule is "between 100 and 200"

wksht.set_Float(5,6, 150); // returns OK since the rule is "between 100 and 200"

workbook.Close();

C++ code


// create a data validation rule based on whether the value
// entered is between 100 and 200

xlsgen::IXlsWorksheetPtr wksht = workbook->AddWorksheet( L"Sheet1" );

// create a data validation rule : "between 100 and 200"

xlsgen::IXlsDataValidationPtr datarule = wksht->NewDataValidation(3, // row1
                                                                     5, // col1
                                                                     5, // row2
                                                                     8);// col2
datarule->ValidationType = xlsgen::validationtype_decimal;
datarule->ValidationCondition->Between(L"100", L"200");

// enable rules enforcement

workbook->EnforceDataValidation = TRUE;

try {
  wksht->Float[5][6] = 50; // returns an error since the rule is "between 100 and 200"
}
catch(...)
{
}

try {
  wksht->Float[5][6] = 150; // returns OK since the rule is "between 100 and 200"
}
catch(...)
{
}

workbook->Close();