Collegare Excel a SQL Server: 3 semplici passaggi

In un precedente articolo, abbiamo parlato del motivo per cui è necessario sia Excel che SQL nelle soluzioni di gestione dei dati, ma come si collega Excel a SQL Server? In questo articolo, ti mostreremo i passaggi ed evidenzieremo un vantaggio chiave dell’utilizzo di un componente aggiuntivo di Excel come gli spread SQL.

Ma, in primo luogo, stiamo andando a ri-cap i vantaggi di collegare Excel ai dati in SQL Server.

Vantaggi della connessione di Excel a SQL Server

Per dimostrare i vantaggi della connessione di Excel a SQL Server, diamo un’occhiata a un esempio.

Questo foglio di calcolo viene utilizzato dai rappresentanti di vendita di un’azienda manifatturiera per preparare preventivi per i clienti. Ogni rappresentante di vendita ha una copia del foglio di calcolo, che include un elenco di articoli di magazzino e dei loro prezzi. L’elenco degli articoli di magazzino e dei prezzi viene estratto dal sistema finanziario da un amministratore. Il foglio preventivo utilizza alcune formule di ricerca di convalida dei dati per recuperare i prezzi quando un rappresentante di vendita seleziona un articolo dal menu a discesa.

Funziona bene fino a quando l’organizzazione non deve aggiornare i prezzi. In tal caso, ogni utente dovrebbe copiare e incollare il nuovo prezzo nel foglio di calcolo (o utilizzare una nuova copia del foglio di calcolo). Inoltre, quando il rappresentante di vendita crea il preventivo, non sa se gli articoli sono effettivamente in magazzino.

Una soluzione migliore sarebbe quella di collegare il foglio di calcolo Excel alla tabella di inventario nel database del sistema finanziario. Ogni volta che il rappresentante di vendita apre il foglio di calcolo preventivo, aggiorna i dati dei prezzi dal database live. Possiamo fare un ulteriore passo avanti e rendere la connessione al database anche riportare i livelli attuali delle scorte in modo che le quotazioni possano essere preparate per gli articoli effettivamente in magazzino.

Quindi, per riassumere, i vantaggi della connessione di Excel a SQL Server sono:

• abbiamo accesso e vista up-to-data informazioni
• stiamo usando una singola, dati attendibili fonte
• siamo in grado di eseguire on-the-fly di analisi o di aggregazione dei dati di origine utilizzando semplici funzioni di Excel come le tabelle pivot

Collegare Excel in un database di SQL Server : 3 semplici passi

Il modo più semplice per collegare Excel in un database di SQL Server con SQL Spread. SQL Spreads è un semplice componente aggiuntivo di Excel.

Il download di SQL Spreads può essere trovato qui e il processo di installazione è descritto qui.

Fase 1: Raccogliere la connessione al database dettagli

Hai bisogno delle seguenti informazioni per la connessione di Excel a SQL Server, in modo da ottenere i dettagli come il primo passo:

• il Nome del Server SQL
• Nome del Database
• Tabella o di una Vista che contiene le informazioni di cui hai bisogno
• dettagli di Login (se non si utilizza l’autenticazione di Windows)

La forma del Nome di SQL Server dipende dal fatto che SQL Server è installato in locale, in rete o in una posizione remota (nota che questo è il nome stesso come quando ci si connette a SQL Server in SQL Server Management Studio).

Tipico nome del server	Caso
Localhost	Quando SQL Server è installato in locale
localhost\sqlexpress	Quando è installato SQL Server Express in locale
Dominio\Servername	Quando è installato SQL Server in una rete aziendale
l’indirizzo IP	Quando SQL Server è installato in una posizione remota

Passo 2: Connetti a SQL Server

Una volta installato SQL Spreads, lo vedrai come una nuova scheda ribbon; vai qui e fai clic sul pulsante Design Mode.

Nel pannello SQL Spreads Designer sul lato destro, fare clic sul pulsante Modifica per aprire la finestra di dialogo Connessione SQL Server.

Immettere il nome di SQL Server nel campo Nome SQL Server:

Selezionare se è necessario connettersi utilizzando il login di Windows (autenticazione di Windows) o immettere un nome utente e una password (autenticazione di SQL Server). L’autenticazione di Windows è la più sicura delle due opzioni (vedi qui per ulteriori informazioni).

Fare clic su OK. Gli spread SQL proveranno a connettersi al database. Se la connessione ha esito positivo, i database verranno visualizzati nel pannello SQL Spreads Designer.

Passo 3: Selezionare la tabella pertinente in SQL Server

Ora che abbiamo creato la connessione da Excel a SQL Server, possiamo selezionare quale tabella di dati vogliamo utilizzare in Excel.

In SQL Spreads Designer, fare clic sul database e quindi selezionare la tabella.

Non appena si seleziona una tabella, i dati nella tabella popolati nel foglio Excel.

Ora è possibile visualizzare tutti i dati nella tabella SQL Server e utilizzarli nella cartella di lavoro di Excel. Il vero potere con gli spread SQL è la possibilità di aggiornare i dati nella tabella in SQL Server direttamente da Excel. Ad esempio, posso aggiornare i prezzi nella tabella dei prodotti direttamente in Excel e salvare le modifiche su SQL Server!

