Excel JavaScript API を使用して表を操作するWork with tables using the Excel JavaScript API

この記事では、Excel JavaScript API を使用して、表に関する一般的なタスクを実行する方法を示すサンプル コードを提供します。This article provides code samples that show how to perform common tasks with tables using the Excel JavaScript API. Table および TableCollection オブジェクトをサポートするプロパティとメソッドの完全なリストについては、「Table オブジェクト (JavaScript API for Excel)」および「TableCollection オブジェクト (JavaScript API for Excel)」を参照してください。For the complete list of properties and methods that the Table and TableCollection objects support, see Table Object (JavaScript API for Excel) and TableCollection Object (JavaScript API for Excel).

表を作成するCreate a table

次のコード サンプルでは、Sample というワークシートに表を作成します。The following code sample creates a table in the worksheet named Sample. 表にはヘッダーがあり、4 つの列と 7 つのデータ行が含まれています。The table has headers and contains four columns and seven rows of data. コードが実行されている Excel ホスト アプリケーションが要件セット ExcelApi 1.2 をサポートしている場合、列の幅と行の高さは表内の現在のデータに最適になるよう設定されます。If the Excel host application where the code is running supports requirement set ExcelApi 1.2, the width of the columns and height of the rows are set to best fit the current data in the table.

注意

表名を指定するには、次の例に示すように、最初に表を作成し、その name プロパティを設定します。To specify a name for a table, you must first create the table and then set its name property, as shown in the example below.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var expensesTable = sheet.tables.add("A1:D1", true /*hasHeaders*/);
    expensesTable.name = "ExpensesTable";

    expensesTable.getHeaderRowRange().values = [["Date", "Merchant", "Category", "Amount"]];

    expensesTable.rows.add(null /*add rows to the end of the table*/, [
        ["1/1/2017", "The Phone Company", "Communications", "$120"],
        ["1/2/2017", "Northwind Electric Cars", "Transportation", "$142"],
        ["1/5/2017", "Best For You Organics Company", "Groceries", "$27"],
        ["1/10/2017", "Coho Vineyard", "Restaurant", "$33"],
        ["1/11/2017", "Bellows College", "Education", "$350"],
        ["1/15/2017", "Trey Research", "Other", "$135"],
        ["1/15/2017", "Best For You Organics Company", "Groceries", "$97"]
    ]);

    if (Office.context.requirements.isSetSupported("ExcelApi", 1.2)) {
        sheet.getUsedRange().format.autofitColumns();
        sheet.getUsedRange().format.autofitRows();
    }

    sheet.activate();

    return context.sync();
}).catch(errorHandlerFunction);

新しい表New table

Excel の新しい表

表に行を追加するAdd rows to a table

次のコード サンプルでは、Sample ワークシート内の ExpensesTable という表に 7 つの新しい行を追加します。The following code sample adds seven new rows to the table named ExpensesTable within the worksheet named Sample. 新しい行は表の末尾に追加されます。The new rows are added to the end of the table. コードが実行されている Excel ホスト アプリケーションが要件セット ExcelApi 1.2 をサポートしている場合、列の幅と行の高さは表内の現在のデータに最適になるよう設定されます。If the Excel host application where the code is running supports requirement set ExcelApi 1.2, the width of the columns and height of the rows are set to best fit the current data in the table.

注意

TableRow オブジェクトの index プロパティは、表の行コレクション内の行のインデックス番号を示しています。The index property of a TableRow object indicates the index number of the row within the rows collection of the table. TableRow オブジェクトには、行を一意に識別できる id プロパティは含まれていません。A TableRow object does not contain an id property that can be used as a unique key to identify the row.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");       
    var expensesTable = sheet.tables.getItem("ExpensesTable");

    expensesTable.rows.add(null /*add rows to the end of the table*/, [
        ["1/16/2017", "THE PHONE COMPANY", "Communications", "$120"],
        ["1/20/2017", "NORTHWIND ELECTRIC CARS", "Transportation", "$142"],
        ["1/20/2017", "BEST FOR YOU ORGANICS COMPANY", "Groceries", "$27"],
        ["1/21/2017", "COHO VINEYARD", "Restaurant", "$33"],
        ["1/25/2017", "BELLOWS COLLEGE", "Education", "$350"],
        ["1/28/2017", "TREY RESEARCH", "Other", "$135"],
        ["1/31/2017", "BEST FOR YOU ORGANICS COMPANY", "Groceries", "$97"]
    ]);

    if (Office.context.requirements.isSetSupported("ExcelApi", 1.2)) {
        sheet.getUsedRange().format.autofitColumns();
        sheet.getUsedRange().format.autofitRows();
    }

    return context.sync();
}).catch(errorHandlerFunction);

