Всем привет! Недавно я обратил внимание, что некоторые пользователи офисных программ используют макросы для упрощения написания документов, однако используют макросы не в полную силу, например, они используют исключительно штатные возможности языка Visual Basic, не прибегая к сторонним библиотекам и приложениям. Я решил исправить эту ситуацию, поэтому написал небольшую инструкцию о том, как можно применять внешние программы и сценарии в своих скриптах, тем самым давая возможность где-то упростить, где-то распараллелить разработку необходимых функций.
Свои примеры я собираюсь показывать главным образом на табличных процессорах на двух продуктах: это Microsoft Office Excel и LibreOffice Calc. Данные примеры должны работать и на других программах, использующих VBA, но я не занимался их тестированием.
В качестве примера я решил использовать создание самописной функции табличного процессора. Итак, допустим нам понадобилось узнать md5 сумму файлов, находящихся в столбце А, а саму сумму мы хотим сохранить в столбец B. Выглядеть это должно примерно так:
В интернете на самом деле есть много примеров как штатными способами реализовать подобную функцию, поэтому это лишь учебный пример для наглядности. Я, например реализовывал другие функции, такие как автоматическое распознавание результатов тестов, таких как опросник 16ФЛО-1 и ему подобных для отсканированных ранее заполненных листов. Причём программа написана на С++ или python с использованием openCV или других специальных библиотек, а VBA лишь использовал выходные данные, которые были получены этими программами. При этом пользователь (психолог) совершенно ничего не знал о технической реализации данной функции, он лишь писал в ячейке что-то типа «=опрос16ФЛО_1(А1)», где в ячейке А1 было название файла скана теста, а функция самостоятельно считала результат. Так же были программы и функции, передающие в ячейки данные из telegram-ботов и другие не менее интересные реализации. Стоит заметить, что результаты выполнения функций и процедур не обязательно должны выполняться мгновенно, программа вполне может долго проработать, пользователь может за это время в программе нажать много кнопок или ввести много текста, прежде чем программа вернёт в ячейку результат.
Итак, существует несколько различных способов реализовать подобную идею:
Рассмотрим первый из них, работает на большинстве офисных программ, использующих basic в качестве скриптового языка, это функция shell. Функция стандартная, не требует дополнений. Если мы хотим реализовать функцию нахождения MD5 суммы, то должны написать примерно такой код:
function MD5sum (a as string) as string on error goto lb:
'a="/home/dmitry/sh1.sh" тут записал пример имени файла
dim s as string
shell "x-terminal-emulator -e 'md5sum "+a+" > /tmp/temp.txt'" f = FreeFile lb: Open "/tmp/temp.txt" For Input As # f
Do While Not EOF(f)
Line Input # f, s
MD5sum=MD5sum+s
Loop
shell "x-terminal-emulator -e 'rm /tmp/temp.txt'" end function
Только сразу оговорюсь, данный код максимально «костыльный», я его сделал по принципу «лишь бы работало» и такой подход скорее всего отработает только в ОС на основе *nix систем. Кому интересно «допилить» его буду рад видеть в комментариях. У данного подхода есть один существенный недостаток: из-за того, что данные записываются в файл, передача данных от запуска функции до записи в ячейку или другое место происходит очень медленно, и если у вас сотни подобных вызовов функции, то ваш офис будет постоянно «висеть», особенно если та или иная функция будет вызываться постоянно. Поэтому лучше применять другой способ, такой как перехват вывода сообщения программой напрямую, минуя запись в файл.
Для того, чтобы миновать лишнюю запись в файл, нам нужно написать дополнительную функцию, оперирующую потоками ввода/вывода приложений, эту функцию я написал не сам, а нашёл на просторах интернета:
Function ShellRun(sCmd As String) As String
Dim oShell As Object
Set oShell = CreateObject("WScript.Shell")
'run command'
Dim oExec As Object
Dim oOutput As Object
Set oExec = oShell.Exec(sCmd)
Set oOutput = oExec.StdOut
'handle the results as they are written to and read from the StdOut object'
Dim s As String
Dim sLine As String
While Not oOutput.AtEndOfStream
sLine = oOutput.ReadLine
If sLine <> "" Then s = s & sLine & vbCrLf
Wend
ShellRun = s
End Function
После чего можно писать уже пользовательские функции, которые будут работать с этой программой, например:
function MD5sum (a as string) as string
MD5sum= ShellRun("certutil -hashfile "+a+" MD5") 'Далее результат нужно немного отредактировать, убрать «лишнее» из результата, но это вне рамок этой статьи.
end function
Я оставил пример реализации очистки содержимого результата функции штатными средствами excel, получилось так:
=ЛЕВСИМВ(ПОДСТАВИТЬ(ShellRun("certutil -hashfile "&A1&" MD5");"MD5 хеш файла "&A1&":";"");50)
но лучше такие действия делать сразу в VBA, в противном случае это усложняет чтение формул в документе, с которыми пользователю придётся работать чаще, чем с кодом.
Думаю, для 90% читателей этот вариант наилучший и дальше читать не обязательно. Для остальных, для тех, кто пользуется свободным аналогом в виде LibreOffice или ему подобного лишены такой возможности, поэтому чтобы реализовать похожую функцию придётся пойти иным путём. Я смог придумать способ, предполагающий установленный в качестве скриптового языка python, скрипты которого будут вызываться basic. Способ установки модуля python я расписывать не стану, инструкции есть в интернете, я напишу примерный способ как написать скрипты. Итак, для начала нам нужно написать небольшую функцию python и заставить офис её «видеть». Итак, создаём файл скрипт python, назовём его, например, pyshell.py и пишем туда пару строк:
«import os def shell(x):return "".join(os.popen(x).readlines())
Сохраняем файл в месте, где у офиса хранятся скрипты python. Я нашёл это место найдя встроенный файл «HelloWorld.py». После этого пишем функцию в basic, аналогичную функции shellrun описанную ранее:
Function ShellRun(sCmd As String) As String script = CreateUnoService ("com.sun.star.script.provider.MasterScriptProviderFactory").createScriptProvider("").getScript("vnd.sun.star.script:pyshell.py$shell?language=Python&location=user")
ShellRun = script.invoke(array(sCmd), array(), array())
End Function
Подробно расписывать что тут происходит не буду, лишь выделю часть текста, где указано имя файла со скриптом. Результат работы этой функции принципиально не отличается от её аналога для MS Office, поэтому после этого пишем уже обычную функцию вызова программы, которая ничем особо не отличается от предыдущей. Напишу лишь отдельный вариант для пользователей Linux:
function MD5sum(a as string) as string
MD5sum=ShellRun("md5sum "+a) 'Далее результат нужно немного отредактировать, убрать «лишнее» из результата, тут я на всякий случай оставил пример как это можно сделать D5sum=mid(trim(b),1,33) end function
Возможно на LO можно сделать аналогичные функции при помощи других скриптовых языков, но честно говоря у меня просто не было желания проверять.
Вызов графических приложений из офиса, которые не сразу возвращают результат принципиально ничем не отличается от консольных, таких как Certutil или md5sum. Например пользователь мог бы некоторое время в них работать, прежде чем получит результат. Однако в LO данная функция не работает, а в MS VBA она не работает с перенаправлением ввода вывода в файл. Поэтому для устранения этих проблем придётся идти другим путём, например, использовать динамически подключаемые библиотеки, которые в ОС Windows они имеют расширение dll. В этом случае имеется два основных способа использования этих библиотек:
- писать все нужные функции непосредственно в библиотеке, после чего вызывать эти функции в VBA и использовать их как обычные функции офиса;
- написать свою функцию, аналог shell на «нормальном» языке, и использовать её в basic LOили VBA.
На первом варианте я подробно останавливаться не стану, я лишь напомню синтаксис вызова функции вызова динамической библиотеки, который кстати нам всё равно понадобится для второго варианта:
Declare PtrSafe Function GetActiveWindow Lib "User32" () As LongPtr
Где и как использовать такую конструкцию подробно расписано в интернете, можно посмотреть конкретную реализацию ниже для второго варианта. Важно лишь помнить, что вы можете сами написать или использовать чужую библиотеку написанную на другом языке, в котором ваша проблема решается проще, чем напрямую в VBA.
На втором варианте я остановлюсь по подробнее.
Для начала нам надо написать функцию, аналогично функции shell, но такой, которая будет ждать выполнения стороннего приложения, пока оно не вернёт результат.
Итак, в моём примере я использовал Visual Studio 2019, создал проект «Динамическая библиотека на с++», после чего создал такой кусок кода:
// dllmain.cpp : Определяет точку входа для приложения DLL.
# include "pch.h" # include <string>
# include <cstring> # include <iostream>
# include <fstream> # include <OleAuto.h> # pragma comment(lib, "OleAut32")
# include <stdio.h> # include <stdlib.h>
# include <atlstr.h>
# include <vector>
BOOL APIENTRY DllMain( HMODULE hModule,DWORD ul_reason_for_call, LPVOID lpReserved){switch (ul_reason_for_call){
case DLL_PROCESS_ATTACH: case DLL_THREAD_ATTACH:
case DLL_THREAD_DETACH: case DLL_PROCESS_DETACH:
break;}
return TRUE;}
// Всё что написано выше, кроме include было изначально.
// ниже — наша функция
extern "C" __declspec(dllexport) bool shellexec(LPWSTR buffer, int* buffer_size){
// LPWSTR buffer — текст, который нам вернёт программа, buffer_size — размер текста
std::setlocale(LC_ALL, "ru_RU.UTF-8");
char* cmd = new char[wcslen(buffer) + 1]; char buf[BUFSIZ];std::wstring ws(buffer); std::string result ="";
// Отсюда и до ближайшего отступа непонятные операции по конвертированию данных из формата в формат
const std::locale locale("");typedef std::codecvt<wchar_t, char, std::mbstate_t> converter_type;const converter_type& converter = std::use_facet<converter_type>(locale);std::vector<char> to(ws.length() * converter.max_length());std::mbstate_t state;const wchar_t* from_next;
char* to_next;const converter_type::result conv_result = converter.out(state, ws.data(), ws.data() + ws.length(), from_next, &to[0], &to[0] + to.size(), to_next); if (conv_result == converter_type::ok or conv_result == converter_type::noconv) {const std::string tmp(&to[0], to_next);
//отсюда и ниже чтение данных, которые произведены программой
FILE* ptr; if ((ptr = _popen(tmp.c_str(), "r")) != NULL) {while (fgets(buf, BUFSIZ, ptr) != NULL)(void)printf("%s", buf);result = result + buf;(void)_pclose(ptr);}
*buffer_size = result.length(); std::wstring wstr(result.begin(), result.end());
wcscpy_s(buffer, wstr.length() + 1, (LPWSTR)wstr.c_str());
return true;//если всё получилось}
return false;//если что-то пошло не так}
Забегая наперёд скажу, что этот код почему-то не заработал в LO, поэтому для него взял аналог без аргументов, с передачей данных через файлы:
extern "C" __declspec(dllexport) bool shellexecf()
std::setlocale(LC_ALL, "ru_RU.UTF-8");char buf[BUFSIZ];std::string result =""; std::ifstream in("C:\\temp\\cmd.txt"); // открываем файл для чтения if (in.is_open()) {getline(in, result);in.close();FILE* ptr; if ((ptr = _popen(result.c_str(), "r")) != NULL) {result = ""; while (fgets(buf, BUFSIZ, ptr) != NULL) result = result + buf; (void)_pclose(ptr);}
extern "C" __declspec(dllexport) bool shellexecf()
std::setlocale(LC_ALL, "ru_RU.UTF-8");char buf[BUFSIZ];std::string result =""; std::ifstream in("C:\\temp\\cmd.txt"); // открываем файл для чтения if (in.is_open()) {getline(in, result);in.close();FILE* ptr; if ((ptr = _popen(result.c_str(), "r")) != NULL) {result = ""; while (fgets(buf, BUFSIZ, ptr) != NULL) result = result + buf; (void)_pclose(ptr);}
После написание библиотеки переходим к написанию функции использования библиотеки:
Public Declare PtrSafe Function shellexec Lib "C:\Shellrun.dll" (ByVal buffer As LongPtr, ByRef Lenght As Long) As Boolean
Function getParametr(a As String) As String
Dim buffer_ As String bufferLenght = 0 buffer_ = a + " " If shellexec(StrPtr(buffer_), bufferLenght) Then getParametr = buffer_
End Function
Честно скажу, у функции костыль на костыле, особенно 5 строка кода. Я не помню почему я так сделал, но оно работает, хотя это явно дурной тон программирования.
Для LO код будет отличаться, из-за действий через файлы C:\temp\cmd.txt и C:\temp\out.txt. К сожалению, полный код для примера утерян, переписывать лень, но я кратко опишу что там сделал:
Public Declare PtrSafe Function shellexecf Lib "C:\Shellrun.dll" ()' нет аргументов
Function getParametr(a As String) As String ' Тут запись в файл C:\temp\cmd.txt содержимого аргумента а
shellexecf ' Никаких аргументов, пустой вызов библиотечной функции ' Тут чтение файла C:\temp\out.txt результата End Function
В качестве примера использования этой функции возьмём приложение на python использующее графическую библиотеку Tkinter:
У данной реализации к сожалению, имеется один существенный недостаток: не корректно распознаётся кириллица. Поэтому функции нуждаются в доработке, с которой я пока не справился. В остальном всё это так или иначе хорошо работает в Microsoft Office VBA и LibreOffice Basic на ОС Windows, а вот линуксоиды видимо должны страдать, так как функция подключения динамических библиотек там просто не работает!
Поэтому тем, кому посчастливилось использовать офис под Линуксом придётся использовать 2 других пути:
- использовать python для подключения динамических библиотек;
- использовать «костыли» - антипаттерны проектирования, различного рода затычки и заглушки, вынуждающие код не прекращать выполнения раньше времени.
Первый вариант я не стал подробно расписывать, так как на практике мне это ни разу не пригодилось, поэтому я лишь кратко распишу один из вариантов как подключать либу в питоне:
from ctypes import CDLL
lib = CDLL("/usr/lib/libscrabblescore.so")
lib.score_word("qi", "dw dlq")
Второй вариант распишу подробнее, тут я наспех реализовал рабочую функцию возвращения результата внешней программы:
function longshell(a as string) as string If Dir("putout.txt") <> "" Then Kill "putout.txt"
shell "x-terminal-emulator -e "+a+" > putout.txt"
do while Dir("putout.txt") = "" ' ждём появления файла loop
ss="" do while ss="" ' ждём заполнения файла f = FreeFile
Open "putout.txt" For Input As # f Do While Not EOF(f) Line Input # f, s
ss=ss+s
Loop Close loop
longshell = ss end function
Суть в том, чтобы пустыми циклами задержать прекращение работы программы, пока она не вернёт результат в файл. Не самое здоровое решение проблемы… но оно работает.
Недостатком данной функции является то, что используется файл для передачи данных и в данной конкретной реализации низкая отказоустойчивость, так как мне лень её было дописывать до идеала.
На этом, пожалуй, на сегодня всё, жду вашей критики, предложений и других комментариев.
