Data Validation
Contents
[
Hide
]
Microsoft Excel provides some good features to auto filter or validate worksheet data. Aspose.Cells fully supports Microsoft Excel’s data validation and AutoFilter features. This article explains how to use the features in Microsoft Excel, and how to code them using Aspose.Cells for JavaScript via C++.
Data Validation Types and Execution
Data validation is the ability to set rules pertaining to data entered on a worksheet. For example, use validation to ensure that a column labeled DATE contains only dates, or that another column contains only numbers. You could even ensure that a column labeled DATE contains only dates within a certain range. With data validation, you can control what is entered into cells in the worksheet.
Microsoft Excel supports a number of different types of data validation. Each type is used to control what type of data is entered into a cell, or cell range. Below, code snippets illustrate how to validate that:
- Numbers are whole, that is, that they don’t have a decimal part.
- Decimal numbers follow the right structure. The code example defines that a range of cells should have two decimal spaces.
- Values are restricted to a list of values. List validation defines a separate list of values that can be applied to a cell, or cell range.
- Dates fall within a specific range.
- A time is within a specific range.
- A text is within a given character length.
Data Validation with Microsoft Excel
To create validations using Microsoft Excel:
- In a worksheet, select the cells to which you want to apply validation.
- From the Data menu, select Validation. The validation dialog will be displayed.
- Click the Settings tab and enter settings.
Data Validation with Aspose.Cells for JavaScript via C++
Data validation is a powerful feature for validating the information entered into worksheets. With data validation, developers can provide users with a list of choices, restrict data entries to a specific type or size, etc. In Aspose.Cells for JavaScript via C++, each Worksheet class has a validations collection which represents a collection of Validation objects. To set up validation, set some of the Validation class' properties as follows:
- Type – represents the validation type, which may be specified by using one of the predefined values in the ValidationType enumeration.
- Operator – represents the operator to be used in the validation, which may be specified by using one of the predefined values in the OperatorType enumeration.
- Formula1 – represents the value or expression associated with the first part of the data validation.
- Formula2 – represents the value or expression associated with the second part of the data validation.
When the Validation object’s properties have been configured, developers can use the CellArea structure to store information about the cell range that will be validated using the created validation.
Types of Data Validation
The ValidationType enumeration has the following members:
| Member Name | Description |
|---|---|
| AnyValue | Denotes a value of any type. |
| WholeNumber | Denotes validation type for whole numbers. |
| Decimal | Denotes validation type for decimal numbers. |
| List | Denotes validation type for drop-down list. |
| Date | Denotes validation type for dates. |
| Time | Denotes validation type for time. |
| TextLength | Denotes validation type for the length of the text. |
| Custom | Denotes custom validation type. |
Whole Number Data Validation
With this type of validation, users can enter only whole numbers within a specified range into the validated cells. The code examples that follow show how to implement the WholeNumber validation type. The example creates the same data validation using Aspose.Cells for JavaScript via C++ that we created using Microsoft Excel above.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Validation Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const resultDiv = document.getElementById('result');
const fileInput = document.getElementById('fileInput');
if (fileInput.files.length > 0) {
// If a file is provided, we will open it; otherwise create a new workbook
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiate workbook from uploaded file
var workbook = new Workbook(new Uint8Array(arrayBuffer));
} else {
// Create a new workbook
var workbook = new Workbook();
}
// Create a worksheet and get the first worksheet.
const ExcelWorkSheet = workbook.worksheets.get(0);
// Accessing the Validations collection of the worksheet
const validations = workbook.worksheets.get(0).validations;
// Create Cell Area
const ca = new AsposeCells.CellArea();
ca.startRow = 0;
ca.endRow = 0;
ca.startColumn = 0;
ca.endColumn = 0;
// Creating a Validation object
const idx = validations.add(ca);
const validation = validations.get(idx);
// Setting the validation type to whole number
validation.type = AsposeCells.ValidationType.WholeNumber;
// Setting the operator for validation to Between
validation.operator = AsposeCells.OperatorType.Between;
// Setting the minimum value for the validation
validation.formula1 = "10";
// Setting the maximum value for the validation
validation.formula2 = "1000";
// Applying the validation to a range of cells from A1 to B2 using the CellArea structure
const area = new AsposeCells.CellArea();
area.startRow = 0;
area.endRow = 1;
area.startColumn = 0;
area.endColumn = 1;
// Adding the cell area to Validation
validation.addArea(area);
// Save the workbook and provide a download link
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData], { type: "application/vnd.ms-excel" });
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.out.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Excel File';
resultDiv.innerHTML = '<p style="color: green;">Validation added successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
List Data Validation
This type of validation allows the user to enter values from a drop-down list. It provides a list: a series of rows that contain data. In the example, a second worksheet is added to hold the list source. Users can only select values from the list. The validation area is the cell range A1:A5 in the first worksheet.
It is important here that you set the Validation.inCellDropDown(boolean) property to true.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Validation List Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, CellArea, ValidationType, OperatorType, ValidationAlertType } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
// This example creates a new workbook in the browser (file input is optional here).
const workbook = new Workbook();
// Get the first worksheet.
const worksheet1 = workbook.worksheets.get(0);
// Add a new worksheet and access it.
const i = workbook.worksheets.add();
const worksheet2 = workbook.worksheets.get(i);
// Create a range in the second worksheet.
const range = worksheet2.cells.createRange("E1", "E4");
// Name the range.
range.name = "MyRange";
// Fill different cells with data in the range.
range.get(0, 0).value = "Blue";
range.get(1, 0).value = "Red";
range.get(2, 0).value = "Green";
range.get(3, 0).value = "Yellow";
// Get the validations collection.
const validations = worksheet1.validations;
// Create Cell Area
const ca = new CellArea();
ca.startRow = 0;
ca.endRow = 0;
ca.startColumn = 0;
ca.endColumn = 0;
// Create a new validation to the validations list.
const validation = validations.get(validations.add(ca));
// Set the validation type.
validation.type = ValidationType.List;
// Set the operator.
validation.operator = OperatorType.None;
// Set the in cell drop down.
validation.inCellDropDown = true;
// Set the formula1.
validation.formula1 = "=MyRange";
// Enable it to show error.
validation.showError = true;
// Set the alert type severity level.
validation.alertStyle = ValidationAlertType.Stop;
// Set the error title.
validation.errorTitle = "Error";
// Set the error message.
validation.errorMessage = "Please select a color from the list";
// Specify the validation area.
const area = new CellArea();
area.startRow = 0;
area.endRow = 4;
area.startColumn = 0;
area.endColumn = 0;
// Add the validation area.
validation.addArea(area);
// Save the Excel file.
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.out.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Validation list created successfully! Click the download link to get the file.</p>';
});
</script>
</html>
Date Data Validation
With this type of validation, users enter date values within a specified range, or meeting specific criteria, into the validated cells. In the example, the user is restricted to enter dates between 1970 to 1999. Here, the validation area is the B1 cell.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Date Validation Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, Worksheet, Cell } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
// This example creates a new workbook in the browser (no file input required)
const workbook = new Workbook();
// Obtain the cells of the first worksheet.
const worksheet = workbook.worksheets.get(0);
const cells = worksheet.cells;
// Put a string value into the A1 cell.
const cellA1 = cells.get("A1");
cellA1.value = "Please enter Date b/w 1/1/1970 and 12/31/1999";
// Set row height and column width for the cells by accessing row/column objects
const row0 = cells.rows.get(0);
row0.height = 31;
const col0 = cells.columns.get(0);
col0.width = 35;
// Get the validations collection.
const validations = worksheet.validations;
// Create Cell Area
const ca = new AsposeCells.CellArea();
ca.startRow = 0;
ca.endRow = 0;
ca.startColumn = 0;
ca.endColumn = 0;
// Add a new validation.
const validationIndex = validations.add(ca);
const validation = validations.get(validationIndex);
// Set the data validation type.
validation.type = AsposeCells.ValidationType.Date;
// Set the operator for the data validation
validation.operator = AsposeCells.OperatorType.Between;
// Set the value or expression associated with the data validation.
validation.formula1 = "1/1/1970";
// The value or expression associated with the second part of the data validation.
validation.formula2 = "12/31/1999";
// Enable the error.
validation.showError = true;
// Set the validation alert style.
validation.alertStyle = AsposeCells.ValidationAlertType.Stop;
// Set the title of the data-validation error dialog box
validation.errorTitle = "Date Error";
// Set the data validation error message.
validation.errorMessage = "Enter a Valid Date";
// Set and enable the data validation input message.
validation.inputMessage = "Date Validation Type";
validation.ignoreBlank = true;
validation.showInput = true;
// Set a collection of CellArea which contains the data validation settings.
const cellArea = new AsposeCells.CellArea();
cellArea.startRow = 0;
cellArea.endRow = 0;
cellArea.startColumn = 1;
cellArea.endColumn = 1;
// Add the validation area.
validation.addArea(cellArea);
// Save the Excel file and provide download link.
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData], { type: "application/vnd.ms-excel" });
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.out.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Workbook created and validation added. Click the download link to get the file.</p>';
});
</script>
</html>
Time Data Validation
With this type of validation, users can enter times within a specified range, or meeting some criteria, into the validated cells. In the example, the user is restricted to enter times between 09:00 to 11:30 AM. Here, the validation area is the B1 cell.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Time Validation Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, Worksheet, Cell } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
let workbook;
if (fileInput.files.length) {
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
workbook = new Workbook(new Uint8Array(arrayBuffer));
} else {
// Create a new workbook if no file is provided
workbook = new Workbook();
}
// Obtain the cells of the first worksheet.
const worksheet = workbook.worksheets.get(0);
const cells = worksheet.cells;
// Put a string value into A1 cell.
const a1 = cells.get("A1");
a1.value = "Please enter Time b/w 09:00 and 11:30 'o Clock";
// Set the row height and column width for the cells using row/column objects.
cells.rows.get(0).height = 31;
cells.columns.get(0).width = 35;
// Get the validations collection.
const validations = worksheet.validations;
// Create Cell Area
const ca = new AsposeCells.CellArea();
ca.startRow = 0;
ca.endRow = 0;
ca.startColumn = 0;
ca.endColumn = 0;
// Add a new validation and obtain it.
const validationIndex = validations.add(ca);
const validation = validations.get(validationIndex);
// Set the data validation type and other properties.
validation.type = AsposeCells.ValidationType.Time;
validation.operator = AsposeCells.OperatorType.Between;
validation.formula1 = "09:00";
validation.formula2 = "11:30";
validation.showError = true;
validation.alertStyle = AsposeCells.ValidationAlertType.Information;
validation.errorTitle = "Time Error";
validation.errorMessage = "Enter a Valid Time";
validation.inputMessage = "Time Validation Type";
validation.ignoreBlank = true;
validation.showInput = true;
// Set a collection of CellArea which contains the data validation settings.
const cellArea = new AsposeCells.CellArea();
cellArea.startRow = 0;
cellArea.endRow = 0;
cellArea.startColumn = 1;
cellArea.endColumn = 1;
// Add the validation area.
validation.addArea(cellArea);
// Save the Excel file.
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData], { type: 'application/vnd.ms-excel' });
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.out.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Operation completed successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
Text Length Data Validation
With this type of validation, users can enter text values of a specified length into the validated cells. In the example, the user is restricted to enter string values with no more than 5 characters. The validation area is the B1 cell.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Example - Add Text Length Validation</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, CellArea, ValidationType, OperatorType, ValidationAlertType } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
// Create a new workbook.
const workbook = new Workbook();
// Obtain the cells of the first worksheet.
const cells = workbook.worksheets.get(0).cells;
// Put a string value into A1 cell.
const cellA1 = cells.get("A1");
cellA1.value = "Please enter a string not more than 5 chars";
// Set row height and column width for the cell.
cells.rows.get(0).height = 31;
cells.columns.get(0).width = 35;
// Get the validations collection.
const validations = workbook.worksheets.get(0).validations;
// Create Cell Area
const ca = new CellArea();
ca.startRow = 0;
ca.endRow = 0;
ca.startColumn = 0;
ca.endColumn = 0;
// Add a new validation.
const validationIndex = validations.add(ca);
const validation = validations.get(validationIndex);
// Set the data validation type.
validation.type = ValidationType.TextLength;
// Set the operator for the data validation.
validation.operator = OperatorType.LessOrEqual;
// Set the value or expression associated with the data validation.
validation.formula1 = "5";
// Enable the error.
validation.showError = true;
// Set the validation alert style.
validation.alertStyle = ValidationAlertType.Warning;
// Set the title of the data-validation error dialog box.
validation.errorTitle = "Text Length Error";
// Set the data validation error message.
validation.errorMessage = " Enter a Valid String";
// Set and enable the data validation input message.
validation.inputMessage = "TextLength Validation Type";
validation.ignoreBlank = true;
validation.showInput = true;
// Set a collection of CellArea which contains the data validation settings.
const cellArea = new CellArea();
cellArea.startRow = 0;
cellArea.endRow = 0;
cellArea.startColumn = 1;
cellArea.endColumn = 1;
// Add the validation area.
validation.addArea(cellArea);
// Save the Excel file.
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.out.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">File created successfully. Click the download link to get the file.</p>';
});
</script>
</html>
Data Validation Rules
When data validations are implemented, validation can be checked by assigning different values in the cells. cell.validationValue can be used to fetch the validation result. The following example demonstrates this feature with different values. The sample file can be downloaded from the following link for testing:
sampleDataValidationRules.xlsx
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Data Validation Check Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
const resultDiv = document.getElementById('result');
resultDiv.innerHTML = '';
if (!fileInput.files.length) {
resultDiv.innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiate the workbook from the uploaded Excel file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Access the first worksheet
const worksheet = workbook.worksheets.get(0);
// Access Cell C1
// Cell C1 has the Decimal Validation applied on it.
// It can take only the values Between 10 and 20
const cell = worksheet.cells.get("C1");
const messages = [];
// Enter 3 inside this cell
// Since it is not between 10 and 20, it should fail the validation
cell.value = 3;
// Check if number 3 satisfies the Data Validation rule applied on this cell
const isValid3 = cell.validationValue;
messages.push(`Is 3 a Valid Value for this Cell: ${isValid3}`);
// Enter 15 inside this cell
// Since it is between 10 and 20, it should succeed the validation
cell.value = 15;
// Check if number 15 satisfies the Data Validation rule applied on this cell
const isValid15 = cell.validationValue;
messages.push(`Is 15 a Valid Value for this Cell: ${isValid15}`);
// Enter 30 inside this cell
// Since it is not between 10 and 20, it should fail the validation again
cell.value = 30;
// Check if number 30 satisfies the Data Validation rule applied on this cell
const isValid30 = cell.validationValue;
messages.push(`Is 30 a Valid Value for this Cell: ${isValid30}`);
// Display results
resultDiv.innerHTML = messages.map(m => `<p>${m}</p>`).join('');
// Save the modified workbook and provide download link
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData], { type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" });
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
});
</script>
</html>
Check if validation in cell is dropdown
As we have seen there are many types of validations that can be implemented within a cell. If you want to check whether validation is a dropdown or not, the validation.inCellDropDown method can be used to test this. The following sample code demonstrates the usage of this property. A sample file for testing can be downloaded from the following link:
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Check Data Validation Drop-downs</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
const resultDiv = document.getElementById('result');
resultDiv.innerHTML = '';
if (!fileInput.files.length) {
resultDiv.innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiating a Workbook object from uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Accessing the worksheet named "Sheet1"
const sheet = workbook.worksheets.get("Sheet1");
const cells = sheet.cells;
const appendMessage = (msg, color = 'black') => {
const p = document.createElement('p');
p.style.color = color;
p.textContent = msg;
resultDiv.appendChild(p);
};
const checkDropDown = (cell, cellRef) => {
const validation = cell.validation;
if (validation.inCellDropDown) {
appendMessage(`${cellRef} is a dropdown`, 'green');
} else {
appendMessage(`${cellRef} is NOT a dropdown`, 'orange');
}
};
checkDropDown(cells.get("A2"), "A2");
checkDropDown(cells.get("B2"), "B2");
checkDropDown(cells.get("C2"), "C2");
});
</script>
</html>
Add CellArea to existing Validation
There might be cases where you might want to add CellArea to existing Validation. When you add CellArea using Validation.addArea(CellArea), Aspose.Cells checks all existing areas to see if the new area already exists. If the file has a large number of validations, this takes a performance hit. To overcome this, the API provides the Validation.addArea(CellArea, boolean, boolean) method. The checkIntersection parameter indicates whether to check the intersection of a given area with existing validation areas. Setting it to false will disable the checking of other areas. The checkEdge parameter indicates whether to check the applied areas. If the new area becomes the top-left area, internal settings are rebuilt. If you are sure that the new area is not the top-left area, you may set this parameter as false.
The following code snippet demonstrates the use of the Validation.addArea(CellArea, boolean, boolean) method to add a new CellArea to existing Validation.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example - Validations</title>
</head>
<body>
<h1>Validations Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
if (!fileInput.files.length) {
document.getElementById('result').innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiating a Workbook object from uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Access first worksheet.
const worksheet = workbook.worksheets.get(0);
// Accessing the Validations collection of the worksheet
const validation = worksheet.validations.get(0);
// Create your cell area.
const cellArea = AsposeCells.CellArea.createCellArea("D5", "E7");
// Adding the cell area to Validation
validation.addArea(cellArea, false, false);
// Save the output workbook and provide download link
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'ValidationsSample_out.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Validation area added successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
The source and output excel files are attached for reference.