Как выполнить запрос из LibreOffice Calc к базе данных MySQL?

Juni

2023

Как выполнить запрос из LibreOffice Calc к базе данных MySQL?

linux

8 Juni 2023 17:33

В статье рассказываю, как реализовать заполнение ячеек таблицы LibreOffice Calc данными с помощью запроса к LibreOffice Base, связанной с MySQL.

Предисловие

Как я ранее рассказывал, на работе мне примерно раз в месяц приходится выполнять сверку между таблицей MS Excel и базой данных MySQL. Промежуточным звеном, реализующем связанные (внешние) таблицы является LibreOffice Base. Реализация связи между Base и MySQL описана в предыдущей статье, все пункты которой нужно выполнить перед использованием макросов Calc с доступом к БД.

Цель данной статьи - показать, как производить запрос из Calc к Base и заполнять ячейки при помощи макросов. Данная статья посвящена "офисному программированию" в электронной таблице LibreOffice Calc и рассчитана на уровень начинающего программиста. Программные средства встроены в пакет LibreOffice. Предполагается, что дополнительные компоненты, MySQL, Base и Коннектор, которые упомянуты в предыдущих двух статьях, настроены.

Схема подключения программ для доступа из LibreOffice Calc к БД MySQL

СУБД MySql <-> mysql коннектор ODBC/JDBC <-> LibreOffice Base <-> Libreoffice Calc

Установка СУБД MySQL рассмотрена в статье Как переустановить MySQL сервер
Настройка соединения mysql коннектора ODBC/JDBC и LibreOffice Base рассказана в статье Как связать LibreOffice Base с MySQL
В данной статье будет рассмотрено подключение из Libreoffice Calc к LibreOffice Base и выполнение SQL запроса с заполнением результатом запроса ячейки электронной таблицы.

1. Введение в программирование LibreOffice Calc

Руководство пользователя пакета LibreOffice находится на локальном ПК в папке
документации по SCalc (файл main0000.html)

cd /usr/share/libreoffice/help/ru/text/scalc/
xdg-open main0000.html

Предложенный в справке способ способ вставки Внешних данных, см. файл webquery.html

cd /usr/share/libreoffice/help/ru/text/scalc/guide
xdg-open webquery.html

без программирования не годится, т.к. он для работы с плоскими таблицами из таких же файлов Calc / Excel, нас же интересует база данных LibreOffice Base. Поэтому обратился к документации по программированию LibreOffice Basic

help.libreoffice.org/latest/ru/text/sbasic/shared/main0601.html

Чтобы данное попало в ячейку, пишется функция, например:

Function WorkDay() Dim sDay As String sDay="будний" if WeekDay(Now) = 1 OR WeekDay(Now) = 7 then sDay="выходной" end if WorkDay=sDay End Function

Пункт меню Сервис - Макросы - Управление макросами - Basic - слева выбираем наш файл и справа нажимаем кнопку Создать:
Выделяем все - удаляем. Вставляем текст - Сохранить, Закрыть. Придумываем имя файла:
1)
2)
3)
4)

На листе в ячейку пишем имя функции со скобками (в которых могут передаваться входные параметры),
перед именем функции которой ставим знак "равно" ("=")

Пример:
=WorkDay()
и нажимаем Enter. Результат:

2. Программирование с использованием баз данных

2.1 Просмотр имён зарегистрированных в системе баз данных LibreOffice Base

Зарегистрированные БД можно посмотреть в меню любой программы из пакета LibreOffice:

Сервис - Параметры ->
LibreOffice Base -> Базы данных

Добавление в данный список происходит в LibreOffice Base при создании новой базы данных: в конце Мастера запрашивается - регистрировать ли базу данных в системе (по умолчанию - регистрировать):

Примеры баз данных:

Путь /home/user/.config/libreoffice/4/user/database/biblio.odb
Имя Bibliography

В моём случае, я создал БД Calc с таким же именем "primer"

Путь /home/user/primer.odb
Имя primer

Имя БД здесь можно указать на русском языке. Также имя БД для LibreOffice Base может отличаться от имени БД MySQL коннектора ODBC/JDBC).

Теперь БД LibreOffice Base зарегистрирована в системе. К ней можно делать запросы из электронной таблицы LibreOffice Calc.

2.2. Использование LibreOffice Calc для доступа к БД - макрос Basic

Function SELPRICE(id) On Error GoTo ErrorHandler Dim dbContext, oDataSource, db, pstmt, oResult, s s="" If Not IsMissing(id) Then dbContext=createUnoService("com.sun.star.sdb.DatabaseContext") oDataSource=dbContext.getByName("primer") db=oDataSource.getConnection("","") sql=buildSelectPrice(id) if sql<>"" Then pstmt=db.prepareStatement(sql) oResult=pstmt.executeQuery() Do while oResult.Next If (s<>"") then s=s + "," End If s=s+oResult.getString(1) Loop


db.Close

db.dispose()

EndIf

Endif

SELPRICE=s

Return

ErrorHandler:

End Function

Function buildSelectPrice(id) Dim sql sql="" If Not IsMissing(id) Then sql = "SELECT price FROM offers WHERE offer_id= " + id EndIf buildSelectPrice=sql End Function

