Comment puis-je interroger une base de données MySQL à partir de LibreOffice Calc ?

juin

2023

Comment puis-je interroger une base de données MySQL à partir de LibreOffice Calc ?

Linux

8 juin 2023 17:33

Dans cet article, je vous explique comment implémenter le remplissage des cellules du tableau LibreOffice Calc avec des données à l'aide d'une requête vers LibreOffice Base associée à MySQL.

Préface

Comme je vous l'ai déjà dit, au travail, je dois effectuer un rapprochement entre un tableau MS Excel et une base de données MySQL environ une fois par mois. Un lien intermédiaire qui implémente les tables liées (externes) est LibreOffice Base. L'implémentation de la connexion entre Base et MySQL est décrite dans l'article précédent, dont tous les points doivent être complétés avant d'utiliser les macros Calc avec accès à la base de données.

Objectif de cet article- montrer comment interroger Calc vers Base et remplir les cellules à l'aide de macros. Cet article est consacré à la « programmation bureautique » dans le tableur LibreOffice Calc et est destiné au niveau d'un programmeur débutant. Les outils logiciels sont intégrés au package LibreOffice. On suppose que les composants supplémentaires, MySQL, Base et Connector, mentionnés dans les deux articles précédents, sont configurés.

Schéma de connexion des programmes d'accès de LibreOffice Calc à la base de données MySQL

SGBD MySql <-> connecteur mysql ODBC/JDBC <-> LibreOffice Base <-> Libreoffice Calc

L'installation du SGBD MySQL est abordée dans l'article Comment réinstaller le serveur MySQL
Configuration de la connexion MySQL connecteur ODBC/JDBC et Base LibreOffice décrit dans l'article Comment lier LibreOffice Base à MySQL
Cet article envisagera de se connecter depuis Libreoffice Calc à Base LibreOffice et exécution requête SQL avec remplissage résultat de la requête cellule électronique tableaux.

1. Introduction à la programmation LibreOffice Calc

Le manuel d'utilisation de LibreOffice se trouve sur votre PC local dans le dossier
Documentation SCalc (fichier main0000.html)

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

La méthode proposée dans l'aide méthode d'insertion de données externes, voir le fichier webquery.html

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

Ce n'est pas bien sans programmation, parce que... c'est pour travailler avec des tableaux plats issus des mêmes fichiers Calc/Excel, la base de données nous intéresse Base LibreOffice. Par conséquent, je me suis tourné vers la documentation de programmation LibreOffice Basic

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

Pour obtenir les données dans la cellule, écrivez une fonction, par exemple :

Function WorkDay() Dim sDay As String sDay="jour de samaine" if WeekDay(Now) = 1 OR WeekDay(Now) = 7 then sDay="jour de congé" end if WorkDay=sDay End Function

Élément de menu Outils - Macros - Gestion des macros - Basique- sélectionnez notre fichier à gauche et appuyez sur le bouton à droite Créer:
Sélectionnez tout et supprimez-le. Insérer du texte - Enregistrer, Fermer. Trouvons un nom de fichier :
1)
2)
3)
4)