新しい行を含む表Table with new rows

Excel の新しい行を含む表

表に列を追加するAdd a column to a table

以下の例では、表に列を追加する方法を示します。These examples show how to add a column to a table. 最初の例では、新しい列に静的な値を作成し、2 番目の例では新しい列に数式を作成します。The first example populates the new column with static values; the second example populates the new column with formulas.

注意

TableColumn オブジェクトの index プロパティは、表の列コレクション内の列のインデックス番号を示しています。The index property of a TableColumn object indicates the index number of the column within the columns collection of the table. TableColumn オブジェクトの id プロパティには、列を識別する一意のキーが含まれています。The id property of a TableColumn object contains a unique key that identifies the column.

静的な値を含む列を追加するAdd a column that contains static values

次のコード サンプルでは、Sample ワークシート内の ExpensesTable という表に新しい列を追加します。The following code sample adds a new column to the table named ExpensesTable within the worksheet named Sample. 新しい列は、表内の既存の列すべての後に追加され、ヘッダー (「曜日」) を含み、列内のセルにデータが作成されます。The new column is added after all existing columns in the table and contains a header ("Day of the Week") as well as data to populate the cells in the column. コードが実行されている Excel ホスト アプリケーションが要件セット ExcelApi 1.2 をサポートしている場合、列の幅と行の高さは表内の現在のデータに最適になるよう設定されます。If the Excel host application where the code is running supports requirement set ExcelApi 1.2, the width of the columns and height of the rows are set to best fit the current data in the table.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");       
    var expensesTable = sheet.tables.getItem("ExpensesTable");

    expensesTable.columns.add(null /*add columns to the end of the table*/, [
        ["Day of the Week"],
        ["Saturday"],
        ["Friday"],
        ["Monday"],
        ["Thursday"],
        ["Sunday"],
        ["Saturday"],
        ["Monday"]
    ]);

    if (Office.context.requirements.isSetSupported("ExcelApi", 1.2)) {
        sheet.getUsedRange().format.autofitColumns();
        sheet.getUsedRange().format.autofitRows();
    }

    return context.sync();
}).catch(errorHandlerFunction);

新しい列を含む表Table with new column

Excel の新しい列を含む表

数式を含む列を追加するAdd a column that contains formulas

次のコード サンプルでは、Sample ワークシート内の ExpensesTable という表に新しい列を追加します。The following code sample adds a new column to the table named ExpensesTable within the worksheet named Sample. 新しい列は表の末尾に追加され、ヘッダー (「曜日」) を含み、数式を使用して列内のそれぞれのデータ セルを作成します。The new column is added to the end of the table, contains a header ("Type of the Day"), and uses a formula to populate each data cell in the column. コードが実行されている Excel ホスト アプリケーションが要件セット ExcelApi 1.2 をサポートしている場合、列の幅と行の高さは表内の現在のデータに最適になるよう設定されます。If the Excel host application where the code is running supports requirement set ExcelApi 1.2, the width of the columns and height of the rows are set to best fit the current data in the table.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var expensesTable = sheet.tables.getItem("ExpensesTable");

    expensesTable.columns.add(null /*add columns to the end of the table*/, [
        ["Type of the Day"],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")'],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")'],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")'],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")'],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")'],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")'],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")']
    ]);

    if (Office.context.requirements.isSetSupported("ExcelApi", 1.2)) {
        sheet.getUsedRange().format.autofitColumns();
        sheet.getUsedRange().format.autofitRows();
    }

    return context.sync();
}).catch(errorHandlerFunction);

新しい集計列を含む表Table with new calculated column

Excel の新しい集計列を含む表

列名を更新するUpdate column name

次のコード サンプルでは、表の最初の列の名前を Purchase date に更新します。コードが実行されている Excel ホスト アプリケーションが 要件セット ExcelApi 1.2 をサポートしている場合、列の幅と行の高さは表内の現在のデータに最適になるよう設定されます。The following code sample updates the name of the first column in the table to Purchase date. If the Excel host application where the code is running supports requirement set ExcelApi 1.2, the width of the columns and height of the rows are set to best fit the current data in the table.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");

    var expensesTable = sheet.tables.getItem("ExpensesTable");
    expensesTable.columns.load("items");

    return context.sync()
        .then(function () {
            expensesTable.columns.items[0].name = "Purchase date";

            if (Office.context.requirements.isSetSupported("ExcelApi", 1.2)) {
                sheet.getUsedRange().format.autofitColumns();
                sheet.getUsedRange().format.autofitRows();
            }

            return context.sync();
        });
}).catch(errorHandlerFunction);

