Работа с диапазонами с использованием API JavaScript для ExcelWork with ranges using the Excel JavaScript API

В этой статье приведены примеры кода, в которых показано, как выполнять стандартные задачи для диапазонов с использованием API JavaScript для Excel.This article provides code samples that show how to perform common tasks with ranges using the Excel JavaScript API. Полный список свойств и методов, Range поддерживаемых объектом, представлен в разделе объект Range (API JavaScript для Excel).For the complete list of properties and methods that the Range object supports, see Range Object (JavaScript API for Excel).

Примечание

Примеры кода, в которых показано, как выполнять более сложные задачи для диапазонов, см. в статье Работа с диапазонами с использованием API JavaScript для Excel (дополнительные задачи).For code samples that show how to perform more advanced tasks with ranges, see Work with ranges using the Excel JavaScript API (advanced).

Получение диапазонаGet a range

В примерах ниже показаны различные способы получения ссылки на диапазон, расположенный на листе.The following examples show different ways to get a reference to a range within a worksheet.

Получение диапазона по адресуGet range by address

В примере кода ниже показано, как получить диапазон с адресом B2: C5 из листа с именем Sample, загружает его address свойство и записывает сообщение в консоль.The following code sample gets the range with address B2:C5 from the worksheet named Sample, loads its address property, and writes a message to the console.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var range = sheet.getRange("B2:C5");
    range.load("address");

    return context.sync()
        .then(function () {
            console.log(`The address of the range B2:C5 is "${range.address}"`);
        });
}).catch(errorHandlerFunction);

Получение диапазона по имениGet range by name

Следующий пример кода получает диапазон с именем MyRange Sample, загружает его address свойство и записывает сообщение в консоль.The following code sample gets the range named MyRange from the worksheet named Sample, loads its address property, and writes a message to the console.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var range = sheet.getRange("MyRange");
    range.load("address");

    return context.sync()
        .then(function () {
            console.log(`The address of the range "MyRange" is "${range.address}"`);
        });
}).catch(errorHandlerFunction);

Получение используемого диапазонаGet used range

Следующий пример кода получает использованный диапазон из листа Sample, загружает его address свойство и записывает сообщение в консоль.The following code sample gets the used range from the worksheet named Sample, loads its address property, and writes a message to the console. Используемый диапазон — это наименьший диапазон, включающий в себя все ячейки листа, которые содержат значение или форматирование.The used range is the smallest range that encompasses any cells in the worksheet that have a value or formatting assigned to them. Если лист пуст, getUsedRange() метод возвращает диапазон, состоящий только из верхней левой ячейки на листе.If the entire worksheet is blank, the getUsedRange() method returns a range that consists of only the top-left cell in the worksheet.

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

    return context.sync()
        .then(function () {
            console.log(`The address of the used range in the worksheet is "${range.address}"`);
        });
}).catch(errorHandlerFunction);

Получение всего диапазонаGet entire range

Следующий пример кода получает весь диапазон листа на листе Sample, загружает его address свойство и записывает сообщение в консоль.The following code sample gets the entire worksheet range from the worksheet named Sample, loads its address property, and writes a message to the console.

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

    return context.sync()
        .then(function () {
            console.log(`The address of the entire worksheet range is "${range.address}"`);
        });
}).catch(errorHandlerFunction);

Вставка диапазона ячеекInsert a range of cells

В примере кода ниже показано, как вставить диапазон ячеек в расположение B4:E4 и сдвинуть другие ячейки вниз, чтобы освободить место для новых ячеек.The following code sample inserts a range of cells in location B4:E4 and shifts other cells down to provide space for the new cells.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var range = sheet.getRange("B4:E4");

    range.insert(Excel.InsertShiftDirection.down);

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

Данные перед вставкой диапазонаData before range is inserted

Данные в Excel перед вставкой диапазона

Данные после вставки диапазонаData after range is inserted

Данные в Excel после вставки диапазона

Очистка диапазона ячеекClear a range of cells

