Работа с excel javascript

Работа с таблицами с использованием 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 (Пример). Параметр index add метода имеет значение 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 $: was $($),` + ` now is $($)`); 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(); >); 

Новая таблица

См. также

Источник

Открываем файлы формата Open XML Excel в JavaScript

Для загрузки информации о торговых точках в наш логистический SaaS-сервис «Муравьиная логистика» из Excel я решил использовать web-браузер. Обычно проще загрузить файл на сервер и с помощью любой библиотеки залить в БД. Но мне было интересно загрузить его построчно для контроля целостности каждой строки на клиенте, ну и, конечно, опробовать так всеми рекламируемое HTML5 FileAPI и Drag and Drop.

Книга Exсel – это ZIP архив с каталогами и файлами XML в формате Open XML. Парсить XML отлично умеет jQuery, а вот зиппить нет. Для этого на просторах сети была найдена библиотека zip.js, которая прекрасно справилась с поставленной задачей.

Итак, попробуем посмотреть, что же находится внутри архива:

var c = document.getElementById("comps"), FileDragHover = function (e) < e.stopPropagation(); e.preventDefault(); if(e.target.id==='comps') e.target.className = (e.type == "dragover" ? "filedrag hover" : "filedrag"); else c.className = (e.type == "dragover" ? "filedrag hover" : "filedrag"); >c.addEventListener("drop", function(e) < e.preventDefault(); c.className = "filedrag"; var files = e.target.files || e.dataTransfer.files; for (var i = 0, f; f = files[i]; i++) < if(f.name.toLowerCase().indexOf('xlsx')<=0) < alert('Это не файл Excel'); >else < zip.createReader(new zip.BlobReader(f), function(reader) < // Получаем все файлы архива reader.getEntries(function(entries) < // В консоли появятся все внутренности архива Excel console.info(entries) return false; >); >, function(error) < alert("Ошибка: " + error) >); > > return false; >, false); c.addEventListener("dragover", FileDragHover, false); c.addEventListener("dragleave", FileDragHover, false); 

Результат можно посмотреть тут. Скачайте пример файла и перетащите его на форму.
В консоли появится список всех файлов архива книги Excel. Среди свойств объектов, появившихся в консоли, есть filename, по нему-то мы и будем искать необходимые нам файлы XML.

Отфильтруем только нужные для нас файлы:

// Получаем все файлы архива reader.getEntries(function(entries) < var a=[],st; for(var i in entries)< var e=entries[i]; var fn=e.filename.toLowerCase(); if(fn.indexOf("sheet")>0) < a.push(e); >else if(fn.indexOf("sharedstring")>0) < st=e; >> // Массив всех листов книги Excel console.info(a) // Ассоциативный массив строк console.info(st) return false; >); 

Результат можно посмотреть тут, закинув файл и посмотрев в консоль.

Далее нам необходимо извлечь данные простыми селекторами, для словаря строк это — st t, для записей таблицы с данными на листе это — sheetdata row.

Добавим функцию для вывода данных из листа Excel:

printExcelData = function(sheets, strings) < var unzipProgress = document.getElementById("progress"); unzipProgress.style.display='block'; strings.getData(new zip.TextWriter(), function(text) < // Получаем все строки листа для ассоциации с их кодами var i,st=$($.parseXML(decodeURIComponent(escape(text)))).find('si t'); for(i=0;i; for(i=0;i if(h) < d.push(o) >else h=o; >); var id_name=""; for(i in h) if(h[i]=='Comp_Id') < id_name=h[i]; break; >// Если поле Comp_Id есть в записи, значит лист наш if(id_name=='Comp_Id') < unzipProgress.style.display='none'; // Это заголовок таблицы данных s=""; for(i=0;i'; $('.result thead tr').append(s) // Это данные s=""; for(j=0; j'; for(i=0; i'+d[j][h[i]].toString()+''; > s+=''; > $('.result tbody').append(s) sheets=[]; return; > if(sheets.length>0) parseSheet(sheets.pop()); >, function(current, total) < unzipProgress.value = current; unzipProgress.max = total; >); > parseSheet(sheets.pop()); >, function(current, total) < unzipProgress.value = current; unzipProgress.max = total; >); > 

Так как Chrome считает преступлением использование HTML File API в кросс-домене (Uncaught SecurityError: An attempt was made to break through the security policy of the user agent.), последний пример выложил на Web-сервер.
Перетаскиваем файл и получаем стандартную таблицу HTML.

P.S.
Да, сейчас, как оказалось, есть Open XML SDK for JavaScript, но это тема для отдельной статьи…

Источник