Convalida dei dati

Microsoft Excel offre alcune buone funzioni per filtrare automaticamente o validare i dati del foglio di lavoro. Aspose.Cells supporta pienamente le funzioni di validazione dei dati e AutoFilter di Microsoft Excel. Questo articolo spiega come usare queste funzioni in Excel e come programmarle utilizzando Aspose.Cells for Node.js via C++.

Tipi ed esecuzione della convalida dei dati

La convalida dei dati è la capacità di impostare regole relative ai dati inseriti in un foglio di lavoro. Ad esempio, utilizzare la convalida per garantire che una colonna denominata DATA contenga solo date, o che un’altra colonna contenga solo numeri. È possibile anche garantire che una colonna denominata DATA contenga solo date entro un determinato intervallo. Con la convalida dei dati è possibile controllare cosa viene inserito nelle celle del foglio di lavoro.

Microsoft Excel supporta diversi tipi di convalida dei dati. Ogni tipo viene utilizzato per controllare quale tipo di dati viene inserito in una cella o in un intervallo di celle. Di seguito, frammenti di codice illustrano come convalidare che:

• I numeri sono interi, cioè non hanno una parte decimale.
• I numeri decimali seguono la struttura corretta. L’esempio di codice definisce che un intervallo di celle dovrebbe avere due decimali.
• I valori sono limitati a un elenco di valori. La convalida dell’elenco definisce un elenco separato di valori che possono essere applicati a una cella o a un intervallo di celle.
• Le date rientrano in un intervallo specifico.
• Un’ora è all’interno di un intervallo specifico.
• Un testo è di una determinata lunghezza di caratteri.

Convalida dei dati con Microsoft Excel

Per creare convalide utilizzando Microsoft Excel:

1. In un foglio di lavoro, selezionare le celle a cui si desidera applicare la convalida.
2. Dal menu Dati, seleziona Convalida. Verrà visualizzata la finestra di dialogo di convalida.
3. Fai clic sulla scheda Impostazioni e inserisci le impostazioni.

Validazione Dati con Aspose.Cells for Node.js via C++

La convalida dei dati è una funzionalità potente per convalidare le informazioni inserite nei fogli di lavoro. Con la convalida dei dati, gli sviluppatori possono fornire agli utenti un elenco di scelte, limitare le voci di dati a un tipo o dimensione specifici, ecc.
In Aspose.Cells for Node.js via C++, ogni classe Worksheet ha un metodo getValidations() che rappresenta una collezione di oggetti Validation. Per configurare la validazione, impostare alcune proprietà della classe Validation come segue:

• Tipo – rappresenta il tipo di validazione, che può essere specificato usando uno dei valori predefiniti nell’enumerazione ValidationType.
• Operatore – rappresenta l’operatore da usare nella validazione, che può essere specificato usando uno dei valori predefiniti nell’enumerazione OperatorType.
• Formula1: rappresenta il valore o l’espressione associata alla prima parte della convalida dei dati.
• Formula2: rappresenta il valore o l’espressione associata alla seconda parte della convalida dei dati.

Quando le proprietà dell’oggetto Validation sono state configurate, gli sviluppatori possono usare la struttura CellArea per memorizzare informazioni sull’intervallo di celle che verrà convalidato usando la validazione creata.

Tipi di Convalida dei Dati

L’enumerazione ValidationType ha i seguenti membri:

Nome Membr*	Descrizione
AnyValue	Denota un valore di qualsiasi tipo.
WholeNumber	Denota il tipo di convalida per i numeri interi.
Decimal	Indica il tipo di convalida per i numeri decimali.
List	Indica il tipo di convalida per elenchi a discesa.
Date	Indica il tipo di convalida per le date.
Time	Indica il tipo di convalida per l’ora.
TextLength	Indica il tipo di convalida per la lunghezza del testo.
Custom	Indica il tipo di convalida personalizzato.

Convalida dei dati del numero intero

