Всем привет, я – Виталий Квитковский, руководитель программистов в небольшой государственной компании. В этом туториале я расскажу про базовые возможности написания макросов в программе "Р7 офис" – российском пакете, являющемся ответвлением Onlyoffice. Я пробовал запускать код в обеих программах, он работает одинаково. Есть некоторые отличия в деталях интерфейса, но в целом базовые функции работают одинаково.

Главная причина, почему мне так нравятся макросы в этих пакетах – я веб-разработчик, а эти макросы пишутся на Javascript :) Они, разумеется, не будут работать в Microsoft Office и других версиях офиса, но макросы чаще создаются для упрощения работы того, кто редактирует файл, а не для тех, кто потом будет смотреть на результат, поэтому для меня этого было вполне достаточно. В этой статье я также ограничусь макросами для таблиц, поскольку большая часть работы в нашей компании происходит именно с Excel-файлами.

В сети уже есть туториал, однако он больше построен по принципу «что можно сделать с данными с помощью нашего API». А я хотел бы построить эту статью по принципу «какие базовые микрозадачи возникают в процессе решения большинства задач и как их решать». Также мы порадуемся доступности большинства функционала классического Javascript на примере работы со строками, объект Math и даже fetch.

Предварительная подготовка

Для того, чтобы нам удобно было разрабатывать макросы, нам понадобится отладчик. Чтобы запускать программу с работающим отладчиком по умолчанию, нужно добавить ключ запуска --ascdesktop-support-debug-info. Например, в Windows это можно сделать в свойствах ярлыка на "Р7 офис", указав во вкладке «Ярлык» в поле «Объект»

"C:\Program Files\R7-Office\Editors\DesktopEditors.exe" --ascdesktop-support-debug-info

Теперь запустите программу, найдите в верхнем меню самую правую вкладку «Плагины», а в ней нажмите кнопку «Макросы». (В Onlyoffice версии 8.2.2, актуальной на 1 декабря 2024 года, кнопка «Макросы» перенесена во вкладку «Вид»). Здесь вы можете создавать макросы и запускать их. К сожалению, окно с кодом загораживает большую часть экрана, для того, чтобы посмотреть, как макрос повлиял на содержимое, зачастую приходится закрыть окно с кодом нажатием кнопки «Ок» и затем снова вернуться в него нажатием на «Макрос».

Тем не менее, отладочную информацию можно получить и без этого. Просто нажав F1 при открытом окне макроса, вы увидите Chrome dev tools! А это значит, что наши любимые console.log и console.dir выведут нам всю полезную отладочную информацию. Именно для работы Chrome dev tools нам и понадобился специальный параметр при запуске.

При создании макроса вам сразу предлагается заготовка для запуска, и остается написать только тело функции. Напишем в консоли Hello world!

(function()
{
    console.log('Hello world!');
})();

Далее его можно выполнить по нажатию кнопки «Выполнить» в "Р7 офис" или кнопки play в Onlyoffice.

Работа с ячейками

Ячейки достать достаточно легко. Если вам нужна ячейка B3 на текущем активном листе, то ее можно получить с помощью

let cell = Api.GetActiveSheet().GetRange("B3");

Однако для целей перебора ячеек нам нужны номера строк и столбцов. Давайте научимся оперировать с ними.

let cell = Api.GetActiveSheet().GetRangeByNumber(2, 1);

Это работа с той же ячейкой. Здесь сначала идет строка, потом столбец, и нумерация начинается с нуля (то есть для ячейки A1 мы бы писали 0, 0). При этом чтобы получить из текущей ячейки ее индекс строки и индекс столбца, есть функции GetCol и GetRow. Что характерно, они возвращают номера столбца и строки, как если бы они нумеровались с единицы. Поэтому для получения текущей ячейки из них надо будет вычесть 1:

let cell = Api.GetActiveSheet().GetRange("B3");
let indexCol = cell.GetCol() - 1;
let indexRow = cell.GetRow() - 1;
let sameCell = Api.GetActiveSheet().GetRangeByNumber(indexCol, indexRow);

Вам это понадобится в тот момент, когда, например, нужно будет, начиная с текущей ячейки, перебрать несколько ячеек, допустим, вниз, и сделать их фон желтым:

let sheet = Api.GetActiveSheet();
let cell = sheet.GetRange("B3");
let indexCol = cell.GetCol() - 1;
let indexRow = cell.GetRow() - 1;
let color = Api.CreateColorFromRGB(255, 255, 200);
for (let i = indexRow; i < indexRow + 5; i++) {
    sheet.GetRangeByNumber(i, indexCol).SetFillColor(color);
}

