数据验证
数据验证类型和执行
数据验证是设置有关工作表上输入的数据的规则的能力。例如,使用验证可确保标有日期的列仅包含日期,或者另一列仅包含数字。甚至可以确保标有日期的列仅包含特定范围内的日期。通过数据验证,可以控制输入工作表中单元格的内容。
Microsoft Excel 支持许多不同类型的数据验证。 每种类型用于控制输入到单元格或单元格范围中的数据类型。 下面的代码片段说明了如何验证:
- 数字是整数,即它们没有小数部分。
- 十进制数遵循正确的结构。代码示例定义了一组单元格应具有两个小数位。
- 值受限于值列表。列表验证定义了可应用于单元格或单元格范围的单独值列表。
- 日期在特定范围内。
- 时间在特定范围内。
- 文本在给定的字符长度内。
Microsoft Excel中的数据验证
要使用Microsoft Excel创建验证:
- 在工作表中,选择要应用验证的单元格。
- 从数据菜单中选择验证。将显示验证对话框。
- 单击设置选项卡并输入设置。
使用Aspose.Cells for Node.js via C++进行数据验证
数据验证是一项强大的功能,可验证输入工作表的信息。借助数据验证,开发人员可以为用户提供选择列表,限制数据输入为特定类型或大小等功能。
在 Aspose.Cells for Node.js via C++ 中,每个 Worksheet 类都有一个 getValidations() 方法,代表一组 Validation 对象。为了设置验证,将一些 Validation 类的属性设为如下:
- 类型——代表验证类型,可以使用 ValidationType 枚举中的预定义值之一进行指定。
- 运算符——代表用于验证的运算符,可以使用 OperatorType 枚举中的预定义值之一进行指定。
- Formula1 – 表示与数据验证的第一部分关联的值或表达式。
- Formula2 – 表示与数据验证的第二部分关联的值或表达式。
当 Validation 对象的属性配置完成后,开发者可以使用 CellArea 结构存储有关使用创建的验证进行验证的单元格范围的信息。
数据验证类型
ValidationType 枚举具有以下成员:
成员名称 | 描述 |
---|---|
AnyValue | 表示任何类型的值。 |
WholeNumber | 表示整数的验证类型。 |
Decimal | 表示十进制数字的验证类型。 |
List | 表示下拉列表的验证类型。 |
Date | 表示日期的验证类型。 |
Time | 表示时间的验证类型。 |
TextLength | 表示文本长度的验证类型。 |
Custom | 表示自定义验证类型。 |
整数数据验证
使用此类型的验证,用户只能在验证的单元格中输入指定范围内的整数。接下来的代码示例展示了如何实现整数字验证类型。该示例使用 Aspose.Cells for Node.js via C++ 创建了与上述 Microsoft Excel 中相同的数据验证。
const path = require("path"); | |
const AsposeCells = require("aspose.cells.node"); | |
// The path to the documents directory. | |
const dataDir = path.join(__dirname, "data"); | |
// Create directory if it is not already present. | |
const fs = require("fs"); | |
if (!fs.existsSync(dataDir)) { | |
fs.mkdirSync(dataDir); | |
} | |
// Create a workbook object. | |
const workbook = new AsposeCells.Workbook(); | |
// Create a worksheet and get the first worksheet. | |
const ExcelWorkSheet = workbook.getWorksheets().get(0); | |
// Accessing the Validations collection of the worksheet | |
const validations = workbook.getWorksheets().get(0).getValidations(); | |
// 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 validation = validations.get(validations.add(ca)); | |
// Setting the validation type to whole number | |
validation.setType(AsposeCells.ValidationType.WholeNumber); | |
// Setting the operator for validation to Between | |
validation.setOperator(AsposeCells.OperatorType.Between); | |
// Setting the minimum value for the validation | |
validation.setFormula1("10"); | |
// Setting the maximum value for the validation | |
validation.setFormula2("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. | |
workbook.save(path.join(dataDir, "output.out.xls")); |
列表数据验证
这种类型的验证允许用户从下拉列表中输入值。它提供了一个列表:包含数据的一系列行。在示例中,添加了第二个工作表来保存列表源。用户只能从列表中选择值。验证区域是第一个工作表中的单元格范围 A1:A5。
这里重要的是要将 Validation.setInCellDropDown(boolean) 属性设为 true。
const AsposeCells = require("aspose.cells.node"); | |
const path = require("path"); | |
// The path to the documents directory. | |
const dataDir = path.join(__dirname, "data"); | |
// Create directory if it is not already present. | |
const fs = require("fs"); | |
if (!fs.existsSync(dataDir)) { | |
fs.mkdirSync(dataDir); | |
} | |
// Create a workbook object. | |
let workbook = new AsposeCells.Workbook(); | |
// Get the first worksheet. | |
let worksheet1 = workbook.getWorksheets().get(0); | |
// Add a new worksheet and access it. | |
let i = workbook.getWorksheets().add(); | |
let worksheet2 = workbook.getWorksheets().get(i); | |
// Create a range in the second worksheet. | |
let range = worksheet2.getCells().createRange("E1", "E4"); | |
// Name the range. | |
range.setName("MyRange"); | |
// Fill different cells with data in the range. | |
range.get(0, 0).putValue("Blue"); | |
range.get(1, 0).putValue("Red"); | |
range.get(2, 0).putValue("Green"); | |
range.get(3, 0).putValue("Yellow"); | |
// Get the validations collection. | |
let validations = worksheet1.getValidations(); | |
// Create Cell Area | |
let ca = new AsposeCells.CellArea(); | |
ca.startRow = 0; | |
ca.endRow = 0; | |
ca.startColumn = 0; | |
ca.endColumn = 0; | |
// Create a new validation to the validations list. | |
let validation = validations.get(validations.add(ca)); | |
// Set the validation type. | |
validation.setType(AsposeCells.ValidationType.List); | |
// Set the operator. | |
validation.setOperator(AsposeCells.OperatorType.None); | |
// Set the in cell drop down. | |
validation.setInCellDropDown(true); | |
// Set the formula1. | |
validation.setFormula1("=MyRange"); | |
// Enable it to show error. | |
validation.setShowError(true); | |
// Set the alert type severity level. | |
validation.setAlertStyle(AsposeCells.ValidationAlertType.Stop); | |
// Set the error title. | |
validation.setErrorTitle("Error"); | |
// Set the error message. | |
validation.setErrorMessage("Please select a color from the list"); | |
// Specify the validation area. | |
let area = new AsposeCells.CellArea(); | |
area.startRow = 0; | |
area.endRow = 4; | |
area.startColumn = 0; | |
area.endColumn = 0; | |
// Add the validation area. | |
validation.addArea(area); | |
// Save the Excel file. | |
workbook.save(path.join(dataDir, "output.out.xls")); |
日期数据验证
使用此类型的验证,用户可以在验证单元格中输入符合指定范围或特定条件的日期值。在此示例中,用户被限制只能输入1970年至1999年之间的日期。这里,验证区域是B1单元格。
const path = require("path"); | |
const AsposeCells = require("aspose.cells.node"); | |
// The path to the documents directory. | |
const dataDir = path.join(__dirname, "data"); | |
// Create directory if it is not already present. | |
const fs = require("fs"); | |
if (!fs.existsSync(dataDir)) { | |
fs.mkdirSync(dataDir); | |
} | |
// Create a workbook. | |
let workbook = new AsposeCells.Workbook(); | |
// Obtain the cells of the first worksheet. | |
let cells = workbook.getWorksheets().get(0).getCells(); | |
// Put a string value into the A1 cell. | |
cells.get("A1").putValue("Please enter Date b/w 1/1/1970 and 12/31/1999"); | |
// Set row height and column width for the cells. | |
cells.setRowHeight(0, 31); | |
cells.setColumnWidth(0, 35); | |
// Get the validations collection. | |
let validations = workbook.getWorksheets().get(0).getValidations(); | |
// Create Cell Area | |
let ca = new AsposeCells.CellArea(); | |
ca.startRow = 0; | |
ca.endRow = 0; | |
ca.startColumn = 0; | |
ca.endColumn = 0; | |
// Add a new validation. | |
let validation = validations.get(validations.add(ca)); | |
// Set the data validation type. | |
validation.setType(AsposeCells.ValidationType.Date); | |
// Set the operator for the data validation | |
validation.setOperator(AsposeCells.OperatorType.Between); | |
// Set the value or expression associated with the data validation. | |
validation.setFormula1("1/1/1970"); | |
// The value or expression associated with the second part of the data validation. | |
validation.setFormula2("12/31/1999"); | |
// Enable the error. | |
validation.setShowError(true); | |
// Set the validation alert style. | |
validation.setAlertStyle(AsposeCells.ValidationAlertType.Stop); | |
// Set the title of the data-validation error dialog box | |
validation.setErrorTitle("Date Error"); | |
// Set the data validation error message. | |
validation.setErrorMessage("Enter a Valid Date"); | |
// Set and enable the data validation input message. | |
validation.setInputMessage("Date Validation Type"); | |
validation.setIgnoreBlank(true); | |
validation.setShowInput(true); | |
// Set a collection of CellArea which contains the data validation settings. | |
let 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. | |
workbook.save(path.join(dataDir, "output.out.xls")); |
时间数据验证
使用此类型的验证,用户可以在验证单元格中输入符合指定范围或特定条件的时间值。在此示例中,用户被限制只能输入上午09:00至11:30之间的时间。这里,验证区域是B1单元格。
const AsposeCells = require("aspose.cells.node"); | |
const path = require("path"); | |
// The path to the documents directory. | |
const dataDir = path.join(__dirname, "data"); | |
// Create directory if it is not already present. | |
if (!require("fs").existsSync(dataDir)) { | |
require("fs").mkdirSync(dataDir); | |
} | |
// Create a workbook. | |
const workbook = new AsposeCells.Workbook(); | |
// Obtain the cells of the first worksheet. | |
const cells = workbook.getWorksheets().get(0).getCells(); | |
// Put a string value into A1 cell. | |
cells.get("A1").putValue("Please enter Time b/w 09:00 and 11:30 'o Clock"); | |
// Set the row height and column width for the cells. | |
cells.setRowHeight(0, 31); | |
cells.setColumnWidth(0, 35); | |
// Get the validations collection. | |
const validations = workbook.getWorksheets().get(0).getValidations(); | |
// 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 validation = validations.get(validations.add(ca)); | |
// Set the data validation type. | |
validation.setType(AsposeCells.ValidationType.Time); | |
// Set the operator for the data validation. | |
validation.setOperator(AsposeCells.OperatorType.Between); | |
// Set the value or expression associated with the data validation. | |
validation.setFormula1("09:00"); | |
// The value or expression associated with the second part of the data validation. | |
validation.setFormula2("11:30"); | |
// Enable the error. | |
validation.setShowError(true); | |
// Set the validation alert style. | |
validation.setAlertStyle(AsposeCells.ValidationAlertType.Information); | |
// Set the title of the data-validation error dialog box. | |
validation.setErrorTitle("Time Error"); | |
// Set the data validation error message. | |
validation.setErrorMessage("Enter a Valid Time"); | |
// Set and enable the data validation input message. | |
validation.setInputMessage("Time Validation Type"); | |
validation.setIgnoreBlank(true); | |
validation.setShowInput(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. | |
workbook.save(path.join(dataDir, "output.out.xls")); |
文本长度数据验证
使用此类型的验证,用户可以在验证单元格中输入指定长度的文本值。在此示例中,用户被限制只能输入不超过5个字符的字符串值。验证区域是B1单元格。
const path = require("path"); | |
const AsposeCells = require("aspose.cells.node"); | |
// The path to the documents directory. | |
const dataDir = path.join(__dirname, "data"); | |
// Create directory if it is not already present. | |
if (!require("fs").existsSync(dataDir)) { | |
require("fs").mkdirSync(dataDir); | |
} | |
// Create a new workbook. | |
const workbook = new AsposeCells.Workbook(); | |
// Obtain the cells of the first worksheet. | |
const cells = workbook.getWorksheets().get(0).getCells(); | |
// Put a string value into A1 cell. | |
cells.get("A1").putValue("Please enter a string not more than 5 chars"); | |
// Set row height and column width for the cell. | |
cells.setRowHeight(0, 31); | |
cells.setColumnWidth(0, 35); | |
// Get the validations collection. | |
const validations = workbook.getWorksheets().get(0).getValidations(); | |
// 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 validation = validations.get(validations.add(ca)); | |
// Set the data validation type. | |
validation.setType(AsposeCells.ValidationType.TextLength); | |
// Set the operator for the data validation. | |
validation.setOperator(AsposeCells.OperatorType.LessOrEqual); | |
// Set the value or expression associated with the data validation. | |
validation.setFormula1("5"); | |
// Enable the error. | |
validation.setShowError(true); | |
// Set the validation alert style. | |
validation.setAlertStyle(AsposeCells.ValidationAlertType.Warning); | |
// Set the title of the data-validation error dialog box. | |
validation.setErrorTitle("Text Length Error"); | |
// Set the data validation error message. | |
validation.setErrorMessage(" Enter a Valid String"); | |
// Set and enable the data validation input message. | |
validation.setInputMessage("TextLength Validation Type"); | |
validation.setIgnoreBlank(true); | |
validation.setShowInput(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. | |
workbook.save(path.join(dataDir, "output.out.xls")); |
数据验证规则
当实现数据验证后,可以通过在单元格中设定不同值来检测验证结果。可以使用 Cell.getValidationValue() 获取验证结果。以下示例用不同的值展示了此功能。测试用的示例文件可以通过以下链接下载:
sampleDataValidationRules.xlsx
const AsposeCells = require("aspose.cells.node"); | |
const path = require("path"); | |
// The path to the documents directory. | |
const dataDir = path.join(__dirname, "data"); | |
// Instantiate the workbook from sample Excel file | |
const workbook = new AsposeCells.Workbook(dataDir + "sample.xlsx"); | |
// Access the first worksheet | |
const worksheet = workbook.getWorksheets().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.getCells().get("C1"); | |
// Enter 3 inside this cell | |
// Since it is not between 10 and 20, it should fail the validation | |
cell.putValue(3); | |
// Check if number 3 satisfies the Data Validation rule applied on this cell | |
console.log("Is 3 a Valid Value for this Cell: " + cell.getValidationValue()); | |
// Enter 15 inside this cell | |
// Since it is between 10 and 20, it should succeed the validation | |
cell.putValue(15); | |
// Check if number 15 satisfies the Data Validation rule applied on this cell | |
console.log("Is 15 a Valid Value for this Cell: " + cell.getValidationValue()); | |
// Enter 30 inside this cell | |
// Since it is not between 10 and 20, it should fail the validation again | |
cell.putValue(30); | |
// Check if number 30 satisfies the Data Validation rule applied on this cell | |
console.log("Is 30 a Valid Value for this Cell: " + cell.getValidationValue()); |
检查单元格中的验证是否为下拉列表
正如我们所见,单元格中可以实现许多类型的验证。如果你想检查验证是否为下拉菜单, 可以使用 Validation.getInCellDropDown() 方法进行测试。以下示例代码展示了此属性的用法。可以从以下链接下载测试的示例文件:
const AsposeCells = require("aspose.cells.node"); | |
const path = require("path"); | |
// The path to the documents directory. | |
const dataDir = path.join(__dirname, "data"); | |
const workbook = new AsposeCells.Workbook(dataDir + "sampleValidation.xlsx"); | |
const sheet = workbook.getWorksheets().get("Sheet1"); | |
const cells = sheet.getCells(); | |
const checkDropDown = (cell, cellRef) => { | |
const validation = cell.getValidation(); | |
if (validation.getInCellDropDown()) { | |
console.log(`${cellRef} is a dropdown`); | |
} else { | |
console.log(`${cellRef} is NOT a dropdown`); | |
} | |
}; | |
checkDropDown(cells.get("A2"), "A2"); | |
checkDropDown(cells.get("B2"), "B2"); | |
checkDropDown(cells.get("C2"), "C2"); |
为现有验证添加CellArea
可能存在你想向现有 Validation 添加 CellArea 的情况。使用 Validation.addArea(CellArea) 添加 CellArea 时,Aspose.Cells 会检查所有现有区域,查看新区域是否已存在。如果文件中验证数量较多,可能会影响性能。为了解决此问题,API 提供了 Validation.addArea(CellArea, boolean, boolean) 方法。checkIntersection 参数表示是否检测给定区域与现有验证区域的交集。设为 false 将禁用对其他区域的检测。checkEdge 参数表示是否检查应用区域。如果新区域成为左上角区域,内部设置会被重建。若确定新区域不是左上角区域,可将此参数设为 false。
以下代码片段演示了如何使用 Validation.addArea(CellArea, boolean, boolean) 方法向现有 Validation 添加一个新 CellArea。
const path = require("path"); | |
const AsposeCells = require("aspose.cells.node"); | |
// The path to the documents directory. | |
const sourceDir = path.join(__dirname, "data"); | |
const outputDir = path.join(__dirname, "output"); | |
const workbook = new AsposeCells.Workbook(path.join(sourceDir, "ValidationsSample.xlsx")); | |
// Access first worksheet. | |
const worksheet = workbook.getWorksheets().get(0); | |
// Accessing the Validations collection of the worksheet | |
const validation = worksheet.getValidations().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. | |
workbook.save(path.join(outputDir, "ValidationsSample_out.xlsx")); |
源和输出的Excel文件已附上供参考。