Microsoft Learn Challenge

Excel JavaScript APIを使用してワークシートで作業する – Office アドイン

この記事では、Excel JavaScript APIを使用してワークシートで一般的なタスクを実行するためのコードサンプルを提供しています。ワークシートとワークシートコレクションのオブジェクトがサポートするプロパティやメソッドの完全なリストについては、公式ドキュメントを参照してください。また、ワークシートの取得、アクティブなワークシートの設定、ワークシートの追加や削除、名前変更、ビジュアリゼーションの変更方法、データの検出、オートフィルターやデータ保護の適用など、さまざまな操作方法が説明されています。

Excel JavaScript APIを使用したワークシートの操作方法

この記事では、Excel JavaScript APIを使用してワークシートで一般的なタスクを実行する方法を示すコードサンプルを提供します。WorksheetおよびWorksheetCollectionオブジェクトがサポートするプロパティおよびメソッドの完全なリストについては、Worksheet Object (JavaScript API for Excel)およびWorksheetCollection Object (JavaScript API for Excel)を参照してください。

注意

この記事の情報は、通常のワークシートにのみ適用されます。「グラフ」シートや「マクロ」シートには適用されません。

ワークシートを取得する

以下のコードサンプルでは、ワークシートのコレクションを取得し、各ワークシートのnameプロパティを読み込み、コンソールにメッセージを書き込みます。

await Excel.run(async (context) => {
    let sheets = context.workbook.worksheets;
    sheets.load("items/name");

    await context.sync();

    if (sheets.items.length > 1) {
        console.log(`ワークブックには ${sheets.items.length} のワークシートがあります。`);
    } else {
        console.log(`ワークブックには1つのワークシートがあります。`);
    }

    sheets.items.forEach(function (sheet) {
        console.log(sheet.name);
    });
});

注意

ワークシートの`id`プロパティは、特定のワークブック内でワークシートを一意に識別します。その値は、ワークシートの名前が変更されたり移動されたりしても同じままです。ExcelのMac版でワークブックからワークシートが削除されると、削除されたワークシートの`id`が、後に作成される新しいワークシートに再割り当てされる可能性があります。

アクティブなワークシートを取得する

以下のコードサンプルでは、アクティブなワークシートを取得し、そのnameプロパティを読み込み、コンソールにメッセージを書き込みます。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.load("name");

    await context.sync();
    console.log(`アクティブなワークシートは "${sheet.name}" です。`);
});

アクティブなワークシートを設定する

以下のコードサンプルでは、ワークシートの名前をSampleに設定し、そのnameプロパティを読み込み、コンソールにメッセージを書き込みます。その名前のワークシートが存在しない場合、activate()メソッドはItemNotFoundエラーをスローします。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    sheet.activate();
    sheet.load("name");

    await context.sync();
    console.log(`アクティブなワークシートは "${sheet.name}" です。`);
});

相対位置でワークシートを参照する

以下の例では、相対位置に基づいてワークシートを参照する方法を示します。

最初のワークシートを取得する

以下のコードサンプルでは、ワークブックの最初のワークシートを取得し、そのnameプロパティを読み込み、コンソールにメッセージを書き込みます。

await Excel.run(async (context) => {
    let firstSheet = context.workbook.worksheets.getFirst();
    firstSheet.load("name");

    await context.sync();
    console.log(`最初のワークシートの名前は "${firstSheet.name}" です。`);
});

最後のワークシートを取得する

以下のコードサンプルでは、ワークブックの最後のワークシートを取得し、そのnameプロパティを読み込み、コンソールにメッセージを書き込みます。

await Excel.run(async (context) => {
    let lastSheet = context.workbook.worksheets.getLast();
    lastSheet.load("name");

    await context.sync();
    console.log(`最後のワークシートの名前は "${lastSheet.name}" です。`);
});

次のワークシートを取得する

