Syncfusion® Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. It also converts Excel documents to PDF files. Using Syncfusion®, we can work with data validation. In this article, we will explore how to validate a URL with data validation. Steps to validate a URL with data validation programmatically: Step 1: Create a new C# console application. Step 2: Install the Syncfusion.XlsIO.Net.Core NuGet package as reference to your .NET Core application from NuGet.org. Step 3: Include the following namespaces in Program.cs file. C# using Syncfusion.XlsIO; Step 4: Include the following code snippet to validate a URL with data validation. C# using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Xlsx; FileStream inputStream = new FileStream("../../../Data/InputTemplate.xlsx", FileMode.Open, FileAccess.Read); IWorkbook workbook = application.Workbooks.Open(inputStream); IWorksheet worksheet = workbook.Worksheets[0]; // Data validation for email format IDataValidation validation = worksheet.Range["G2:G7"].DataValidation; validation.AllowType = ExcelDataType.Formula; validation.FirstFormula = "=AND(ISNUMBER(SEARCH(\"@\", G2:G7)), ISNUMBER(SEARCH(\".\", G2:G7, SEARCH(\"@\", G2:G7))))"; // Shows the error message validation.ErrorBoxText = "Please enter a valid Email address."; validation.ErrorBoxTitle = "Invalid Email Format"; validation.PromptBoxText = "Enter an Email address"; validation.IsPromptBoxVisible = true; validation.ShowPromptBox = true; // Saving the workbook as stream FileStream OutputStream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite); workbook.SaveAs(OutputStream); // Dispose stream inputStream.Dispose(); OutputStream.Dispose(); } Download a complete working sample demonstrating how to validate a URL with data validation using C# from here. If you enter a URL that does not satisfy the URL format, an error message will pop up as configured. Below is the output file for URL validation using data validation. Take a moment to peruse the documentation, where you can find basic worksheet data manipulation options along with features like Conditional Formatting, worksheet calculations through Formulas, adding Charts in worksheets or workbooks, organizing and analyzing data through Tables and Pivot Tables, appending multiple records to a worksheet using Template Markers, and most importantly PDF and Image conversions with code examples. Refer here to explore the rich set of Syncfusion® Excel (XlsIO) library features. Note: Starting with v16.2.0.x, if you reference Syncfusion® assemblies from the trial setup or the NuGet feed, include a license key in your projects. Refer to the link to learn about generating and registering the Syncfusion® license key in your application to use the components without a trial message. Conclusion I hope you enjoyed learning about how to validate a URL with data validation in .NET Core Excel using C#. You can refer to our ASP.NET Core XIsIO feature tour page to know about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. You can also explore our ASP.NET Core XIsIO example to understand how to create and manipulate data. For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls. If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!
Syncfusion® Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. It also, converts Excel documents to PDF files. Using Syncfusion®, we can work with data validation. In this article, we will explore how to validate an email address using data validation. Steps to validate an email address with data validation programmatically: Step 1: Create a new C# console application. Step 2: Install the Syncfusion.XlsIO.Net.Core NuGet package as reference to your .NET Core application from NuGet.org. Step 3: Include the following namespaces in Program.cs file. C# using Syncfusion.XlsIO; Step 4: Include the following code snippet to validate an email address with data validation. C# using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Xlsx; FileStream inputStream = new FileStream("../../../Data/InputTemplate.xlsx", FileMode.Open, FileAccess.Read); IWorkbook workbook = application.Workbooks.Open(inputStream); IWorksheet worksheet = workbook.Worksheets[0]; // Data validation for email format IDataValidation validation = worksheet.Range["G2:G7"].DataValidation; validation.AllowType = ExcelDataType.Formula; validation.FirstFormula = "=AND(ISNUMBER(SEARCH(\"@\", G2:G7)), ISNUMBER(SEARCH(\".\", G2:G7, SEARCH(\"@\", G2:G7))))"; // Shows the error message validation.ErrorBoxText = "Please enter a valid Email address."; validation.ErrorBoxTitle = "Invalid Email Format"; validation.PromptBoxText = "Enter an Email address"; validation.IsPromptBoxVisible = true; validation.ShowPromptBox = true; // Saving the workbook as a stream FileStream OutputStream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite); workbook.SaveAs(OutputStream); // Dispose streams inputStream.Dispose(); OutputStream.Dispose(); } Download a complete working sample demonstrating how to validate an email address using data validation with C# from here. If you enter an email address that does not satisfy the email format, an error message will pop up as configured. Below is the output file for email validation using data validation. Take a moment to peruse the documentation, where you can find basic worksheet data manipulation options along with features like Conditional Formatting, worksheet calculations through Formulas, adding Charts in worksheets or workbooks, organizing and analyzing data through Tables and Pivot Tables, appending multiple records to a worksheet using Template Markers, and most importantly PDF and Image conversions with code examples. Refer here to explore the rich set of Syncfusion Excel (XlsIO) library features. Note: Starting with v16.2.0.x, if you reference Syncfusion assemblies from the trial setup or the NuGet feed, include a license key in your projects. Refer to the link to learn about generating and registering the Syncfusion license key in your application to use the components without a trial message. Conclusion I hope you enjoyed learning about how to validate an email address with data validation in .NET Core Excel using C#. You can refer to our ASP.NET Core XIsIO feature tour page to know about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. You can also explore our ASP.NET Core XIsIO example to understand how to create and manipulate data. For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls. If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!
Syncfusion® Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. It also, converts Excel documents to PDF files. Using Syncfusion®, we can work with data validation. In this article, we will explore how to achieve multiple dependent dropdown lists with multi-selection using data validation. Steps for creating multiple dependent dropdown lists with multi-selection programmatically: Step 1: Create a new C# console application project. Step 2: Install the Syncfusion.XlsIO.Net.Core NuGet package as reference to your .NET Core application from NuGet.org. Step 3: Include the following namespaces in the Program.cs file. C# using Syncfusion.XlsIO; using Syncfusion.Office; Step 4: Include the following code snippet to create multiple dependent dropdown lists with multi-selection. C# using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Xlsx; FileStream inputStream = new FileStream("../../../Data/InputTemplate.xlsx", FileMode.Open, FileAccess.Read); IWorkbook workbook = application.Workbooks.Open(inputStream); IWorksheet worksheet = workbook.Worksheets[0]; // Set name range IName name1 = workbook.Names.Add("Country"); name1.RefersToRange = worksheet.Range["A2:A5"]; IName name2 = workbook.Names.Add("India"); name2.RefersToRange = worksheet.Range["B2:B6"]; IName name3 = workbook.Names.Add("Brazil"); name3.RefersToRange = worksheet.Range["B7:B10"]; IName name4 = workbook.Names.Add("Australia"); name4.RefersToRange = worksheet.Range["B11:B13"]; IName name5 = workbook.Names.Add("USA"); name5.RefersToRange = worksheet.Range["B14:B16"]; worksheet.Range["E1"].Text = "Country"; worksheet.Range["E1"].CellStyle.Font.Bold = true; // Data validation in E2 IDataValidation validation = worksheet.Range["E2"].DataValidation; validation.AllowType = ExcelDataType.User; validation.FirstFormula = "=Country"; // Shows the error message validation.ErrorBoxText = "Enter the valid country"; validation.ErrorBoxTitle = "ERROR"; validation.PromptBoxText = "Enter the country"; validation.ShowPromptBox = true; worksheet.Range["F1"].Text = "States"; worksheet.Range["F1"].CellStyle.Font.Bold = true; // Data validation in F2 IDataValidation validation2 = worksheet.Range["F2"].DataValidation; validation2.AllowType = ExcelDataType.User; validation2.FirstFormula = "=Indirect(E2)"; // Shows the error message validation2.ErrorBoxText = "Enter the valid states"; validation2.ErrorBoxTitle = "ERROR"; validation2.PromptBoxText = "Enter the state"; validation2.ShowPromptBox = true; // Creating Vba project IVbaProject project = workbook.VbaProject; // Accessing vba modules collection IVbaModules vbaModules = project.Modules; // Accessing sheet module IVbaModule vbaModule = vbaModules[worksheet.CodeName]; // Adding vba code to the module vbaModule.Code = @"Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDropdown As Range Dim oldValue As String Dim newValue As String Dim DelimiterType As String DelimiterType = ", " ' Assuming you want to use a comma followed by a space as the delimiter If Target.Count > 1 Then Exit Sub On Error Resume Next If Target.Column <> 5 And Target.Column <> 6 Then GoTo exitError Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitError If rngDropdown Is Nothing Then GoTo exitError If Intersect(Target, rngDropdown) Is Nothing Then ' Do nothing for non-dropdown cells Else Application.EnableEvents = False newValue = Target.Value Application.Undo oldValue = Target.Value Target.Value = newValue ' Check if the change was in E2 and clear F2 If Target.Address = ""$E$2"" Then Range(""F2"").ClearContents ElseIf Target.Address = ""$F$2"" Then ' Append new value to F2 with the delimiter if F2 is not empty If oldValue <> """" Then If newValue <> """" Then If oldValue = newValue Or _ InStr(1, oldValue, DelimiterType & newValue) > 0 Or _ InStr(1, oldValue, newValue & DelimiterType) > 0 Then ' Do nothing if the new value is the same or already exists Else ' Append the new value with the delimiter Target.Value = oldValue & DelimiterType & newValue End If End If Else ' Set F2 directly to the new value if it's empty Target.Value = newValue End If End If ' Re-enable events after handling the change Application.EnableEvents = True End If exitError: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub"; // Saving the workbook as stream FileStream stream = new FileStream("Output.xlsm", FileMode.Create, FileAccess.ReadWrite); workbook.SaveAs(stream); } Download a complete working sample demonstrating how to create multiple dependent dropdown lists with multi-selection using C# from here. By executing the program, you will get the output Excel file as shown below. See Also: Working with Data Validation using XlsIO. Take a moment to peruse the documentation, where you can find basic worksheet data manipulation options along with features like Conditional Formatting, worksheet calculations through Formulas, adding Charts in worksheets or workbooks, organizing and analyzing data through Tables and Pivot Tables, appending multiple records to a worksheet using Template Markers, and most importantly PDF and Image conversions with code examples. Refer here to explore the rich set of Syncfusion® Excel (XlsIO) library features. Note: Starting with v16.2.0.x, if you reference Syncfusion® assemblies from the trial setup or the NuGet feed, include a license key in your projects. Refer to the link to learn about generating and registering the Syncfusion® license key in your application to use the components without a trial message. Conclusion I hope you enjoyed learning about how to read an uploaded Excel document using Uploader in C#. You can refer to our .NET Excel library page to know about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. You can also explore our .NET Excel library example to understand how to create and manipulate data. For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls. If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!
This knowledge base explains how to apply data validation for particular values present in the cells of an Angular spreadsheet. You can achieve this requirement by using the addDataValidation method and the created event. Apply the data validation for particular cell values by iterating the used range cells in the created event. [app.component.html] <div class="control-section"> <ejs-spreadsheet #default (created)="created()"> <e-sheets> <e-sheet> <e-ranges> <e-range [dataSource]="data"></e-range> </e-ranges> <e-columns> <e-column [width]=130></e-column> <e-column [width]=130></e-column> <e-column [width]=130></e-column> </e-columns> </e-sheet> </e-sheets> </ejs-spreadsheet> </div> [app.component.ts] import { Component, ViewEncapsulation, ViewChild } from '@angular/core'; import { getDefaultData } from './data'; import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet'; import { CellModel, getCell, getCellAddress, SheetModel, UsedRangeModel } from '@syncfusion/ej2-spreadsheet'; import { isNullOrUndefined } from '@syncfusion/ej2-base'; @Component({ selector: 'app-root', templateUrl: 'app.component.html', styleUrls: ['app.component.css'], encapsulation: ViewEncapsulation.None, }) export class AppComponent { constructor() {} @ViewChild('default') public spreadsheetObj: SpreadsheetComponent; public data: Object[] = getDefaultData(); created() { this.spreadsheetObj.cellFormat( { fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:C1' ); let sheet: SheetModel = this.spreadsheetObj.getActiveSheet(); let usedRange: UsedRangeModel = sheet.usedRange; // Iterate between used range to check whether the value is present or not. for (let i: number = 0; i <= usedRange.rowIndex; i++) { // Fetch the cells in the row. let cells: CellModel[] = sheet.rows[i] ? sheet.rows[i].cells : undefined; for (let j: number = 0; j <= usedRange.colIndex; j++) { // Fetch the cell model. let cell: CellModel = getCell(i, j, sheet); // Condition to check whether the cell value is present or not. let cellValue: string = isNullOrUndefined(cell) ? undefined : cell.value; if (cells && cellValue === 'Valid') { // Get the address of validation applying cell. let newAddress: string = getCellAddress(i, j); // Add validation to the corresponding cell using addDataValidation method. this.spreadsheetObj.addDataValidation( { type: 'List', value1: 'Red,Yellow,Green,Blue' }, newAddress ); } } } } } Sample Link: https://stackblitz.com/edit/angular-bdanxu-gktxdb?file=app.component.ts Output: Documentation link: https://ej2.syncfusion.com/angular/documentation/spreadsheet/cell-range#data-validation Demo link: https://ej2.syncfusion.com/angular/demos/#/material3/spreadsheet/data-validation
This knowledge base explains how to prevent entering characters in the numbered columns of a JavaScript spreadsheet. To achieve this requirement, use the addDataValidation method and the dialogBeforeOpen event. Apply the data validation in the created event first, and then use the dialogBeforeOpen event to customize the alert dialog content. [HTML] <div id="spreadsheet"></div> [TS] import { Spreadsheet, DialogBeforeOpenEventArgs } from '@syncfusion/ej2-spreadsheet'; let spreadsheet: Spreadsheet = new Spreadsheet({ created: (): void => { //Applies data validation to specified range in the active sheet. spreadsheet.addDataValidation( { type: 'WholeNumber', operator: 'Between', value1: '0', value2: '100' }, 'A1:A100' ); }, dialogBeforeOpen: (args: DialogBeforeOpenEventArgs): void => { // Edit the dialog content using the dialogBeforeOpen event. if (args.dialogName == 'ValidationErrorDialog') { args.content = 'Characters are not allowed in Number Column'; } }, }); spreadsheet.appendTo('#spreadsheet'); Sample Link: https://stackblitz.com/edit/rdjujx-amffkr?file=index.ts Output: Demo Link: https://ej2.syncfusion.com/demos/#/bootstrap5/spreadsheet/data-validation.html Documentation Link: https://ej2.syncfusion.com/documentation/spreadsheet/cell-range#data-validation