В примере кода ниже показано, как удалить все содержимое и форматирование ячеек в диапазоне E2:E5.The following code sample clears all contents and formatting of cells in the range E2:E5.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var range = sheet.getRange("E2:E5");

    range.clear();

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

Данные перед очисткой диапазонаData before range is cleared

Данные в Excel перед очисткой диапазона

Данные после очистки диапазонаData after range is cleared

Данные в Excel после очистки диапазона

Удаление диапазона ячеекDelete a range of cells

В примере кода ниже показано, как удалить ячейки в диапазоне B4:E4 и сдвинуть другие ячейки вверх, чтобы заполнить место, освободившееся после удаления ячеек.The following code sample deletes the cells in the range B4:E4 and shift other cells up to fill the space that was vacated by the deleted cells.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var range = sheet.getRange("B4:E4");

    range.delete(Excel.DeleteShiftDirection.up);

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

Данные перед удалением диапазонаData before range is deleted

Данные в Excel перед удалением диапазона

Данные после удаления диапазонаData after range is deleted

Данные в Excel после удаления диапазона

Задание выделенного диапазонаSet the selected range

В примере кода ниже показано, как выделить диапазон B2:E6 на активном листе.The following code sample selects the range B2:E6 in the active worksheet.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:E6");

    range.select();

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

Выделенный диапазон B2:E6Selected range B2:E6

Выделенный диапазон в Excel

Получение выделенного диапазонаGet the selected range

Следующий пример кода получает выбранный диапазон, загружает его address свойство и записывает сообщение в консоль.The following code sample gets the selected range, loads its address property, and writes a message to the console.

Excel.run(function (context) {
    var range = context.workbook.getSelectedRange();
    range.load("address");

    return context.sync()
        .then(function () {
            console.log(`The address of the selected range is "${range.address}"`);
        });
}).catch(errorHandlerFunction);

Задание значений или формулSet values or formulas

В примерах ниже показано, как задать значения и формулы для одной ячейки или диапазона ячеек.The following examples show how to set values and formulas for a single cell or a range of cells.

Задание значения для одной ячейкиSet value for a single cell

В примере кода ниже показано, как присвоить ячейке C3 значение 5, а затем настроить ширину столбцов для наилучшего размещения данных.The following code sample sets the value of cell C3 to "5" and then sets the width of the columns to best fit the data.

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

    var range = sheet.getRange("C3");
    range.values = [[ 5 ]];
    range.format.autofitColumns();

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

Данные перед изменением значения ячейкиData before cell value is updated

Данные в Excel перед изменением значения ячейки

Данные после изменения значения ячейкиData after cell value is updated

Данные в Excel после изменения значения ячейки

Задание значений для диапазона ячеекSet values for a range of cells

В примере кода ниже показано, как присвоить значения ячейкам в диапазоне B5:D5, а затем настроить ширину столбцов для наилучшего размещения данных.The following code sample sets values for the cells in the range B5:D5 and then sets the width of the columns to best fit the data.

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

    var data = [
        ["Potato Chips", 10, 1.80],
    ];

    var range = sheet.getRange("B5:D5");
    range.values = data;
    range.format.autofitColumns();

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

Данные перед изменением значений ячеекData before cell values are updated

Данные в Excel перед изменением значений ячеек

Данные после изменения значений ячеекData after cell values are updated

Данные в Excel после изменения значений ячеек

Задание формулы для одной ячейкиSet formula for a single cell

В примере кода ниже показано, как задать формулу для ячейки E3, а затем настроить ширину столбцов для наилучшего размещения данных.The following code sample sets a formula for cell E3 and then sets the width of the columns to best fit the data.

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

    var range = sheet.getRange("E3");
    range.formulas = [[ "=C3 * D3" ]];
    range.format.autofitColumns();

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

Данные перед заданием формулы для ячейкиData before cell formula is set

Данные в Excel перед заданием формулы для ячейки

Данные после задания формулы для ячейкиData after cell formula is set

Данные в Excel после задания формулы для ячейки

Задание формул для диапазона ячеекSet formulas for a range of cells

В примере кода ниже показано, как задать формулы для ячеек в диапазоне E2:E6, а затем настроить ширину столбцов для наилучшего размещения данных.The following code sample sets formulas for cells in the range E2:E6 and then sets the width of the columns to best fit the data.

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

    var data = [
        ["=C3 * D3"],
        ["=C4 * D4"],
        ["=C5 * D5"],
        ["=SUM(E3:E5)"]
    ];

    var range = sheet.getRange("E3:E6");
    range.formulas = data;
    range.format.autofitColumns();

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

Данные перед заданием формул для ячеекData before cell formulas are set

Данные в Excel перед заданием формул для ячеек

Данные после задания формул для ячеекData after cell formulas are set

Данные в Excel после задания формул для ячеек

Получение значений, текста или формулGet values, text, or formulas

В примерах ниже показано, как получать значения, текст и формулы из диапазона ячеек.These examples show how to get values, text, and formulas from a range of cells.

Получение значений из диапазона ячеекGet values from a range of cells

Следующий пример кода получает диапазон B2: E6, загружает его values свойство и записывает значения в консоль.The following code sample gets the range B2:E6, loads its values property, and writes the values to the console. valuesСвойство диапазона указывает необработанные значения, содержащиеся в ячейках.The values property of a range specifies the raw values that the cells contain. Даже если некоторые ячейки в диапазоне содержат формулы, values свойство диапазона указывает необработанные значения для этих ячеек, а не формулы.Even if some cells in a range contain formulas, the values property of the range specifies the raw values for those cells, not any of the formulas.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var range = sheet.getRange("B2:E6");
    range.load("values");

    return context.sync()
        .then(function () {
            console.log(JSON.stringify(range.values, null, 4));
        });
}).catch(errorHandlerFunction);

Данные в диапазоне (значения в столбце E представляют собой результат вычисления формул)Data in range (values in column E are a result of formulas)

Данные в Excel после задания формул для ячеек

range.values (как записано в консоль в примере кода выше)range.values (as logged to the console by the code sample above)

[
    [
        "Product",
        "Qty",
        "Unit Price",
        "Total Price"
    ],
    [
        "Almonds",
        2,
        7.5,
        15
    ],
    [
        "Coffee",
        1,
        34.5,
        34.5
    ],
    [
        "Chocolate",
        5,
        9.56,
        47.8
    ],
    [
        "",
        "",
        "",
        97.3
    ]
]

Получение текста из диапазона ячеекGet text from a range of cells

Следующий пример кода получает диапазон B2: E6, загружает его text свойство и записывает его в консоль.The following code sample gets the range B2:E6, loads its text property, and writes it to the console. textСвойство диапазона определяет отображаемые значения для ячеек в диапазоне.The text property of a range specifies the display values for cells in the range. Даже если некоторые ячейки в диапазоне содержат формулы, text свойство диапазона определяет отображаемые значения для этих ячеек, а не формулы.Even if some cells in a range contain formulas, the text property of the range specifies the display values for those cells, not any of the formulas.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var range = sheet.getRange("B2:E6");
    range.load("text");

    return context.sync()
        .then(function () {
            console.log(JSON.stringify(range.text, null, 4));
        });
}).catch(errorHandlerFunction);

Данные в диапазоне (значения в столбце E представляют собой результат вычисления формул)Data in range (values in column E are a result of formulas)

Данные в Excel после задания формул для ячеек

range.text (как записано в консоль в примере кода выше)range.text (as logged to the console by the code sample above)

[
    [
        "Product",
        "Qty",
        "Unit Price",
        "Total Price"
    ],
    [
        "Almonds",
        "2",
        "7.5",
        "15"
    ],
    [
        "Coffee",
        "1",
        "34.5",
        "34.5"
    ],
    [
        "Chocolate",
        "5",
        "9.56",
        "47.8"
    ],
    [
        "",
        "",
        "",
        "97.3"
    ]
]

Получение формул из диапазона ячеекGet formulas from a range of cells