以下のコードサンプルでは、ワークブック内のアクティブなワークシートに続くワークシートを取得し、そのnameプロパティを読み込み、コンソールにメッセージを書き込みます。アクティブなワークシートの後にワークシートがない場合、getNext()メソッドはItemNotFoundエラーをスローします。

await Excel.run(async (context) => {
    let currentSheet = context.workbook.worksheets.getActiveWorksheet();
    let nextSheet = currentSheet.getNext();
    nextSheet.load("name");

    await context.sync();
    console.log(`アクティブなワークシートに続くワークシートの名前は "${nextSheet.name}" です。`);
});

前のワークシートを取得する

以下のコードサンプルでは、ワークブック内のアクティブなワークシートの前にあるワークシートを取得し、そのnameプロパティを読み込み、コンソールにメッセージを書き込みます。アクティブなワークシートの前にワークシートがない場合、getPrevious()メソッドはItemNotFoundエラーをスローします。

await Excel.run(async (context) => {
    let currentSheet = context.workbook.worksheets.getActiveWorksheet();
    let previousSheet = currentSheet.getPrevious();
    previousSheet.load("name");

    await context.sync();
    console.log(`アクティブなワークシートの前のワークシートの名前は "${previousSheet.name}" です。`);
});

ワークシートを追加する

以下のコードサンプルでは、新しいワークシートをSampleという名前でワークブックに追加し、そのnameおよびpositionプロパティを読み込み、コンソールにメッセージを書き込みます。新しいワークシートは、既存のすべてのワークシートの後に追加されます。

await Excel.run(async (context) => {
    let sheets = context.workbook.worksheets;

    let sheet = sheets.add("Sample");
    sheet.load("name, position");

    await context.sync();
    console.log(`ワークシート "${sheet.name}" を位置 ${sheet.position} に追加しました。`);
});

既存のワークシートをコピーする

Worksheet.copy メソッドを使用すると、既存のワークシートのコピーを作成する新しいワークシートが追加されます。新しいワークシートの名前には、既存のワークシートの名前の末尾に番号が付加されます（例：MySheet (2)）。Worksheet.copy は、2つのオプションのパラメータを取ることができます。

  • positionType: 新しいワークシートをワークブックに追加する位置を指定するWorksheetPositionType列挙体です。
  • relativeTo: positionType がBeforeまたはAfterの場合、新しいシートを追加する基準となるワークシートを指定する必要があります（このパラメータは、「何の前または後に？」に対する回答です）。

以下のコードサンプルでは、現在のワークシートをコピーし、現在のワークシートのすぐ後に新しいシートを挿入します。

await Excel.run(async (context) => {
    let myWorkbook = context.workbook;
    let sampleSheet = myWorkbook.worksheets.getActiveWorksheet();
    let copiedSheet = sampleSheet.copy(Excel.WorksheetPositionType.after, sampleSheet);
    await context.sync();
});

ワークシートを削除する

以下のコードサンプルでは、ワークブックの最後のワークシートを削除し（それが唯一のシートでない限り）、コンソールにメッセージを書き込みます。

await Excel.run(async (context) => {
    let sheets = context.workbook.worksheets;
    sheets.load("items/name");

    await context.sync();
    if (sheets.items.length === 1) {
        console.log("ワークブック内の唯一のワークシートを削除することはできません");
    } else {
        let lastSheet = sheets.items[sheets.items.length - 1];

        console.log(`ワークシート "${lastSheet.name}" を削除します。`);
        lastSheet.delete();

        await context.sync();
    }
});

注意

可視性が「Very Hidden」に設定されているワークシートは、`delete`メソッドで削除できません。ワークシートを削除するには、まず可視性を変更する必要があります。

ワークシートの名前を変更する

以下のコードサンプルでは、アクティブなワークシートの名前をNew Nameに変更します。

await Excel.run(async (context) => {
    let currentSheet = context.workbook.worksheets.getActiveWorksheet();
    currentSheet.name = "New Name";

    await context.sync();
});

ワークシートの位置を移動する

