Прошу помощи с вынужденным переездом с Excel на платформу Linux (заменить VBA)

[LuchS]

Я работаю в строительстве. В связи с последними веяниями, из-за которых наша страна решила переходить на отечественное ПО
http://publication.pravo.gov.ru/Document/View/0001202203300001
встал вопрос, как развивать свое приложение, работающее в рамках малой автоматизации рутин, которые облегчают заполнение шаблонных документов инженерам ПТО.

В рамках этой статьи я сравнивал работоспособность под Линукс некоторые базовые функции своего приложения, минимально-необходимый набор,
https://habr.com/ru/post/674580/
но столкнулся со следующими проблемами:
1. относительно низкая скорость на запись StarBasic в ячейку по сравнению с VBA даже работающем через Wine в Linux (порядка 30секунд против 6минут для поля 1000 х 1000 ячеек)
2. неполное соответствие формул и функционала Excel и Calc
3. LO и OO открытое ПО, но не входят в реестр российского ПО, однако многие российские дистрибутивы Linux поставляются именно с LO.

Соответственно я в поиске платформы для замены связки Excel + VBA, как самой распространенной, с одной стороны есть ли под Linux аналог Visual Studio? т.е. будет ли лучше, например, совсем отказаться от Basic'а и вынести макрос в отдельный модуль, или наоборот можно ли как то ускорить StarBasic в самом LO?

[mikekaganski]

Здравствуйте.

1. относительно низкая скорость на запись StarBasic в ячейку по сравнению с VBA даже работающем через Wine в Linux (порядка 30секунд против 6минут для поля 1000 х 1000 ячеек)

Прежде всего - пожалуйста, отправляйте багрепорты о таких вещах, с подробным описанием среды (ОС, версия ЛО, как установлена) и минимальным кодом для воспроизведения (пожалуйста, не делайте код, демонстрирующий две, три и тем более пятнадцать проблем).
Во-вторых, важно определить, насколько оптимальеыми методами Вы пользуетесь для записи в ячейки. Перебор по одной ячейке очень неоптимален.
Но да, производительность макросов ЛО на порядки проигрывает - в основном не из-за бейсика, а из-за дороговизны UNO.

2. неполное соответствие формул ... Excel и Calc

Каждое такое несоответствие должно по умолчанию рассматриваться как баг. С соответствующим багрепортом.

неполное соответствие ... функционала Excel и Calc

Здесь сложнее, и нужно описание. Но опять же, нужны запросы на улучшения, а там уж будет понятно, можно это реализовать или нет.

[sokol92]

Добрый день!
Спасибо за интересную статью!

Главная проблема для программиста при переходе с Microsoft Office (MO) на LibreOffice (LO) - коренные различия в объектной модели. У меня при неплохом знании Excel переход к относительно "комфортному" состоянию в Calc занял полтора года, могу конспективно поделиться опытом.

Первое дело - учебники (или, что было бы прекрасно, учителя). По счастью, кроме документации есть замечательная "классическая" книга А.Питоньяка OpenOffice.org Macros Explained. Огромную пользу мне принесли материалы этого сайта, спасибо Администрации сайта и персональное спасибо - Михаилу Каганскому. С литературой на русском языке есть несомненные проблемы...

Одно из главных преимуществ LO перед MO - наличие нескольких "полноценных" языков программирования (в MO почти только VBA), в том числе Python'а, что дает практически неограниченные возможности.

По Вашим вопросам.

1. Относительно низкая скорость на запись StarBasic в ячейку по сравнению с VBA даже работающем через Wine в Linux (порядка 30секунд против 6минут для поля 1000 х 1000 ячеек)

Я (в свое время) тоже первым делом взял макрос VBA для чтения и записи диапазона ячеек, проверил его быстродействие в Calc и, как и Вы, пришел в ужас.
Причина низкого быстродействия - в реализации разработчиками эмуляции объектов Excel (конкретно, свойства Range.Value). Режим поддержки макросов на VBA никогда не рассматривался как пригодный для промышленной эксплуатации.

Если использовать "родные" методы (setDataArray), то LO дает вполне удовлетворительные результаты.

2. неполное соответствие формул и функционала Excel и Calc