Esempio: un semplice modulo di citazione in Excel collegato a SQL Server

Torniamo al nostro precedente esempio del modulo di citazione e dell’elenco di inventario. Cambieremo questo in modo che il modulo di citazione ottenga i suoi dati dalla tabella nel server SQL invece di un elenco statico nel foglio di calcolo.

Se si desidera seguire questo esempio, è necessario avere accesso al database di esempio AdventureWorks, che è possibile ottenere da qui.

Per prima cosa, creeremo un nuovo foglio nella cartella di lavoro quote_sample e lo lasceremo con il nome predefinito di Sheet2 – i dati da SQL Server verranno popolati qui.

Successivamente, ci collegheremo al database AdventureWorks utilizzando i passaggi descritti sopra.

Punto (1) : Dettagli di connessione

Ecco i dettagli di connessione che userò per connettermi al mio database SQL Server:

• Nome del server SQL: Andy \ SQLExpress
• Nome del database: AdventureWorks
• Tabella o Vista: Produzione.Prodotto
• Dettagli di accesso: autenticazione Windows

Step (2): Connessione a SQL Server

Vogliamo connetterci alla tabella dei prodotti in SQL Server e compilare il foglio “Prodotto” nel foglio di lavoro preventivo. Per fare ciò, facciamo clic su SQL Spreads > Modalità di progettazione e inseriamo i dettagli della connessione ed espandiamo il database corretto (nel nostro caso, AdventureWorks) in modo da poter selezionare la tabella pertinente (nel nostro caso, Produzione.Prodotto).

Step (3): Selezionare la Produzione.Tabella dei prodotti in SQL Server

Fare clic sulla Produzione.Tabella dei prodotti nell’elenco e i dati vengono popolati automaticamente nella scheda prodotto.

Ora possiamo utilizzare questi dati come fonte per le nostre ricerche nel modello di citazione, ma prima di farlo, vedremo come filtrare i dati restituiti da SQL in modo che vengano visualizzati solo gli articoli in magazzino.

Fare clic sulla scheda Filtri in SQL Spreads Designer. Sotto l’opzione Tipo di filtro, possiamo selezionare ‘Colonna’ o ‘SQL personalizzato’. Stiamo andando a selezionare ‘SQL personalizzato’ perché abbiamo bisogno di eseguire una query SQL che restituirà solo gli articoli in magazzino, e questo significa che abbiamo bisogno di unire la tabella del prodotto alla tabella ProductInventory.

Quando si fa clic sull’opzione ‘SQL personalizzato’, viene visualizzata una casella di testo in cui è possibile immettere la query SQL. Per il nostro caso d’uso, vogliamo restituire un elenco di prodotti tramite una query join, dovremo utilizzare una query secondaria. Quindi, dobbiamo incollare quanto segue:

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)

nella casella SQL personalizzato:

Quando si fa clic su “Salva”, i dati nella tabella Prodotto vengono ridotti ai record filtrati. Infine, aggiorneremo l’intervallo denominato utilizzato dalle ricerche di convalida dei dati nel foglio delle quotazioni:

E, abbiamo anche bisogno di aggiornare la funzione XLOOKUP che il foglio di citazione utilizza per ottenere il prezzo quando un utente seleziona un elemento – ha bisogno di fare riferimento alla nuova Table2 che è i dati da SQL Server.

Il gioco è fatto! I nostri rappresentanti di vendita possono ora creare preventivi basati su prezzi aggiornati per gli articoli attualmente in magazzino. Ogni volta che il rappresentante di vendita apre il foglio di calcolo, i dati verranno aggiornati da SQL Server.

Bonus! Aggiornamento dei prezzi in Excel e salvataggio in SQL Server

Abbiamo collegato Excel a SQL Server in modo che i nostri rappresentanti di vendita possano creare preventivi basati su prezzi e disponibilità aggiornati.

Possiamo fare un ulteriore passo avanti e fornire la possibilità per un utente designato (ad esempio un Amministratore) di aggiornare i Prezzi di vendita degli articoli direttamente in Excel, e avere gli aggiornamenti salvati di nuovo per l ‘Produzione.Tabella del prodotto in SQL Server.

Ad esempio, se volessi cambiare il prezzo del primo elemento nell’elenco a $39.99, posso digitare la cella ListPrice e fare clic su “Salva nel database” – è così facile! Naturalmente, vorremmo che questa funzionalità fosse bloccata solo per determinati utenti, e questo è fatto facilmente attraverso gli spread SQL.

Riepilogo

In questo articolo abbiamo mostrato quanto sia facile connettere Excel a SQL Server utilizzando il componente aggiuntivo SQL Spreads. Abbiamo anche esaminato un esempio per mostrare come un semplice foglio di calcolo quotes può utilizzare dati aggiornati sui prezzi di vendita da un database SQL e come i prezzi nel database possono anche essere aggiornati direttamente da Excel.

Scarica la versione di prova di SQL Spreads e connetti Excel a SQL Server oggi stesso.