Тестирование производительности таблиц офисных пакетов в Linux на примере MS Office, LibreOffice, МойОфис, OnlyOffice
Актуальность темы обусловлена форсированием перехода Государственных ведомств и госкомпаний России с Windows на Astra Linux. Это было сделано на фоне заявления корпорации Microsoft о сворачивании бизнеса в РФ. Так как у автора, да и у многих других пользователей MS Office, за долгие годы работы скопилось ряд наработок, то вопрос об их работоспособности и переносе под новый офисный пакет, на платформе Linux, стоит остро, как и возможность малой автоматизации рутинных действий.
1. Введение
Настоящая статья есть субъективный взгляд автора и попытка сравнить офисные продукты, представленные на рынке, доступные обычному пользователю.
Все ссылки на файлы, созданные в ходе подготовки этой статьи, будут приведены в конце статьи. В связи с обилием графики в тексте большая часть картинок, а так же тексты макросов, убраны под спойлер.
У каждого инженера со стажем рано или поздно скапливается множество программок, табличек, макросов упрощающих или облегчающих работу. Однако учитывая тот факт, что на законодательном уровне принято решение о прекращении закупок импортного ПО для критической информационной инфраструктуры и набирающие обороты процессы импортозамещения, на мой взгляд, пришло время поговорить о том, как рядовой инженер, скажем инженер-сметчик/инженер-ПТО сможет перевести часть своего легаси (устаревший код, который больше не поддерживается, не обновляется, но используется) на один из предлагающихся к импортозамещению офисных пакетов.
2. Сравнение и тестирование функциональности и производительности Офисных пакетов в Linux
2.1 Информация об оборудовании на котором будет происходить тестирование
Тестирование производительности я произвел на ноутбуке Hasee ZX7-CT5DA (он же Clevo N957TC), обзор которого я уже делал ранее на данном ресурсе, с предустановленным гипервизором Proxmox, в виртуальной машине с проброшенной видеокартой. При этом гостевой операционной системой для тестов у меня используется полюбившийся мне Debian 11.3 non-free
Характеристики ноутбука:
ЦП — Intel Core i7–8700
ВК — GTX 1660Ti
ОЗУ — 16 GB
Установленные диски — 512GB Phison SSD SATA m.2/1TB Kingston KC2500 SSD NVMe m.2/250GB Samsung EVO 850 SSD SATA 2.5»
Дисплей — 15,6 ''IPS 45% NTSC
Настройки виртуальной машины
ProxMox работает на 1TB Kingston KC2500 SSD NVMe m.2, на нем же хранятся файлы виртуальных машин. Несмотря на проброшенный диск 512GB Phison SSD SATA m.2 в тестировании он участвовать не будет и служит лишь для обмена данными между виртуальной машиной и хостом (устройство, предоставляющее сервисы, выступающее сервером).
2.2 Краткая характеристика тестируемого ПО
MicroSoft Office 2010, установленный при помощи PlayOnLinux в Wine. Ныне устаревший офисный пакет от MicroSoft будет выступать в качестве системы для легаси, который необходимо будет перенести в другой офисный пакет по результатам тестов. 2010-я версия выбрана лишь только потому что, в моем случае, это последняя версия, которая устанавливается без проблем в PlayOnLinux на моей конфигурации в Debian 11. В общем и целом в представлении не нуждается.
LibreOffice 7.0.4.2 — стандартный офисный пакет, который шел в комплекте с Debian 11.
Кроссплатформенный, свободно распространяемый офисный пакет с открытым исходным кодом, созданный как ответвление OpenOffice.org в 2010 году. Разрабатывается сообществом из более чем 480 программистов под эгидой некоммерческого фонда The Document Foundation за счёт пожертвований отдельных лиц и организаций.
по заверениям Wikipedia
МойОфис Домашняя версия 2022.01. Сборка 4 — по моему скромному мнению офис, менеджеры которого наиболее активны в PR’е на различных площадках с целью продвижения своего продукта.
Экосистема приложений для совместной работы с документами с возможностью интеграции в закрытые облачные инфраструктуры и специализированные информационные системы. Разработчик «Новые облачные технологии» Продукты МойОфис включены в Единый реестр российских программ для электронных вычислительных машин и баз данных, полностью соответствуют законодательству РФ и дополнительным требованиям к офисному ПО, согласно постановлению Правительства №325 от 23 марта 2017 года. Приложения ориентированы на коммерческих и государственных заказчиков, сертифицированы на соответствие требованиям по информационной безопасности ФСТЭК, ФСБ и МО РФ и могут применяться для работы с конфиденциальной информацией и сведениями, составляющими государственную тайну.
по заверениям Wikipedia
OnlyOffice Desktop Editors версия 7.1.0.215
Офисный пакет с открытым исходным кодом, разработанный компанией Ascensio System SIA с головным офисом в Риге (Латвия). Решение включает в себя систему для управления документами, проектами, взаимоотношениями с клиентами и электронной почтой.
Доступны облачная версия и версия для развертывания в локальной сети. Кроме того, редакторы ONLYOFFICE интегрируются с популярными платформами для совместной работы, включая ownCloud, Nextcloud, SharePoint и другими. ONLYOFFICE является официальным технологическим партнером Nextcloud, ownCloud, SeaFile, Pydio, eXo Platform и XWiki.
по заверениям Wikipedia
Другие офисные программы в рамках данной статьи расматриваться не будут по разным причинам, например потому что WPS Office в бесплатной версии не имеет макросов, зато заявлена поддержка VBA, OpenOffice не встает в систему рядом с LibreOffice и требует его полного удаления, Р7-Офис — мои земляки, требует обязательную регистрацию что, на мой взгляд, совершенно неоправданно с т.з. популяризации ПО.
По сути из Российского ПО здесь только МойОфис, наверное на этом можно можно было бы и закончить не начав, но все же я продолжу, потому что и OnlyOffice открыл код редакторов под лицензией GNU AGPL v. 3, и LibreOffice под общественной лицензией MPL 2.0, а значит есть все основания сравнивать Российское ПО с открытым.
2.3 Постановка задачи
Производится Сравнение и тестирование Табличных редакторов Офисных пакетов будет с точки зрения удобства и функциональности программирования простых, но крайне необходимых макросов, которые будут автоматизировать работу в части формирования и заполнения шаблонных документов, а так же будет частично затронут вопрос работоспособности ключевых формул, которые будут помогать в форматировании и подготовке таких документов.
Иными словами рассмотрим возможность переписать программу, написанную мной в 2019-м году, для MS Excel при помощи VBA, для тех кто хочет заполнять файлы по своим шаблонам, с открытым VBA кодом. Выбор тестов продиктован желанием повторить все то, что мною было сделано несколько лет назад в рамках малой автоматизации, по заполнению шаблонов документов заранее подготовленными данными, с открытым VBA кодом.
2.4 Интерфейс и формулы
Далее будут приводится скриншоты для каждой операции каждого офисного табличного процессора из указанных выше офисных пакетов: MS Excel, LibreOffice Calc, МойОфис Таблицы Домашняя версия, OnlyOffice Таблица.
Последующий текст будет ориентирован на продвинутого пользователя программного пакета MS Office для русскоязычных пользователей. В конце статьи будет ссылка на файлы, в которых будут хранится все этапы тестов, для тех кто захочет попробовать повторить и сравнить на своих ПК.
2.4.1 Именованные диапазоны
Именованный диапазон это ячейка/диапазон ячеек, которому присвоено имя. Применяется в формулах, для определения границ печати (в MS Excel для выводимого диапазона на печать всегда присваивается имя диапазону Область_печати), а так же для выпадающих списков, но это мы немного забегаем вперед.
Для того что бы создать Именованный диапазон необходимо:
а) в MS Excel открыть вкладку «Формулы» и выбрать пункт «Диспетчер имен»
б) в LibreOffice Calc выделить диапазон данных, открыть меню «Данные» и выбрать пункт «Задать диапазон…»
в) в МойОфис Таблицы Домашняя версияможно использовать именованные диапазоны, созданные только с помощью сторонних программ, по состоянию на июнь 2022 года.
г) в OnlyOffice Таблица открыть вкладку «Формула», выбрать меню «Именованные диапазоны» и выбрать пункт «Диспетчер имен»
2.4.2 Выпадающий список в ячейке
Выпадающий список в ячейке служит, с одной стороны, для удобства Пользователя при выборе регламентированных вариантов ответа, с другой стороны, исключаются случайные ошибки при заполнении значениями там где это важно. При этом будьте аккуратными, потому что если ссылаться напрямую на диапазон ячеек отличный от текущего листа, то в MS Office 2007 и младше приводит к сбросу настроек выпадающего списка в ячейке через непродолжительное время. Но для MS Office 2013 и старше это уже не так актуально, поэтому для порядка и удобства ссылки на такие списки лучше делать через Именованный диапазон.
Для того что бы создать Выпадающий список в ячейке необходимо:
а) в MS Excel открыть вкладку «Данные» и выбрать пункт «Проверка данных»
б) в LibreOffice Calc открыть меню «Данные» и выбрать пункт «Проверка…»
в) в МойОфис Таблицы Домашняя версия, по состоянию на июнь 2022 года, выпадающий список своими средствами или сделанный в другом офисном пакете, сделать/задействовать невозможно.
г) в OnlyOffice Таблица открыть вкладку «Данные» и выбрать пункт «Проверка данных».
2.4.3 Условное форматирование
Условное форматирование (цветовая раскраска и форматирование текста) применяют тогда и там, когда привносят в таблицы элемент дизайна и наглядности, и когда необходимо выделить ошибки/выбивающиеся значение и т.д. и т.п., т.е. все то на что необходимо заострить/обратить внимание. Такое форматирование ячеек происходит по различным наборам условий.
Для того что бы создать Условное форматирование в ячейке необходимо:
а) в MS Excel выделить диапазон ячеек, открыть вкладку «Главная», открыть меню «Условное форматирование» и выбрать пункт «Создать правило…»
б) в LibreOffice Calc выделить диапазон ячеек, открыть меню «Формат», открыть подменю «Условное» и выбрать пункт «Условие…»
в) в МойОфис Таблицы Домашняя версия, по состоянию на июнь 2022 года, функционал Условного форматирования своими средствами сделать невозможно, но файлы созданные в других офисных программах отрабатываются корректно.
г) в OnlyOffice Таблица выделить диапазон ячеек, открыть вкладку «Главная», открыть меню «Условное форматирование» и выбрать пункт «Новое правило»
2.4.4 Получить диапазон области печати текущего листа формулой
Этот пункт и следующий будут из разряда колдунства. Такие функции необходимы в том случае, если Вам необходимо решать вопросы форматирования таблица/шаблона с учетом вывода на печать/принтер. Для этого используется формула MS Excel, которая для текущего листа выводит адрес диапазона печати в формате диапазона ячеек. Как мы помним MS Excel хранит диапазоны печати в именованных диапазонах «Область_печати», а так же оперативно отслеживает ее изменение в случае изменения границ области печати. =СЦЕПИТЬ(АДРЕС(СТРОКА(Область_печати);СТОЛБЕЦ(Область_печати);1;1);":";АДРЕС(СТРОКА(Область_печати)+ЧСТРОК(Область_печати)-1;СТОЛБЕЦ(Область_печати)+ЧИСЛСТОЛБ(Область_печати)-1;1;1))
После ввода формулы в ячейке:
а) в MS Excel обязательно настроить область печати на текущем листе и радоваться рабочей формуле. При настраивании области печати автоматически создается именованный диапазон с именем «Область_печати»
б) в LibreOffice Calc данный способ не работает, в этом табличном процессоре не поддерживается функция MS Excel ЧИСЛСТОЛБ, а LibreOffice Calc при задании границ печати не создает именованный диапазон со значениями для печати и хранит данные в другом виде. Если загрузить предварительно сохраненный рабочий файл в MS Excel, то формула отразит последнее значение отображенное в таблице при закрытии в MS Excel. Соответственно этот вариант не работает, т.е. нужен другой подход.
в) в МойОфис Таблицы Домашняя версия после ввода формулы в ячейке и задания границ печати получаем ошибку, т.к. у офисного пакета МойОфис сложные отношения с Именованными диапазонами. Если загрузить предварительно сохраненный рабочий файл в MS Excel, то мы получим нерабочую формулу.
г) в OnlyOffice Таблица обязательно настроить область печати на текущем листе и радоваться рабочей формуле.
2.4.5 Перенос текста формулами массива
Необходимость в переносе возникает тогда, когда текст должен быть разнесен по разным строкам, не путем изменения высоты ячейки, а именно быть разбит на несколько ячеек, да и еще с учетом максимально возможного количества отображаемых символов при печати
=ПСТР(A1;1;105-ПОИСКПОЗ(" *";ПРАВСИМВ(ПСТР(A1;1;105);СТРОКА($B$1:$C$104));))Вторая и последующие строки A4 и далее (протянуть):
=ПСТР(A$1;СУММ(ДЛСТР(A$3:A3))+2;105-2-ПОИСКПОЗ(" *";ПРАВСИМВ(ПСТР(A$1;СУММ(ДЛСТР(A$3:A3));105);СТРОКА($B$1:$C$104));))Вводить формулы только через комбинацию клавиш Ctrl+Shift+Enter (в случае MS Excel)
Здесь:
— число 105 — максимальное число символов в строке, т.е. 105 символов;
— A1, ПСТР (A$1 — жесткая привязка на ячейку в которой содержится текст, который требуется разбить на несколько строк. При правках сохранять знак символа $;
— ДЛСТР (A$3: A3) — диапазон строк перед текущей строкой, необходим для определения начала позиции для копирования последующего блока текста. При правках сохранять знак символа $;
— СТРОКА ($B$1:$C$104) — малофункциональный блок, который лучше не трогать, но стоит иметь ввиду, что если Ваш блок будет после 104-й строки, то необходимо будет диапазон скорректировать в сторону увеличения до максимальной строки в Вашем блоке текста. При правках сохранять знак символа $
После ввода формул в ячейке:
а) в MS Excel в ячейку А1 забиваем очень длинную тестовую фразу, с количеством символов больше 105 в несколько раз и радуемся результату:
б) в LibreOffice Calc данный способ не работает, т.к. он не знает функцию ПРАВСИМВ, а так же мне сходу не удалось запустить эти формулы ни через одну из комбинаций клавиш. Если загрузить предварительно сохраненный рабочий файл в MS Excel, то формула так же не работает. Соответственно нужен другой подход.
в) в МойОфис Таблицы Домашняя версия после ввода формулы в ячейки ведет себя так же как и LibreOffice Calc, с той лишь разницей, что у МойОфис нет проблем с функцией ПРАВСИМВ.
г) в OnlyOffice Таблица после ввода формулы в ячейки ведет себя так же как и LibreOffice Calc и МойОфис Таблицы Домашняя версия. Если загрузить предварительно сохраненный рабочий файл в MS Excel, то первоначально отображает вид как перед закрытием, но в случае правок в ячейке А1 пересчет формул заканчивается ошибкой.
2.4.6 Открытие файлов с большим количеством строк
Достаточно простой тест на ресурсоемкость. Давайте представим что у нас есть файл с большим числом строк данных, выгруженных из условной 1С, для чего смоделируем утрированную нагрузку, для этого:
а) в MS Excel в ячейку A1 вводим арабскую единицу, в соседнюю ячейку B1 вводим формулу »=A1+1», протягиваем ячейку B1 вправо до тех пор, пока не будет цифра 100, а затем копируем формулы на 11750 строк, получаем файл размером 9,1Мб и радуемся результату:
При этом сам MS Excel в оперативной памяти занимает порядка 251 308 КиБ, прокрутка работает быстро и плавно, а так же ничего не тормозит.
б) в LibreOffice Calc открытие того же самого файла осуществляется быстро, прокрутка осуществляется быстро и плавно, а так же ничего не тормозит. В оперативной памяти LibreOffice Calc занимает порядка 701 254 КиБ.
в) в МойОфис Таблицы Домашняя версия открытие того же файла началось с вывода окна ожидания, прокрутка осуществляется с заметными глазу небольшими задержками, скролинг осуществляется неудобно из-за постоянных подгрузок, в оперативной памяти МойОфис Таблицы Домашняя версия занимает порядка 1 858 726 КиБ.
г) в OnlyOffice Таблица открытие того же файла осуществляется быстро, прокрутка происходит быстро и плавно, хотя на мой взгляд не такая плавная как LibreOffice Calc, в оперативной памяти OnlyOffice Таблица занимает порядка 618 188 КиБ.
2.5 Макросы
2.5.1 Краткий обзор редакторов Макросов в офисных пакетах
а) в MS Excel редактор VBA в представлении не нуждается. Visual Basic for Applications, на текущий момент, представляет собой немного упрощенную реализацию языка программирования Visual Basic 6.0. VBA, будучи языком, построенным на COM, позволяет использовать все доступные в операционной системе COM объекты и компоненты ActiveX.
VBA хорошо документирован. Справочная информация предоставлена как в локальном варианте, так и онлайн. Кроме того пользователи MS Office создали огромное число площадок, сайтов, групп, видео, книг по обучению и обмену опытом, в результате чего возможность программировать на VBA достаточно просто, т.к. относительно легко найти в сети подходящий пример, при условии что Вы правильно формулируете запрос.
Дополнительной возможностью, не мало важной для нас, является еще и тот факт, что выполнение макроса можно связать с кнопкой, которую можно расположить на рабочем листе книги, что так же повышает удобство.
б) в LibreOffice Calc используется LibreOffice Basic (LO Basic, LibreOffice Basic, OOo Basic, он же StarBasic), который в свою очередь частично совместим с VBA, но реализован иначе, из-за чего мы имеем разную производительность абсолютно идентичных макросов. Справочная информация предоставлена как в локальном варианте, так и онлайн, однако ресурсов посвященных этому BASIC’у намного меньше чем у VBA, тем не менее совместимость с VBA частично помогает решать вопросы.
Дополнительной возможностью, немаловажной для нас, является еще и тот факт, что выполнение макроса можно связать с кнопкой, которую можно расположить на рабочем листе книги, что так же повышает удобство.
в) в МойОфис Таблицы Домашняя версия в настоящий момент реализована поддержка Lua версии 5.3.2, который является свободно распространяемым, с открытым исходным кодом. На сколько я знаю, из широко известных программных продуктов, он так же используется для написания плагинов к FAR. Здесь по ссылке можно скачать справочник макрокоманд. В настоящий момент в рунете сообществ посвященных программированию на Lua только у FAR’а. Редактор имеет спартанский интерфейс, но есть поддержка, правда в тестовом режиме, записи макросов. Назначить кнопку для запуска макроса нельзя.
г) в OnlyOffice Таблица используется JavaScript API. Документация по нему… доступна на сайте разработчика. Интерфейс редактора макросов — спартанский, записи макросов нет. Назначить кнопку для запуска макроса нельзя.
2.5.2 Пишем тестирующий производительность макрос
Техзадание на макрос:
Тестовый этап №1 — в первую очередь нас интересует производительность операций ввода-вывода в ячейки. Для чего на каждом языке программирования будет написан код, который будет заполнять матрицу 1000×1000 ячеек случайными числами;
Тестовый этап №2 — затем будет осуществляться чтение с заполненного листа в массив (память);
Тестовый этап №3 — после чего макросом осуществим запись заполненного листа в 10 файлов на жесткий диск;
Все три тестовых этапа должны будут сопровождаться фиксацией времени выполнения каждого этапа, собственно это время мы и будем сравнивать в рамках статьи. Таким образом, мы протестируем операции ввода-вывода.
Ну, а теперь более развернуто по каждому из офисных пакетов.
2.6.2.1 Макрос VBA в MS Excel
Rem -= Заполняем область на Лист1 1000х1000 ячеек случайными значениями до 1000 =-
Dim wb As Workbook
Dim x As Integer, y As Integer
Set wb = ThisWorkbook
wb.Sheets("Лист1").Cells(1, "B") = Format(time, "hh:mm:ss")
Randomize time
For x = 1 To 1000 Step 1
For y = 1 To 1000 Step 1
wb.Sheets("Лист1").Cells(y + 6, x) = rnd * 1000
Next y
Next x
wb.Sheets("Лист1").Cells(2, "B") = Format(time, "hh:mm:ss")
Rem -= Читаем область на Лист1 1000х1000 ячеек в массив в память устройства =-
Dim q(1000, 1000) As Single
For x = 1 To 1000 Step 1
For y = 1 To 1000 Step 1
Let q(x, y) = wb.Sheets("Лист1").Cells(y + 6, x)
Next y
Next x
wb.Sheets("Лист1").Cells(4, "B") = Format(time, "hh:mm:ss")
Rem -= Сохраняем Лист1 на жесткий диск в виде MS Excel фала 10 раз =-
Dim ИмяФайла As String
Application.DisplayAlerts = False
For x = 1 To 10 Step 1
Let ИмяФайла = ThisWorkbook.Path + "\" + "Test"
Let ИмяФайла = ИмяФайла + CStr(x) + ".xlsx"
wb.Sheets("Лист1").Copy
ActiveWorkbook.SaveAs Filename:=ИмяФайла, _
FileFormat:=51
ActiveWindow.Close
Next x
Application.DisplayAlerts = True
wb.Sheets("Лист1").Cells(6, "B") = Format(time, "hh:mm:ss")
Собственно, здесь все просто и без выкрутасов. Два вложенных цикла, в которых перебираются ячейки по X и Y, сперва заполняя случайными числами, а потом считывая их в массив. Сохранение тоже без каких-либо неожиданностей. Результат выполнения макроса в приведенном скриншоте.
2.6.2.2 Макрос LibreOffice Basic в LibreOffice Calc
Rem -= Заполняем область на Лист1 1000х1000 ячеек случайными значениями до 1000 =-
Dim wb As Workbook
Dim x As Integer, y As Integer
Set wb = ThisWorkbook
wb.Sheets("Лист1").Cells(1, "B") = Format(time, "hh:mm:ss")
Randomize time
For x = 1 To 1000 Step 1
For y = 1 To 1000 Step 1
wb.Sheets("Лист1").Cells(y + 6, x) = rnd * 1000
Next y
Next x
wb.Sheets("Лист1").Cells(2, "B") = Format(time, "hh:mm:ss")
Rem -= Читаем область на Лист1 1000х1000 ячеек в массив в память устройства =-
Dim q(1000, 1000) As Single
For x = 1 To 1000 Step 1
For y = 1 To 1000 Step 1
Let q(x, y) = wb.Sheets("Лист1").Cells(y + 6, x)
Next y
Next x
wb.Sheets("Лист1").Cells(4, "B") = Format(time, "hh:mm:ss")
Rem -= Сохраняем Лист1 на жесткий диск в виде MS Excel фала 10 раз =-
Dim ИмяФайла As String
Application.DisplayAlerts = False
For x = 1 To 10 Step 1
Let ИмяФайла = ThisWorkbook.Path + "\" + "Test"
Let ИмяФайла = ИмяФайла + CStr(x) + ".xlsx"
wb.Sheets("Лист1").Copy
ActiveWorkbook.SaveAs Filename:=ИмяФайла, _
FileFormat:=51
ActiveWindow.Close
Next x
Application.DisplayAlerts = True
wb.Sheets("Лист1").Cells(6, "B") = Format(time, "hh:mm:ss")
Несмотря на то что VBA и LibreOffice Basic имеют отличия, в этом фрагменте макрос LibreOffice Basic идеально повторяет код для VBA. Так же все просто и без выкрутасов. Два вложенных цикла, в которых перебираются ячейки по X и Y, сперва заполняя случайными числами, а потом считывая их в массив. Сохранение тоже без каких-либо неожиданностей. Результат выполнения макроса в приведенном скриншоте. Заметна разница в операциях заполнения/чтения ячеек в файле, по сравнению с VBA, при чем на порядок, но тут же в 2,5 раза выше скорость доступа к диску, при записи файлов.
2.6.2.3 Макрос Lua в МойОфис Таблица Домашняя версия
Предвосхищая удивленный возглас — да, здесь все сложно. К сожалению мне не удалось разобраться с записью макросом таблиц в несколько файлов, а так же с передачей времени в переменную. Тут меня ждало фиаско: Lua позволяет работать со временем, т.е. можно получить текущее время в переменную и записать его в ячейку, но проблема в том, что os.time () здесь не работает, а при использовании DocumentAPI.DateTime из инструкции макрос выдает ошибку: либо nil, либо пусто, если прописать его в переменную с конвертацией типа в текст. Примеров по использованию этого API в сети нет. Соответственно все замеры времени сделаны на секундомере смартфона и содержат некоторую погрешность, в отличие от других случаев оценки времени для других Офисов в статье. Каюсь, ибо грешен.
Продолжим, если совершить действие по вставке в ячейку любого числа и записать его, то мы получим монструозный код.
-- This macros is generated by the MacroRecorder feature.
function changeTableSelection(anchorOffsetRow, anchorOffsetColumn, cursorOffsetRow, cursorOffsetColumn)
local selection = EditorAPI.getSelection()
local tbl = selection:getTable()
local beginRow = selection:getBeginRow() + anchorOffsetRow
local beginColumn = selection:getBeginColumn() + anchorOffsetColumn
local lastRow = cursorOffsetRow and selection:getLastRow() + cursorOffsetRow or beginRow
local lastColumn = cursorOffsetColumn and selection:getLastColumn() + cursorOffsetColumn or beginColumn
assert(beginRow >= 0 and beginRow <= lastRow, 'Selection goes out of the top border of the sheet')
assert(beginColumn >= 0 and beginColumn <= lastColumn, 'Selection goes out of the left border of the sheet')
if (lastRow >= tbl:getRowsCount()) then
tbl:insertRowAfter(tbl:getRowsCount() - 1, false, lastRow - tbl:getRowsCount() + 1)
end
if (lastColumn >= tbl:getColumnsCount()) then
tbl:insertColumnAfter(tbl:getColumnsCount() - 1, false, lastColumn - tbl:getColumnsCount() + 1)
end
local position = DocumentAPI.CellRangePosition(beginRow, beginColumn, lastRow, lastColumn)
EditorAPI.setSelection(tbl:getCellRange(position))
end
function updateFormulaInCurrentCell(value)
local selection = EditorAPI.getSelection()
local cellPos = DocumentAPI.CellPosition(selection:getBeginRow(), selection:getBeginColumn())
selection:getTable():getCell(cellPos):setContent(value)
end
--main
changeTableSelection(5, 2, nil, nil)
updateFormulaInCurrentCell('1')
EditorAPI.changeSelection(EditorAPI.SelectionMode.Move, EditorAPI.SelectionDirection.Down, EditorAPI.TableSelectionUnit.ToClosestCell)
Здесь монструозная текстом функция changeTableSelection вызывает смещение относительно текущей позиции курсора на задаваемое число ячеек по строкам и столбцам, а EditorAPI.changeSelection(EditorAPI.SelectionMode.Move, EditorAPI.SelectionDirection.Down, EditorAPI.TableSelectionUnit.ToClosestCell) сдвиг курсора после ввода на одну ячейку вниз. Собственно на этой механике постоянного перемещения курсора и строится адресация записи в ячейку, сама же запись происходит через функцию updateFormulaInCurrentCell. Пользуясь этим мы пишем Первый вариант макроса на заполнение поля 1000 на 1000 ячеек.
-- This macros is generated by the MacroRecorder feature.
function changeTableSelection(anchorOffsetRow, anchorOffsetColumn, cursorOffsetRow, cursorOffsetColumn)
local selection = EditorAPI.getSelection()
local tbl = selection:getTable()
local beginRow = selection:getBeginRow() + anchorOffsetRow
local beginColumn = selection:getBeginColumn() + anchorOffsetColumn
local lastRow = cursorOffsetRow and selection:getLastRow() + cursorOffsetRow or beginRow
local lastColumn = cursorOffsetColumn and selection:getLastColumn() + cursorOffsetColumn or beginColumn
assert(beginRow >= 0 and beginRow <= lastRow, 'Selection goes out of the top border of the sheet')
assert(beginColumn >= 0 and beginColumn <= lastColumn, 'Selection goes out of the left border of the sheet')
if (lastRow >= tbl:getRowsCount()) then
tbl:insertRowAfter(tbl:getRowsCount() - 1, false, lastRow - tbl:getRowsCount() + 1)
end
if (lastColumn >= tbl:getColumnsCount()) then
tbl:insertColumnAfter(tbl:getColumnsCount() - 1, false, lastColumn - tbl:getColumnsCount() + 1)
end
local position = DocumentAPI.CellRangePosition(beginRow, beginColumn, lastRow, lastColumn)
EditorAPI.setSelection(tbl:getCellRange(position))
end
function updateFormulaInCurrentCell(value)
local selection = EditorAPI.getSelection()
local cellPos = DocumentAPI.CellPosition(selection:getBeginRow(), selection:getBeginColumn())
selection:getTable():getCell(cellPos):setContent(value)
end
--main
-- перед стартом поместить курсор в ячейку А1
for x=1, 1000, 1 do
for y=1, 1000, 1 do
updateFormulaInCurrentCell(math.random()*1000)
EditorAPI.changeSelection(EditorAPI.SelectionMode.Move, EditorAPI.SelectionDirection.Down, EditorAPI.TableSelectionUnit.ToClosestCell)
end
changeTableSelection(-1000, 1, nil, nil)
end
Тут нужно сказать еще об одном нюансе. МойОфис таблицы не позволяет иметь на листе кол-во столбцов больше 1000 (ALL). Что наводит на некоторые мысли связанные с ограничениями работы ПО.
Однако я отвлекся, т.к. время выполнения макроса было слишком большое, то мне пришлось оценивать скорость по выполнению лишь одной его части — записи одного столбца. При заполнении 1-го столбца на 1000 ячеек случайными числами от 1 до 1000 тратится время 1 мин. 54 сек. Таким образом поле 1000×1000 ячеек будет заполнено за время =(60+54)сек/столбец * 1000 столбцов / 60сек / 60 мин = 31,67час.
Однако в инструкции есть другой метод адресации для записи в ячейку через getCell(DocumentAPI.CellPosition(y, х)). Пишем теперь уже совсем простенький макрос:
local tbl = document:getBlocks():getTable(1)
for x=0, 1000, 1 do
for y=0, 1000, 1 do
tbl:getCell(DocumentAPI.CellPosition(y, x)):setNumber(math.random()*1000)
end
end
Что выглядит значительно проще для восприятия и логики работы, но при этом… Оценка времени велась так же: при заполнении 1-го столбца на 1000 ячеек случайными числами от 1 до 1000 тратится время 6 минут. Таким образом поле 1000×1000 ячеек будет заполнено за время = 6 мин/столбец * 1000 столбцов / 60 мин = 100час. Видимо из-за этой разницы функционал записи макроса генерирует не этот код, а другой, более объемный.
Дальше пишем макрос на чтение поля 1000×1000 ячеек в массив.
local tbl = document:getBlocks():getTable(1)
Arr = {}
for x=1, 1000, 1 do
Arr[x] = {}
for y=1, 1000, 1 do
Arr[x][y]=tostring(tbl:getCell(DocumentAPI.CellPosition(y-1, x-1)):getFormattedValue());
end
end
Который выполняется за… 12 секунд. С чем связана такая разница между временем записи данными в ячейки и считыванием их в память на Lua в МойОфис — хороший вопрос!
При беглом знакомстве был выявлен минус работы с макросами это то, что область таблицы недоступна для навигации и правок, пока открыт редактор макросов. В том же MS Excel можно без проблем переключаться между таблицей и редактором. Так же, в отличие от того же редактора VBA, не исправляется регистр букв в соответствии с внутренними представлениями о прекрасном. Зато тут есть консоль в которую выводятся как ошибки выполнения, так и через print() содержание переменных, что удобно при отладке. Работа с массивами непривычна, т.к. они не жесткие, как я привык по Basic, Pascal, C. В сети мало информации и примеров. Документация на сайте представлена всего лишь одной инструкцией, которая хоть и идет с примерами, но их явно недостаточно. В сети нет сообществ по обмену опытом программирования макросами Lua в МойОфис, но, справедливости ради, есть сообщество по Lua, что не одно и то же. Нет жесткой типизации переменных. В общем это другая парадигма отличная от той что меня учили в 90-е… может это и хорошо, но в целом непривычно.
2.6.2.4 Макрос JS в OnlyOffice Desktop Editors
© Habrahabr.ru
