Datavalidering

Microsoft Excel tillhandahåller några bra funktioner för att autofiltrera eller validera arbetsbladsdata.

Datavalidering är förmågan att ställa regler för data som matas in på ett arbetsblad. Använd till exempel validering för att säkerställa att en kolumnmärkt DATUM endast innehåller datum eller att en annan kolumn endast innehåller nummer. Du kan till och med se till att en kolumnmärkt DATUM endast innehåller datum inom ett visst intervall. Med datavalidering kan du kontrollera vad som matas in i celler på arbetsbladet. Aspose.Cells stöder fullt ut Microsoft Excels datavalidering och autofiltreringsfunktioner. Denna artikel förklarar hur man använder funktionerna i Microsoft Excel och hur man kodar dem med användning av Aspose.Cells.

Datavalideringstyper och utförande

Microsoft Excel stöder ett antal olika typer av datavalidering. Varje typ används för att styra vilken typ av data som skrivs in i en cell eller cellområde. Nedan illustrerar kodsnuttar hur man validerar att:

• Siffror är hela, det vill säga att de inte har en decimaldel.
• Decimaltal följer rätt struktur. Kodexemplet definierar att en rad celler bör ha två decimalplatser.
• Värden är begränsade till en lista över värden. Listvalidering definierar en separat lista över värden som kan tillämpas på en cell eller cellintervall.
• Datum faller inom ett specifikt intervall.
• Tiden är inom ett specifikt intervall.
• En text är inom en given teckenlängd.

Datavalidering med Microsoft Excel

För att skapa valideringar med Microsoft Excel:

1. I ett kalkylblad, välj de celler till vilka du vill applicera validering.

2. Från menyn Data väljer du Validering. Valideringsdialogrutan visas.

3. Klicka på fliken Inställningar och ange inställningar enligt nedan. 

   Datavalideringsinställningar

Datavalidering med Aspose.Cells

Datavalidering är en kraftfull funktion för att validera information som matas in i kalkylblad. Med datavalidering kan utvecklare tillhandahålla användare med en lista över val, begränsa datamatare till en specifik typ eller storlek, osv. I Aspose.Cells har varje Worksheet-klass en Validations-objekt som representerar en samling Validation-objekt. För att ställa in validering, ange några av egenskaperna i Validation-klassen:

• Typ: representerar valideringstypen, som kan anges genom att använda något av de fördefinierade värdena i ValidationType-uppräkningen.
• Operator: representerar operatören som ska användas i valideringen, vilket kan anges genom att använda något av de fördefinierade värdena i OperatorType-uppräkningen.
• Formula1: representerar värdet eller uttrycket förknippat med den första delen av datavalideringen.
• Formula2: representerar värdet eller uttrycket förknippat med den andra delen av datavalideringen.

När egenskaperna för objektet Validation har konfigurerats kan utvecklare använda strukturen CellArea för att lagra information om cellområdet som kommer att valideras med den skapade valideringen.

Typer av Datavalidering

Datavalidering gör det möjligt att bygga affärsregler in i varje cell så att felaktiga inmatningar resulterar i felmeddelanden. Affärsregler är de policys och förfaranden som styr hur ett företag fungerar. Aspose.Cells stöder alla viktiga typer av datavalidering.

Uppräkningen ValidationType har följande medlemmar:

Medlemsnamn	Beskrivning
ANY_VALUE	Anger ett värde av vilken typ som helst.
WHOLE_NUMBER	Anger valideringstyp för heltal.
DECIMAL	Anger valideringstyp för decimaltal.
LIST	Anger valideringstyp för nedrullningslist.
DATE	Anger valideringstyp för datum.
TIME	Anger valideringstyp för tid.
TEXT_LENGTH	Anger valideringstyp för längden på texten.
CUSTOM	Anger anpassad valideringstyp.

Programexempel: Heltalsdatavalidering

