Connectez Excel à SQL Server: 3 Étapes faciles

Dans un article précédent, nous avons expliqué pourquoi vous avez besoin d’Excel et de SQL dans vos solutions de gestion de données, mais comment connectez-vous Excel à SQL Server? Dans cet article, nous allons vous montrer les étapes et mettre en évidence un avantage clé de l’utilisation d’un complément Excel comme les Spreads SQL.

Mais, d’abord, nous allons plafonner à nouveau les avantages de la connexion d’Excel aux données dans SQL Server.

Avantages de la connexion d’Excel à SQL Server

Pour démontrer les avantages de la connexion d’Excel à SQL Server, regardons un exemple.

Cette feuille de calcul est utilisée par les représentants commerciaux d’une entreprise de fabrication pour préparer des devis pour les clients. Chaque représentant a une copie de la feuille de calcul, qui comprend une liste des articles en inventaire et leurs prix. La liste des articles en stock et des prix est extraite du système financier par un administrateur. La feuille de devis utilise des formules de recherche de validation des données pour récupérer les prix lorsqu’un représentant commercial sélectionne un article dans la liste déroulante.

Cela fonctionne bien jusqu’à ce que l’organisation ait besoin de mettre à jour les prix. Dans ce cas, chaque utilisateur devra copier et coller la nouvelle tarification dans la feuille de calcul (ou utiliser une nouvelle copie de la feuille de calcul). De plus, lorsque le représentant des ventes crée le devis, il ne sait pas si les articles sont réellement en stock.

Une meilleure solution serait de connecter la feuille de calcul Excel au tableau d’inventaire de la base de données du système financier. Chaque fois que le représentant des ventes ouvre la feuille de calcul des devis, il actualise les données de prix de la base de données en direct. Nous pouvons aller plus loin et faire la connexion à la base de données également ramener les niveaux de stock actuels afin que les devis puissent être préparés pour les articles réellement en stock.

Ainsi, pour résumer, les avantages de la connexion d’Excel à SQL Server sont les suivants:

• nous avons accès et pouvons afficher des informations à jour
• nous utilisons une source de données unique et fiable
• nous pouvons effectuer une analyse à la volée ou une agrégation des données source à l’aide de fonctions Excel simples telles que des tableaux croisés dynamiques

Connecter Excel à une base de données SQL Server: 3 étapes faciles

Le le moyen le plus simple de connecter Excel à une base de données SQL Server consiste à utiliser des spreads SQL. SQL Spreads est un simple complément Excel.

Le téléchargement des Spreads SQL se trouve ici, et le processus d’installation est décrit ici.

Étape 1: Rassemblez les détails de connexion à la base de données

Vous aurez besoin des informations suivantes pour connecter Excel à SQL Server, alors obtenez les détails en première étape:

• Nom SQL Server
• Nom de la base de données
• Table ou Vue contenant les informations dont vous avez besoin
• Détails de connexion (si vous n’utilisez pas l’authentification Windows)

La forme du nom SQL Server dépendra de l’installation locale de SQL Server, sur un réseau ou un emplacement distant (notez qu’il s’agit du même nom que lorsque vous vous connectez à SQL Server dans SQL Server Management Studio).

Nom de serveur typique	Cas
Localhost	Lorsque SQL Server est installé localement
localhost\sqlexpress	Lorsque SQL Server Express est installé localement
Domain\Servername	Lorsque SQL Server est installé dans un réseau d’entreprise
Adresse IP	Lorsque SQL Server est installé dans un emplacement distant

Étape 2: Connectez-vous à SQL Server

Une fois que SQL Spreads est installé, vous le verrez comme un nouvel onglet de ruban; allez ici et cliquez sur le bouton Mode de conception.

Dans le panneau Concepteur de Spreads SQL sur le côté droit, cliquez sur le bouton Modifier pour ouvrir la boîte de dialogue de connexion au serveur SQL.

Entrez le nom de votre serveur SQL dans le champ Nom du serveur SQL:

Sélectionnez si vous devez vous connecter à l’aide de votre identifiant Windows (authentification Windows) ou entrez un nom d’utilisateur et un mot de passe (authentification SQL Server). L’authentification Windows est la plus sécurisée des deux options (voir ici pour plus d’informations).

Cliquez sur OK. Les spreads SQL essaieront de se connecter à la base de données. Si la connexion est réussie, vos bases de données apparaîtront dans le panneau SQL Spreads Designer.

Étape 3: Sélectionnez la table pertinente dans SQL Server

Maintenant que nous avons créé la connexion d’Excel à SQL Server, nous pouvons sélectionner la table de données que nous voulons utiliser dans Excel.

Dans le concepteur de Spreads SQL, cliquez sur la base de données, puis sélectionnez votre table.

Dès que vous sélectionnez une table, les données de la table sont remplies dans la feuille Excel.

Vous pouvez maintenant voir toutes les données de votre table SQL Server et les utiliser dans votre classeur Excel. La puissance réelle des Spreads SQL est la possibilité de mettre à jour les données de la table dans SQL Server directement à partir d’Excel. Par exemple, je peux mettre à jour les prix dans la table des produits directement dans Excel et sauvegarder les modifications sur SQL Server!