新しい列名を含む表Table with new column name

Excel の新しい列名を含む表

表からデータを取得するGet data from a table

次のコード サンプルでは、Sample ワークシートから ExpensesTable という表のデータを読み取り、そのデータを同じワークシートの表の下に出力します。The following code sample reads data from a table named ExpensesTable in the worksheet named Sample and then outputs that data below the table in the same worksheet.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var expensesTable = sheet.tables.getItem("ExpensesTable");

    // Get data from the header row
    var headerRange = expensesTable.getHeaderRowRange().load("values");

    // Get data from the table
    var bodyRange = expensesTable.getDataBodyRange().load("values");

    // Get data from a single column
    var columnRange = expensesTable.columns.getItem("Merchant").getDataBodyRange().load("values");

    // Get data from a single row
    var rowRange = expensesTable.rows.getItemAt(1).load("values");

    // Sync to populate proxy objects with data from Excel
    return context.sync()
        .then(function () {
            var headerValues = headerRange.values;
            var bodyValues = bodyRange.values;
            var merchantColumnValues = columnRange.values;
            var secondRowValues = rowRange.values;

            // Write data from table back to the sheet
            sheet.getRange("A11:A11").values = [["Results"]];
            sheet.getRange("A13:D13").values = headerValues;
            sheet.getRange("A14:D20").values = bodyValues;
            sheet.getRange("B23:B29").values = merchantColumnValues;
            sheet.getRange("A32:D32").values = secondRowValues;

            // Sync to update the sheet in Excel
            return context.sync();
        });
}).catch(errorHandlerFunction);

表とデータの出力Table and data output

Excel の表データ

表内でデータを並べ替えるSort data in a table

次のコード サンプルでは、表の 4 番目の列の値に従って降順で表データを並べ替えます。The following code sample sorts table data in descending order according to the values in the fourth column of the table.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var expensesTable = sheet.tables.getItem("ExpensesTable");

    // Queue a command to sort data by the fourth column of the table (descending)
    var sortRange = expensesTable.getDataBodyRange();
    sortRange.sort.apply([
        {
            key: 3,
            ascending: false,
        },
    ]);

    // Sync to run the queued command in Excel
    return context.sync();
}).catch(errorHandlerFunction);

金額 (降順) で並べ替えた表データTable data sorted by Amount (descending)

Excel の表データ

表にフィルターを適用するApply filters to a table

次のコード サンプルでは、表内の Amount 列と Category 列にフィルターを適用しています。The following code sample applies filters to the Amount column and the Category column within a table. フィルター処理の結果、Category が指定した値であり、Amount が表示されている行の平均値未満の行のみが表示されます。As a result of the filters, only rows where Category is one of the specified values and Amount is below the average value for all rows is shown.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var expensesTable = sheet.tables.getItem("ExpensesTable");

    // Queue a command to apply a filter on the Category column
    filter = expensesTable.columns.getItem("Category").filter;
    filter.apply({
        filterOn: Excel.FilterOn.values,
        values: ["Restaurant", "Groceries"]
    });

    // Queue a command to apply a filter on the Amount column
    var filter = expensesTable.columns.getItem("Amount").filter;
    filter.apply({
        filterOn: Excel.FilterOn.dynamic,
        dynamicCriteria: Excel.DynamicFilterCriteria.belowAverage
    });

    // Sync to run the queued commands in Excel
    return context.sync();
}).catch(errorHandlerFunction);

Category と Amount にフィルターを適用した表データTable data with filters applied for Category and Amount

Excel でフィルター処理された表データ

表フィルターのクリアClear table filters

次のコード サンプルでは、表に現在適用されているフィルターをクリアします。The following code sample clears any filters currently applied on the table.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var expensesTable = sheet.tables.getItem("ExpensesTable");

    expensesTable.clearFilters();

    return context.sync();
}).catch(errorHandlerFunction);

フィルターが適用されていない表データTable data with no filters applied

Excel のフィルター処理されていない表データ

フィルター処理された表から、表示されている範囲を取得するGet the visible range from a filtered table

次のコード サンプルでは、指定した表内で現在表示されているセルのデータのみを含む範囲を取得し、その範囲の値をコンソールに書き込みます。The following code sample gets a range that contains data only for cells that are currently visible within the specified table, and then writes the values of that range to the console. 次に示すとおり、getVisibleView() メソッドを使用して、列フィルターが適用されているときに表に表示されるコンテンツを取得します。You can use the getVisibleView() method as shown below to get the visible contents of a table whenever column filters have been applied.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var expensesTable = sheet.tables.getItem("ExpensesTable");

    var visibleRange = expensesTable.getDataBodyRange().getVisibleView();
    visibleRange.load("values");

    return context.sync()
        .then(function() {
            console.log(visibleRange.values);
        });
}).catch(errorHandlerFunction);

