Připojte Excel k serveru SQL: 3 Snadné kroky

v předchozím článku jsme hovořili o tom, proč potřebujete Excel i SQL ve svých řešeních pro správu dat, ale jak připojíte Excel k serveru SQL? V tomto článku vám ukážeme kroky a zvýrazníme klíčovou výhodu použití doplňku Excel, jako jsou spready SQL.

ale nejprve se chystáme znovu omezit výhody připojení aplikace Excel k datům v SQL Serveru.

výhody připojení aplikace Excel k serveru SQL

Chcete-li demonstrovat výhody připojení aplikace Excel k serveru SQL, podívejme se na příklad.

tuto tabulku používají obchodní zástupci ve výrobní společnosti k přípravě nabídek pro zákazníky. Každý obchodní zástupce má kopii tabulky, která obsahuje seznam položek inventáře a jejich ceny. Seznam položek a cen zásob je extrahován z finančního systému správcem. List nabídek používá některé vzorce vyhledávání ověření dat k načtení cen, když obchodní zástupce vybere položku z rozbalovací nabídky.

to funguje dobře, dokud organizace potřebuje aktualizovat ceny. V takovém případě by každý uživatel musel zkopírovat a vložit nové ceny do tabulky (nebo použít novou kopii tabulky). Také, když obchodní zástupce vytvoří nabídku, nevědí, zda jsou položky skutečně na skladě.

lepším řešením by bylo připojení tabulky Excel k tabulce zásob v databázi finančního systému. Pokaždé, když obchodní zástupce otevře tabulku nabídek, obnovují údaje o cenách z živé databáze. Můžeme jít o krok dále a provést připojení k databázi také přivést zpět aktuální úrovně zásob, takže citace mohou být připraveny pro položky skutečně na skladě.

abychom to shrnuli, výhody připojení aplikace Excel k serveru SQL jsou:

• máme přístup a můžeme zobrazit aktuální informace
• používáme jeden jediný důvěryhodný zdroj dat
• můžeme provádět on-the-fly analýzu nebo agregaci zdrojových dat pomocí jednoduchých funkcí Excelu, jako jsou kontingenční tabulky

Připojte Excel k databázi serveru SQL: 3 Snadné kroky

nejjednodušší způsob, jak připojit Excel k databázi serveru SQL, je pomocí spreadů SQL. SQL spready je jednoduchý doplněk aplikace Excel.

stahování SQL spreadů najdete zde a proces instalace je popsán zde.

Krok 1: Shromážděte podrobnosti o připojení databáze

pro připojení aplikace Excel k serveru SQL budete potřebovat následující informace, takže podrobnosti získáte jako první krok:

• název serveru SQL
• název databáze
• tabulka nebo pohled, který obsahuje informace, které potřebujete
• přihlašovací údaje (pokud nepoužíváte ověřování systému Windows)

forma názvu serveru SQL bude záviset na tom, zda je SQL Server nainstalován lokálně, v síti nebo na vzdáleném místě (všimněte si, že se jedná o stejný název jako při připojení k serveru SQL Server v SQL Server Management Studio).

typický název serveru	případ
Localhost	když je SQL Server nainstalován lokálně
localhost \ sqlexpress	když SQL Server Express je nainstalován lokálně
Domain \ Servername	když je SQL Server nainstalován v podnikové síti
IP adresa	když je SQL Server nainstalován na vzdáleném místě

Krok 2: Připojte se k SQL Serveru

jakmile je nainstalován SQL spread, uvidíte jej jako novou kartu pásu karet; přejděte sem a klikněte na tlačítko Design Mode.

v panelu SQL spready Designer na pravé straně klikněte na tlačítko Upravit pro otevření dialogu připojení SQL Server.

zadejte název serveru SQL do pole Název serveru SQL:

vyberte, zda se chcete připojit pomocí přihlašovacího jména systému Windows (ověřování systému Windows) nebo zadejte uživatelské jméno a heslo (ověřování serveru SQL). Ověřování systému Windows je bezpečnější ze dvou možností (další informace naleznete zde).

klikněte na OK. SQL Spready se pokusí připojit k databázi. Pokud je připojení úspěšné, vaše databáze se zobrazí v panelu SQL Spreades Designer.

Krok 3: Vyberte příslušnou tabulku v SQL Server

Nyní, když jsme vytvořili připojení z Excelu na SQL Server, můžeme vybrat, kterou tabulku dat chceme použít v Excelu.

v SQL spread Designer klikněte na databázi a poté vyberte tabulku.

Jakmile vyberete tabulku, data v tabulce vyplněná v listu aplikace Excel.