Если же вам для вставки в формулу нужно получить имя ячейки, то это делается с помощью метода GetAddress:

let cell = Api.GetActiveSheet().GetRangeByNumber(2, 1);
console.log(cell.GetAddress());

даст вам B3. Если вам нужно добавить в формулу символ доллара (фиксация строки/столбца ячейки при протягивании), то нужно указывать первые два параметра как true (доллар нужен) или false (не нужен, по умолчанию). cell.GetAddress(true, false) даст B$3, а cell.GetAddress(false, true) - $B3. Если вам нужен ввод формулы в стиле R1C1, то указывайте третьим параметром 'xlR1C1':

let cell = Api.GetActiveSheet().GetRangeByNumber(2, 1);
console.log(cell.GetAddress(false, false, 'xlR1C1')); //выведет R[2]C[1]

Наконец, если вам нужен полный путь до ячейки с учетом названия книги, то за это отвечает четвертый параметр, надо поставить его в true:

let cell = Api.GetActiveSheet().GetRangeByNumber(2, 1);
console.log(cell.GetAddress(false, false, 'xlA1', true)); //выведет
//[Книга1.xlsx]'Лист1'!B3

Формат вывода номера ячейки по умолчанию называется xlA1, но в принципе вы можете вписать в качестве параметра что угодно, кроме xlR1C1 (даже true), это сработает.

Если вам нужно будет только название листа, от названия книги легко избавиться с помощью стандартных функций Javascript.

Если вы оперируете ячейками на другом листе, в выводе команды GetAddress названия листа все равно не будет (если только 4-й параметр не указан как true).

let cell = Api.GetSheet('Лист1').GetRange('B1');
cell.SetValue(cell.GetAddress()); //выведет B1

Работа с содержимым ячеек и стилями

В этом разделе будет предполагаться, что для всех примеров мы уже выполнили

let cell = Api.GetActiveSheet().GetRange("B3");

Если вы хотите получить значение ячейки, воспользуйтесь методом GetValue:

console.log(cell.GetValue());

Если вам нужна формула как строка – это метод GetFormula:

console.log(cell.GetFormula());

Если вам нужно сохранить данные в ячейку – воспользуйтесь SetValue. Формулы тоже сохраняются через SetValue, если первым символом значения является =. Названия функций в формуле можно вводить как на английском, так и на русском языках (по крайней мере, если при установке программы был выбран русский язык).

cell.SetValue('234');
cell.SetValue('=MIN(C3:C10)');
cell.SetValue('=МИН(C3:C10)');

Если вы хотите ввести в одну ячейку много строк текста, то их можно разделять через \n, но для того, чтобы отображение стало корректным, нужно встать на эту ячейку и нажать кнопку «Перенос строк», разрешив перенос строк, либо нажать дабл-клик на эту ячейку и сохранить ее. Иначе текст будет отображаться слепленным.

cell.SetValue("2\n\n3\n\n4");

Так произойдет, даже если вы получите значение из другой ячейки с множеством строк с помощью GetValue. При этом с полученным значением можно будет работать, считая, что разрывы строк – это \n, например:

let sheet = Api.GetActiveSheet();
let multilineText = sheet.GetRange("H14").GetValue();
let rowsArray = multilineText.split("\n");
console.log(rowsArray); // будет массивом с набором строк
oWorksheet.GetRange("H15").SetValue(multilineText); //после выполнения макроса
//нужно будет на ячейе нажать «перенос строк», чтобы она отображалась корректно

Если вам нужно внести одинаковое значение во много ячеек, то можно в GetRange указать диапазон. Чаще это используется для одинакового форматирования блока целиком:

let cells = Api.GetSheet('Лист1').GetRange('B1:D40'); //здесь и далее
cells.SetValue(‘123’);

Теперь давайте разберемся с форматированием.

cells.SetBold(); //установить ячейкам жирный шрифт, с параметром false – наоборот, убрать жирный шрифт
cells.SetItalic(false); //аналогично для курсива
cells.SetUnderline(); //и для подчеркнутого текста
cells.SetStrikeout(true); //для зачеркивания нужно указать true
cells.SetNumberFormat("0.00"); //два знака после запятой; больше примеров в документации https://api.onlyoffice.com/docs/office-api/usage-api/spreadsheet-api/apirange/setnumberformat/
cells.SetFontName('Courier New'); //шрифт
cells.SetFontSize(14); //размер шрифта, пунктов
//значения – top | center | bottom
cells.SetAlignVertical('bottom'); //вертикальное выравнивание
//значения – left | center | right | justify
cells.SetAlignHorizontal('justify'); //горизонтальное выравнивание

