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 :

Fonction Jour Travail()
Dim sDay sous forme de chaîne
sDay = "jour de la semaine"
si WeekDay(Maintenant) = 1 OU WeekDay(Maintenant) = 7 alors
sDay="выходной"
finir si
Jour Travail=sJour
Fin de fonction

É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

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

Fonction SELPRICE(id)
En cas d'erreur, GoTo ErrorHandler
Dim dbContext, oDataSource, db, pstmt, oResult, s
s=""
Si ce n'est pas manquant (id), alors
dbContext=createUnoService("com.sun.star.sdb.DatabaseContext")
oDataSource=dbContext.getByName("primer")
db=oDataSource.getConnection("","")
sql=buildSelectPrice(id)
si sql<>"" Alors
pstmt=db.prepareStatement(sql)
oResult=pstmt.executeQuery()
Faire pendant oResult.Next
Si (s<>"") alors
s=s + ","
Fin si
s=s+oResult.getString(1)
Boucle

Fonction buildSelectPrice(id)
Faible SQL
sql=""
Si ce n'est pas manquant (id), alors
sql = "SELECT price FROM deals WHERE offer_id= " + id
FinSi
buildSelectPrice=sql
Fin de fonction

2.3. Utiliser une macro dans une feuille de calcul Calc

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

où A1 contient l'ID d'entrée (par exemple, 1). Le prix de la base de données LibreOffice Base, qui est connectée à la base de données MySQL, sera affiché dans la cellule Calc.

2.4 Utilisation de plusieurs champs dans une requête SELECT

Code :

Fonction buildSelectOfferPrice(id, statut)
Faible SQL
sql=""
Si Not IsMissing(id) et Not IsMissing(status) Alors
sql = "SÉLECTIONNERprixDEoffresOÙoffre_id= " + identifiant + " ETafficher= " + statut
FinSi
buildSelectOfferPrice=sql
Fonction de fin

db.Fermer
db.dispose()
FinSi
Fin
SELECTACTUALPRICE=s
étiquette :
Fin de fonction

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

Еслet si nécessaire, vous pouvez définir un point d'arrêt dans le code de la macro et exécuter Données - Contenu des cellules - Recalculer (F9). Le curseur s'arrêtera au point d'arrêt. Vous pouvez ensuite exécuter les instructions ligne par ligne, pas à pas, à l'aide des touches F8- "intervenez" ou Maj+F8- "étape avec détour". Pour voir l'erreur, vous devez définir le symbole de commentaire - une apostrophe, s'il y a une ligne dans la fonction 'en cas d'erreur, allez à l'étiquette et enregistrez la macro Fichier - Enregistrer. Appuyez ensuite sur F9 et F8. Les noms des bases de données enregistrées peuvent être vus après l'exécution de l'instruction dbContext=createUnoService("com.sun.star.sdb.DatabaseContext")- en visualisant la variable objet contexte de base de données, dans le domaine duquel Noms des éléments les valeurs de chaîne des noms de bases de données sont visibles.

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

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

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.

Publications connexes

Travailler avec Flatpak ou APT via un serveur proxy

Résolution du problème de démarrage de Linux Mint avec la carte graphique Intel intégrée

Arrêtez et désactivez TimeShift pour économiser de l'espace disque

Résolution des erreurs de connexion L2TP/IPsec «Could not add IPsec connection» et «no acceptable traffic selectors found»

Partitionnement du disque dur pour installer Linux Mint sur un ancien PC avec BIOS en mode GPT