以下のコードサンプルでは、ワークシートをワークブックの最後の位置から最初の位置に移動します。

await Excel.run(async (context) => {
    let sheets = context.workbook.worksheets;
    sheets.load("items");
    await context.sync();

    let lastSheet = sheets.items[sheets.items.length - 1];
    lastSheet.position = 0;
    await context.sync();
});

ワークシートの可視性を設定する

以下に、ワークシートの可視性を設定する方法を示します。

ワークシートを非表示にする

以下のコードサンプルでは、Sampleという名前のワークシートの可視性を非表示に設定し、そのnameプロパティを読み込み、コンソールにメッセージを書き込みます。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    sheet.visibility = Excel.SheetVisibility.hidden;
    sheet.load("name");

    await context.sync();
    console.log(`ワークシート "${sheet.name}" は非表示になりました`);
});

ワークシートの表示を元に戻す

以下のコードサンプルでは、Sampleという名前のワークシートの可視性を表示に設定し、そのnameプロパティを読み込み、コンソールにメッセージを書き込みます。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    sheet.visibility = Excel.SheetVisibility.visible;
    sheet.load("name");

    await context.sync();
    console.log(`ワークシート "${sheet.name}" は表示されています`);
});

ワークシート内の単一のセルを取得する

以下のコードサンプルでは、Sampleという名前のワークシート内の行2、列5に位置するセルを取得し、そのaddressおよびvaluesプロパティを読み込み、コンソールにメッセージを書き込みます。getCell(row: number, column:number)メソッドに渡す値は、0から始まる行番号および列番号です。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let cell = sheet.getCell(1, 4);
    cell.load("address, values");

    await context.sync();
    console.log(`行2、列5のセルの値は "${cell.values[0][0]}" で、セルのアドレスは "${cell.address}" です。`);
});

データの変更を検出する

アドインは、ユーザーがワークシート内のデータを変更したときに反応する必要があります。これらの変更を検出するには、ワークシートのonChangedイベントに対してイベントハンドラーを登録します。onChangedイベントのイベントハンドラーは、イベントが発生したときにWorksheetChangedEventArgsオブジェクトを受け取ります。

WorksheetChangedEventArgsオブジェクトは、変更内容とそのソースに関する情報を提供します。onChangedは、データの形式または値が変更されたときにトリガーされるため、アドインが実際に値が変更されたかどうかを確認するのに便利です。detailsプロパティは、これらの情報をChangedEventDetailとしてカプセル化します。以下のコードサンプルでは、変更されたセルの前後の値とタイプを表示します。

// この関数はWorksheet.onChangedイベントのイベントハンドラーとして使用されます。
function onWorksheetChanged(eventArgs) {
    Excel.run(function (context) {
        let details = eventArgs.details;
        let address = eventArgs.address;

        // セルの前後のタイプと値をコンソールに出力します。
        console.log(`アドレス ${address}: 変更前は ${details.valueBefore}(${details.valueTypeBefore}),`
            + ` 現在は ${details.valueAfter}(${details.valueTypeAfter}) です。`);
        return context.sync();
    });
}

数式の変更を検出する

アドインでは、ワークシート内の数式の変更を追跡できます。これは、ワークシートが外部データベースに接続されている場合に便利です。ワークシート内の数式が変更されると、このシナリオのイベントは外部データベース内の対応する更新をトリガーします。

数式の変更を検出するには、ワークシートのonFormulaChangedイベントに対してイベントハンドラーを登録します。onFormulaChangedイベントのイベントハンドラーは、イベントが発生したときにWorksheetFormulaChangedEventArgsオブジェクトを受け取ります。

重要

`onFormulaChanged`イベントは、数式そのものが変更されたときにトリガーされ、数式の計算結果としてのデータ値ではありません。

以下のコードサンプルでは、onFormulaChangedイベントハンドラーを登録し、WorksheetFormulaChangedEventArgsオブジェクトを使用して変更された数式のformulaDetails配列を取得し、変更された数式の詳細をFormulaChangedEventDetailプロパティを使用して出力します。

