Spuštění dotazů SQL na soubory aplikace Excel

Ačkoli akce aplikace Excel zvládnou většinu scénářů automatizace aplikace Excel, dotazy SQL mohou efektivněji načítat významná množství dat aplikace Excel a pracovat s nimi.

Předpokládejme, že tok musí upravit pouze registry aplikace Excel, které obsahují konkrétní hodnotu. K dosažení této funkce bez dotazů SQL potřebujete smyčky, podmíněné položky a více akcí aplikace Excel.

Alternativně můžete tuto funkci implementovat pomocí dotazů SQL s využitím pouhých dvou akcí, a to Otevřít připojení SQL a Provádět příkazy SQL.

Otevření připojení SQL k souboru aplikace Excel

Před spuštěním dotazu SQL musíte otevřít připojení se souborem Excel, ke kterému chcete získat přístup.

Chcete-li navázat připojení, vytvořte novou proměnnou s názvem %Excel_File_Path% a inicializujte ji pomocí cesty k souboru Excel. Volitelně můžete tento krok přeskočit a později v toku použít pevně zakódovanou cestu k souboru.

Nyní nasaďte akci Otevřít připojení SQL a vyplňte následující připojovací řetězec v jejích vlastnostech.

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

Poznámka:

Aby bylo možné úspěšně použít prezentovaný řetězec propojení, musíte si stáhnout a nainstalovat Redistribuovatelný Microsoft Access Database Engine 2010.

Otevření připojení SQL k souboru aplikace Excel chráněnému heslem

Jiný přístup je vyžadován ve scénářích, kde spouštíte dotazy SQL na soubory aplikace Excel chráněné heslem. Akce Otevřete připojení SQL se nemůže připojit k souborům Excel chráněným heslem, takže musíte ochranu odebrat.

Chcete-li toho dosáhnout, spusťte soubor Excel pomocí akce Spustit Excel. Soubor je chráněn heslem, proto vyplňte příslušné heslo do pole Heslo.

Dále nasaďte příslušné akce automatizace uživatelského rozhraní a přejděte na Soubor>Informace>Zamknout sešit>Šifrovat pomocí hesla. Další informace o automatizaci uživatelského rozhraní a způsobu použití příslušných akcí najdete v tématu Automatizace desktopových aplikací.

Po výběru možnosti Šifrovat pomocí hesla vyplňte prázdný řetězec v rozbalovacím dialogu pomocí akce Naplnit textové pole v okně. Chcete-li vyplnit prázdný řetězec, použijte následující výraz: %""%.

Stisknutím tlačítka OK tlačítko v dialogovém okně a použitím změn nasaďte akci Stisknout tlačítko v okně.

Nakonec nasaďte akci Zavřít aplikaci Excel k uložení nechráněného sešitu jako nového souboru aplikace Excel.

Po uložení souboru postupujte podle pokynů v části Otevření připojení SQL k souborům aplikace Excel pro otevření připojení.

Po dokončení práce se souborem aplikace Excel použijte akci Odstranit soubory k odstranění nechráněné kopie souboru aplikace Excel.

Čtení obsahu tabulky aplikace Excel

I když akce Číst z listu aplikace Excel může číst obsah listu aplikace Excel, opakování může trvat značnou dobu kvůli procházení načtených dat.

Efektivnějším způsobem, jak načíst konkrétní hodnoty z tabulek, je zacházet se soubory Excel jako s databázemi a provádět na nich dotazy SQL. Tento přístup je rychlejší a zvyšuje výkonnost toku.

Chcete-li načíst veškerý obsah tabulky, můžete použít následující dotaz SQL v akci Provést příkaz SQL.

SELECT * FROM [SHEET$]

Poznámka:

Chcete-li ve svých tocích použít tento dotaz SQL, nahraďte zástupný symbol SHEET názvem tabulky, ke které chcete přistupovat.

Chcete-li načíst řádky, které obsahují konkrétní hodnotu v konkrétním sloupci, použijte následující dotaz SQL:

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

Poznámka:

Chcete-li použít tento dotaz SQL ve svých tocích, nahraďte:

• SHEET názvem tabulky, ke které chcete přistupovat.
• NÁZEV SLOUPCE sloupcem, který obsahuje hodnotu, kterou chcete najít. Sloupce v prvním řádku listu aplikace Excel jsou identifikovány jako názvy sloupců tabulky.
• HODNOTA hodnotou, kterou chcete najít.

Odstranění dat z řádku aplikace Excel

I když Excel nepodporuje dotaz SQL DELETE, můžete použít dotaz UPDATE k nastavení všech buněk konkrétního řádku na null.

Přesněji můžete použít následující dotaz SQL:

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

Chcete-li při vývoji toku nahradit zástupný symbol SHEET názvem tabulky, ke které chcete přistupovat.

Zástupné symboly SLOUPEC 1 a SLOUPEC 2 představují názvy sloupců, se kterými se manipuluje. V tomto příkladu jsou dva sloupce, ale ve skutečném scénáři může být počet sloupců odlišný. Sloupce v prvním řádku listu aplikace Excel jsou identifikovány jako názvy sloupců tabulky.

Část dotazu COLUMN1='VALUE' definuje řádek, který chcete aktualizovat. Ve svém toku použijte název sloupce a hodnotu podle toho, která kombinace popisuje řádky jednoznačně.

Načtení dat aplikace Excel s výjimkou konkrétního řádku

V některých scénářích možná budete muset načíst veškerý obsah tabulky aplikace Excel s výjimkou konkrétního řádku.

Pohodlným způsobem, jak toho dosáhnout, je nastavit hodnoty nežádoucího řádku na null a poté načíst všechny hodnoty kromě těch null.

Chcete-li změnit hodnoty konkrétního řádku v tabulce, můžete použít dotaz SQL UPDATE, jak je prezentováno v tématu Odstranění dat z řádku aplikace Excel:

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

Dále spusťte následující dotaz SQL a načtěte všechny řádky tabulky, které neobsahují hodnoty null:

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

Zástupné symboly SLOUPEC 1 a SLOUPEC 2 představují názvy sloupců, se kterými se manipuluje. V tomto příkladu jsou dva sloupce, ale ve skutečné tabulce může být počet sloupců odlišný. Všechny sloupce v prvním řádku listu aplikace Excel jsou identifikovány jako názvy sloupců tabulky.