Если мы хотим работать с высотой строки или шириной столбца, то это лучше делать через лист:

let sheet = Api.GetActiveSheet();
sheet.SetRowHeight(12, 20); //нумерация с нуля, высота в пикселях
sheet.SetColumnWidth(1, 30); //нумерация с нуля, ширина в более крупных единицах, точно установить не удалось

Но можно и через ячейку:

let cell = Api.GetActiveSheet().GetRange("B3");
cell.SetRowHeight(20);
cell.SetColumnWidth(30);

Набор данных как таблица

Если вы хотите работать с блоком данных как с таблицей (строка заголовков, фильтр, сортировка, вам понадобится метод FormatAsTable. При этом рекомендую предварительно отформатировать данные под нужный формат, например:

let sheet = Api.GetSheet('Лист1');
let cells = sheet.GetRange('H7:I40'); // Диапазон с числовыми данными
cells.SetNumberFormat("0.000"); // Форматируем как число с тремя знаками после запятой
Api.GetSheet('Лист1').FormatAsTable('G6:I40'); // В верхней строке у нас заголовки, в столбце G – строки, не требующие специального форматирования

Цвета и границы

В Onlyoffice работа с цветами возможна либо по их RGB-коду, либо через предустановленные цвета. Для того чтобы получить цвет по RGB-наполнению, нам нужно вызвать метод

let color = Api.CreateColorFromRGB(255, 255, 200);

Первый параметр – уровень красного, второй – зеленого, третий – синего (от 0 до 255).

Альтернативный вариант – выбрать из предустановленных цветов. Там, помимо очевидных вроде yellow и cyan, достаточно большой список, который можно найти на странице https://support.r7-office.ru/using-api-document-builder/global-2/, поискав по слову PresetColor.

let color = Api.CreateColorByName("dodgerBlue");

Методы CreateRGBColor, CreatePresetColor и CreateSchemeColor нужны для рисования фигур, для текста и ячеек они не подойдут. Пример можно посмотреть по ссылке https://api.onlyoffice.com/docs/office-api/usage-api/spreadsheet-api/api/createpresetcolor/ , а возможные значения для метода CreateSchemeColor – по предыдущей ссылке, поискав по слову SchemeColorId.

Дальше с помощью цвета можно устанавливать цвет текста ячеек или их фона:

let cells = Api.GetSheet('Лист1').GetRange('B1:D40');
cells.SetFillColor(color); //для фона
cells.SetFontColor(color); //для текста

Установка толщины и цвета границ диапазона производится следующим способом:

let cells = Api.GetSheet('Лист1').GetRange('B1:D40');
let color = Api.CreateColorFromRGB(255, 255, 200);
cells.SetBorders('Left', 'Thin', color);

Варианты значений для первого параметра SetBorders – обратите внимание, что они пишутся с большой буквы, и что Left, Right, Top и Bottom означают только внешние границы диапазона – это, помимо них, еще InsideHorizontal, InsideVertical (все внутренние горизонтальные или вертикальные границы), а также DiagonalDown и DiagonalUp (перечеркивание ячейки снизу вверх или сверху вниз).

Работа Javascript-функций

Очень многие возможности нативного Javascript работают в полном объеме. Например, объектом Math можно воспользоваться, чтобы сгенерировать случайный цвет, на котором должно быть видно черный текст. Собственно, это практически любой цвет, у которого каждый из параметров RGB больше 155. Давайте напишем функцию генерации такого цвета. Ее можно расположить над кодом макроса:

function rand() {
     return 155 + Math.round(100 * Math.random());
}

function generateColor() {
    return Api.CreateColorFromRGB(rand(), rand(), rand());
}

Далее, если мы хотим получить данные из какого-нибудь внешнего источника, то fetch также прекрасно работает, а если мы получаем оттуда json, то его можно распарсить с помощью JSON.parse. Например, получим курсы валют с сайта, загружающего их с сайта ЦБ:

function getCurrencies() {
    fetch('https://www.cbr-xml-daily.ru/daily_json.js').then(response => {
        let src = JSON.parse(response.value);         
        let start = 2;
        for (let chr in src.Valute) {
            let arCourses = src.Valute[chr];
            getCell('A' + start).SetValue(chr);
            getCell('B' + start).SetValue(arCourses.Value);
            getCell('C' + start).SetValue(arCourses.Previous);
            start++;
        }
    }); 
}

Работа вне окна макроса

Осталось еще одно небольшое неудобство. Окно макроса, как правило, нависает над таблицей (и его нельзя унести на другой экран), да и в целом некоторые макросы хочется запускать через комбинацию клавиш или клик куда-нибудь. И это почти возможно (с некоторым ограничением).

Реализуется это также с помощью стандартного функционала Javascript:

addEventListener("keyup", function(event) {
    if(event.ctrlKey) {
        let code = event.keyCode;
        if (code == 53) { // Ctrl+5
            someFunction(); //какая-то ваша функция
        }
        if (code == 54) { // Ctrl+6
            someOtherFunction(); //какая-то другая ваша функция
        }     
    } 
});

Если нам понадобится узнать, какой сейчас активный лист или ячейка, это всегда можно узнать с помощью

Api.GetActiveSheet().GetName(); //имя активного листа
Api.GetActiveSheet().GetActiveCell().GetAddress(); //имя активной ячейки

Также вы можете повесить событие на клик по ячейке. Как раз определение имени активной ячейки нам здесь пригодится.

addEventListener("click", function(event) {
    let cellName = Api.GetActiveSheet().GetActiveCell().GetAddress(false, false, 'xlA1');
    if (cellName === 'B3') {
        someFunction();
    }
    if (cellName === 'F3') {
        someOtherFunction();
    } 
});

Проблема, однако, в том, что хитрый Onlyoffice не обновляет данные, измененные таким образом, пока не будет изменена хотя бы одна ячейка в таблице. Делать редактирование ячейки на каждую комбинацию клавиш выглядит как-то очень глупо, и я нашел пару «лайфхаков», одну для «клавиатурщиков», другую – для «мышечников». Если вы предпочитаете не снимать рук с клавиатуры, то сразу после комбинации клавиш, которую вы нажали, вам нужно нажать F9 – пересчет всех формул на листе, и обновления отобразятся. Если вам больше нравится работать мышкой, то вы можете нажать ту из кнопок выравнивания по вертикали в верхней панели, которая уже и так нажата.

Соответственно, инструктируя тех, кто будет пользоваться вашим макросом, вы можете просто сразу комбинировать эти действия, например, функция someFunction запускается через комбинацию клавиш Ctrl+5 и F9, или же по нажатию на ячейку B3 (позаботьтесь, чтобы там была имитация кнопки с подписью) и потом на кнопку выравнивания текста по вертикали.

Я полагаю, что эта проблема должна решаться, если сделать из макроса плагин; думаю, об этом напишут в комментариях. Но плагины достойны отдельной статьи.

Автозапуск

Довольно очевидно, что макрос, запускающий addEventListener, должен запускаться автоматически при открытии файла. За это отвечает кнопка «автозапуск» в редакторе макросов, и автозапускаемые макросы помечены значком (А). При открытии файла Onlyoffice спрашивает, запустить ли автозапускаемые макросы.

К сожалению, иногда в макросах бывают ошибки. Если ваши макросы перестали запускаться, или если вдруг вы отредактировали макрос, а он не изменился, вам нужно закрыть и заново открыть программу.

Если вы поставили галочку «Всегда запускать макросы на входе», а в макросе появилась ошибка, то система не даст вам редактировать макросы. В этом случае отключение автоматического автозапуска макросов находится в пункте меню «Файл – Дополнительные параметры – Настройки макросов» (для возврата к варианту по умолчанию выберите «Показывать уведомления»).

Заключение

Разумеется, этот туториал посвящен только базовым возможностям настройки макросов для таблиц в Onlyoffice и "Р7 офис". Вот еще несколько полезных ссылок:

Очень важная ссылка: содержит возможные значения некоторых совершенно неочевидных свойств. https://support.r7-office.ru/using-api-document-builder/global-2/
Официальный (но более скудный) туториал можно найти здесь https://api.onlyoffice.com/docs/plugin-and-macros/macros/getting-started/ (на английском языке) и здесь https://support.r7-office.ru/category/desktop_editors/plugins-and-macros/macros-plugins-and-macros/ (на русском).
Для тех, кто хочет знать больше, есть документация: https://api.onlyoffice.com/docs/office-api/usage-api/spreadsheet-api/api/ (на английском языке) и https://support.r7-office.ru/category/using-api-document-builder/api-tables/ (на русском).
Если вам и этого недостаточно, всегда можно скопировать исходный код с https://github.com/ONLYOFFICE/sdkjs (в этом репозитории я нашел все, что связано с Javascript API, возможно, вам пригодятся и другие репозитории Onlyoffice) и посмотреть все, что вам нужно.

Спасибо за внимание!

Frontender`s Spectre

Еще раз про отличия Excel Vba vs OnlyOffice/Р7 офис JavaScript

Onlyoffice и Р7 офис: макросы на Javascript (туториал)

Ищем замену Excel — OnlyOffice/Р7