数据验证
Microsoft Excel 提供一些良好的功能来自动过滤或验证工作表数据。
数据验证是设置有关工作表上输入的数据规则的能力。 例如,使用验证来确保标记为日期的列仅包含日期,或者另一列仅包含数字。 甚至可以确保标记为日期的列只包含特定范围内的日期。 通过数据验证,您可以控制输入到工作表单元格中的内容。 Aspose.Cells 完全支持 Microsoft Excel 的数据验证和自动筛选功能。 本文解释了如何在 Microsoft Excel 中使用这些功能,以及如何使用 Aspose.Cells 对其进行编码。
数据验证类型和执行
Microsoft Excel 支持许多不同类型的数据验证。 每种类型用于控制输入到单元格或单元格范围中的数据类型。 下面的代码片段说明了如何验证:
- 数字是整数,即它们没有小数部分。
- 十进制数字遵循正确结构。代码示例定义了一个范围的单元格应具有两个小数位数。
- 值受限于值列表。列表验证定义了可应用于单元格或单元格范围的单独值列表。
- 日期落在特定范围内。
- 时间在特定范围内。
- 文本在给定字符长度内。
Microsoft Excel中的数据验证
要使用Microsoft Excel创建验证:
-
在工作表中,选择要应用验证的单元格。
-
从数据菜单中,选择验证。 将显示验证对话框。
-
单击设置选项卡,并输入下方显示的设置。
数据验证设置
Aspose.Cells中的数据验证
数据验证是一项强大的功能,可验证输入工作表的信息。借助数据验证,开发人员可以为用户提供选择列表,限制数据输入为特定类型或大小等功能。 在Aspose.Cells中,每个Worksheet类都有一个Validations对象,该对象表示一组Validation对象。要设置验证,请设置Validation类的一些属性:
- Type:表示验证类型,可以使用ValidationType枚举中的预定义值来指定。
- Operator:表示验证中要使用的运算符,可以使用OperatorType枚举中的预定义值来指定。
- Formula1:表示与数据验证第一部分相关联的值或表达式。
- Formula2:表示与数据验证第二部分相关联的值或表达式。
当Validation对象的属性配置完成后,开发人员可以使用CellArea结构来存储关于将使用创建的验证进行验证的单元格范围的信息。
数据验证类型
数据验证允许您在每个单元格中构建业务规则,以便不正确的输入会导致错误消息。业务规则是管理业务运作的政策和程序。Aspose.Cells支持所有重要类型的数据验证。
ValidationType枚举具有以下成员:
成员名称 | 描述 |
---|---|
ANY_VALUE | 表示任何类型的值。 |
WHOLE_NUMBER | 表示整数验证类型。 |
DECIMAL | 表示小数验证类型。 |
LIST | 表示下拉列表验证类型。 |
DATE | 表示日期验证类型。 |
TIME | 表示时间验证类型。 |
TEXT_LENGTH | 表示文本长度验证类型。 |
CUSTOM | 表示自定义验证类型。 |
编程示例:整数数据验证
使用此类型的验证,用户只能在验证的单元格中输入指定范围内的整数。接下来的代码示例展示了如何实现WHOLE_NUMBER验证类型。示例创建了一个与我们在Microsoft Excel中创建的相同的数据验证,使用Aspose.Cells。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getSharedDataDir(WholeNumberDataValidation.class) + "data/"; | |
// Instantiating an Workbook object | |
Workbook workbook = new Workbook(); | |
WorksheetCollection worksheets = workbook.getWorksheets(); | |
// Accessing the Validations collection of the worksheet | |
Worksheet worksheet = worksheets.get(0); | |
// Applying the validation to a range of cells from A1 to B2 using the | |
// CellArea structure | |
CellArea area = new CellArea(); | |
area.StartRow = 0; | |
area.StartColumn = 0; | |
area.EndRow = 1; | |
area.EndColumn = 1; | |
ValidationCollection validations = worksheet.getValidations(); | |
// Creating a Validation object | |
int index = validations.add(area); | |
Validation validation = validations.get(index); | |
// Setting the validation type to whole number | |
validation.setType(ValidationType.WHOLE_NUMBER); | |
// Setting the operator for validation to Between | |
validation.setOperator(OperatorType.BETWEEN); | |
// Setting the minimum value for the validation | |
validation.setFormula1("10"); | |
// Setting the maximum value for the validation | |
validation.setFormula2("1000"); | |
// Saving the Excel file | |
workbook.save(dataDir + "WNDValidation_out.xls"); | |
// Print message | |
System.out.println("Process completed successfully"); |
编程示例:小数数据验证
使用此类型的验证,用户可以在验证的单元格中输入小数。在示例中,用户被限制只能输入小数值,并且验证区域是A1:A10。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getSharedDataDir(DecimalDataValidation.class) + "data/"; | |
// Create a workbook object. | |
Workbook workbook = new Workbook(); | |
// Create a worksheet and get the first worksheet. | |
Worksheet ExcelWorkSheet = workbook.getWorksheets().get(0); | |
// Specify the validation area of cells. | |
CellArea area = new CellArea(); | |
area.StartRow = 0; | |
area.StartColumn = 0; | |
area.EndRow = 9; | |
area.EndColumn = 0; | |
// Obtain the existing Validations collection. | |
ValidationCollection validations = ExcelWorkSheet.getValidations(); | |
// Create a validation object adding to the collection list. | |
int index = validations.add(area); | |
Validation validation = validations.get(index); | |
// Set the validation type. | |
validation.setType(ValidationType.DECIMAL); | |
// Specify the operator. | |
validation.setOperator(OperatorType.BETWEEN); | |
// Set the lower and upper limits. | |
validation.setFormula1("10"); | |
validation.setFormula2("1000"); | |
// Set the error message. | |
validation.setErrorMessage("Please enter a valid integer or decimal number"); | |
// Save the workbook. | |
workbook.save(dataDir + "DDValidation_out.xls"); | |
// Print message | |
System.out.println("Process completed successfully"); |
编程示例:列表数据验证
此验证类型允许用户从下拉列表中输入值。它提供了一个列表:包含数据的一系列行。用户只能从列表中选择值。验证区域是第一个工作表中的单元格范围A1:A5。
在这里很重要的是将Validation.setInCellDropDown属性设置为true。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getSharedDataDir(ListDataValidation.class) + "data/"; | |
// Create a workbook object. | |
Workbook workbook = new Workbook(); | |
// Get the first worksheet. | |
Worksheet ExcelWorkSheet = workbook.getWorksheets().get(0); | |
// Add a new worksheet and access it. | |
int sheetIndex = workbook.getWorksheets().add(); | |
Worksheet worksheet2 = workbook.getWorksheets().get(sheetIndex); | |
// Create a range with name in the second worksheet. | |
Range range = worksheet2.getCells().createRange(0, 4, 4, 4); | |
range.setName("MyRange"); | |
// Fill different cells with data in the range. | |
range.get(0, 0).setValue("Blue"); | |
range.get(1, 0).setValue("Red"); | |
range.get(2, 0).setValue("Green"); | |
range.get(3, 0).setValue("Yellow"); | |
// Specify the validation area of cells. | |
CellArea area = new CellArea(); | |
area.StartRow = 0; | |
area.StartColumn = 0; | |
area.EndRow = 4; | |
area.EndColumn = 0; | |
// Obtain the existing Validations collection. | |
ValidationCollection validations = ExcelWorkSheet.getValidations(); | |
// Create a validation object adding to the collection list. | |
int index = validations.add(area); | |
Validation validation = validations.get(index); | |
// Set the validation type. | |
validation.setType(ValidationType.LIST); | |
// 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(ValidationAlertType.STOP); | |
// Set the error title. | |
validation.setErrorTitle("Error"); | |
// Set the error message. | |
validation.setErrorMessage("Please select a color from the list"); | |
// Save the excel file. | |
workbook.save(dataDir + "LDValidation_out.xls"); | |
// Print message | |
System.out.println("Process completed successfully"); |
编程示例: 日期数据验证
使用此类型的验证,用户可以在验证单元格中输入符合指定范围或特定条件的日期值。在此示例中,用户被限制只能输入1970年至1999年之间的日期。这里,验证区域是B1单元格。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getSharedDataDir(DateDataValidation.class) + "data/"; | |
// Create a workbook. | |
Workbook workbook = new Workbook(); | |
// Obtain the cells of the first worksheet. | |
Cells cells = workbook.getWorksheets().get(0).getCells(); | |
// Put a string value into the A1 cell. | |
cells.get("A1").setValue("Please enter Date b/w 1/1/1970 and 12/31/1999"); | |
// Wrap the text. | |
Style style = cells.get("A1").getStyle(); | |
style.setTextWrapped(true); | |
cells.get("A1").setStyle(style); | |
// Set row height and column width for the cells. | |
cells.setRowHeight(0, 31); | |
cells.setColumnWidth(0, 35); | |
// Set a collection of CellArea which contains the data validation | |
// settings. | |
CellArea area = new CellArea(); | |
area.StartRow = 0; | |
area.StartColumn = 1; | |
area.EndRow = 0; | |
area.EndColumn = 1; | |
// Get the validations collection. | |
ValidationCollection validations = workbook.getWorksheets().get(0).getValidations(); | |
// Add a new validation. | |
int i = validations.add(area); | |
Validation validation = validations.get(i); | |
// Set the data validation type. | |
validation.setType(ValidationType.DATE); | |
// Set the operator for the data validation | |
validation.setOperator(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(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); | |
// Save the excel file. | |
workbook.save(dataDir + "DDValidation_out.xls", FileFormatType.EXCEL_97_TO_2003); | |
// Print message | |
System.out.println("Process completed successfully"); |
编程示例: 时间数据验证
使用此类型的验证,用户可以在验证单元格中输入符合指定范围或特定条件的时间值。在此示例中,用户被限制只能输入上午09:00至11:30之间的时间。这里,验证区域是B1单元格。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getSharedDataDir(TimeDataValidation.class) + "data/"; | |
// Create a workbook. | |
Workbook workbook = new Workbook(); | |
// Obtain the cells of the first worksheet. | |
Cells cells = workbook.getWorksheets().get(0).getCells(); | |
// Put a string value into A1 cell. | |
cells.get("A1").setValue("Please enter Time b/w 09:00 and 11:30 'o Clock"); | |
// Wrap the text. | |
Style style = cells.get("A1").getStyle(); | |
style.setTextWrapped(true); | |
cells.get("A1").setStyle(style); | |
// Set row height and column width for the cells. | |
cells.setRowHeight(0, 31); | |
cells.setColumnWidth(0, 35); | |
// Set a collection of CellArea which contains the data validation | |
// settings. | |
CellArea area = new CellArea(); | |
area.StartRow = 0; | |
area.StartColumn = 1; | |
area.EndRow = 0; | |
area.EndColumn = 1; | |
// Get the validations collection. | |
ValidationCollection validations = workbook.getWorksheets().get(0).getValidations(); | |
// Add a new validation. | |
int i = validations.add(area); | |
Validation validation = validations.get(i); | |
// Set the data validation type. | |
validation.setType(ValidationType.TIME); | |
// Set the operator for the data validation | |
validation.setOperator(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(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); | |
// Save the excel file. | |
workbook.save(dataDir + "TDValidation_out.xls", FileFormatType.EXCEL_97_TO_2003); | |
// Print message | |
System.out.println("Process completed successfully"); |
编程示例: 文本长度数据验证
使用此类型的验证,用户可以在验证单元格中输入指定长度的文本值。在此示例中,用户被限制只能输入不超过5个字符的字符串值。验证区域是B1单元格。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getSharedDataDir(TextLengthDataValidation.class) + "data/"; | |
// Create a new workbook. | |
Workbook workbook = new Workbook(); | |
// Obtain the cells of the first worksheet. | |
Cells cells = workbook.getWorksheets().get(0).getCells(); | |
// Put a string value into A1 cell. | |
cells.get("A1").setValue("Please enter a string not more than 5 chars"); | |
// Wrap the text. | |
Style style = cells.get("A1").getStyle(); | |
style.setTextWrapped(true); | |
cells.get("A1").setStyle(style); | |
// Set row height and column width for the cells. | |
cells.setRowHeight(0, 31); | |
cells.setColumnWidth(0, 35); | |
// Set a collection of CellArea which contains the data validation | |
// settings. | |
CellArea area = new CellArea(); | |
area.StartRow = 0; | |
area.StartColumn = 1; | |
area.EndRow = 0; | |
area.EndColumn = 1; | |
// Get the validations collection. | |
ValidationCollection validations = workbook.getWorksheets().get(0).getValidations(); | |
// Add a new validation. | |
int i = validations.add(area); | |
Validation validation = validations.get(i); | |
// Set the data validation type. | |
validation.setType(ValidationType.TEXT_LENGTH); | |
// Set the operator for the data validation. | |
validation.setOperator(OperatorType.LESS_OR_EQUAL); | |
// 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(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); | |
// Save the excel file. | |
workbook.save(dataDir + "TLDValidation_out.xls", FileFormatType.EXCEL_97_TO_2003); | |
// Print message | |
System.out.println("Process completed successfully"); |
数据验证规则
当实施数据验证时,可以通过在单元格中分配不同的值来检查验证。Cell.GetValidationValue() 可用于获取验证结果。以下示例演示了使用不同值的此功能。可从以下链接下载示例文件进行测试:
SampleDataValidationRules.xlsx
示例代码
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// Instantiate the workbook from sample Excel file | |
Workbook workbook = new Workbook(srcDir + "sampleDataValidationRules.xlsx"); | |
// Access the first worksheet | |
Worksheet 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 | |
*/ | |
Cell 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 | |
System.out.println("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 | |
System.out.println("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 | |
System.out.println("Is 30 a Valid Value for this Cell: " + cell.getValidationValue()); | |
// Enter large number 12345678901 inside this cell | |
// Since it is not between 1 and 999999999999, it should pass the validation again | |
Cell cell2 = worksheet.getCells().get("D1"); | |
cell2.putValue(12345678901l); | |
// Check if number 12345678901 satisfies the Data Validation rule applied on this cell | |
System.out.println("Is 12345678901 a Valid Value for this Cell: " + cell2.getValidationValue()); |
检查单元格中的验证是否为下拉框
正如我们所见,可以在单元格中实施许多类型的验证。如果您想检查验证是否为下拉框,可以使用Validation.InCellDropDown 属性进行测试。以下示例代码演示了该属性的用法。可从以下链接下载示例文件进行测试:
sampleDataValidationRules.xlsx
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
Workbook book = new Workbook(srcDir + "sampleValidation.xlsx"); | |
Worksheet sheet = book.getWorksheets().get("Sheet1"); | |
Cells cells = sheet.getCells(); | |
Cell a2 = cells.get("A2"); | |
Validation va2 = a2.getValidation(); | |
if(va2.getInCellDropDown()) { | |
System.out.println("A2 is a dropdown"); | |
} else { | |
System.out.println("A2 is NOT a dropdown"); | |
} | |
Cell b2 = cells.get("B2"); | |
Validation vb2 = b2.getValidation(); | |
if(vb2.getInCellDropDown()) { | |
System.out.println("B2 is a dropdown"); | |
} else { | |
System.out.println("B2 is NOT a dropdown"); | |
} | |
Cell c2 = cells.get("C2"); | |
Validation vc2 = c2.getValidation(); | |
if(vc2.getInCellDropDown()) { | |
System.out.println("C2 is a dropdown"); | |
} else { | |
System.out.println("C2 is NOT a dropdown"); | |
} |
为现有验证添加CellArea
也许会有这样的情况,您可能希望向现有验证添加CellArea。通过使用Validation.AddArea(CellArea cellArea),Aspose.Cells会检查所有现有区域,看看新区域是否已经存在。如果文件具有大量验证,这将影响性能。为解决此问题,API提供了Validation.AddAreaCellArea cellArea, bool checkIntersection, bool checkEdge) 方法。checkIntersection参数指示是否检查给定区域与现有验证区域的交集。将其设置为false将禁用其他区域的检查。checkEdge参数指示是否检查已应用的区域。如果新区域成为左上角区域,内部设置将重新构建。如果您确信新区域不是左上角区域,可以将此参数设置为false。
以下代码片段演示了使用Validation.AddAreaCellArea cellArea, bool checkIntersection, bool checkEdge) 方法向现有验证添加新的CellArea。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the directories. | |
String sourceDir = Utils.Get_SourceDirectory(); | |
String outputDir = Utils.Get_OutputDirectory(); | |
Workbook workbook = new Workbook(sourceDir + "ValidationsSample.xlsx"); | |
// Access first worksheet. | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Accessing the Validations collection of the worksheet | |
Validation validation = worksheet.getValidations().get(0); | |
// Create your cell area. | |
CellArea cellArea = CellArea.createCellArea("D5", "E7"); | |
// Adding the cell area to Validation | |
validation.addArea(cellArea, false, false); | |
// Save the output workbook. | |
workbook.save(outputDir + "ValidationsSample_out.xlsx"); |
源和输出的Excel文件已附上供参考。