Uruchamianie zapytań SQL w plikach programu Excel

Chociaż akcje programu Excel mogą obsługiwać większość scenariuszy automatyzacji programu Excel, zapytania SQL mogą pobierać i manipulować znacznymi ilościami danych programu Excel bardziej efektywnie.

Załóżmy, że przepływ musi zmodyfikować tylko te rejestry programu Excel, które zawierają określoną wartość. Aby osiągnąć tę funkcjonalność bez zapytań SQL, potrzebne są pętle, warunki i wiele akcji Excela.

Można również zaimplementować tę funkcjonalność za pomocą zapytań SQL, używając tylko dwóch akcji: Otwórz połączenie SQL oraz Wykonaj instrukcje akcji SQL.

Otwieranie połączenia SQL z plikiem programu Excel

Przed uruchomieniem zapytania SQL należy otworzyć połączenie z plikiem programu Excel, do którego chcemy uzyskać dostęp.

Aby nawiązać połączenie, należy utworzyć nową zmienną o nazwie %Excel_File_Path% i zainicjować ją ścieżką do pliku Excela. Opcjonalnie możesz pominąć ten krok i użyć zakodowanej na stałe ścieżki do pliku w dalszej części przepływu.

Teraz wdrażamy akcję Otwórz połączenie SQL i wpisujemy następujący ciąg połączenia w jej właściwościach.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%Excel_File_Path%;Extended Properties="Excel 12.0 Xml;HDR=YES";

Uwaga

Aby pomyślnie użyć przedstawionego ciągu połączenia, należy pobrać i zainstalować Microsoft Access Database Engine 2010.

Otwieranie połączenia SQL z plikiem programu Excel chronionym hasłem

Inne podejście jest wymagane w scenariuszach, w których uruchamiamy zapytania SQL na chronionych hasłem plikach Excela. Akcja Otwórz połączenie SQL nie może połączyć się z plikami Excela chronionymi hasłem, dlatego należy usunąć zabezpieczenie.

W tym celu należy uruchomić plik programu Excel za pomocą akcji Uruchom program Excel. Plik jest chroniony hasłem, dlatego wprowadź odpowiednie hasło w polu Hasło.

Następnie wdrażamy odpowiednie akcje automatyzacji UI i przechodzimy do Plik>Informacje>Ochrona skoroszytu>Zaszyfruj za pomocą hasła. Więcej informacji na temat automatyzacji interfejsu użytkownika oraz sposobu korzystania z odpowiednich akcji można znaleźć w temacie Automatyzowanie aplikacji klasycznych.

Po wybraniu opcji Szyfruj za pomocą hasła wpisz pusty ciąg znaków w wyskakującym oknie dialogowym za pomocą akcji Wypełnij pole tekstowe w oknie. Aby wypełnić pusty ciąg, użyj następującego wyrażenia: %""%.

Aby nacisnąć przycisk OK w oknie dialogowym i zastosować zmiany, należy wdrożyć Naciśnij przycisk w akcji okna.

Na koniec wdróż akcję Zamknij program Excel, aby zapisać niechroniony skoroszyt jako nowy plik programu Excel.

Po zapisaniu pliku postępuj zgodnie z instrukcjami w części Otwieranie połączenia SQL z plikiem programu Excel, aby otworzyć połączenie z nim.

Po zakończeniu modyfikowania pliku programu Excel użyj akcji Usuń plik , aby usunąć niechronioną kopię pliku programu Excel.

Odczytaj zawartość arkusza kalkulacyjnego Excel

Chociaż akcja Odczytaj z arkusza Excela może odczytać zawartość arkusza Excela, iteracja po pobranych danych może zająć dużo czasu.

Bardziej efektywnym sposobem pobierania określonych wartości z arkuszy kalkulacyjnych jest traktowanie plików Excela jako baz danych i wykonywanie na nich zapytań SQL. Takie podejście jest szybsze i zwiększa wydajność przepływu.

Aby pobrać całą zawartość arkusza kalkulacyjnego, możesz użyć następującego zapytania SQL w akcji Wykonaj instrukcje akcji SQL.

SELECT * FROM [SHEET$]

Uwaga

Aby zastosować to zapytanie SQL w swoich przepływach, należy zastąpić znacznik miejsca SHEET nazwą arkusza kalkulacyjnego, do którego chcemy uzyskać dostęp.

Aby pobrać wiersze zawierające określoną wartość w określonej kolumnie, należy użyć następującego zapytania SQL:

SELECT * FROM [SHEET$] WHERE [COLUMN NAME] = 'VALUE'

Uwaga

Aby zastosować to zapytanie SQL do przepływów, zastąp:

• SHEET nazwą arkusza kalkulacyjnego, do którego chcesz uzyskać dostęp.
• COLUMN NAME wartością kolumny, którą chcesz znaleźć. Kolumny w pierwszym wierszu arkusza programu Excel są identyfikowane jako nazwy kolumn tabeli.
• VALUE wartością, którą chcesz znaleźć.

Usuwanie danych z wiersza w Excelu

Chociaż Excel nie obsługuje zapytania SQL DELETE, możesz użyć zapytania UPDATE, aby ustawić wszystkie komórki określonego wiersza jako puste.

Dokładniej, możesz użyć następującego zapytania SQL:

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

Podczas opracowywania przepływu, musisz zastąpić, należy zastąpić znacznik miejsca SHEET nazwą arkusza kalkulacyjnego, do którego chcemy uzyskać dostęp.

Symbole zastępcze COLUMN1 i COLUMN2 reprezentują nazwy kolumn do obsługi. W tym przykładzie kolumny są dwie, ale w prawdziwym scenariuszu liczba kolumn może być inna. Kolumny w pierwszym wierszu arkusza programu Excel są identyfikowane jako nazwy kolumn tabeli.

Część zapytania [COLUMN1]='VALUE' definiuje wiersz, który ma zostać zaktualizowany. W swoim przepływie użyj nazwy kolumny i wartości w oparciu o to, która kombinacja opisuje wiersze w sposób unikalny.

Odzyskaj dane programu Excel z wyjątkiem określonego wiersza

W niektórych scenariuszach może zajść potrzeba pobrania całej zawartości arkusza kalkulacyjnego programu Excel z wyjątkiem określonego wiersza.

Wygodnym sposobem na osiągnięcie tego celu jest ustawienie wartości niechcianego wiersza na null, a następnie pobranie wszystkich wartości oprócz null.

Aby zmienić wartości określonego wiersza w arkuszu kalkulacyjnym, możesz użyć zapytania SQL UPDATE, tak jak to przedstawiono w Usuwanie danych z wiersza Excel:

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

Następnie wykonaj następujące zapytanie SQL, aby pobrać wszystkie wiersze arkusza kalkulacyjnego, które nie zawierają wartości null:

SELECT * FROM [SHEET$] WHERE [COLUMN1] IS NOT NULL OR [COLUMN2] IS NOT NULL

Symbole zastępcze COLUMN1 i COLUMN2 reprezentują nazwy kolumn do obsługi. W tym przykładzie kolumny są dwie, ale w prawdziwej tabeli liczba kolumn może być inna. Wszystkie kolumny w pierwszym wierszu arkusza programu Excel są identyfikowane jako nazwy kolumn tabeli.