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

1 min read

By default, the grid does not have direct support for validating cell values in 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 below example, the first column of grid and excel sheet is validated and it shows the error message when entered the value less than 5.

Code snippet

//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 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"

Screenshot

Showing cell validation in grid