Anslut Excel till SQL Server: 3 enkla steg

i en tidigare artikel talade vi om varför du behöver både Excel och SQL i dina datahanteringslösningar, men hur ansluter du Excel till SQL Server? I den här artikeln kommer vi att visa dig stegen och markera en viktig fördel med att använda ett Excel-tillägg som SQL-spridningar.

men först, vi kommer att åter cap fördelarna med att ansluta Excel till data i SQL Server.

fördelar med att ansluta Excel till SQL Server

för att visa fördelarna med att ansluta Excel till SQL Server, låt oss titta på ett exempel.

detta kalkylblad används av säljare på ett tillverkningsföretag för att förbereda offerter för kunder. Varje säljare har en kopia av kalkylbladet, som innehåller en lista över lagervaror och deras priser. Listan över lagervaror och priser extraheras från finanssystemet av en administratör. Offertbladet använder vissa datavalideringsformler för att hämta priserna när en säljare väljer ett objekt från rullgardinsmenyn.

detta fungerar bra tills organisationen behöver uppdatera priserna. I så fall måste varje användare kopiera och klistra in den nya prissättningen i kalkylbladet (eller använda en ny kopia av kalkylbladet). När säljaren skapar offerten vet de inte om artiklarna faktiskt finns i lager.

en bättre lösning skulle vara att ansluta Excel-kalkylbladet till inventeringstabellen i finanssystemets databas. Varje gång säljaren öppnar offertkalkylbladet uppdaterar de prisdata från live-databasen. Vi kan gå ett steg längre och göra anslutningen till databasen också ta tillbaka de nuvarande lagernivåerna så att Citat kan förberedas för artiklar som faktiskt finns i lager.

så för att sammanfatta är fördelarna med att ansluta Excel till SQL Server:

• vi har tillgång till och kan visa aktuell information
• vi använder en enda, pålitlig datakälla
• vi kan utföra on-the-fly analys eller aggregering av källdata med hjälp av enkla Excel-funktioner som pivottabeller

Anslut Excel till en SQL Server-databas : 3 enkla steg

det enklaste sättet att ansluta Excel till en SQL Server-databas är med SQL-spridningar. SQL Spreads är ett enkelt Excel-tillägg.

SQL sprider nedladdning kan hittas här, och installationsprocessen beskrivs här.

Steg 1: Samla databasanslutningsinformationen

du behöver följande information för att ansluta Excel till SQL Server, så få detaljerna som det första steget:

• SQL Server namn
• databasnamn
• tabell eller vy som innehåller den information du behöver
• inloggningsuppgifter (om du inte använder Windows-autentisering)

formen för SQL Server-namnet beror på om SQL Server är installerat lokalt, i ett nätverk eller en fjärrplats (Observera att detta är samma namn som när du ansluter till SQL Server i SQL Server Management Studio).

typiskt servernamn	Case
Localhost	när SQL Server installeras lokalt
localhost\sqlexpress	när SQL Server Express installeras lokalt
Domain \ Servername	när SQL Server är installerat i ett företagsnätverk
IP-adress	när SQL Server installeras på en avlägsen plats

steg 2: Anslut till SQL Server

när SQL-spridningar är installerade ser du det som en ny ribbon-flik; gå hit och klicka på Design Mode-knappen.

i SQL Spreads Designer-panelen till höger klickar du på knappen Redigera för att öppna dialogrutan SQL Server connection.

ange namnet på din SQL Server i fältet SQL Server name:

Välj om du ska ansluta med din Windows-inloggning (Windows-autentisering) eller ange ett användarnamn och lösenord (SQL Server-autentisering). Windows-autentisering är det säkrare av de två alternativen (se här för mer information).

klicka på OK. SQL-spridningar försöker ansluta till databasen. Om anslutningen lyckas visas dina databaser i SQL Spreads Designer-panelen.

steg 3: Välj relevant tabell i SQL Server

nu när vi har skapat anslutningen från Excel till SQL Server kan vi välja vilken datatabell vi vill använda i Excel.

i SQL Spreads Designer, klicka på databasen och välj sedan din tabell.

så snart du väljer en tabell fylls data i tabellen i Excel-arket.

du kan nu se alla data i SQL Server-tabellen och använda den i Excel-arbetsboken. Den verkliga kraften med SQL-spridningar är möjligheten att uppdatera data i tabellen i SQL Server direkt från Excel. Till exempel kan jag uppdatera priserna i produktabellen direkt i Excel och spara ändringarna tillbaka till SQL Server!

