We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

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

Platform: WinForms |
Control: GridControl |
Published Date: December 21, 2017 |
Last Revised Date: May 27, 2019

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

C#

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

 

VB

'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

Showing error message for validation in excel

 

Sample Link:

C#: Data Validation in Excel_CS

VB : Data Validation in Excel_VB

 

 

 

2X faster development

The ultimate WinForms UI toolkit to boost your development speed.
ADD COMMENT
You must log in to leave a comment

Please sign in to access our KB

This page will automatically be redirected to the sign-in page in 10 seconds.

Up arrow icon

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon