Работа с таблицами с использованием API JavaScript для Excel
В этой статье приведены примеры кода, в которых показано, как выполнять стандартные задачи для таблиц с использованием API JavaScript для Excel. Полный список свойств и методов, поддерживаемых объектами и, см. в Table разделах Объект таблицы (API JavaScript для Excel) и Объект TableCollection (API JavaScript для Excel).TableCollection
Создание таблицы
В примере кода ниже показано, как создать таблицу на листе Sample (Пример). В таблице имеются заголовки, а также четыре столбца и семь строк с данными. Если приложение Excel, в котором выполняется код, поддерживает набор обязательных требованийExcelApi 1.2, ширина столбцов и высота строк задаются в соответствии с текущими данными в таблице.
Примечание.
Чтобы указать имя таблицы, необходимо сначала создать таблицу, а затем задать ее name свойство, как показано в следующем примере.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let 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();
await context.sync();
});
Новая таблица
Добавление строк в таблицу
В примере ниже показано, как добавить семь новых строк в таблицу ExpensesTable (Таблица расходов) на листе Sample (Пример). Параметр indexadd метода имеет значение null, что указывает, что строки добавляются после существующих строк в таблице. Параметр alwaysInsert имеет значение true, что указывает, что новые строки должны быть вставлены в таблицу, а не под таблицей. Затем ширина столбцов и высота строк задаются в соответствии с текущими данными в таблице.
Примечание.
Свойство index объекта TableRow указывает номер индекса строки в коллекции строк таблицы. Объект TableRow не содержит id свойство, которое можно использовать в качестве уникального ключа для идентификации строки.
// This code sample shows how to add rows to a table that already exists
// on a worksheet named Sample.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let expensesTable = sheet.tables.getItem("ExpensesTable");
expensesTable.rows.add(
null, // index, Adds 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"]
],
true, // alwaysInsert, Specifies that the new rows be inserted into the table.
);
sheet.getUsedRange().format.autofitColumns();
sheet.getUsedRange().format.autofitRows();
await context.sync();
});
Таблица с новыми строками
Добавление столбца в таблицу
В примерах ниже показано, как добавить столбец в таблицу. В первом примере показано, как заполнить новый столбец статическими значениями, во втором — как заполнить новый столбец формулами.
Примечание.
Свойство index объекта TableColumn указывает номер индекса столбца в коллекции столбцов таблицы. Свойство id объекта TableColumn содержит уникальный ключ, идентифицирующий столбец.
Добавление столбца, содержащего статические значения
В примере кода ниже показано, как добавить новый столбец в таблицу ExpensesTable (Таблица расходов) на листе Sample (Пример). Новый столбец будет добавлен после всех существующих столбцов в таблице. Он будет содержать заголовок Day of the Week (День недели), а также данные для заполнения ячеек в столбце. Затем ширина столбцов и высота строк задаются в соответствии с текущими данными в таблице.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let 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"]
]);
sheet.getUsedRange().format.autofitColumns();
sheet.getUsedRange().format.autofitRows();
await context.sync();
});
Таблица с новым столбцом
Добавление столбца, содержащего формулы
В примере кода ниже показано, как добавить новый столбец в таблицу ExpensesTable (Таблица расходов) на листе Sample (Пример). Новый столбец будет добавлен в конец таблицы, будет содержать заголовок Type of the Day (Тип дня), и в нем будет использована формула для заполнения каждой ячейки столбца. Затем ширина столбцов и высота строк задаются в соответствии с текущими данными в таблице.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let 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")']
]);
sheet.getUsedRange().format.autofitColumns();
sheet.getUsedRange().format.autofitRows();
await context.sync();
});
Таблица с новым столбцом, содержащим вычисленные значения
Изменение размера таблицы
Надстройка может изменять размер таблицы, не добавляя в нее данные или не изменяя значения ячеек. Чтобы изменить размер таблицы, используйте метод Table.resize . В следующем примере кода показано, как изменить размер таблицы. В этом примере кода используется таблица ExpensesTable из раздела Создание таблицы ранее в этой статье и для нового диапазона таблицы устанавливается значение A1:D20.
await Excel.run(async (context) => {
// Retrieve the worksheet and a table on that worksheet.
let sheet = context.workbook.worksheets.getItem("Sample");
let expensesTable = sheet.tables.getItem("ExpensesTable");
// Resize the table.
expensesTable.resize("A1:D20");
await context.sync();
});
Важно!
Новый диапазон таблицы должен перекрываться исходным диапазоном, а заголовки (или верхняя часть таблицы) должны находиться в одной строке.
Таблица после изменения размера
Изменение имени столбца
В примере кода ниже показано, как изменить имя первого столбца в таблице на Purchase date. Затем ширина столбцов и высота строк задаются в соответствии с текущими данными в таблице.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let expensesTable = sheet.tables.getItem("ExpensesTable");
expensesTable.columns.load("items");
await context.sync();
expensesTable.columns.items[0].name = "Purchase date";
sheet.getUsedRange().format.autofitColumns();
sheet.getUsedRange().format.autofitRows();
await context.sync();
});
Таблица со столбцом с новым именем
Получение данных из таблицы
В примере кода ниже показано, как считать данные из таблицы ExpensesTable (Таблица расходов), размещенной на листе Sample (Пример), а затем отобразить эти данные под таблицей на том же листе.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let expensesTable = sheet.tables.getItem("ExpensesTable");
// Get data from the header row.
let headerRange = expensesTable.getHeaderRowRange().load("values");
// Get data from the table.
let bodyRange = expensesTable.getDataBodyRange().load("values");
// Get data from a single column.
let columnRange = expensesTable.columns.getItem("Merchant").getDataBodyRange().load("values");
// Get data from a single row.
let rowRange = expensesTable.rows.getItemAt(1).load("values");
// Sync to populate proxy objects with data from Excel.
await context.sync();
let headerValues = headerRange.values;
let bodyValues = bodyRange.values;
let merchantColumnValues = columnRange.values;
let 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.
await context.sync();
});
Таблица и выведенные данные
Обнаружение изменений данных
Возможно, надстройке потребуется реагировать на изменения пользователями данных в таблице. Чтобы обнаружить эти изменения, можно зарегистрировать обработчик событий для события onChanged таблицы. Обработчики события onChanged получают объект TableChangedEventArgs при возникновении события.
Объект TableChangedEventArgs предоставляет сведения об изменениях и источнике. Так как событие onChanged возникает при изменении формата или значения данных, может быть полезно, чтобы надстройка проверяла, действительно ли значения изменились. Свойство details объединяет эти сведения в виде интерфейса ChangedEventDetail. В следующем примере кода показано, как отобразить значения и типы измененной ячейки до и после изменения.
// This function would be used as an event handler for the Table.onChanged event.
async function onTableChanged(eventArgs) {
await Excel.run(async (context) => {
let details = eventArgs.details;
let address = eventArgs.address;
// Print the before and after types and values to the console.
console.log(`Change at ${address}: was ${details.valueBefore}(${details.valueTypeBefore}),`
+ ` now is ${details.valueAfter}(${details.valueTypeAfter})`);
await context.sync();
});
}
Сортировка данных в таблице
В примере кода ниже показано, как отсортировать данные по убыванию в четвертом столбце таблицы.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let expensesTable = sheet.tables.getItem("ExpensesTable");
// Queue a command to sort data by the fourth column of the table (descending).
let sortRange = expensesTable.getDataBodyRange();
sortRange.sort.apply([
{
key: 3,
ascending: false,
},
]);
// Sync to run the queued command in Excel.
await context.sync();
});
Данные таблицы, отсортированные по столбцу Amount (Сумма) в порядке убывания
При сортировке данных на листе создается уведомление о событии. Дополнительные сведения о событиях, связанных с сортировкой, и о регистрации обработчиков событий надстройкой в ответ на такие события см. в статье Обработка событий сортировки.
Применение фильтров к таблице
В примере кода ниже показано, как применить фильтры для столбцов Amount (Сумма) и Category (Категория) в таблице. В результате применения фильтров будут отображены только те строки, у которых в столбце Category (Категория) содержится одно из указанных значений, а значения в столбце Amount (Сумма) меньше среднего значения для всех строк.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let expensesTable = sheet.tables.getItem("ExpensesTable");
// Queue a command to apply a filter on the Category column.
let categoryFilter = expensesTable.columns.getItem("Category").filter;
categoryFilter.apply({
filterOn: Excel.FilterOn.values,
values: ["Restaurant", "Groceries"]
});
// Queue a command to apply a filter on the Amount column.
let amountFilter = expensesTable.columns.getItem("Amount").filter;
amountFilter.apply({
filterOn: Excel.FilterOn.dynamic,
dynamicCriteria: Excel.DynamicFilterCriteria.belowAverage
});
// Sync to run the queued commands in Excel.
await context.sync();
});
Таблица данных, в которой применены фильтры для столбцов Category (Категория) и Amount (Сумма)
Удаление фильтров в таблице
В примере кода ниже показано, как удалить все фильтры, примененные к таблице.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let expensesTable = sheet.tables.getItem("ExpensesTable");
expensesTable.clearFilters();
await context.sync();
});
Данные таблицы без фильтров
Получение отображаемого диапазона из отфильтрованной таблицы
В примере кода ниже показано, как получить диапазон, содержащий данные только из тех ячеек, которые в данный момент отображаются в указанной таблице, и записать значения из этого диапазона в консоль. Метод, как показано ниже, можно использовать getVisibleView() для получения видимого содержимого таблицы при каждом применении фильтров столбцов.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let expensesTable = sheet.tables.getItem("ExpensesTable");
let visibleRange = expensesTable.getDataBodyRange().getVisibleView();
visibleRange.load("values");
await context.sync();
console.log(visibleRange.values);
});
Автофильтр
Надстройка может использовать объект AutoFilter таблицы для фильтрации данных. Объект AutoFilter является целой структурой фильтра таблицы или диапазона. Все операции фильтрации, описанные выше в этой статье, совместимы с автофильтром. Единая точка доступа упрощает доступ к нескольким фильтрам и управление ими.
В следующем примере кода показана такая же фильтрация данных, как в примере кода выше, но выполненная полностью с помощью автофильтра.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let expensesTable = sheet.tables.getItem("ExpensesTable");
expensesTable.autoFilter.apply(expensesTable.getRange(), 2, {
filterOn: Excel.FilterOn.values,
values: ["Restaurant", "Groceries"]
});
expensesTable.autoFilter.apply(expensesTable.getRange(), 3, {
filterOn: Excel.FilterOn.dynamic,
dynamicCriteria: Excel.DynamicFilterCriteria.belowAverage
});
await context.sync();
});
Объект AutoFilter можно также применять к диапазону на уровне листа. Дополнительные сведения см. в статье Работа с листами с использованием API JavaScript для Excel.
Форматирование таблицы
В примере кода ниже показано, как применить форматирование к таблице. В примере показано, как указать различные цвета заливки для строки заголовков, основной части, второй строки и первого столбца таблицы. Сведения о свойствах, которые вы можете использовать для задания формата, см. в статье Объект RangeFormat (API JavaScript для Excel).
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let 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";
await context.sync();
});
Таблица после применения форматирования
Преобразование диапазона в таблицу
В примере кода ниже показано, как создать диапазон данных и преобразовывать его в таблицу. Затем ширина столбцов и высота строк задаются в соответствии с текущими данными в таблице.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
// Define values for the range.
let 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.
let range = sheet.getRange("A1:E7");
range.values = values;
sheet.getUsedRange().format.autofitColumns();
sheet.getUsedRange().format.autofitRows();
sheet.activate();
// Convert the range to a table.
let expensesTable = sheet.tables.add('A1:E7', true);
expensesTable.name = "ExpensesTable";
await context.sync();
});
Данные в диапазоне (перед его преобразованием в таблицу)
Данные в таблице (после преобразования диапазона в таблицу)
Импорт данных JSON в таблицу
В примере кода ниже показано, как создать таблицу на листе Sample (Пример), а затем заполнить ее с помощью объекта JSON, который определяет две строки данных. Затем ширина столбцов и высота строк задаются в соответствии с текущими данными в таблице.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let expensesTable = sheet.tables.add("A1:D1", true /*hasHeaders*/);
expensesTable.name = "ExpensesTable";
expensesTable.getHeaderRowRange().values = [["Date", "Merchant", "Category", "Amount"]];
let transactions = [
{
"DATE": "1/1/2017",
"MERCHANT": "The Phone Company",
"CATEGORY": "Communications",
"AMOUNT": "$120"
},
{
"DATE": "1/1/2017",
"MERCHANT": "Southridge Video",
"CATEGORY": "Entertainment",
"AMOUNT": "$40"
}
];
let newData = transactions.map(item =>
[item.DATE, item.MERCHANT, item.CATEGORY, item.AMOUNT]);
expensesTable.rows.add(null, newData);
sheet.getUsedRange().format.autofitColumns();
sheet.getUsedRange().format.autofitRows();
sheet.activate();
await context.sync();
});
Новая таблица
См. также
Office Add-ins
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по