データ検証

Microsoft Excelはワークシートの自動フィルタや検証の良い機能を提供しています。Aspose.CellsはMicrosoft Excelのデータ検証と自動フィルタの機能を完全にサポートしています。この記事では、Microsoft Excelでのこれらの機能の使用方法と、それらをAspose.Cells for Node.js via C++を使用してコーディングする方法を説明します。

データ検証の種類と実行

データ検証は、ワークシートに入力されたデータに関するルールを設定する機能です。たとえば、「日付」と記載された列には日付のみが含まれるようにし、他の列には数字のみが含まれるようにすることができます。あるいは、「日付」と記載された列には特定の範囲内の日付のみが含まれるようにすることもできます。データ検証を使用すると、ワークシートのセルに入力される内容を制御することができます。

Microsoft Excel はさまざまな種類のデータ検証をサポートしています。それぞれの種類は、セルまたはセル範囲に入力されるデータの種類を制御するために使用されます。以下は、それぞれの種類を確認するためのコードスニペットの例です。

• 数値が整数で、つまり小数部を持たないこと。
• 小数点以下の桁に構造が正しいこと。コード例では、セルの範囲に2つの小数点以下があることを定義しています。
• 一覧からの値に制限されていること。一覧の検証では、セルやセル範囲に適用する別々の値の一覧が定義されます。
• 特定の範囲内の日付であること。
• 特定の範囲内の時間であること。
• 指定された文字長内のテキストであること。

Microsoft Excel でデータ検証

Microsoft Excel を使用して検証を作成するには:

1. ワークシートで、検証を適用したいセルを選択します。
2. データ メニューから 検証 を選択します。 検証のダイアログが表示されます。
3. 設定 タブをクリックし、設定を入力します。

Aspose.Cells for Node.js via C++を用いたデータ検証

データ検証は、ワークシートに入力された情報を検証するための強力な機能です。データ検証を使用すると、開発者はユーザーに選択肢のリストを提供したり、データの入力を特定のタイプやサイズに制限したりすることができます。
Aspose.Cells for Node.js via C++の各Worksheetクラスには、getValidations()メソッドがあり、これはValidationオブジェクトのコレクションを表します。検証を設定するには、Validationクラスのいくつかのプロパティを次のように設定します。

• 種類 – 検証タイプを表し、ValidationType列挙体の事前定義された値の1つを使用して指定できます。
• オペレーター – 検証で使用されるオペレーターを表し、OperatorType列挙体の事前定義された値のいずれかを使用して指定できます。
• Formula1：データ検証の最初の部分に関連付けられた値または式を表します。
• Formula2：データ検証の2番目の部分に関連付けられた値または式を表します。

Validationオブジェクトのプロパティが設定されたら、開発者はCellArea構造体を使用して、作成された検証を使用して検証されるセル範囲に関する情報を格納できます。

データ検証の種類

ValidationType列挙体には次のメンバーがあります：

メンバー名	説明
AnyValue	任意の型の値を示します。
WholeNumber	整数の検証タイプを示します。
Decimal	10進数の検証タイプを示します。
List	ドロップダウンリストの検証タイプを示します。
Date	日付の検証タイプを示します。
Time	時刻の検証タイプを示します。
TextLength	テキストの長さの検証タイプを示します。
Custom	カスタム検証タイプを示します。

整数データの検証

このタイプの検証では、ユーザーは指定された範囲内の整数のみを検証されたセルに入力できます。以下のコード例では、WholeNumber検証タイプの実装方法を示しています。例は、上記のMicrosoft Excelを使用して作成したのと同じデータ検証をAspose.Cells for Node.js via C++で作成します。

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

リストデータの検証

この種類の検証では、ユーザーはドロップダウンリストから値を入力できます。リストにはデータを含む一連の行があります。この例では、リストのソースを保持するために2番目のワークシートが追加されます。ユーザーはリストからのみ値を選択できます。検証エリアは最初のワークシートのセル範囲 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()メソッドを使用してこれをテストできます。以下のサンプルコードはこのプロパティの使用例を示しています。テスト用のサンプルファイルは以下のリンクからダウンロードできます：

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

既存の検証に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"));

ソースエクセルファイルと出力エクセルファイルが添付されています。

ソースファイル

出力ファイル

高度なトピック

• ODS ファイルでのセル検証を取得
• セルに適用された検証を取得する
• セルの値がデータ検証ルールを満たすかどうかを確認する