Con questo tipo di convalida, gli utenti possono inserire solo numeri interi all’interno di un intervallo specificato nelle celle convalidates. Gli esempi di codice che seguono mostrano come implementare il tipo di convalida NumberIntero. L’esempio crea la stessa convalida dei dati usando Aspose.Cells for Node.js via C++ che abbiamo creato in Microsoft Excel sopra.

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

Convalida dei dati della lista

Questo tipo di convalida consente all’utente di inserire valori da un elenco a discesa. Fornisce un elenco: una serie di righe che contengono dati. Nell’esempio, viene aggiunta un secondo foglio di lavoro per contenere la fonte dell’elenco. Gli utenti possono selezionare solo valori dall’elenco. L’area di convalida è l’intervallo di celle A1:A5 nel primo foglio di lavoro.

È importante qui impostare la proprietà Validation.setInCellDropDown(boolean) su 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"));

Convalida dei dati della data

Con questo tipo di convalida, gli utenti inseriscono valori di data entro un intervallo specificato, o che soddisfano determinati criteri, nelle celle convalidate. Nell’esempio, all’utente è vietato inserire date comprese tra il 1970 e il 1999. Qui, l’area di convalida è la cella 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"));

Convalida dei dati dell’ora

Con questo tipo di convalida, gli utenti possono inserire orari entro un intervallo specificato, o soddisfare alcuni criteri, nelle celle convalidate. Nell’esempio, all’utente è vietato inserire orari tra le 09:00 e le 11:30 del mattino. Qui, l’area di convalida è la cella 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"));

Convalida della lunghezza del testo

Con questo tipo di convalida, gli utenti possono inserire valori di testo di una lunghezza specificata nelle celle convalidate. Nell’esempio, all’utente è vietato inserire valori di stringa con più di 5 caratteri. L’area di convalida è la cella 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"));

Regole di convalida dei dati

Quando le validazioni dei dati vengono implementate, è possibile verificarle assegnando valori diversi nelle celle. Cell.getValidationValue() può essere usato per ottenere il risultato della validazione. Il seguente esempio dimostra questa funzione con valori diversi. Il file di esempio può essere scaricato dal link sottostante per testare:

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

Verifica se la convalida nella cella è a discesa

Come abbiamo visto, ci sono molti tipi di validazioni che possono essere implementate all’interno di una cella. Se vuoi verificare se la validazione è un menu a tendina o meno, puoi usare il metodo Validation.getInCellDropDown() per testare questa proprietà. Un esempio di codice che dimostra l’uso di questa proprietà può essere scaricato dal link:

sampleValidation.xlsx

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

Aggiungi CellArea alla convalida esistente

Potrebbero esserci casi in cui vuoi aggiungere CellArea a un Validation esistente. Quando aggiungi CellArea usando Validation.addArea(CellArea), Aspose.Cells verifica tutte le aree esistenti per vedere se l’area nuova è già presente. Se il file ha molte validazioni, questo comporta un impatto sulle prestazioni. Per ovviare a questo, l’API fornisce il metodo Validation.addArea(CellArea, boolean, boolean). Il parametro checkIntersection indica se verificare l’intersezione di un’area data con le aree di validazione esistenti. Impostarlo su false disabiliterà il controllo di altre aree. Il parametro checkEdge indica se verificare le aree applicate. Se la nuova area diventa l’area in alto a sinistra, le impostazioni interne vengono ricostruite. Se sei sicuro che la nuova area non sia quella in alto a sinistra, puoi impostare questo parametro su false.

Il seguente esempio di codice dimostra l’uso del metodo Validation.addArea(CellArea, boolean, boolean) per aggiungere una nuova CellArea a un Validation esistente.

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

I file excel sorgente e di output sono allegati a scopo informativo.

File di origine

File di output

Argomenti avanzati

• Ottieni la Convalida Cellulare nei File ODS
• Ottieni la Convalida Applicata su una Cella
• Verifica che il Valore della Cella Soddisfi le Regole di Convalida dei Dati