注意

このコードサンプルは、1つの数式が変更された場合のみ機能します。

async function run() {
    await Excel.run(async (context) => {
        // ワークシート "Sample" を取得します。
        let sheet = context.workbook.worksheets.getItem("Sample");

        // このワークシートの数式変更イベントハンドラーを登録します。
        sheet.onFormulaChanged.add(formulaChangeHandler);

        await context.sync();
    });
}

async function formulaChangeHandler(event) {
    await Excel.run(async (context) => {
        // 数式変更イベントに関する詳細を取得します。
        // 注意: このメソッドは、同時に1つの数式が変更されることを前提としています。 
        let cellAddress = event.formulaDetails[0].cellAddress;
        let previousFormula = event.formulaDetails[0].previousFormula;
        let source = event.source;

        // 変更イベントの詳細を出力します。
        console.log(
          `セル ${cellAddress} の数式が変更されました。 
          前の数式は: ${previousFormula} です。 
          変更のソースは: ${source} です。`
        );         
    });
}

ソートイベントを処理する

onColumnSortedおよびonRowSortedイベントは、ワークシート内の任意のデータがソートされたときに発火します。これらのイベントは、個々のWorksheetオブジェクトに接続されており、ワークブックのWorkbookCollectionにも接続されています。ソートがプログラム的に行われる場合でも、Excelユーザーインターフェイスを介して手動で行われる場合でも発火します。

注意

`onColumnSorted`は、左から右へのソート操作の結果として列がソートされたときに発火します。`onRowSorted`は、上から下へのソート操作の結果として行がソートされたときに発火します。列のヘッダー上のドロップダウンメニューを使用してテーブルをソートすると、`onRowSorted`イベントが発火します。イベントは、移動されるものに対応し、ソート基準として考慮されるものには対応しません。

onColumnSortedおよびonRowSortedイベントは、イベントに関するより多くの詳細を提供するWorksheetColumnSortedEventArgsまたはWorksheetRowSortedEventArgsをコールバックに提供します。特に、両方のEventArgsには、ソート操作の結果として移動された行または列を表すaddressプロパティがあります。ソートされたコンテンツがあるセルはすべて含まれます。たとえそのセルの値がソートの基準の一部ではなかったとしてもです。

以下の画像は、ソートイベントのためにaddressプロパティが返す範囲を示しています。最初に、ソート前のサンプルデータです。

Excelのソート前のテーブルデータ。

"Q1"に基づいて（"B"の値）、上から下へのソートを実行した場合、次のようにハイライトされた行がWorksheetRowSortedEventArgs.addressによって返されます。

上から下へのソート後のExcelのテーブルデータ。移動した行がハイライトされています。

"Quinces"（"4"の値）に基づいて左から右にソートを実行した場合、次のようにハイライトされた列がWorksheetColumnsSortedEventArgs.addressによって返されます。

左から右へのソート後のExcelのテーブルデータ。移動した列がハイライトされています。

以下のコードサンプルでは、Worksheet.onRowSortedイベントのためのイベントハンドラーを登録する方法を示しています。ハンドラーのコールバックは範囲の塗りつぶしをクリアし、次に移動された行のセルを塗りつぶします。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();

    // このイベントは、ソート操作の結果として行が移動された場合に発火します。
    sheet.onRowSorted.add(async (event) => {
        await Excel.run(async (context) => {
            console.log("行がソートされました: " + event.address);
            let sheet = context.workbook.worksheets.getActiveWorksheet();

            // セクションのフォーマットをクリアし、ソートされた範囲をハイライトします。
            sheet.getRange("A1:E5").format.fill.clear();
            if (event.address !== "") {
                sheet.getRanges(event.address).format.fill.color = "yellow";
            }

            await context.sync();
        });
    });

    await context.sync();
});

一致するテキストを持つすべてのセルを見つける