Здесь нужна конкретика. Мы работаем с файлами, содержащими тысячи сложных формул, и каких-то "кардинальных" проблем с формулами нет. Про VBA см. выше.

3. LO и OO открытое ПО, но не входят в реестр российского ПО, однако многие российские дистрибутивы Linux поставляются именно с LO.

LO является неотъемлемой частью, к примеру, операционной системы AstraLinux, которая, разумеется, входит в Реестр отечественного ПО. Должны ли отдельные части операционной системы (если да, то какие) отдельно регистрироваться в Реестре - вопрос дискуссионный.

[economist]

Нужно как можно дольше использовать лучшее из двух миров.

LibreOffice с опцией

Код Выделить Развернуть

Option VBASupport 1 - поддерживает ~60% кода на VBA, код этот в 4 раза более компактный, и вы его уже знаете. Значит можно продолжать кодить на VBA на машине Windows и Excel. А то что не работает - переписать на StarBasic.

Скорость выполнения SB/VBA-макросов в LO - ниже примерно в 3-4 раза, а не как получилось у вас в 12 раз (ищите другую причину).

Если начать писать для LO и MSO на Python, особенно если вынести тяжелые расчеты в Pandas - будет ускорение в 4-6 раз по сравнению с чистым Excel под Windows. На мой взгляд это самый правильный путь миграции, по которому пошел и я.

Очень важно для начала перестать верить системным архитекторам-пропагандистам единственного офиса (особенно тем что не без отката).

Важно лично проверить и признать факт, что LibreOffice имеет из коробки лучший и более удобный (чем в MSO) механизм получения данных из любых внешних баз данных (NativeSQL/JDBC/ODBC) для заполнения шаблонов документов. Искать на Форуме по строке "Ctrl+Shift+F4". Это же касается концепции Стилей, Шаблонов, Записи изменений с паролем и сборки документов из частей: Составные документы, Разделы в виде внешних файлов, DDE-таблицы во Writer. Об этом вы нигде не прочтете, нужно проверять самому, а для этого недельку надо потратить на чтение, изучение и опыты.

Под Linux средств разработки не меньше чем под Windows. Разработку на 30+ языках программирования можно вести в Visual Studio Code / VS Codium, который есть во всех репах, а также snap/flat-пакетах. Он же и почти повсеместный лидер рейтингов. Python - язык №1 для DataScience и в неспешной офисной рутине на нем можно сделать все что угодно.

Макросы "для всех" - просто придется вынести в сетевую библиотеку, т.к. иначе их не получится обновлять сразу всем.      

Если есть возможность - сначала нужно переехать с MSO на LO, и лишь затем переезжать с Windows на Linux, так как это несоизмеримо больший шок для пользователя. Под Linux невозможно полностью сделать как хочется. А в LO - можно сделать даже лучше, чем было в MSO. Но для разработчика - на VBA пишется в 4 раза быстрее. На столько же больше материалов, советов, примера кода.

Вот почему нужно как можно дольше использовать лучшее из двух миров.

[sokol92]

Кстати, пример, который заполняет 1000 строк и 1000 столбцов числами (номер столбца).

Код Выделить Развернуть

Option Explicit
Sub Test
 Dim arr(999) , arr2(999), j as Long, t As Long
 
 For j=0 To Ubound(arr2)    ' массив для столбцов
   arr2(j)=j
 Next j  
 
 For j=0 To Ubound(arr)    ' массив (массивов) для строк
   arr(j)=arr2              
 Next j  
 
 t=GetSystemTicks()
 ThisComponent.Sheets(0).getCellRangeByPosition(0, 0, Ubound(arr2), Ubound(arr)).setDataArray arr
 Msgbox GetSystemTicks()-t
End Sub


У меня выполняется за полторы секунды, что соизмеримо с аналогичным временем Excel 2016 Win 10.

И еще. Сильно сомневаюсь, что Excel способен полноценно работать под Wine.

Ускорение LO Basic - интересная тема.

[LuchS]

2sokol92:

ThisComponent.Sheets(0).getCellRangeByPosition(0, 0, Ubound(arr2), Ubound(arr)).setDataArray arr