Следующий пример кода получает диапазон B2: E6, загружает его formulas свойство и записывает его в консоль.The following code sample gets the range B2:E6, loads its formulas property, and writes it to the console. formulasСвойство диапазона определяет формулы для ячеек в диапазоне, содержащих формулы, и необработанные значения для ячеек в диапазоне, которые не содержат формул.The formulas property of a range specifies the formulas for cells in the range that contain formulas and the raw values for cells in the range that do not contain formulas.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var range = sheet.getRange("B2:E6");
    range.load("formulas");

    return context.sync()
        .then(function () {
            console.log(JSON.stringify(range.formulas, null, 4));
        });
}).catch(errorHandlerFunction);

Данные в диапазоне (значения в столбце E представляют собой результат вычисления формул)Data in range (values in column E are a result of formulas)

Данные в Excel после задания формул для ячеек

range.formulas (как записано в консоль в примере кода выше)range.formulas (as logged to the console by the code sample above)

[
    [
        "Product",
        "Qty",
        "Unit Price",
        "Total Price"
    ],
    [
        "Almonds",
        2,
        7.5,
        "=C3 * D3"
    ],
    [
        "Coffee",
        1,
        34.5,
        "=C4 * D4"
    ],
    [
        "Chocolate",
        5,
        9.56,
        "=C5 * D5"
    ],
    [
        "",
        "",
        "",
        "=SUM(E3:E5)"
    ]
]

Задание формата диапазонаSet range format

В примерах ниже показано, как задать цвет шрифта, цвет заливки и формат чисел для ячеек в диапазоне.The following examples show how to set font color, fill color, and number format for cells in a range.

Задание цвета шрифта и цвета заливкиSet font color and fill color

В примере ниже показано, как задать цвет шрифта и цвет заливки для ячеек в диапазоне B2: E2.The following code sample sets the font color and fill color for cells in range B2:E2.

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

    var range = sheet.getRange("B2:E2");
    range.format.fill.color = "#4472C4";
    range.format.font.color = "white";

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

Данные в диапазоне перед заданием цвета шрифта и цвета заливкиData in range before font color and fill color are set

Данные в Excel перед заданием формата

Данные в диапазоне после задания цвета шрифта и цвета заливкиData in range after font color and fill color are set

Данные в Excel после задания формата

Задание формата чиселSet number format

В примере ниже показано, как задать формат чисел для ячеек в диапазоне D3:E5.The following code sample sets the number format for the cells in range D3:E5.

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

    var formats = [
        ["0.00", "0.00"],
        ["0.00", "0.00"],
        ["0.00", "0.00"]
    ];

    var range = sheet.getRange("D3:E5");
    range.numberFormat = formats;

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

Данные в диапазоне перед заданием формата чиселData in range before number format is set

Данные в Excel перед заданием формата чисел

Данные в диапазоне после задания формата чиселData in range after number format is set

Данные в Excel после задания числового формата

Чтение из неограниченного диапазона и запись в негоRead or write to an unbounded range

Чтение из неограниченного диапазонаRead an unbounded range

Неограниченный адрес диапазона — это адрес диапазона, в котором задаются либо все столбцы, либо все строки.An unbounded range address is a range address that specifies either entire columns or entire rows. Пример:For example:

  • Диапазоны адресов состоят из целых столбцов:Range addresses comprised of entire columns:
    • C:C
    • A:F
  • Диапазоны адресов состоят из целых строк:Range addresses comprised of entire rows:
    • 2:2
    • 1:4

Когда API отправляет запрос на получение неограниченного диапазона (например, getRange('C:C')), ответ будет содержать значения null для свойств уровня ячейки, например свойств values, text, numberFormat и formula. Другие свойства диапазона, например address и cellCount, будут содержать допустимые значения для неограниченного диапазона.When the API makes a request to retrieve an unbounded range (for example, getRange('C:C')), the response will contain null values for cell-level properties such as values, text, numberFormat, and formula. Other properties of the range, such as address and cellCount, will contain valid values for the unbounded range.

Запись в неограниченный диапазонWrite to an unbounded range