2.3. Использование макроса на листе Calc

В ячейку B2 пишем вызов макроса:
=SELPRICE(A2)

где A1 содержит ИД записи (например, 1). В ячейку Calc будет выведена цена из базы данных LibreOffice Base, которая связана с БД MySQL.

2.4 Использование нескольких полей в запросе SELECT

Код:

Function buildSelectOfferPrice(id, status) Dim sql sql="" If Not IsMissing(id) and Not IsMissing(status) Then sql = "SELECT price FROM offers WHERE offer_id = " + id + " AND display = " + status EndIf buildSelectOfferPrice=sql End Function


Function SELECTACTUALPRICE(id, status)

on error goto label

Dim dbContext, oDataSource, db, pstmt, oResult, s

s=""

If Not IsMissing(id) and Not IsMissing(status) Then

dbContext=createUnoService("com.sun.star.sdb.DatabaseContext")

oDataSource=dbContext.getByName("primer")

db=oDataSource.getConnection("","")
sql=buildSelectOfferPrice(id, status)
if sql<>"" Then

pstmt=db.prepareStatement(sql)

oResult=pstmt.executeQuery()

Do while oResult.Next

If (s<>"") then

s=s + ","

End If

s=s+oResult.getString(1)

Loop

db.Close db.dispose() EndIf Endif SELECTACTUALPRICE=s label: End Function

Результат приведен ниже (в первом столбце параметр функции display=1, а во втором display=0):

Отладка макроса

Если потребуется, можно в коде макроса установить точку останова и запустить Данные - Содержимое ячейки - Пересчитать (F9). Курсор остановится на точке остановка. Затем можно выполнять операторы построчно по шагам при помощи клавиш F8 - "шаг с заходом" или Shift+F8 - "шаг с обходом". Чтобы увидеть ошибку, нужно установить символ комментария - апостроф, если в функции есть строчка 'on error goto label и сохранить макрос Файл - Сохранить. Затем нажимаем F9 и F8. Имена зарегистрированных баз данных можно увидеть после выполнения оператора dbContext=createUnoService("com.sun.star.sdb.DatabaseContext") - с помощью просмотра переменной объекта dbContext, в поле которого ElementNames видны строковые значения имён БД.

Если при выполнении оператора db=oDataSource.getConnection("","") выдается исключение "[unixODBC][DriverManager]Data source name not found and no default driver specified at" , такое бывает когда файл базы *.ODB, к которой обращаемся, к которой обращаемся, перенесён от другого пользователя. В ней некорректно настроена связь в Сервис-Связи. Проще удалить файл Libre Office Base (*.ODB) и создать новый файл БД Base. В которой корректно настроить связь на коннектор ODBC/JDBC и имя источника данных на данном компьютере:

Регистрация новой БД в LibreOffice Base, установка связи с внешним ODBC-источником

запустить LibreOffice Base
в мастере "Соединиться с существующей базой данных"
ODBC
Далее
Имя источника ODBC - Обзор - OK
Далее
Имя пользователя - пустое. Тест подключения.
Готово
указываем имя и расположение базы
Сохранить

Об этом подробно говорится в статье Как связать LibreOffice Base с MySQL.

Теперь соединение работает - в окне LibreOffice Base видны таблицы внешней базы данных, и из Calc макрос запускается без ошибки.
.
Выбираем в Base сохранить "Файл" - "Сохранить как", сохраняем файл Base *.ODB в домашнем каталоге пользователя с понятным именем. В Calc - соответственно, сохраняем электронную таблицу с макросами Calc в файл *.ODS.

Справочная информация (руководство пользователя по программированию)

Документация по LibreOffice Basic находится на диске

cd /usr/share/libreoffice/help/ru/text/sbasic/shared/
xdg-open main0601.html

Справка по LibreOffice Basic в Интернет, с примером подключения к базе данных:

BASIC_Guide

Справка по объектам UNO:

cd /usr/share/libreoffice/help/ru/text/sbasic/shared/
xdg-open uno_objects.html

Справка по обращению к базам данных в LibreOffice

documentfoundation.org -> Database_Access

Справка LibreOffice по источникам баз данных Data Sources:

documentfoundation.org -> Data_Sources_in_LibreOffice

Вывод

Запросы из LibreOffice Calc к БД LibreOffice Base и далее к MySQL работают.

Другие ссылки, справочные материалы:

Руководства по объекту dbContext=createUnoService("com.sun.star.sdb.DatabaseContext") приведено в статьях на английском языке:

OpenOffice Data Sources
LibreOffice Database_Access#DatabaseContext
примеры исходного кода OpenOffice Database Access

Дополнение. Замечание по поводу хранения макросов

Макросы могут храниться в самом файле электронной таблицы Calc (*.ODS), либо в общем хранилище макросов пользователя. Рекомендую первый способ, т.к. такие файлы можно переносить между дисками ПК или между пользователями без потери макросов. Второй способ - макросы хранятся в хранилище и утрачиваются при сбое жесткого диска ПК (т.к. в ODS файлах не при способе №2 они содержатся, это является проблемой). Поэтому лучше хранить макросы в ODS-файлах, а не в разделе "Мои макросы и диалоги" редактора Basic.

Blog über Radio und Linux