Эм... это Basic?

И еще. Сильно сомневаюсь, что Excel способен полноценно работать под Wine.

2010й в Debian 11.3 через PlayOnLinux работает без проблем, как и 2007й. Все что выше у меня запустить не получилось, хотя на том же Ютубе есть примеры с установкой и запуском 2013го, некоторые хвалят CrossOver, но он платный, а значит это решение не для всех.

Первое дело - учебники (или, что было бы прекрасно, учителя). По счастью, кроме документации есть замечательная "классическая" книга А.Питоньяка OpenOffice.org Macros Explained. Огромную пользу мне принесли материалы этого сайта, спасибо Администрации сайта и персональное спасибо - Михаилу Каганскому. С литературой на русском языке есть несомненные проблемы...

Одно из главных преимуществ LO перед MO - наличие нескольких "полноценных" языков программирования (в MO почти только VBA), в том числе Python'а, что дает практически неограниченные возможности.

Спасибо, завтра попробую посмотреть что к чему. У меня главный критерий что бы после того как будет сделано ПО юзеру было максимально просто им воспользоваться, а так же минимум установок и настроек что бы начать работать на то что есть.

2economist:

Если начать писать для LO и MSO на Python, особенно если вынести тяжелые расчеты в Pandas - будет ускорение в 4-6 раз по сравнению с чистым Excel под Windows.

У меня не расчеты, у меня портянка данных которая переносится через сопоставляемые текстовые привязки внутри текста. Я перебираю такие маркеры по всей странице и если нахожу совпадение, то подставляю соответствующее значение из листа. Это не самый оптимальный способ, но достаточно простой что бы любой пользователь офис зная это правило оформил под себя шаблон и затем стал набрасывать данные для него в таблицу. Описание механики здесь на видео:
https://youtu.be/6V9QPaEwalA?t=850

Собственно это первый уровень абстракции, второй уровень это организация данных таким образом что бы через выпадающие списки, графики, перекрестные ссылки часть информации дублировалась наглядно, что бы человек видел что уйдет в заполнение. Это удобно, когда однотипными данными заполняются десятки и даже сотни актов во время строительства. Самое главное это наглядно и если есть помарка/ошибка/неточность, то это можно отследить на портянке данных, а не при печати или при последовательном открытии/закрытии готовых актов. Т.е. экономия времени на заполнении и на косвенных затратах при самоконтроле.

Если есть возможность - сначала нужно переехать с MSO на LO, и лишь затем переезжать с Windows на Linux, так как это несоизмеримо больший шок для пользователя. Под Linux невозможно полностью сделать как хочется. А в LO - можно сделать даже лучше, чем было в MSO. Но для разработчика - на VBA пишется в 4 раза быстрее. На столько же больше материалов, советов, примера кода.

Да, и это тоже. Возможно что-то изменится спустя пару лет...

2mikekaganski:

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

Я не знаю баг это или фича, т.к. ЛО и ОО не использую. Пока что не использую.

Во-вторых, важно определить, насколько оптимальеыми методами Вы пользуетесь для записи в ячейки. Перебор по одной ячейке очень неоптимален.
Но да, производительность макросов ЛО на порядки проигрывает - в основном не из-за бейсика, а из-за дороговизны UNO.

В статье я сравнивал макросы в лоб, т.е. использовал один и тот же код. Нюансы работы со StartBasic я не знаю. Чуть выше я описал логику своего ПО. Все же это малая автоматизация, распространяемая свободно и бесплатно, а не коммерческий продукт.

[sokol92]

Эм... это Basic?

Да, это LO Basic. Синтаксис языка практически такой же, а объектные модели Excel и Calc разные.

Вы можете перенести этот текст в VBA. При компиляции VBA будут сообщения об отсутствии функции GetSystemTicks и объекта ThisComponent, что естественно.

В MS Windows можно написать код на VBA (или VBS), который откроет LibreOffice (если еще не открыт) и будет полноценно работать с объектами LibreOffice. Но это экзотика...

[economist]

у меня портянка данных которая переносится через сопоставляемые текстовые привязки внутри текста. Я перебираю такие маркеры по всей странице и если нахожу совпадение, то подставляю соответствующее значение из листа.

