数据验证
数据验证类型和执行
数据验证是设置有关工作表上输入的数据的规则的能力。例如,使用验证可确保标有日期的列仅包含日期,或者另一列仅包含数字。甚至可以确保标有日期的列仅包含特定范围内的日期。通过数据验证,可以控制输入工作表中单元格的内容。
Microsoft Excel 支持许多不同类型的数据验证。 每种类型用于控制输入到单元格或单元格范围中的数据类型。 下面的代码片段说明了如何验证:
- 数字是整数,即它们没有小数部分。
- 十进制数遵循正确的结构。代码示例定义了一组单元格应具有两个小数位。
- 值受限于值列表。列表验证定义了可应用于单元格或单元格范围的单独值列表。
- 日期在特定范围内。
- 时间在特定范围内。
- 文本在给定的字符长度内。
Microsoft Excel中的数据验证
要使用Microsoft Excel创建验证:
- 在工作表中,选择要应用验证的单元格。
- 从数据菜单中选择验证。将显示验证对话框。
- 单击设置选项卡并输入设置。
Aspose.Cells中的数据验证
数据验证是一项强大的功能,可验证输入工作表的信息。借助数据验证,开发人员可以为用户提供选择列表,限制数据输入为特定类型或大小等功能。 在Aspose.Cells中,每个Worksheet类都有一个Validations属性,该属性表示一组Validation对象。要设置验证,设置一些Validation类的属性,如下所示:
- Type – 表示验证类型,可以使用ValidationType枚举中预定义的值之一来指定。
- Operator – 表示验证中要使用的运算符,可以通过在 OperatorType 枚举中使用预定义的值来指定。
- Formula1 – 表示与数据验证的第一部分关联的值或表达式。
- Formula2 – 表示与数据验证的第二部分关联的值或表达式。
当配置了 Validation 对象的属性后,开发人员可以使用 CellArea 结构来存储有关使用创建的验证进行验证的单元格范围的信息。
数据验证类型
ValidationType 枚举具有以下成员:
成员名称 | 描述 |
---|---|
AnyValue | 表示任何类型的值。 |
WholeNumber | 表示整数的验证类型。 |
Decimal | 表示十进制数字的验证类型。 |
List | 表示下拉列表的验证类型。 |
Date | 表示日期的验证类型。 |
Time | 表示时间的验证类型。 |
TextLength | 表示文本长度的验证类型。 |
Custom | 表示自定义验证类型。 |
整数数据验证
使用这种类型的验证,用户只能输入指定范围内的整数到被验证的单元格中。接下来的代码示例展示了如何实现整数验证类型。示例创建了同样的数据验证,用Aspose.Cells来创建,就像我们以上使用Microsoft Excel创建的一样。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create directory if it is not already present. | |
bool IsExists = System.IO.Directory.Exists(dataDir); | |
if (!IsExists) | |
System.IO.Directory.CreateDirectory(dataDir); | |
// Create a workbook object. | |
Workbook workbook = new Workbook(); | |
// Create a worksheet and get the first worksheet. | |
Worksheet ExcelWorkSheet = workbook.Worksheets[0]; | |
// Accessing the Validations collection of the worksheet | |
ValidationCollection validations = workbook.Worksheets[0].Validations; | |
// Create Cell Area | |
CellArea ca = new CellArea(); | |
ca.StartRow = 0; | |
ca.EndRow = 0; | |
ca.StartColumn = 0; | |
ca.EndColumn = 0; | |
// Creating a Validation object | |
Validation validation = validations[validations.Add(ca)]; | |
// Setting the validation type to whole number | |
validation.Type = ValidationType.WholeNumber; | |
// Setting the operator for validation to Between | |
validation.Operator = 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 | |
CellArea area; | |
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(dataDir + "output.out.xls"); |
列表数据验证
这种类型的验证允许用户从下拉列表中输入值。它提供了一个列表:包含数据的一系列行。在示例中,添加了第二个工作表来保存列表源。用户只能从列表中选择值。验证区域是第一个工作表中的单元格范围 A1:A5。
在这里重要的是,将 Validation.InCellDropDown 属性设置为 true。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create directory if it is not already present. | |
bool IsExists = System.IO.Directory.Exists(dataDir); | |
if (!IsExists) | |
System.IO.Directory.CreateDirectory(dataDir); | |
// Create a workbook object. | |
Workbook workbook = new Workbook(); | |
// Get the first worksheet. | |
Worksheet worksheet1 = workbook.Worksheets[0]; | |
// Add a new worksheet and access it. | |
int i = workbook.Worksheets.Add(); | |
Worksheet worksheet2 = workbook.Worksheets[i]; | |
// Create a range in the second worksheet. | |
Range range = worksheet2.Cells.CreateRange("E1", "E4"); | |
// Name the range. | |
range.Name = "MyRange"; | |
// Fill different cells with data in the range. | |
range[0, 0].PutValue("Blue"); | |
range[1, 0].PutValue("Red"); | |
range[2, 0].PutValue("Green"); | |
range[3, 0].PutValue("Yellow"); | |
// Get the validations collection. | |
ValidationCollection validations = worksheet1.Validations; | |
// Create Cell Area | |
CellArea ca = new CellArea(); | |
ca.StartRow = 0; | |
ca.EndRow = 0; | |
ca.StartColumn = 0; | |
ca.EndColumn = 0; | |
// Create a new validation to the validations list. | |
Validation validation = validations[validations.Add(ca)]; | |
// Set the validation type. | |
validation.Type = Aspose.Cells.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. | |
CellArea area; | |
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(dataDir + "output.out.xls"); |
日期数据验证
使用此类型的验证,用户可以在验证单元格中输入符合指定范围或特定条件的日期值。在此示例中,用户被限制只能输入1970年至1999年之间的日期。这里,验证区域是B1单元格。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create directory if it is not already present. | |
bool IsExists = System.IO.Directory.Exists(dataDir); | |
if (!IsExists) | |
System.IO.Directory.CreateDirectory(dataDir); | |
// Create a workbook. | |
Workbook workbook = new Workbook(); | |
// Obtain the cells of the first worksheet. | |
Cells cells = workbook.Worksheets[0].Cells; | |
// Put a string value into the A1 cell. | |
cells["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. | |
ValidationCollection validations = workbook.Worksheets[0].Validations; | |
// Create Cell Area | |
CellArea ca = new CellArea(); | |
ca.StartRow = 0; | |
ca.EndRow = 0; | |
ca.StartColumn = 0; | |
ca.EndColumn = 0; | |
// Add a new validation. | |
Validation validation = validations[validations.Add(ca)]; | |
// Set the data validation type. | |
validation.Type = ValidationType.Date; | |
// Set the operator for the data validation | |
validation.Operator = 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 = 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. | |
CellArea 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(dataDir + "output.out.xls"); |
时间数据验证
使用此类型的验证,用户可以在验证单元格中输入符合指定范围或特定条件的时间值。在此示例中,用户被限制只能输入上午09:00至11:30之间的时间。这里,验证区域是B1单元格。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create directory if it is not already present. | |
bool IsExists = System.IO.Directory.Exists(dataDir); | |
if (!IsExists) | |
System.IO.Directory.CreateDirectory(dataDir); | |
// Create a workbook. | |
Workbook workbook = new Workbook(); | |
// Obtain the cells of the first worksheet. | |
Cells cells = workbook.Worksheets[0].Cells; | |
// Put a string value into A1 cell. | |
cells["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. | |
ValidationCollection validations = workbook.Worksheets[0].Validations; | |
// Create Cell Area | |
CellArea ca = new CellArea(); | |
ca.StartRow = 0; | |
ca.EndRow = 0; | |
ca.StartColumn = 0; | |
ca.EndColumn = 0; | |
// Add a new validation. | |
Validation validation = validations[validations.Add(ca)]; | |
// Set the data validation type. | |
validation.Type = ValidationType.Time; | |
// Set the operator for the data validation. | |
validation.Operator = OperatorType.Between; | |
// Set the value or expression associated with the data validation. | |
validation.Formula1 = "09:00"; | |
// The value or expression associated with the second part of the data validation. | |
validation.Formula2 = "11:30"; | |
// Enable the error. | |
validation.ShowError = true; | |
// Set the validation alert style. | |
validation.AlertStyle = ValidationAlertType.Information; | |
// Set the title of the data-validation error dialog box. | |
validation.ErrorTitle = "Time Error"; | |
// Set the data validation error message. | |
validation.ErrorMessage = "Enter a Valid Time"; | |
// Set and enable the data validation input message. | |
validation.InputMessage = "Time Validation Type"; | |
validation.IgnoreBlank = true; | |
validation.ShowInput = true; | |
// Set a collection of CellArea which contains the data validation settings. | |
CellArea 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(dataDir + "output.out.xls"); |
文本长度数据验证
使用此类型的验证,用户可以在验证单元格中输入指定长度的文本值。在此示例中,用户被限制只能输入不超过5个字符的字符串值。验证区域是B1单元格。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create directory if it is not already present. | |
bool IsExists = System.IO.Directory.Exists(dataDir); | |
if (!IsExists) | |
System.IO.Directory.CreateDirectory(dataDir); | |
// Create a new workbook. | |
Workbook workbook = new Workbook(); | |
// Obtain the cells of the first worksheet. | |
Cells cells = workbook.Worksheets[0].Cells; | |
// Put a string value into A1 cell. | |
cells["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. | |
ValidationCollection validations = workbook.Worksheets[0].Validations; | |
// Create Cell Area | |
CellArea ca = new CellArea(); | |
ca.StartRow = 0; | |
ca.EndRow = 0; | |
ca.StartColumn = 0; | |
ca.EndColumn = 0; | |
// Add a new validation. | |
Validation validation = validations[validations.Add(ca)]; | |
// 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. | |
CellArea 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(dataDir + "output.out.xls"); |
数据验证规则
当数据验证被实现后,可以通过为单元格分配不同的值来检查验证。Cell.GetValidationValue可以用来获取验证结果。以下示例演示了使用不同值的此功能。可以从以下链接下载示例文件进行测试:
sampleDataValidationRules.xlsx
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Instantiate the workbook from sample Excel file | |
Workbook workbook = new Workbook(dataDir+ "sample.xlsx"); | |
// Access the first worksheet | |
Worksheet worksheet = workbook.Worksheets[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.Cells["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.WriteLine("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.WriteLine("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.WriteLine("Is 30 a Valid Value for this Cell: " + cell.GetValidationValue()); |
检查单元格中的验证是否为下拉列表
正如我们所见,可以在单元格中实施许多类型的验证。如果要检查验证是否为下拉列表,可以使用Validation.InCellDropDown属性来测试。以下示例代码演示了如何使用此属性。测试样本文件可从以下链接下载:
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
Workbook book = new Workbook(sourceDir + "sampleValidation.xlsx"); | |
Worksheet sheet = book.Worksheets["Sheet1"]; | |
Cells cells = sheet.Cells; | |
Cell a2 = cells["A2"]; | |
Validation va2 = a2.GetValidation(); | |
if (va2.InCellDropDown) | |
{ | |
Console.WriteLine("A2 is a dropdown"); | |
} | |
else | |
{ | |
Console.WriteLine("A2 is NOT a dropdown"); | |
} | |
Cell b2 = cells["B2"]; | |
Validation vb2 = b2.GetValidation(); | |
if (vb2.InCellDropDown) | |
{ | |
Console.WriteLine("B2 is a dropdown"); | |
} | |
else | |
{ | |
Console.WriteLine("B2 is NOT a dropdown"); | |
} | |
Cell c2 = cells["C2"]; | |
Validation vc2 = c2.GetValidation(); | |
if (vc2.InCellDropDown) | |
{ | |
Console.WriteLine("C2 is a dropdown"); | |
} | |
else | |
{ | |
Console.WriteLine("C2 is NOT a dropdown"); | |
} |
为现有验证添加CellArea
可能存在这样的情况,您可能希望通过使用Validation.AddArea(CellArea cellArea)向现有的Validation添加CellArea。当您使用Validation.AddArea(CellArea cellArea)添加CellArea时,Aspose.Cells将检查所有现有区域,以查看新区域是否已经存在。如果文件具有大量验证,这会导致性能下降。为了克服这个问题,API提供了Validation.AddAreaCellArea cellArea, bool checkIntersection, bool checkEdge)方法。checkIntersection参数指示是否检查给定区域与现有验证区域的交集。将其设置为false将禁用对其他区域的检查。checkEdge参数指示是否检查应用区域。如果新区域成为左上角区域,则内部设置将被重新构建。如果您确定新区域不是左上角区域,可以将此参数设置为false。
以下代码片段演示了如何使用Validation.AddAreaCellArea cellArea, bool checkIntersection, bool checkEdge)方法向现有的Validation添加新的CellArea。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// directories | |
string SourceDir = RunExamples.Get_SourceDirectory(); | |
string outputDir = RunExamples.Get_OutputDirectory(); | |
Workbook workbook = new Workbook(SourceDir + "ValidationsSample.xlsx"); | |
// Access first worksheet. | |
Worksheet worksheet = workbook.Worksheets[0]; | |
// Accessing the Validations collection of the worksheet | |
Validation validation = worksheet.Validations[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文件已附上供参考。