Med den här typen av validering kan användare endast ange heltal inom ett angivet intervall i de validerade cellerna. Kodexemplen nedan visar hur man implementerar valideringstypen WHOLE_NUMBER med Aspose.Cells. Exemplet skapar samma datavalidering med Aspose.Cells som vi skapade med Microsoft Excel ovan.

// 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");

Programexempel: Decimaltaldatavalidering

Med den här typen av validering kan användaren ange decimaltal i de validerade cellerna. I exemplet är användaren begränsad att ange endast decimalvärde och valideringsområdet är 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");

Programexempel: Lista för datavalidering

Denna typ av validering gör att användaren kan ange värden från en nedrullningslista. Det tillhandahåller en lista: en serie rader som innehåller data. Användare kan bara välja värden från listan. Valideringsområdet är cellområdet A1:A5 i den första arbetsboken.

Det är viktigt här att du ställer in egenskapen Validation.setInCellDropDown till 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");

Programexempel: Datumdatavalidering

Med denna typ av validering anger användare datumvärden inom ett angivet intervall eller enligt specifika kriterier i de validerade cellerna. I exemplet är användaren begränsad att ange datum mellan 1970 och 1999. Här är valideringsområdet cell 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");

Programexempel: Tidsdatavalidering

Med denna typ av validering kan användare ange tider inom ett angivet intervall eller enligt vissa kriterier i de validerade cellerna. I exemplet är användaren begränsad att ange tider mellan 09:00 och 11:30 förmiddag. Här är valideringsområdet cell 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");

Programexempel: Textlängd för datavalidering

Med denna typ av validering kan användare ange textvärden av en angiven längd i de validerade cellerna. I exemplet är användaren begränsad att ange strängvärden med högst 5 tecken. Valideringsområdet är cell 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");

Data valideringsregler

När datavalideringar är implementerade kan valideringen kontrolleras genom att tilldela olika värden i cellerna. Cell.GetValidationValue() kan användas för att hämta valideringsresultatet. Följande exempel demonstrerar denna funktion med olika värden. Testfilen kan laddas ner från följande länk för testning:

SampleDataValidationRules.xlsx

Exempelkod

// 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());

Kontrollera om validering i en cell är en nedrullningslista

Som vi har sett finns det många typer av valideringar som kan implementeras inom en cell. Om du vill kontrollera om valideringen är en nedrullningslista eller inte kan egenskapen Validation.InCellDropDown användas för att testa detta. Följande kodexempel demonstrerar användningen av denna egenskap. Testfilen kan laddas ner från följande länk för testning:

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");
}

Lägg till CellArea till befintlig validering

Det kan finnas fall där du vill lägga till CellArea till befintlig Validation. När du lägger till CellArea med hjälp av Validation.AddArea(CellArea cellArea), kontrollerar Aspose.Cells alla befintliga områden för att se om det nya området redan finns. Om filen har ett stort antal valideringar påverkar detta prestandan. För att komma över detta tillhandahåller API:et metoden Validation.AddAreaCellArea cellArea, bool checkIntersection, bool checkEdge). Parametern checkIntersection anger områdets snitt med befintliga valideringsområden. Om du ställer in den till false inaktiveras kontrollen av andra områden. Parametern checkEdge anger om kanten ska kontrolleras. Om det nya området blir det övre vänstra området byggs interna inställningar om igen. Om du är säker på att det nya området inte är det övre vänstra området kan du ställa in den här parametern som false.

Följande kodsnutt demonstrerar användningen av metoden Validation.AddAreaCellArea cellArea, bool checkIntersection, bool checkEdge) för att lägga till ny CellArea till befintlig Validation.

// 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");

Käll- och utdataexcelfilerna är bilagda som referens.

Källfil

Output-fil

Fortsatta ämnen

• Hämta cellvalidering i ODS-filer
• Få validering som tillämpas på en cell
• Verifiera att cellvärdet uppfyller datavalideringsreglerna