Как вам такой пайплайн: Задаете значения в Таблице XLSX в Excel/Calc - неважно. Жмем Кнопка [Выгрузить в CSV]. Отрываем Writer (файл *.ODT), и жмем на кнопку [Загрузить из CSV]. Она вызывает простой макрос:

Код Выделить Развернуть


Sub ReplaceUserFieldsFromTXT
' Обновляет UserFields-поля в ODT ч/з TXT-файл (с)economist
aFile = "D:\Показатели Сметы Тоннель А-25.txt"
Open aFile For Input As 1
While Not EOF(1)
Line Input #1, sLine
varic = str(split(sLine, "==")(0))
varic_val= split(sLine, "==")(1)
if UBound(split(sLine, "=="))>1 then msgbox("СТОП! Символ == в тексте поля недопустим!"): End
oUserField=ThisComponent.TextFieldMasters.getByName("com.sun.star.text.fieldmaster.User." & varic)
oUserField.Content = varic_val
Wend
Close #1
msgbox("Обновление Полей документа из таблиц Excel/Calc (CSV) завершено!")
End Sub    


При этом происходит некая внутренняя магия LO Writer. Например вы в Таблице напечатали в 2-х ячейках:

КС2июль2022Дата==25.07.2022
КС2июль2022Выполнено==14500000

При обновлении полей (кстати, мгновенном) - Writer не только красиво перенесет по слогам увеличившиеся из-за роста суммы строки абзацев, но и продолжит проверять по ним орфографию. А главное - он загрузит из внешнего файла форму КС-2, и в ней отобразит нужные поля. В зависимости от полей - будут авто-прятаться и или выводиться целые слова, абзацы, разделы, и даже "приложения". Это сработали механизмы Условного, Скрытого текста/абзаца Writer. А т.н. текстовые формулы вида =SUM(<B2>:<B10>)-C9 - будут авто-пересчитаны. Конечно, увязывать все формы в файлы с полями нужно заранее все продумав.

Я не верю что ничего не надо считать в сметном деле, т.к. делал несколько "больших волшебных кнопок для ПТО" за 27 лет "макрухи". Считать "не-деньги" приходится всегда: периоды времени, доли выполнения, суммы/даты рассчитать "прописью", кол-во подписантов для проверки кворума, лимит задолженности по материалам итд. И лучше чем пользовательская функция или просто формула в Excel/Calc - это не реализовать (но главное - эту простую, но важную работу можно спихнуть на других, и они будут счастливы автоматизировать себя, безо всяких ТЗ и прочих UML/BPM).

Вот нету в Calc ф-ии для вывода диапазона печати. Но вы можете написать на Basic свою UDF, которые выведет его. А лучше пусть макрос сам переразметит все как надо.

Большим плюсом остается макрорекордер Excel для тонкой доводки форматов, те же самые параметры печати, ведь 60-70% его кода заработает в LO Calc. Переписать на родной и более надежный StarBasic можно потом, когда появится куча свободного времени.

А что касается вопроса распространения ваших наработок - предлагаю использовать заранее настроенный вами официальный Portable LO. Архив с ним весом ~300 Мб распаковывается за пару минут, с заменой прежней папки. И все у всех просто заработает. Распаковку может инициировать что угодно: макрос (с закрытием LO), сам юзер с ограниченными правами, доменный logon-скрипт (это самое правильное - людям спокойнее), bat/sh-файл итд.

А что насчет формирования всего пакета КС-ок, Актов вып. работ,  сразу по сотне подрядчиков? Тут, имхо, лучше обойтись вообще без LO: пишем Python-скрипт. Он считывает все CSV и перебирает все готовые ODT-шаблоны форм, в которых подменяет строки вида <Подрядчик> на нужное значение. ODT - это zip-файл, заменить в нем значение - легко и быстро, не открывая файл в LO. Ускорение будет десятикратным. Вы сможете сосредоточить внимание на автоматизации сбора исходных данных в Calc (в нем есть общий доступ, куча выразительных инструментов - усл. форматирование, автофильтры, формулы итд), то есть можно обойтись почти без макросов (раз уж они не быстрые).