exempel: ett enkelt Citatformulär i Excel anslutet till SQL Server

Låt oss gå tillbaka till vårt tidigare exempel på citatformuläret och inventeringslistan. Vi kommer att ändra detta så att citatformuläret får sina data från tabellen i SQL server istället för en statisk lista i kalkylbladet.

om du vill följa med det här exemplet måste du ha tillgång till AdventureWorks exempeldatabas, som du kan få härifrån.

först ska vi skapa ett nytt ark i arbetsboken quote_sample och lämna det med standardnamnet på Sheet2 – data från SQL Server kommer att fyllas här.

därefter kommer vi att ansluta till AdventureWorks-databasen med hjälp av stegen som beskrivs ovan.

Steg (1) : Anslutningsdetaljer

här är anslutningsinformationen jag ska använda för att ansluta till min SQL Server-databas:

• SQL Server namn: Andy\SQLExpress
• databasnamn: AdventureWorks
• tabell eller vy: produktion.Produkt
• inloggningsuppgifter: Windows-autentisering

steg (2): Anslut till SQL Server

vi vill ansluta till produkttabellen i SQL Server och fylla i produktbladet i citatkalkylbladet. För att göra detta klickar vi på SQL Spreads > designläge och anger anslutningsinformationen och utökar rätt databas (i vårt fall AdventureWorks) så att vi kan välja relevant tabell (i vårt fall produktion.Produkt).

steg (3): Välj produktion.Produkttabell i SQL Server

klicka på produktionen.Produkttabell i listan och data fylls automatiskt i produktbladet.

vi kan nu använda dessa data som källa för våra sökningar i Offertmallen, men innan vi gör det kommer vi att titta på hur man filtrerar data som returneras från SQL så att endast lagervaror visas.

klicka på fliken Filter i SQL Spreads Designer. Under alternativet filtertyp kan vi välja ’kolumn ’ eller’anpassad SQL’. Vi kommer att välja ’Anpassad SQL’ eftersom vi måste köra en SQL-fråga som bara returnerar objekt i lager, och det betyder att vi måste gå med i Produktabellen till ProductInventory-tabellen.

när du klickar på alternativet ’Anpassad SQL’ visas en textruta där du kan ange SQL-frågan. För vårt användningsfall vill vi returnera en lista över produkter via en kopplingsfråga, vi måste använda en underfråga. Så vi måste klistra in följande:

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)

i rutan Anpassad SQL:

när du klickar på ’Spara’ reduceras data i Produktabellen till de filtrerade posterna. Slutligen kommer vi att uppdatera det namngivna intervallet som användes av datavalideringsuppslagningarna i Offertbladet:

och vi måste också uppdatera xlookup-funktionen som Citatbladet använder för att få priset när en användare väljer ett objekt – det måste referera till den nya Tabellen2 som är data från SQL Server.

det är det! Våra säljare kan nu skapa offerter baserade på aktuella priser för artiklar som för närvarande finns i lager. Varje gång säljaren öppnar kalkylbladet uppdateras data från SQL Server.

Bonus Funktion! Uppdatera priser i Excel och spara till SQL Server

vi har anslutit Excel till SQL Server så att våra säljare kan skapa offerter baserade på up-to-the-minuten priser och tillgänglighet.

vi kan gå ett steg längre och ge möjlighet för en utsedd användare (t.ex. en administratör) att uppdatera försäljningspriserna för artiklarna direkt i Excel och få uppdateringarna sparade tillbaka till ’produktionen.Produkt ’ tabell i SQL Server.

till exempel om jag ville ändra priset på det första objektet i listan till $39.99, jag kan skriva in ListPrice-cellen och klicka på ’Spara till Databas’ – det är så enkelt! Naturligtvis vill vi ha den här funktionen låst till vissa användare, och det görs enkelt genom SQL-spridningar.

sammanfattning

i den här artikeln har vi visat hur enkelt det är att ansluta Excel till SQL Server med SQL Spreads-tillägget. Vi gick också igenom ett exempel för att visa hur ett enkelt citatkalkylblad kan använda uppdaterade försäljningsprisdata från en SQL-databas och hur priserna i databasen också kan uppdateras direkt från Excel.

ladda ner testversionen av SQL Spreads och anslut Excel till SQL Server idag.