Exemple: Un simple formulaire de devis dans Excel connecté à SQL Server

Revenons à notre exemple précédent du formulaire de devis et de la liste d’inventaire. Nous allons changer cela pour que le formulaire de devis obtienne ses données de la table du serveur SQL au lieu d’une liste statique dans la feuille de calcul.

Si vous souhaitez suivre cet exemple, vous devrez avoir accès à l’exemple de base de données AdventureWorks, que vous pouvez obtenir à partir d’ici.

Tout d’abord, nous allons créer une nouvelle feuille dans le classeur quote_sample et la laisser avec le nom par défaut de Sheet2 – les données de SQL Server seront remplies ici.

Ensuite, nous allons nous connecter à la base de données AdventureWorks en suivant les étapes décrites ci-dessus.

Étape (1) : Détails de connexion

Voici les détails de connexion que je vais utiliser pour me connecter à ma base de données SQL Server:

• Nom du serveur SQL : Andy\SQLExpress
• Nom de la base de données : AdventureWorks
• Table ou Vue: Production.Produit
• Informations de connexion: Authentification Windows

Étape (2): Connexion à SQL Server

Nous voulons nous connecter à la table de produits dans SQL Server et remplir la feuille « Produit » dans la feuille de calcul de devis. Pour ce faire, nous cliquons sur SQL Spreads > Mode de conception et entrons les détails de connexion et développons la base de données correcte (dans notre cas, AdventureWorks) afin que nous puissions sélectionner la table pertinente (dans notre cas, Production.Produit).

Étape (3): Sélectionnez la production.Tableau des produits dans SQL Server

Cliquez sur la production.Tableau des produits dans la liste et les données sont automatiquement remplies dans la fiche produit.

Nous pouvons maintenant utiliser ces données comme source pour nos recherches dans le modèle de devis, mais avant de le faire, nous allons voir comment filtrer les données renvoyées par SQL afin que seuls les éléments en stock soient affichés.

Cliquez sur l’onglet Filtres dans le concepteur de Spreads SQL. Sous l’option Type de filtre, nous pouvons sélectionner ‘Colonne’ ou ‘SQL personnalisé’. Nous allons sélectionner ‘SQL personnalisé’ car nous devons exécuter une requête SQL qui ne retournera que les articles en stock, ce qui signifie que nous devons joindre la table de produits à la table ProductInventory.

Lorsque vous cliquez sur l’option ‘SQL personnalisé’, une zone de texte s’affiche dans laquelle vous pouvez saisir la requête SQL. Pour notre cas d’utilisation, nous voulons renvoyer une liste de produits via une requête de jointure, nous devrons utiliser une sous-requête. Nous devons donc coller ce qui suit:

Name in (SELECT P.Name AS ProductFROM AdventureWorks.Production.Product AS PJOIN AdventureWorks.Production.ProductInventory AS PI ON P.ProductID = PI.ProductIDJOIN AdventureWorks.Production.Location AS L ON PI.LocationID = L.LocationIDwhere L.Name = 'Finished Goods Storage' AND PI.Quantity > 0GROUP BY P.Name, P.ListPrice)

dans la zone SQL personnalisée :

Lorsque vous cliquez sur « Enregistrer », les données de la table de produits sont réduites aux enregistrements filtrés. Enfin, nous allons mettre à jour la plage nommée qui a été utilisée par les recherches de validation des données dans la feuille de devis:

Et, nous devons également mettre à jour la fonction XLOOKUP utilisée par la feuille de devis pour obtenir le prix lorsqu’un utilisateur sélectionne un élément – il doit référencer la nouvelle Table2 qui est les données de SQL Server.

C’est tout! Nos représentants commerciaux peuvent désormais créer des devis basés sur les prix à la minute pour les articles actuellement en stock. Chaque fois que le représentant commercial ouvre la feuille de calcul, les données sont actualisées à partir de SQL Server.

Fonction Bonus! Mettez à jour les prix dans Excel et enregistrez-les sur SQL Server

Nous avons connecté Excel à SQL Server afin que nos représentants commerciaux puissent créer des devis en fonction des prix et de la disponibilité à la minute près.

Nous pouvons aller plus loin et offrir la possibilité à un utilisateur désigné (par exemple un administrateur) de mettre à jour les Prix de vente des articles directement dans Excel, et de sauvegarder les mises à jour dans la Production.Table des produits dans SQL Server.

Par exemple, si je voulais changer le prix du premier article de la liste à 39 $.99, je peux taper dans la cellule ListPrice et cliquer sur « Enregistrer dans la base de données » – c’est aussi simple que cela! Bien sûr, nous voudrions que cette fonctionnalité soit verrouillée uniquement à certains utilisateurs, et cela se fait facilement via des spreads SQL.

Résumé

Dans cet article, nous avons montré à quel point il est facile de connecter Excel à SQL Server à l’aide du complément SQL Spreads. Nous avons également parcouru un exemple pour montrer comment une simple feuille de calcul de devis peut utiliser des données de prix de vente à jour à partir d’une base de données SQL, et comment les prix de la base de données peuvent également être mis à jour directement à partir d’Excel.

Téléchargez la version d’essai des Spreads SQL et connectez Excel à SQL Server dès aujourd’hui.