How to apply cell validation in excel sheet as same as in GridControl?

1 min read

By default, the WinForms GridControl does not have direct support for validating cell values in an Excel sheet while exporting to Excel, but it can be achieved by using, DataValidation property of Workbook after exporting the grid to Excel.

In the example below, the first column of the grid and Excel sheet is validated, and it shows the error message when the value is less than 5.

//Triggering the event.
this.gridControl1.CurrentCellValidating += new CancelEventHandler(this.gridControl1_CurrentCellValidating);
 
//Event Handling.
private void gridControl1_CurrentCellValidating(object sender, CancelEventArgs e)
{
    object value = this.gridControl1.CurrentCell.Renderer.ControlValue;
    if ((int)value < 5)
    {
        MessageBox.Show("Enter Valid Value");
        e.Cancel = true;
    }
}
 
//ExcelExporting
ExcelEngine Engine = new ExcelEngine();
IWorkbook workbook = Engine.Excel.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];

//Setting cell validation in ExcelSheet.
workbook.ActiveSheet.Columns[0].DataValidation.CompareOperator = ExcelDataValidationComparisonOperator.Greater ;
workbook.ActiveSheet.Columns. [0].DataValidation.AllowType = ExcelDataType.Integer ;

//To Set minimum value to be entered.
workbook.ActiveSheet.Columns[0].DataValidation.FirstFormula = "5" ;
workbook.ActiveSheet.Columns[0].DataValidation.ErrorBoxTitle = "Warning" 
workbook.ActiveSheet.Columns[0].DataValidation.ErrorBoxText = "Value is not a valid"

'Triggering the event.
AddHandler gridControl1.CurrentCellValidating, AddressOf gridControl1_CurrentCellValidating

'Event Handling.
Private Sub gridControl1_CurrentCellValidating(ByVal sender As Object, ByVal e As CancelEventArgs) Handles gridControl1.CurrentCellValidating
 Dim value As Object = Me.gridControl1.CurrentCell.Renderer.ControlValue
 If CInt(Fix(value)) < 5 Then
  MessageBox.Show("Enter Valid Value")
  e.Cancel = True
 End If
End Sub 

'Excel Exporting.
Dim Engine As New ExcelEngine()
Dim workbook As IWorkbook = Engine.Excel.Workbooks.Create(1)
Dim sheet As IWorksheet = workbook.Worksheets(0)

'Setting validation in Excel sheet.
workbook.ActiveSheet.Columns(0).DataValidation.CompareOperator = ExcelDataValidationComparisonOperator.Greater
workbook.ActiveSheet.Columns(0).DataValidation.AllowType = ExcelDataType.Integer

'To set the minimum value to be entered.
workbook.ActiveSheet.Columns(0).DataValidation.FirstFormula = "5"
workbook.ActiveSheet.Columns(0).DataValidation.ErrorBoxTitle = "Warning"
workbook.ActiveSheet.Columns(0).DataValidation.ErrorBoxText = "Value is not a valid"