Невозможно задать свойства уровня ячейки values , например, numberFormat и formula для неограниченного диапазона, так как входной запрос слишком велик.You cannot set cell-level properties such as values, numberFormat, and formula on an unbounded range because the input request is too large. Например, приведенный ниже фрагмент кода недопустим, так как он пытается указать свойство values для неограниченного диапазона.For example, the following code snippet is not valid because it attempts to specify values for an unbounded range. API возвращает ошибку при попытке установить свойства на уровне ячейки для неограниченного диапазона.The API returns an error if you attempt to set cell-level properties for an unbounded range.

var range = context.workbook.worksheets.getActiveWorksheet().getRange('A:B');
range.values = 'Due Date';

Чтение из большого диапазона и запись в негоRead or write to a large range

Если диапазон содержит большое количество ячеек, значений, форматов чисел или формул, то, возможно, не удастся выполнить операции API над этим диапазоном. API всегда делает все возможное, чтобы выполнить запрошенную операцию над диапазоном (то есть получить или записать указанные данные), но попытка выполнить операцию чтения или записи для большого диапазона может привести к ошибке API из-за чрезмерного потребления ресурсов. Чтобы избежать таких ошибок, мы рекомендуем выполнять отдельные операции чтения или записи для небольших подмножеств большого диапазона, а не пытаться выполнить одну операцию чтения или записи для большого диапазона.If a range contains a large number of cells, values, number formats, and/or formulas, it may not be possible to run API operations on that range. The API will always make a best attempt to run the requested operation on a range (i.e., to retrieve or write the specified data), but attempting to perform read or write operations for a large range may result in an API error due to excessive resource utilization. To avoid such errors, we recommend that you run separate read or write operations for smaller subsets of a large range, instead of attempting to run a single read or write operation on a large range.

Подробнее об ограничениях системы можно узнать в разделе "надстройки Excel" ограничений ресурсов и оптимизации производительности для надстроек Office.For details on the system limitations, see the "Excel add-ins" section of Resource limits and performance optimization for Office Add-ins.

Условное форматирование диапазоновConditional formatting of ranges

В диапазонах может применяться форматирование к отдельным ячейкам на основе условий.Ranges can have formats applied to individual cells based on conditions. Дополнительные сведения об этом см. в статье Применение условного форматирования к диапазонам Excel.For more information about this, see Apply conditional formatting to Excel ranges.

Поиск ячейки с помощью сопоставления строкFind a cell using string matching

У объекта Range есть метод find для поиска указанной строки в диапазоне.The Range object has a find method to search for a specified string within the range. Он возвращает диапазон первой ячейки с текстом, соответствующим критериям.It returns the range of the first cell with matching text. Приведенный ниже пример кода находит первую ячейку со значением, соответствующим строке Food (Еда), и заносит ее адрес в консоль.The following code sample finds the first cell with a value equal to the string Food and logs its address to the console. Обратите внимание, что метод find выдает ошибку ItemNotFound, если указанной строки не существует в диапазоне.Note that find throws an ItemNotFound error if the specified string doesn't exist in the range. Если ожидается, что указанная строка может отсутствовать в диапазоне, используйте вместо этого метод findOrNullObject, чтобы ваш код корректно обработал этот сценарий.If you expect that the specified string may not exist in the range, use the findOrNullObject method instead, so your code gracefully handles that scenario.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var table = sheet.tables.getItem("ExpensesTable");
    var searchRange = table.getRange();
    var foundRange = searchRange.find("Food", {
        completeMatch: true, // find will match the whole cell value
        matchCase: false, // find will not match case
        searchDirection: Excel.SearchDirection.forward // find will start searching at the beginning of the range
    });

    foundRange.load("address");
    return context.sync()
        .then(function() {
            console.log(foundRange.address);
    });
}).catch(errorHandlerFunction);

Если метод find вызывается для диапазона, представляющего одну ячейку, поиск выполняется во всем листе.When the find method is called on a range representing a single cell, the entire worksheet is searched. Поиск начинается в этой ячейке и продолжается в направлении, которое определяется параметром SearchCriteria.searchDirection, охватывающим концы листа при необходимости.The search begins at that cell and goes in the direction specified by SearchCriteria.searchDirection, wrapping around the ends of the worksheet if needed.

См. такжеSee also