Sur la feuille, dans une cellule on écrit le nom de la fonction entre parenthèses (dans laquelle les paramètres d'entrée peuvent être passés),
avant le nom de la fonction on met le signe égal ("=")

Exemple :
=Jour Travail()
et appuyez sur Entrée. Résultat :

2. Programmation à l'aide de bases de données

2.1 Affichage des noms des bases de données LibreOffice Base enregistrées dans le système

Les bases de données enregistrées peuvent être consultées dans le menu de n'importe quel programme du package LibreOffice :
*Service-Options ->*
Base LibreOffice -> Bases de données**

Ajout cette liste apparaît dans LibreOffice Base lors de la création d'une nouvelle base de données : à la fin de l'assistant, il vous est demandé si vous souhaitez enregistrer la base de données dans le système (par défaut -s'inscrire) :

Exemples de bases de données :

Chemin/home/user/.config/libreoffice/4/user/database/biblio.odb
Nom bibliographie

Dans mon cas, j'ai créé une base de données Calc du même nom "primer"

Chemin/home/user/primer.odb
Nom amorce

Le nom de la base de données peut être spécifié ici en russe. De plus, le nom de la base de données LibreOffice Base peut différer du nom de la base de données MySQL du connecteur ODBC/JDBC).

La base de données LibreOffice Base est maintenant enregistrée dans le système. Vous pouvez l'interroger à partir d'une feuille de calcul LibreOffice Calc.

2.2. Utiliser LibreOffice Calc pour accéder à une base de données - Macro de base

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. Utiliser une macro dans une feuille de calcul Calc

Dans la cellule B2, nous écrivons un appel de macro :
=SELPRICE(A2)

La cellule A2 contient l'identifiant de l'enregistrement (par exemple, 1). La cellule B2 de Calc affichera le prix provenant de la base de données LibreOffice Base, liée à la base de données MySQL.

2.4 Utilisation de plusieurs champs dans une requête SELECT

Code :

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

Le résultat est affiché ci-dessous (dans la première colonne le paramètre de fonction display=1, et dans la deuxième display=0) :

Débogage d'une macro

Si nécessaire, vous pouvez définir un point d'arrêt dans le code de la macro et exécuter Données > Contenu de la cellule > Recalculer (F9). Le curseur s'arrêtera au point d'arrêt. Vous pouvez ensuite parcourir les instructions ligne par ligne à l'aide de F8 (pas à pas détaillé) ou Shift+F8 (pas à pas suivant). Pour afficher l'erreur, vous devez ajouter un caractère de commentaire (une apostrophe) si la fonction contient la ligne « on error goto label » et enregistrer la macro (Fichier > Enregistrer). Appuyez ensuite sur F9 et F8. Les noms des bases de données enregistrées s'affichent après l'exécution de l'instruction dbContext=createUnoService("com.sun.star.sdb.DatabaseContext") en consultant la variable objet dbContext, dont le champ ElementNames affiche les valeurs de chaîne des noms de bases de données.

Si, lors de l'exécution de l'opérateur db=oDataSource.getConnection("",""), l'exception "[unixODBC][DriverManager]Nom de la source de données introuvable et aucun pilote par défaut spécifié sur" est levée, cela se produit lorsque le fichier de base de données *.ODB auquel nous accédons a été transféré depuis un autre utilisateur. Sa connexion est mal configurée dans Service Communications. Il est plus facile de supprimer le fichier Libre Office Base (*.ODB) et de créer un nouveau fichier Base DB. Dans lequel vous pouvez configurer correctement la connexion au connecteur ODBC/JDBC et le nom de la source de données sur cet ordinateur :

Enregistrement d'une nouvelle base de données dans LibreOffice Base, établissement d'une connexion avec une source ODBC externe

lancer LibreOfficeBase
dans l'assistant "Se connecter à une base de données existante"
ODBC
Suivant
Nom de la source ODBC - Parcourir - OK
Suivant
Le nom d'utilisateur est vide. Test de connexion.
Terminé
indiquer le nom et l'emplacement de la base de données
Enregistrer

Ceci est discuté en détail dans l’article.Comment lier LibreOffice Base à MySQL.

La connexion fonctionne désormais : les tables de la base de données externe sont visibles dans la fenêtre LibreOffice Base et la macro s'exécute depuis Calc sans erreur.
.
Sélectionnez dans Base save "Fichier" - "Enregistrer sous", enregistrez le fichier Base *.ODB dans le répertoire personnel de l'utilisateur avec un nom clair. Dans Calc - en conséquence, nous enregistrons la feuille de calcul avec les macros Calc dans le fichier *.ODS.

Aide (Guide de l'utilisateur de programmation)

La documentation de LibreOffice Basic est sur le disque

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

Aide de LibreOffice Basic sur Internet, avec un exemple de connexion à une base de données :
*BASIC_Guide

Aide sur l'objet UNO :

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

Aide pour accéder aux bases de données dans LibreOffice

documentfoundation.org -> Database_Access

Aide LibreOffice pour les sources de données :

documentfoundation.org -> Data_Sources_in_LibreOffice

Conclusion

Les requêtes de LibreOffice Calc vers la base de données LibreOffice Base, puis vers MySQL fonctionnent.

Autres liens, documents de référence :

Pour obtenir des conseils sur l'objet dbContext=createUnoService("com.sun.star.sdb.DatabaseContext"), consultez les articles suivants :
OpenOffice Sources de données
LibreOffice Accès_base de données#Contexte de base de données

exemples de codes sources Accès à la base de données OpenOffice

Ajout. Une note sur le stockage des macros

Les macros peuvent être stockées dans le fichier de feuille de calcul Calc lui-même (*.ODS) ou dans le stockage de macros partagé de l'utilisateur. Je recommande la première méthode, car... ces fichiers peuvent être transférés entre lecteurs de PC ou entre utilisateurs sans perdre de macros. La deuxième méthode est que les macros sont stockées dans le stockage et sont perdues en cas de panne du disque dur du PC (puisque les fichiers ODS ne les contiennent pas dans la méthode n°2, c'est un problème). Il est donc préférable de stocker les macros dans des fichiers ODS plutôt que dans la section « Mes macros et boîtes de dialogue » de l'éditeur Basic.

Blog sur la Radio et Linux