表を書式設定するFormat a table

次のコード サンプルでは、表に書式を適用します。The following code sample applies formatting to a table. 表のヘッダー行、表の本体、表の 2 行目、表の 1 列目にそれぞれ別の塗りつぶし色を指定します。It specifies different fill colors for the header row of the table, the body of the table, the second row of the table, and the first column of the table. 書式の指定に使用できるプロパティの詳細については、「RangeFormat オブジェクト (Excel JavaScript API)」を参照してください。For information about the properties you can use to specify format, see RangeFormat Object (JavaScript API for Excel).

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var expensesTable = sheet.tables.getItem("ExpensesTable");

    expensesTable.getHeaderRowRange().format.fill.color = "#C70039";
    expensesTable.getDataBodyRange().format.fill.color = "#DAF7A6";
    expensesTable.rows.getItemAt(1).getRange().format.fill.color = "#FFC300";
    expensesTable.columns.getItemAt(0).getDataBodyRange().format.fill.color = "#FFA07A";

    return context.sync();
}).catch(errorHandlerFunction);

書式設定を適用後の表Table after formatting is applied

Excel の書式設定を適用後の表

範囲を表に変換するConvert a range to a table

次のコード サンプルでは、データ範囲を作成し、その範囲を表に変換します。The following code sample creates a range of data and then converts that range to a table.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");

    // Define values for the range
    var values = [["Product", "Qtr1", "Qtr2", "Qtr3", "Qtr4"],
    ["Frames", 5000, 7000, 6544, 4377],
    ["Saddles", 400, 323, 276, 651],
    ["Brake levers", 12000, 8766, 8456, 9812],
    ["Chains", 1550, 1088, 692, 853],
    ["Mirrors", 225, 600, 923, 544],
    ["Spokes", 6005, 7634, 4589, 8765]];

    // Create the range
    var range = sheet.getRange("A1:E7");
    range.values = values;

    if (Office.context.requirements.isSetSupported("ExcelApi", 1.2)) {
        sheet.getUsedRange().format.autofitColumns();
        sheet.getUsedRange().format.autofitRows();
    }

    sheet.activate();

    // Convert the range to a table
    var expensesTable = sheet.tables.add('A1:E7', true);
    expensesTable.name = "ExpensesTable";

    return context.sync();
}).catch(errorHandlerFunction);

範囲データ (範囲を表に変換する前)Data in the range (before the range is converted to a table)

Excel の範囲データ

表データ (範囲を表に変換した後)Data in the table (after the range is converted to a table)

Excel の表データ

JSON データを表にインポートするImport JSON data into a table

次のコード サンプルでは、Sample ワークシートに表を作成し、2 行のデータを定義する JSON オブジェクトを使用して表にデータを入力します。The following code sample creates a table in the worksheet named Sample and then populates the table by using a JSON object that defines two rows of data. コードが実行されている Excel ホスト アプリケーションが要件セット ExcelApi 1.2 をサポートしている場合、列の幅と行の高さは表内の現在のデータに最適になるよう設定されます。If the Excel host application where the code is running supports requirement set ExcelApi 1.2, the width of the columns and height of the rows are set to best fit the current data in the table.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");

    var expensesTable = sheet.tables.add("A1:D1", true /*hasHeaders*/);
    expensesTable.name = "ExpensesTable";
    expensesTable.getHeaderRowRange().values = [["Date", "Merchant", "Category", "Amount"]];

    var transactions = [
      {
        "DATE": "1/1/2017",
        "MERCHANT": "The Phone Company",
        "CATEGORY": "Communications",
        "AMOUNT": "$120"
      },
      {
        "DATE": "1/1/2017",
        "MERCHANT": "Southridge Video",
        "CATEGORY": "Entertainment",
        "AMOUNT": "$40"
      }
    ];

    var newData = transactions.map(item =>
        [item.DATE, item.MERCHANT, item.CATEGORY, item.AMOUNT]);

    expensesTable.rows.add(null, newData);

    if (Office.context.requirements.isSetSupported("ExcelApi", 1.2)) {
        sheet.getUsedRange().format.autofitColumns();
        sheet.getUsedRange().format.autofitRows();
    }

    sheet.activate();

    return context.sync();
}).catch(errorHandlerFunction);

新しい表New table

Excel の新しい表

関連項目See also