nyní můžete vidět všechna data v tabulce serveru SQL a použít je v sešitu aplikace Excel. Skutečnou silou spreadů SQL je schopnost aktualizovat data v tabulce v SQL Serveru přímo z Excelu. Například mohu aktualizovat ceny v tabulce produktů přímo v aplikaci Excel a uložit změny zpět na server SQL!

příklad: jednoduchý formulář nabídky v aplikaci Excel připojený k serveru SQL

vraťme se k našemu dřívějšímu příkladu formuláře nabídky a seznamu zásob. Změníme to tak, aby formulář nabídky získal svá data z tabulky na serveru SQL místo statického seznamu v tabulce.

pokud chcete následovat tento příklad, musíte mít přístup k ukázkové databázi AdventureWorks, kterou můžete získat odtud.

nejprve vytvoříme nový list v sešitu quote_sample a ponecháme jej s výchozím názvem Sheet2 – zde budou vyplněna data z SQL Serveru.

dále se připojíme k databázi AdventureWorks pomocí výše uvedených kroků.

Krok (1) : Podrobnosti o připojení

zde jsou podrobnosti o připojení, které použiji pro připojení k databázi SQL Server:

• název serveru SQL: Andy \ SQLExpress
• název databáze: AdventureWorks
• tabulka nebo pohled: výroba.Produkt
• přihlašovací údaje: ověřování systému Windows

Krok (2): Připojení k serveru SQL

chceme se připojit k tabulce produktů v SQL Serveru a naplnit list „produktu“ v listu nabídky. Za tímto účelem klikneme na SQL Spreades > Design Mode a zadáme podrobnosti připojení a rozbalíme správnou databázi (v našem případě AdventureWorks), abychom mohli vybrat příslušnou tabulku (v našem případě výrobu.Produkt).

Krok (3): Vyberte produkci.Tabulka produktů v SQL Serveru

klikněte na Production.Tabulka produktů v seznamu a data jsou automaticky vyplněna do produktového listu.

Nyní můžeme použít tato data jako zdroj pro naše vyhledávání v šabloně nabídky, ale než to uděláme, podíváme se na to, jak filtrovat data vrácená z SQL tak, aby se zobrazovaly pouze položky na skladě.

klikněte na záložku filtry v SQL spread Designer. V části typ filtru můžeme vybrat „sloupec“ nebo „vlastní SQL“. Vybereme „vlastní SQL“, protože musíme spustit dotaz SQL, který vrátí pouze položky na skladě, a to znamená, že se musíme připojit k tabulce produktů do tabulky ProductInventory.

když kliknete na možnost „vlastní SQL“, zobrazí se textové pole, kde můžete zadat dotaz SQL. Pro náš případ použití chceme vrátit seznam produktů pomocí dotazu připojit, budeme muset použít dílčí dotaz. Takže musíme vložit následující:

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)

v poli Custom SQL box:

po klepnutí na tlačítko „Uložit“ se data v tabulce produktů sníží na filtrované záznamy. Nakonec aktualizujeme pojmenovaný rozsah, který byl použit vyhledáváním ověření dat v citačním listu:

a musíme také aktualizovat funkci XLOOKUP, kterou citační list používá k získání ceny, když uživatel vybere položku-musí odkazovat na novou Tabulku2 což jsou data z SQL Serveru.

to je ono! Naši obchodní zástupci nyní mohou vytvářet kotace na základě aktuálních cen položek, které jsou aktuálně na skladě. Pokaždé, když obchodní zástupce otevře tabulku, budou data aktualizována z SQL Serveru.

Bonusová Funkce! Aktualizace cen v Excelu a uložit na SQL Server

připojili jsme Excel k SQL Serveru, takže naši obchodní zástupci mohou vytvářet nabídky na základě up-to-the-minute cen a dostupnosti.

můžeme jít o krok dále a poskytnout možnost určenému uživateli (např.Tabulka produktu v SQL Serveru.

například, pokud jsem chtěl změnit cenu první položky v seznamu na 39$.99, mohu zadat buňku ListPrice a kliknout na “ Uložit do databáze – – je to tak snadné! Samozřejmě bychom chtěli, aby tato schopnost byla uzamčena pouze pro určité uživatele, a to lze snadno provést pomocí spreadů SQL.

shrnutí

v tomto článku jsme ukázali, jak snadné je připojení aplikace Excel k serveru SQL pomocí doplňku SQL Spreades. Také jsme prošli příkladem, abychom ukázali, jak může tabulka jednoduchých nabídek používat aktuální údaje o prodejních cenách z databáze SQL a jak lze ceny v databázi také aktualizovat přímo z aplikace Excel.

Stáhněte si zkušební verzi SQL spreadů a připojte Excel k SQL Serveru ještě dnes.