Połącz Excel z SQL Server: 3 proste kroki

w poprzednim artykule rozmawialiśmy o tym, dlaczego potrzebujesz zarówno Excela, jak i SQL w swoich rozwiązaniach do zarządzania danymi, ale jak podłączyć Excel do SQL Server? W tym artykule pokażemy kroki i podkreślimy kluczową zaletę korzystania z dodatku Excel, takiego jak spready SQL.

ale najpierw ponownie wykorzystamy zalety połączenia Excela z danymi w SQL Server.

korzyści z połączenia programu Excel z serwerem SQL

aby zademonstrować korzyści z połączenia programu Excel z serwerem SQL, spójrzmy na przykład.

ten arkusz kalkulacyjny jest używany przez przedstawicieli handlowych w firmie produkcyjnej do przygotowania ofert dla klientów. Każdy przedstawiciel handlowy ma kopię arkusza kalkulacyjnego, który zawiera listę pozycji zapasów i ich ceny. Lista pozycji zapasów i cen jest pobierana z systemu finansowego przez administratora. Arkusz wyceny wykorzystuje niektóre formuły sprawdzania poprawności danych, aby pobrać ceny, gdy przedstawiciel handlowy wybierze element z rozwijanego menu.

działa to dobrze, dopóki organizacja nie będzie musiała aktualizować cen. W takim przypadku każdy użytkownik musiałby skopiować i wkleić nową cenę do arkusza kalkulacyjnego (lub użyć nowej kopii arkusza kalkulacyjnego). Ponadto, gdy przedstawiciel handlowy tworzy ofertę, nie wiedzą, czy przedmioty są rzeczywiście w magazynie.

lepszym rozwiązaniem byłoby podłączenie arkusza kalkulacyjnego Excel do tabeli zapasów w bazie danych systemu finansowego. Za każdym razem, gdy przedstawiciel handlowy otwiera arkusz kalkulacyjny, odświeża dane cenowe z aktywnej bazy danych. Możemy pójść o krok dalej i nawiązać połączenie z bazą danych, a także przywrócić bieżące stany magazynowe, aby można było przygotować oferty dla przedmiotów faktycznie znajdujących się w magazynie.

podsumowując, korzyści płynące z połączenia Excela z SQL Server to:

• mamy dostęp i możemy przeglądać aktualne informacje
• korzystamy z jednego zaufanego źródła danych
• możemy przeprowadzić analizę w locie lub agregację danych źródłowych za pomocą prostych funkcji programu Excel, takich jak tabele przestawne

podłącz program Excel do bazy danych SQL Server : 3 proste kroki

najprostszym sposobem połączenia Excela z bazą danych SQL Server jest użycie spreadów SQL. Spready SQL to prosty dodatek do programu Excel.

Pobierz SQL Spreads można znaleźć tutaj, a proces instalacji jest opisany tutaj.

Krok 1: Zbierz szczegóły połączenia z bazą danych

aby połączyć program Excel z serwerem SQL, potrzebujesz następujących informacji, więc uzyskaj szczegóły jako pierwszy krok:

• nazwa serwera SQL
• Nazwa bazy danych
• tabela lub widok zawierający potrzebne informacje
• dane logowania (jeśli nie przy użyciu uwierzytelniania systemu Windows)

forma nazwy SQL Server będzie zależeć od tego, czy SQL Server jest zainstalowany lokalnie, w sieci, czy w lokalizacji zdalnej (zauważ, że jest to ta sama nazwa, co podczas łączenia się z SQL Server w SQL Server Management Studio).

typowa nazwa serwera
Localhost	gdy serwer SQL jest zainstalowany lokalnie
localhost\sqlexpress	gdy SQL Server Express jest zainstalowany lokalnie
domena\Nazwa serwera	gdy serwer SQL jest zainstalowany w sieci firmowej
adres IP	gdy SQL Server jest zainstalowany w zdalnej lokalizacji

Krok 2: Połącz się z SQL Server

po zainstalowaniu spreadów SQL zobaczysz go jako nową kartę wstążki; przejdź tutaj i kliknij przycisk Tryb projektowania.

w Panelu projektant spreadów SQL po prawej stronie kliknij przycisk Edytuj, aby otworzyć okno dialogowe połączenia z serwerem SQL.

wprowadź nazwę swojego serwera SQL w polu Nazwa serwera SQL:

Wybierz, czy chcesz połączyć się za pomocą loginu systemu Windows (uwierzytelnianie systemu Windows) lub wprowadź nazwę użytkownika i hasło (Uwierzytelnianie serwera SQL). Uwierzytelnianie systemu Windows jest bezpieczniejszą z dwóch opcji (zobacz tutaj, aby uzyskać więcej informacji).

kliknij OK. Spready SQL spróbują połączyć się z bazą danych. Jeśli połączenie się powiedzie, Twoje bazy danych pojawią się w Panelu projektant spreadów SQL.

Krok 3: Wybierz odpowiednią tabelę w SQL Server

teraz, gdy utworzyliśmy połączenie z Excela do SQL Server, możemy wybrać tabelę danych, której chcemy użyć w Excelu.

w projektorze spreadów SQL kliknij bazę danych, a następnie wybierz tabelę.

gdy tylko wybierzesz tabelę, dane w tabeli zostaną wypełnione w arkuszu programu Excel.

