Si la plupart de nos clients utilisent XLCubed pour concevoir des rapports ou analyser et visualiser les données, un nombre croissant d'entre eux l'utilisent aussi pour la mise à jour de données, soit dans le cadre de solutions de budgétisation et de prévision, soit dans des applications métier spécifiques.
XL3RunSQL est une solution efficace qui permet aux utilisateurs de construire rapidement des formulaires de saisie dans Excel (qui seront également utilisables sur le web dès leur publication), pour mettre à jour des données SQL.
Dans ce billet, nous allons voir pas à pas comment comment mettre cela en oeuvre.
Voici un tableau XLCubed, basé sur une requête SQL, qui montre les détails des employés, y compris leur salaire et leur commission
Nous allons utiliser une formule XL3RunSQL pour modifier et mettre à jour le salaire et la commission de chaque employé.
Les colonnes P et Q contiendront les nouvelles valeurs pour les commissions et les salaires qui seront utilisées pour mettre à jour le tableau :
La formule XL3RunSQL a la syntaxe suivante :
=XL3RunSQL(ExecuteSQL, Connection, SQLStatement, [TableSheet], [Table])
Pour définir les paramètres de chaque ligne à utiliser dans l'instruction XL3RunSQL, ajoutez quatre calculs aux tableaux des colonnes K-N. Nous ajouterons ensuite quelques formules pour gérer la mise à jour. L'ajout de ces formules au sein du tableau XLCubed signifie que celles-ci seront automatiquement recopiées ou supprimées en fonction des lignes effectivement affichées.
Voici des détails sur les arguments à utiliser pour cette fonction:
1. ExecuteSQL
Une référence de cellule qui contient "VRAI" lorsque l'instruction SQL doit être exécutée. Après exécution, la référence de la cellule sera changée en "FAUX" ou en un message d'erreur si une erreur s'est produite.
Nous allons créer un lien XL3Link dans la colonne N afin que lorsqu'on clique dessus, cette formule écrive VRAI dans une cellule, ce qui permetra alors à la formule XL3RunSQL d'exécuter l'instruction SQL paramétrée. Utilisez la formule suivante :
=XL3Link(“”,”Update”,,XL3Address($L4), if(and($P4<>””,$Q4<>””),”TRUE”,”FALSE”)
La cellule L4 ne sera ainsi mise à "VRAI" que lorsqu'il y aura de nouvelles valeurs dans les colonnes P et Q - cela garantit que la requête SQL ne sera exécutée que lorsque de nouvelles valeurs auront été ajoutées.
2. Connexion
La connexion à utiliser pour se connecter à la base de données. Il s'agit normalement de l'ID de connexion d'une connexion relationnelle du classeur ou d'une chaîne de connexion.
Extrait du ruban XLCubed > Connexions :
3. SQLStatement
La syntaxe SQL à exécuter.
Nous avons mis en place l'instruction SQL dans la colonne K en utilisant une formule CONCATENER:
=CONCATENER("UPDATE Employees SET Salary = "; TEXTE(SI($P4="";$I4; $P4);0);";commission_pct="; TEXTE(SI($Q4="";$J4;$Q4);"0.00″); "WHERE employee_id = "; TEXTE($B4;0);"")
Cette formule construira l'instruction SQL requise pour appliquer la mise à jour du salaire et de la commission aux nouvelles valeurs. S'il n'y a pas de nouvelles valeurs, l’instruction SQL ne sera pas exécutée et les valeurs resteront inchangées.
4. TableSheet (facultatif)
Nom de la feuille de calcul contenant la table de requête ou la table XLCubed à rafraîchir après l'exécution SQL.
5. Table (facultatif)
Le nom de la table de requête ou de la table XLCubed à rafraîchir après l'exécution SQL.
Pour donner un nom à une table XLCubed, il suffit de le renseigner dans l'onglet principal de la fenêtre de propriétés, accessible via le menu contextuel du clique droit ou le bouton du ruban.
XL3RunSQL
Nous avons maintenant les paramètres nécessaires pour mettre en place la formule XL3RunSQL.
Nous allons construire notre formule dans la colonne M :
=XL3RunSQL($L4;1;$K4;;;"Employee_Details")
Nous constatons maintenant que si nous ajoutons des nouvelles valeurs pour le salaire ou la commission, le tableau s'actualise dès que nous cliquons sur "Update". Dans cet exemple, nous avons masqué les colonnes de calcul K-M afin que le tableau soit plus clair et n'affiche pas d'informations inutiles.
Une fois les données données mises à jour, il est souhaitable de pouvoir effacer les données des colonnes P et Q . Pour cela on peut utiliser de nouveau la formule XL3link avec la syntaxe ci-dessous:
=XL3Link("";"Clear Inputs";;XL3Address($P4:$P100);"";XL3Address($Q4:$Q100);"")
Pour rendre la zone de saisie dynamique, on peut utiliser une zone nommée pour les colonnes P et Q.
Dans l'exemple ci-dessous, un segment est appliqué pour filtrer les informations en fonction du type d'emploi. Les formules de calcul étant incluses au sein du tableau, celles-ci sont automatiquement copiées ou supprimées dès que le nombre de lignes change, et ce sans aucune intervention manuelle
La formule XL3RunSQLProc permet d'utiliser des procédures stockées avec une logique similaire.
