Актуальность темы обусловлена форсированием перехода Государственных ведомств и госкомпаний России с 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. В общем и целом в представлении не нуждается.
Внешний вид MicroSoft Office
LibreOffice 7.0.4.2 — стандартный офисный пакет, который шел в комплекте с Debian 11.
Кроссплатформенный, свободно распространяемый офисный пакет с открытым исходным кодом, созданный как ответвление OpenOffice.org в 2010 году. Разрабатывается сообществом из более чем 480 программистов под эгидой некоммерческого фонда The Document Foundation за счёт пожертвований отдельных лиц и организаций.по заверениям Wikipedia
Внешний вид и Версия LibreOffice
МойОфис Домашняя версия 2022.01. Сборка 4 — по моему скромному мнению офис, менеджеры которого наиболее активны в PR'е на различных площадках с целью продвижения своего продукта.
Экосистема приложений для совместной работы с документами с возможностью интеграции в закрытые облачные инфраструктуры и специализированные информационные системы. Разработчик «Новые облачные технологии» Продукты МойОфис включены в Единый реестр российских программ для электронных вычислительных машин и баз данных, полностью соответствуют законодательству РФ и дополнительным требованиям к офисному ПО, согласно постановлению Правительства №325 от 23 марта 2017 года. Приложения ориентированы на коммерческих и государственных заказчиков, сертифицированы на соответствие требованиям по информационной безопасности ФСТЭК, ФСБ и МО РФ и могут применяться для работы с конфиденциальной информацией и сведениями, составляющими государственную тайну.по заверениям Wikipedia
Внешний вид и Версия МойОфис
OnlyOffice Desktop Editors версия 7.1.0.215
Офисный пакет с открытым исходным кодом, разработанный компанией Ascensio System SIA с головным офисом в Риге (Латвия). Решение включает в себя систему для управления документами, проектами, взаимоотношениями с клиентами и электронной почтой.по заверениям Wikipedia
Доступны облачная версия и версия для развертывания в локальной сети. Кроме того, редакторы ONLYOFFICE интегрируются с популярными платформами для совместной работы, включая ownCloud, Nextcloud, SharePoint и другими. ONLYOFFICE является официальным технологическим партнером Nextcloud, ownCloud, SeaFile, Pydio, eXo Platform и XWiki.
Внешний вид и Версия OnlyOffice
Другие офисные программы в рамках данной статьи расматриваться не будут по разным причинам, например потому что 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 открыть вкладку «Формулы» и выбрать пункт «Диспетчер имен»
Скриншот окна в MS Excel
б) в LibreOffice Calc выделить диапазон данных, открыть меню «Данные» и выбрать пункт «Задать диапазон...»
Скриншот окна в LibreOffice Calc
в) в МойОфис Таблицы Домашняя версия можно использовать именованные диапазоны, созданные только с помощью сторонних программ, по состоянию на июнь 2022 года.
Скриншот окна в МойОфис Таблицы Домашняя версия
г) в OnlyOffice Таблица открыть вкладку «Формула», выбрать меню «Именованные диапазоны» и выбрать пункт «Диспетчер имен»
Скриншот окна в OnlyOffice Таблица
2.4.2 Выпадающий список в ячейке
Выпадающий список в ячейке служит, с одной стороны, для удобства Пользователя при выборе регламентированных вариантов ответа, с другой стороны, исключаются случайные ошибки при заполнении значениями там где это важно. При этом будьте аккуратными, потому что если ссылаться напрямую на диапазон ячеек отличный от текущего листа, то в MS Office 2007 и младше приводит к сбросу настроек выпадающего списка в ячейке через непродолжительное время. Но для MS Office 2013 и старше это уже не так актуально, поэтому для порядка и удобства ссылки на такие списки лучше делать через Именованный диапазон.
Для того что бы создать Выпадающий список в ячейке необходимо:
а) в MS Excel открыть вкладку «Данные» и выбрать пункт «Проверка данных»
Скриншот окна в MS Excel
б) в LibreOffice Calc открыть меню «Данные» и выбрать пункт «Проверка...»
Скриншот окна в LibreOffice Calc
в) в МойОфис Таблицы Домашняя версия, по состоянию на июнь 2022 года, выпадающий список своими средствами или сделанный в другом офисном пакете, сделать/задействовать невозможно.
Скриншот окна в МойОфис Таблицы Домашняя версия
г) в OnlyOffice Таблица открыть вкладку «Данные» и выбрать пункт «Проверка данных».
Скриншот окна в OnlyOffice Таблица
2.4.3 Условное форматирование
Условное форматирование (цветовая раскраска и форматирование текста) применяют тогда и там, когда привносят в таблицы элемент дизайна и наглядности, и когда необходимо выделить ошибки/выбивающиеся значение и т.д. и т.п., т.е. все то на что необходимо заострить/обратить внимание. Такое форматирование ячеек происходит по различным наборам условий.
Для того что бы создать Условное форматирование в ячейке необходимо:
а) в MS Excel выделить диапазон ячеек, открыть вкладку «Главная», открыть меню «Условное форматирование» и выбрать пункт «Создать правило...»
Скриншот окна в MS Excel
б) в LibreOffice Calc выделить диапазон ячеек, открыть меню «Формат», открыть подменю «Условное» и выбрать пункт «Условие...»
Скриншот окна в LibreOffice Calc
в) в МойОфис Таблицы Домашняя версия, по состоянию на июнь 2022 года, функционал Условного форматирования своими средствами сделать невозможно, но файлы созданные в других офисных программах отрабатываются корректно.
Скриншот окна в МойОфис Таблицы Домашняя версия
г) в OnlyOffice Таблица выделить диапазон ячеек, открыть вкладку «Главная», открыть меню «Условное форматирование» и выбрать пункт «Новое правило»
Скриншот окна в OnlyOffice Таблица
2.4.4 Получить диапазон области печати текущего листа формулой
Этот пункт и следующий будут из разряда колдунства. Такие функции необходимы в том случае, если Вам необходимо решать вопросы форматирования таблица/шаблона с учетом вывода на печать/принтер. Для этого используется формула MS Excel, которая для текущего листа выводит адрес диапазона печати в формате диапазона ячеек. Как мы помним MS Excel хранит диапазоны печати в именованных диапазонах «Область_печати», а так же оперативно отслеживает ее изменение в случае изменения границ области печати.
=СЦЕПИТЬ(АДРЕС(СТРОКА(Область_печати);СТОЛБЕЦ(Область_печати);1;1);":";АДРЕС(СТРОКА(Область_печати)+ЧСТРОК(Область_печати)-1;СТОЛБЕЦ(Область_печати)+ЧИСЛСТОЛБ(Область_печати)-1;1;1))После ввода формулы в ячейке:
а) в MS Excel обязательно настроить область печати на текущем листе и радоваться рабочей формуле. При настраивании области печати автоматически создается именованный диапазон с именем «Область_печати»
Скриншот окна в MS Excel
б) в LibreOffice Calc данный способ не работает, в этом табличном процессоре не поддерживается функция MS Excel ЧИСЛСТОЛБ, а LibreOffice Calc при задании границ печати не создает именованный диапазон со значениями для печати и хранит данные в другом виде. Если загрузить предварительно сохраненный рабочий файл в MS Excel, то формула отразит последнее значение отображенное в таблице при закрытии в MS Excel. Соответственно этот вариант не работает, т.е. нужен другой подход.
Скриншот окна в LibreOffice Calc
в) в МойОфис Таблицы Домашняя версия после ввода формулы в ячейке и задания границ печати получаем ошибку, т.к. у офисного пакета МойОфис сложные отношения с Именованными диапазонами. Если загрузить предварительно сохраненный рабочий файл в MS Excel, то мы получим нерабочую формулу. 
Скриншот окна в МойОфис Таблицы Домашняя версия
г) в OnlyOffice Таблица обязательно настроить область печати на текущем листе и радоваться рабочей формуле.
Скриншот окна в OnlyOffice Таблица
2.4.5 Перенос текста формулами массива
Необходимость в переносе возникает тогда, когда текст должен быть разнесен по разным строкам, не путем изменения высоты ячейки, а именно быть разбит на несколько ячеек, да и еще с учетом максимально возможного количества отображаемых символов при печати
Формула + объяснение
Первая строка в A3:
Вторая и последующие строки A4 и далее (протянуть):
Вводить формулы только через комбинацию клавиш Ctrl+Shift+Enter (в случае MS Excel)
Здесь:
— число 105 – максимальное число символов в строке, т.е. 105 символов;
— A1, ПСТР(A$1 – жесткая привязка на ячейку в которой содержится текст, который требуется разбить на несколько строк. При правках сохранять знак символа $;
— ДЛСТР(A$3:A3) – диапазон строк перед текущей строкой, необходим для определения начала позиции для копирования последующего блока текста. При правках сохранять знак символа $;
— СТРОКА($B$1:$C$104) – малофункциональный блок, который лучше не трогать, но стоит иметь ввиду, что если Ваш блок будет после 104-й строки, то необходимо будет диапазон скорректировать в сторону увеличения до максимальной строки в Вашем блоке текста. При правках сохранять знак символа $
=ПСТР(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 в несколько раз и радуемся результату:
Скриншот окна в MS Excel
б) в LibreOffice Calc данный способ не работает, т.к. он не знает функцию ПРАВСИМВ, а так же мне сходу не удалось запустить эти формулы ни через одну из комбинаций клавиш. Если загрузить предварительно сохраненный рабочий файл в MS Excel, то формула так же не работает. Соответственно нужен другой подход.
Скриншот окна в LibreOffice Calc
в) в МойОфис Таблицы Домашняя версия после ввода формулы в ячейки ведет себя так же как и LibreOffice Calc, с той лишь разницей, что у МойОфис нет проблем с функцией ПРАВСИМВ.
Скриншот окна в МойОфис Таблицы Домашняя версия
г) в OnlyOffice Таблица после ввода формулы в ячейки ведет себя так же как и LibreOffice Calc и МойОфис Таблицы Домашняя версия. Если загрузить предварительно сохраненный рабочий файл в MS Excel, то первоначально отображает вид как перед закрытием, но в случае правок в ячейке А1 пересчет формул заканчивается ошибкой. 
Скриншот окна в OnlyOffice Таблица
2.4.6 Открытие файлов с большим количеством строк
Достаточно простой тест на ресурсоемкость. Давайте представим что у нас есть файл с большим числом строк данных, выгруженных из условной 1С, для чего смоделируем утрированную нагрузку, для этого:
а) в MS Excel в ячейку A1 вводим арабскую единицу, в соседнюю ячейку B1 вводим формулу "=A1+1", протягиваем ячейку B1 вправо до тех пор, пока не будет цифра 100, а затем копируем формулы на 11750 строк, получаем файл размером 9,1Мб и радуемся результату:
Скриншот окна в MS Excel
При этом сам MS Excel в оперативной памяти занимает порядка 251 308 КиБ, прокрутка работает быстро и плавно, а так же ничего не тормозит.
б) в LibreOffice Calc открытие того же самого файла осуществляется быстро, прокрутка осуществляется быстро и плавно, а так же ничего не тормозит. В оперативной памяти LibreOffice Calc занимает порядка 701 254 КиБ.
Скриншот окна в LibreOffice Calc
в) в МойОфис Таблицы Домашняя версия открытие того же файла началось с вывода окна ожидания, прокрутка осуществляется с заметными глазу небольшими задержками, скролинг осуществляется неудобно из-за постоянных подгрузок, в оперативной памяти МойОфис Таблицы Домашняя версия занимает порядка 1 858 726 КиБ.
Скриншот окна в МойОфис Таблицы Домашняя версия
г) в OnlyOffice Таблица открытие того же файла осуществляется быстро, прокрутка происходит быстро и плавно, хотя на мой взгляд не такая плавная как LibreOffice Calc, в оперативной памяти OnlyOffice Таблица занимает порядка 618 188 КиБ.
Скриншот окна в OnlyOffice Таблица
2.5 Макросы
2.5.1 Краткий обзор редакторов Макросов в офисных пакетах
а) в MS Excel редактор VBA в представлении не нуждается. Visual Basic for Applications, на текущий момент, представляет собой немного упрощенную реализацию языка программирования Visual Basic 6.0. VBA, будучи языком, построенным на COM, позволяет использовать все доступные в операционной системе COM объекты и компоненты ActiveX.
VBA хорошо документирован. Справочная информация предоставлена как в локальном варианте, так и онлайн. Кроме того пользователи MS Office создали огромное число площадок, сайтов, групп, видео, книг по обучению и обмену опытом, в результате чего возможность программировать на VBA достаточно просто, т.к. относительно легко найти в сети подходящий пример, при условии что Вы правильно формулируете запрос.
Дополнительной возможностью, не мало важной для нас, является еще и тот факт, что выполнение макроса можно связать с кнопкой, которую можно расположить на рабочем листе книги, что так же повышает удобство.
Скриншот окна в MS Excel
б) в LibreOffice Calc используется LibreOffice Basic (LO Basic, LibreOffice Basic, OOo Basic, он же StarBasic), который в свою очередь частично совместим с VBA, но реализован иначе, из-за чего мы имеем разную производительность абсолютно идентичных макросов. Справочная информация предоставлена как в локальном варианте, так и онлайн, однако ресурсов посвященных этому BASIC'у намного меньше чем у VBA, тем не менее совместимость с VBA частично помогает решать вопросы.
Дополнительной возможностью, немаловажной для нас, является еще и тот факт, что выполнение макроса можно связать с кнопкой, которую можно расположить на рабочем листе книги, что так же повышает удобство.
Скриншот окна в LibreOffice Calc
в) в МойОфис Таблицы Домашняя версия в настоящий момент реализована поддержка Lua версии 5.3.2, который является свободно распространяемым, с открытым исходным кодом. На сколько я знаю, из широко известных программных продуктов, он так же используется для написания плагинов к FAR. Здесь по ссылке можно скачать справочник макрокоманд. В настоящий момент в рунете сообществ посвященных программированию на Lua только у FAR'а. Редактор имеет спартанский интерфейс, но есть поддержка, правда в тестовом режиме, записи макросов. Назначить кнопку для запуска макроса нельзя.
Скриншот окна в МойОфис Таблицы Домашняя версия
г) в OnlyOffice Таблица используется JavaScript API. Документация по нему… доступна на сайте разработчика. Интерфейс редактора макросов — спартанский, записи макросов нет. Назначить кнопку для запуска макроса нельзя.
Скриншот окна в OnlyOffice Таблица
2.5.2 Пишем тестирующий производительность макрос
Техзадание на макрос:
Тестовый этап №1 — в первую очередь нас интересует производительность операций ввода-вывода в ячейки. Для чего на каждом языке программирования будет написан код, который будет заполнять матрицу 1000х1000 ячеек случайными числами;Тестовый этап №2 — затем будет осуществляться чтение с заполненного листа в массив (память);
Тестовый этап №3 — после чего макросом осуществим запись заполненного листа в 10 файлов на жесткий диск;
Все три тестовых этапа должны будут сопровождаться фиксацией времени выполнения каждого этапа, собственно это время мы и будем сравнивать в рамках статьи. Таким образом, мы протестируем операции ввода-вывода.
Ну а теперь более развернуто по каждому из офисных пакетов.
2.6.2.1 Макрос VBA в MS Excel
Макрос 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
Макрос 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 в сети нет. Соответственно все замеры времени сделаны на секундомере смартфона и содержат некоторую погрешность, в отличие от других случаев оценки времени для других Офисов в статье. Каюсь, ибо грешен.Продолжим, если совершить действие по вставке в ячейку любого числа и записать его, то мы получим монструозный код.
Макрос Lua в МойОфис Таблица Домашняя версия
-- 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 ячеек.
Первый вариант макроса на заполнение поля 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, х)). Пишем теперь уже совсем простенький макрос:
Второй вариант макроса на заполнение поля 1000 на 1000 ячеек.
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 ячеек в массив.
Чтение поля 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
Макрос JS в OnlyOffice Desktop Editors
var oWorksheet = Api.GetActiveSheet();
var arr = new Array(1001);
// Из-за отсутствия прямой цифровой адресации по колонкам обхожу ограничение через костыль
for (let x1 = 1; x1 <= 1001; x1++) {
let t="A" + x1;
arr[x1]=oWorksheet.GetRange(t).GetValue();
}
// Заполняем поле на листе 1000 х 1000 ячеек
var currentdate1 = new Date();
let t1 = currentdate1.getHours() + ":"
+ currentdate1.getMinutes() + ":"
+ currentdate1.getSeconds();
oWorksheet.GetRange("C1").SetValue(t1);
for (let y = 7; y <= 1007; y++) {
for (let x2 = 2; x2 <= 1001; x2++) {
oWorksheet.GetRange(arr[x2] + y).SetValue(Math.random()*1000);
}
oWorksheet.GetRange("D1").SetValue(y);
}
var currentdate2 = new Date();
let t2 = currentdate2.getHours() + ":"
+ currentdate2.getMinutes() + ":"
+ currentdate2.getSeconds();
oWorksheet.GetRange("C3").SetValue(t2);
// читаем поле на листе 1000 х 1000 ячеек в массив
var arr1 = Array(1001);
for (let y1 = 7; y1 <= 1007; y1++) {
oWorksheet.GetRange("D1").SetValue(y1);
for (let x3 = 1; x3 <= 1000; x3++) {
arr1[x3]= oWorksheet.GetRange(arr[x3] + y1).GetValue();
}
oWorksheet.GetRange("D1").SetValue(arr1[1000]);
}
var currentdate3 = new Date();
let t3 = currentdate3.getHours() + ":"
+ currentdate3.getMinutes() + ":"
+ currentdate3.getSeconds();
oWorksheet.GetRange("C4").SetValue(t3);
Здесь сразу возникли трудности, с частью из которых удалось разобраться благодаря справке на сайте OnlyOffice , другие вещи пришлось google'ить. В ряде случаев выручили примеры кода, так что справка очень даже полезная. Например из чтения документации я понял что в явном виде JS не поддерживает многомерные массивы и это нужно обходить, потом вышли накладки с повторным использованием переменных в коде, из-за чего пришлось их дублировать. Что не критично, но в то же время не совсем корректно. При адресации к ячейке необходимо прописывать адреса исключительно текстом, например «D7», в отличие от тех же Basic'ов/Lua где столбец-строка допускают в макросе использовать цифровую адресацию, что не удобно, но не критично и решается через дополнительный массив.
Какие-то моменты от меня ускользнули, т.к. это мой первый код на JS да и еще в виде макроса. К сожалению OnlyOffice при такой масштабной портянке значений 1000 х 1000 ячеек постоянно вис, вылетал, из-за чего код пришлось прогонять частями. При этом, насколько я понял из описания, поправьте если ошибаюсь, JS не позволяет макросом сохранить текущий лист в файл. Нет, OnlyOffice двигает функционал генерации документов с помощью ONLYOFFICE DocumentBuilder, здесь про него написано на официальном сайте, но это внешний модуль, благодаря которому программируя можно создавать новые файлы (однако придется прописывать весь текст и форматирование таким образом отдельным макросом), в то время как я планирую использовать уже готовые шаблоны, для чего открывать файлы с диска, производить манипуляции и сохранять как новый файл, таким образом исключая необходимость программировать стили оформления и заполняемый текст. Запросив поддержку я получил ответ в чате, что по десктопной версии они не консультируют и предложили пролистать несколько десятков страниц форумов и справки.
Еще один минус работы с макросами в ONLYOFFICE это то, что как и в МойОфис область таблицы недоступна для навигации и правок, пока открыт редактор макросов. В отличие от MS Excel, где можно без проблем переключаться между таблицей и редактором. Так же, в отличие от того же редактора VBA, не исправляется регистр букв в соответствии с внутренними представлениями о прекрасном. Зато есть всплывающая подсказка, предлагающая закончить вводимый текст, что удобно. Общие ошибки подсвечиваются, но комментарии по ним куцые. Часто код останавливает без вывода ошибок, если они связаны с логикой, и разобраться почему ты напортачил, т.е. подсмотреть по коду ошибки куда копать — нельзя.
Результат выполнения макроса в приведенном скриншоте. В операциях ввода-вывода на текущем листе макрос отрабатывает быстрее чем VBA, но не критично, но при этом файл на больших данных виснет. Сохранение такого файла занимает 20сек.
3. Выводы
3.1 Оценка производительности Офисных продуктов
Результаты тестирования возможностей.
Результаты тестирования макросов.
3.2 Оценка альтернатив для замены MS Office
На текущий момент, из рассмотренных офисных пакетов, оптимальной заменой MS Office, с учетом связки возможностей таблиц и макросов, будет LibreOffice, а если не касаться вопросов макросов и огромных файлов, то OnlyOffice так же может быть рекомендован к домашнему использованию. МойОфис, на фоне остальных офисных продуктов, откровенно подкачал, здесь и меньшее количество поддерживаемых формул/функционала, здесь и слабая справка по продукту, особенно в части макросов.
Что же касается проблемы переноса моей программы «Автоматизированное заполнение документации» для заполнения Ваших шаблонов и документов, то лучше всего ее переносить на базу LibreOffice с полной перезаписью кода. При этом полученный вариант будет работать медленнее чем на MS Office, из-за медленного StarBasic. На текущий момент ни МойОфис, ни OnlyOffice не смогут в полной мере справиться с реализацией такого функционала, а искренне жаль…
3.3 Оценка перспективы будущего малой автоматизации в Linux
Все нижеперечисленное есть лишь скромное мнение автора, который не претендует на истину в последней инстанции.На мой взгляд объективно лучшим для малой автоматизации есть пакет MS Office, не смотря на старые подходы, тому же VBA уже 29 лет, если считать с момента первого появления в составе Офисного пакета в далеком 1993м году. Однако он работает и на нем написано огромное количество кода, а если учесть что такой подход не требует дополнительной установки ПО/библиотек, то это означает отсутствие барьеров для распространения и внедрения кроме воли самих пользователей.
И JS, и Lua, и StarBasic уступают, по разным причинам, VBA. Отказ от иностранного ПО часть таких барьеров возводит, потому что ограничивает выбор ПО, и здесь мы получаем ситуацию, когда альтернативы сами по себе, без установки дополнительного ПО, откровенно не вывозят по сравнению с VBA. На текущий момент, VBA в таких условиях для сохранения возможностей, перспективнее всего будет заменить внешним ПО, позволяющим многофункциональную работу и редактирование форматов файлов электронных таблиц, например python или аналогичных. Что поднимет не только порог вхождения новым пользователям/разработчикам, но так же вызовет вопросы с установкой и настройкой нового ПО. Однако это не прогресс в технологиях, а откат, т.к. сократится число потенциальных пользователей, которые смогут выступить в роли программиста на час. Что в свою очередь вызовет увеличение трудоемкости в документообороте страны.
PS.
Использованные справочные материалы:
Ссылка на облако с файлами для статьи
Всем Добра!