możesz teraz zobaczyć wszystkie dane w tabeli SQL Server i użyć ich w skoroszycie programu Excel. Prawdziwą siłą spreadów SQL jest możliwość aktualizacji danych w tabeli w SQL Server bezpośrednio z programu Excel. Na przykład mogę zaktualizować ceny w tabeli produktów bezpośrednio w programie Excel i zapisać zmiany z powrotem na SQL Server!

przykład: prosty formularz wyceny w Excelu połączony z serwerem SQL

wróćmy do naszego wcześniejszego przykładu formularza wyceny i listy inwentaryzacji. Zmienimy to tak, aby formularz cytowania pobierał dane z tabeli w SQL server zamiast statycznej listy w arkuszu kalkulacyjnym.

jeśli chcesz śledzić ten przykład, musisz mieć dostęp do przykładowej bazy danych AdventureWorks, którą możesz uzyskać tutaj.

najpierw utworzymy nowy arkusz w skoroszycie quote_sample i zostawimy go z domyślną nazwą Sheet2 – dane z serwera SQL zostaną wypełnione tutaj.

następnie połączymy się z bazą danych AdventureWorks, wykonując kroki opisane powyżej.

Krok (1) : Szczegóły połączenia

oto szczegóły połączenia, których użyję do połączenia z moją bazą danych SQL Server:

• nazwa serwera SQL: Andy\SQLEXPRESS
• Nazwa bazy danych: AdventureWorks
• tabela lub widok: produkcja.Produkt
• dane logowania: uwierzytelnianie systemu Windows

Krok (2): Połącz się z SQL Server

chcemy połączyć się z tabelą produktów w SQL Server i wypełnić arkusz „produkt” w arkuszu wyceny. W tym celu klikamy na SQL Spreads > Design Mode i wprowadzamy szczegóły połączenia oraz rozwijamy poprawną bazę danych (w naszym przypadku AdventureWorks) tak abyśmy mogli wybrać odpowiednią tabelę (w naszym przypadku Production.Produkt).

Krok (3): Wybierz produkcję.Tabela produktów w SQL Server

kliknij na Production.Tabela produktów na liście i dane są automatycznie wypełniane w karcie produktu.

możemy teraz użyć tych danych jako źródła dla naszych wyszukiwań w szablonie Cytuj, ale zanim to zrobimy, przyjrzymy się, jak filtrować dane zwracane z SQL, aby wyświetlały się tylko pozycje w magazynie.

kliknij kartę Filtry w Projektancie spreadów SQL. Pod opcją Filter Type możemy wybrać 'Column’ lub 'Custom SQL’. Wybierzemy 'Custom SQL’, ponieważ musimy uruchomić zapytanie SQL, które zwróci tylko pozycje w magazynie, a to oznacza, że musimy dołączyć tabelę produktów do tabeli ProductInventory.

po kliknięciu opcji „Niestandardowy SQL” zostanie wyświetlone pole tekstowe, w którym można wprowadzić zapytanie SQL. W naszym przypadku użycia chcemy zwrócić listę produktów za pomocą zapytania join, musimy użyć zapytania podrzędnego. Tak więc, musimy wkleić następujące:

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)

w polu Niestandardowy SQL:

po kliknięciu przycisku „Zapisz” dane w tabeli produktów zostaną zredukowane do przefiltrowanych rekordów. Na koniec zaktualizujemy nazwany zakres, który był używany przez wyszukiwanie walidacji danych w arkuszu Wyceny:

i, musimy również zaktualizować funkcję XLOOKUP, której używa arkusz Quote, aby uzyskać cenę, gdy użytkownik wybierze element – musi odwołać się do nowej Table2, która jest danymi z SQL Server.

to wszystko! Nasi przedstawiciele handlowi mogą teraz tworzyć oferty na podstawie aktualnych cen produktów, które są obecnie w magazynie. Za każdym razem, gdy przedstawiciel handlowy otworzy arkusz kalkulacyjny, dane będą odświeżane z SQL Server.

Bonus! Zaktualizuj ceny w Excelu i zapisz na SQL Server

połączyliśmy Excel z SQL Server, aby nasi przedstawiciele handlowi mogli tworzyć oferty na podstawie aktualnych cen i dostępności.

możemy pójść o krok dalej i zapewnić wyznaczonemu użytkownikowi (np. administratorowi) możliwość aktualizacji cen sprzedaży artykułów bezpośrednio w programie Excel i zapisywania aktualizacji z powrotem do 'produkcji.Product ’ tabela w SQL Server.

na przykład, jeśli chciałem zmienić cenę pierwszego elementu na liście na 39 USD.99, mogę wpisać komórkę ListPrice i kliknąć ” Zapisz do bazy danych – – to takie proste! Oczywiście chcielibyśmy, aby ta zdolność była zablokowana tylko dla niektórych użytkowników, a można to łatwo zrobić za pomocą spreadów SQL.

podsumowanie

w tym artykule pokazaliśmy, jak łatwo jest połączyć Excel z SQL Server za pomocą dodatku SQL Spreads. Przejrzeliśmy również przykład, aby pokazać, w jaki sposób prosty arkusz kalkulacyjny może wykorzystywać aktualne dane cen sprzedaży z bazy danych SQL, a także w jaki sposób ceny w bazie danych mogą być aktualizowane bezpośrednio z programu Excel.

Pobierz wersję próbną spreadów SQL i połącz Excel z SQL Server już dziś.