Worksheetオブジェクトには、ワークシート内で指定された文字列を検索するためのfindAllメソッドがあります。これは、編集可能なRangeAreasオブジェクトのコレクションを返します。

以下のコードサンプルでは、値がCompleteという文字列と等しいすべてのセルを見つけ、それらを緑色に塗りつぶします。findAllは、指定された文字列がワークシートに存在しない場合、ItemNotFoundエラーをスローします。指定された文字列がワークシートに存在するか不明な場合は、findAllOrNullObjectメソッドを使用してそのシナリオを適切に処理できます。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let foundRanges = sheet.findAll("Complete", {
        completeMatch: true, /* セルの値全体を一致させる、テキストの一部ではありません。 */
        matchCase: false /* 大文字と小文字を無視して検索します。 */
    });

    await context.sync();
    foundRanges.format.fill.color = "green";
});

注意

このセクションでは、`Worksheet`オブジェクトのメソッドを使用してセルや範囲を見つける方法について説明しています。範囲の取得に関する詳細は、オブジェクト固有の記事で確認できます。

データをフィルタリングする

AutoFilterは、ワークシート内の範囲にデータフィルタを適用します。これは、Worksheet.autoFilter.applyで作成され、以下のパラメータを持っています。

  • range: フィルタを適用する範囲を指定します。Rangeオブジェクトまたは文字列として指定できます。
  • columnIndex: フィルタ基準が評価されるゼロから始まる列インデックスです。
  • criteria: 列のセルに基づいてどの行をフィルタすべきかを決定するFilterCriteriaオブジェクトです。

最初のコードサンプルでは、ワークシートの使用範囲にフィルタを追加します。このフィルタは、列3の値に基づいて上位25%に入っていないエントリを隠します。

// このメソッドは、アクティブなワークシートにカスタムAutoFilterを追加し
// 使用範囲の列にフィルタを適用します。
await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let farmData = sheet.getUsedRange();

    // このフィルタは、列3の上位25%の値を持つ行のみを表示します。
    sheet.autoFilter.apply(farmData, 3, { criterion1: "25", filterOn: Excel.FilterOn.topPercent });
    await context.sync();
});

次のコードサンプルでは、データが変更されたときにフィルタを再適用するために、reapplyメソッドを使用します。これは、範囲内のデータが変更されたときに行う必要があります。

// このメソッドは、AutoFilterを更新して変更をキャッチします。
await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.autoFilter.reapply();
    await context.sync();
});

以下のコードサンプルでは、clearColumnCriteriaメソッドを使用して、1つの列からだけAutoFilterをクリアし、他の列のフィルタをアクティブに保ちます。

// このメソッドは、1つの列からAutoFilter設定をクリアします。
await Excel.run(async (context) => {
    // アクティブなワークシートを取得します。
    let sheet = context.workbook.worksheets.getActiveWorksheet();

    // 列3からフィルタをクリアします。
    sheet.autoFilter.clearColumnCriteria(3);
    await context.sync();
});

最後のAutoFilterのコードサンプルでは、removeメソッドを使用してワークシートからAutoFilterを削除します。

// このメソッドは、アクティブなワークシートからすべてのAutoFiltersを削除します。
await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.autoFilter.remove();
    await context.sync();
});

AutoFilterは、個々のテーブルにも適用できます。詳しくはExcel JavaScript APIを使用したテーブルの操作を参照してください。

データ保護

アドインは、ワークシート内のデータを編集するユーザーの能力を制御できます。ワークシートのprotectionプロパティは、WorksheetProtectionオブジェクトであり、protect()メソッドを持っています。以下の例では、アクティブなワークシートの完全な保護を切り替える基本的なシナリオを示しています。

await Excel.run(async (context) => {
    let activeSheet = context.workbook.worksheets.getActiveWorksheet();
    activeSheet.load("protection/protected");
    await context.sync();

    if (!activeSheet.protection.protected) {
        activeSheet.protection.protect();
    }
});

protectメソッドには、2つのオプションのパラメータがあります。

  • options: 特定の編集制限を定義するWorksheetProtectionOptionsオブジェクト。
  • password: ユーザーが保護を回避してワークシートを編集するために必要なパスワードを表す文字列。

記事"ワークシートを保護する"では、ワークシートの保護およびExcel UIを介しての変更の方法について説明しています。

ワークシートの保護状態の変更を検出する

ワークシートの保護状態は、アドインまたはExcel UIを介して変更される可能性があります。保護状態の変更を検出するには、ワークシートのonProtectionChangedイベントに対してイベントハンドラーを登録します。onProtectionChangedイベントのイベントハンドラーは、イベントが発生したときにWorksheetProtectionChangedEventArgsオブジェクトを受け取ります。

以下のコードサンプルでは、onProtectionChangedイベントハンドラーを登録する方法を示し、WorksheetProtectionChangedEventArgsオブジェクトを使用してisProtected、worksheetId、およびsourceプロパティを取得します。

// この関数は、ワークシートのonProtectionChangedイベントのイベントハンドラーを登録します。
async function run() {
    await Excel.run(async (context) => {
        // ワークシート "Sample" を取得します。
        let sheet = context.workbook.worksheets.getItem("Sample");

        // onProtectionChangedイベントハンドラーを登録します。
        sheet.onProtectionChanged.add(checkProtection);
        await context.sync();
    });
}

// この関数は、ワークシートの保護状態と変更されたワークシートに関する情報を返すイベントハンドラーです。
async function checkProtection(event) {
    await Excel.run(async (context) => {
        // イベントの保護状態、ワークシートID、ソースプロパティを取得します。
        let protectionStatus = event.isProtected;
        let worksheetId = event.worksheetId;
        let source = event.source;

        // イベントプロパティをコンソールに出力します。
        console.log("保護状態が変更されました。保護状態は: " + protectionStatus);
        console.log("    変更されたワークシートのID: " + worksheetId);
        console.log("    変更イベントのソース: " + source);    
    });
}

参考文献

この記事では、さまざまなワークシート関連のタスクを実行するためのコードサンプルを提供しました。さらに詳しい情報については、Excel JavaScript APIのドキュメントを参照してください。

————-

Work with worksheets using the Excel JavaScript API – Office Add-ins

Source link

This article provides code samples for managing common tasks with worksheets using the Excel JavaScript API. It covers various operations applicable to regular worksheets, as opposed to chart or macro sheets. Key functionalities include:

  1. Getting Worksheets: Retrieve all worksheets and their names, including methods for accessing the first, last, next, and previous worksheets based on their positional relationships.

  2. Active Worksheet Operations: Examples show how to retrieve the active worksheet, set it, and manipulate its properties.

  3. Managing Worksheets:

    • Add a new worksheet.
    • Copy an existing worksheet.
    • Delete a worksheet, with a note about "Very Hidden" worksheets.
    • Rename a worksheet and move it within the workbook.

  4. Visibility Control: Code samples demonstrate how to hide and unhide worksheets.

  5. Cell Interaction: Retrieve specific cells and their values, detect changes to cell data or formulas, and handle data sorting events.

  6. Filtering Data: Apply, refresh, and remove filters on worksheet data using AutoFilters linked to specified ranges and columns.

  7. Data Protection: Control the ability of users to edit worksheet data, including toggling protection and detecting changes to a worksheet’s protection state.

  8. Event Handling: Register event handlers for various changes, such as data modifications, formula updates, sorting actions, and protection status changes.

Overall, this article serves as a comprehensive guide for developers looking to integrate or enhance interaction with Excel worksheets through programming. It emphasizes the JavaScript API’s capabilities, providing explicit code samples for common worksheet tasks.

マイクロソフトがフォレスター・ウェーブ™：ビジネスインテリジェンスプラットフォーム、2025年第2四半期でリーダーに選出 | Microsoft Power BI ブログ 前の記事 「新事業進出補助金の第1回申込み開始のお知らせ」 次の記事

関連記事