Práce se soubory Aplikace Excel pomocí úlohy skriptu

Platí pro:SQL Server SSIS Integration Runtime v rámci Azure Data Factory

Integrační služby poskytují správce spojení Excel, zdrojový kód Excel a cílovou platformu Excel pro práci s daty uloženými v tabulkách ve formátu Microsoft Excel. Techniky popsané v tomto tématu využívají úlohu Script k získání informací o dostupných databázích Excel (soubory pracovních sešitů) a tabulkách (pracovní listy a pojmenované rozsahy).

Important

Podrobné informace o připojování k excelovým souborům a o omezeních a známých problémech při načítání dat z nebo do excelových souborů najdete v tématu Načtení dat z nebo do Excelu pomocí služby SSIS (Služby Integrace SQL Serveru).

Tip

Pokud chcete vytvořit úkol, který můžete znovu použít napříč více balíčky, zvažte použití kódu v tomto ukázkovém skriptu úlohy jako výchozího bodu pro vlastní úkol. Další informace naleznete v tématu Vývoj vlastníhoúloh .

Konfigurace balíčku pro testování vzorků

Můžete si nastavit jeden balíček pro testování všech vzorků v tomto tématu. Ukázky používají mnoho stejných proměnných balíčku a stejné třídy .NET Frameworku.

Pro konfiguraci balíčku pro použití s příklady v tomto tématu

  1. Vytvořte nový projekt Integration Services v SQL Server Data Tools (SSDT) a otevřete výchozí balíček pro úpravy.

  2. Proměnné. Otevřete okno Proměnné a definujte následující proměnné:

    • ExcelFile, typu String. Zadejte kompletní cestu a název souboru do existujícího Excel sešitu.

    • ExcelTable, typu String. Zadejte název existujícího pracovního listu nebo pojmenovaného rozsahu do sešitu pojmenovaného v hodnotě proměnné ExcelFile . Tato hodnota je závislá na velikosti písmen.

    • ExcelFileExists, typu Boolean.

    • ExcelTableExists, typu Boolean.

    • ExcelFolder, typu String. Zadejte kompletní cestu složky, která obsahuje alespoň jeden Excel sešit.

    • ExcelFiles, typu Objekt.

    • ExcelTables, typu Objekt.

  3. Výpisy o dovozu. Většina ukázek kódu vyžaduje, abyste importovali jeden nebo oba následující jmenné prostory .NET Framework v horní části vašeho skriptového souboru:

    • System.IO, pro operace souborového systému.

    • System.Data.OleDb, pro otevření Excel souborů jako zdrojů dat.

  4. Reference. Ukázky kódu, které čtou informace o schématu z Excel souborů, vyžadují další odkaz ve skriptovém projektu na jmenný prostor System.Xml.

  5. Nastavte výchozí skriptovací jazyk pro komponentu Script pomocí možnosti Scripting language na stránce Obecné v dialogovém okně Options . Další informace najdete na stránce Obecné.

Příklad 1 Popis: Zkontrolujte, zda existuje Excel soubor

Tento příklad určuje, zda existuje soubor Excel workbook specifikovaný v proměnnéExcelFile, a poté nastaví Booleovu hodnotu ExcelFileExists proměnné na výsledek. Tuto Booleovskou hodnotu můžete použít pro větvení v pracovním toku balíčku.

Pro konfiguraci tohoto příkladu Script Task

  1. Přidejte do balíčku nový skriptní úkol a změňte jeho název na ExcelFileExists.

  2. V editoru úloh skriptů na záložce Script klikněte na ReadOnlyVariables a zadejte hodnotu vlastnosti pomocí jedné z následujících metod:

    • Napište ExcelFile.

      nebo

    • Klikněte na tlačítko s elipse (...) vedle pole vlastností a v dialogovém okně Vybrat proměnné vyberte proměnnou ExcelFile .

  3. Klikněte na ReadWriteVariables a zadejte hodnotu vlastnosti pomocí jedné z následujících metod:

    • Napište ExcelFileExists.

      nebo

    • Klikněte na tlačítko s elipse (...) vedle pole vlastností a v dialogovém okně Výběr proměnných vyberte proměnnou ExcelFileExists .

  4. Klikněte na Edit Script pro otevření editoru skriptu.

  5. Přidejte příkaz Imports pro jmenný prostor System.IO nahoře ve skriptovém souboru.

  6. Přidejte následující kód.

Příklad 1 Kód

Public Class ScriptMain  
  Public Sub Main()  
    Dim fileToTest As String  
  
    fileToTest = Dts.Variables("ExcelFile").Value.ToString  
    If File.Exists(fileToTest) Then  
      Dts.Variables("ExcelFileExists").Value = True  
    Else  
      Dts.Variables("ExcelFileExists").Value = False  
    End If  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class  

public class ScriptMain  
{  
  public void Main()  
  {  
    string fileToTest;  
  
    fileToTest = Dts.Variables["ExcelFile"].Value.ToString();  
    if (File.Exists(fileToTest))  
    {  
      Dts.Variables["ExcelFileExists"].Value = true;  
    }  
    else  
    {  
      Dts.Variables["ExcelFileExists"].Value = false;  
    }  
  
    Dts.TaskResult = (int)ScriptResults.Success;  
  }  
}

Příklad 2 Popis: Zkontrolujte, zda existuje tabulka Excel

Tento příklad určuje, zda se v souboru Excel specifikovaného v proměnné nachází pracovní list nebo pojmenovaný rozsah v ExcelTable Excel souboru specifikovaném v proměnnéExcelFile, a poté nastaví Booleovskou hodnotu ExcelTableExists proměnné na výsledek. Tuto Booleovskou hodnotu můžete použít pro větvení v pracovním toku balíčku.

Pro konfiguraci tohoto příkladu Script Task

  1. Přidejte do balíčku nový skriptový úkol a změňte jeho název na ExcelTableExists.

  2. V editoru úloh skriptů na záložce Script klikněte na ReadOnlyVariables a zadejte hodnotu vlastnosti pomocí jedné z následujících metod:

    • Napište ExcelTable a ExcelFile oddělené čárkami.

      nebo

    • Klikněte na tlačítko s tečkou (...) vedle pole vlastností a v dialogovém okně Výběr proměnných vyberte proměnné ExcelTable a ExcelFile .

  3. Klikněte na ReadWriteVariables a zadejte hodnotu vlastnosti pomocí jedné z následujících metod:

    • Napište ExcelTableExists.

      nebo

    • Klikněte na tlačítko s tečkou (...) vedle pole vlastností a v dialogovém okně Vybrat proměnné vyberte proměnnou ExcelTableExists .

  4. Klikněte na Edit Script pro otevření editoru skriptu.

  5. Přidejte odkaz na sestavení System.XML v projektu skriptu.

  6. Přidejte příkazy Imports pro jmenné prostory System.IO a System.Data.OleDb do horní části skriptového souboru.

  7. Přidejte následující kód.

Příklad 2 Kód

Public Class ScriptMain  
  Public Sub Main()  
    Dim fileToTest As String  
    Dim tableToTest As String  
    Dim connectionString As String  
    Dim excelConnection As OleDbConnection  
    Dim excelTables As DataTable  
    Dim excelTable As DataRow  
    Dim currentTable As String  
  
    fileToTest = Dts.Variables("ExcelFile").Value.ToString  
    tableToTest = Dts.Variables("ExcelTable").Value.ToString  
  
    Dts.Variables("ExcelTableExists").Value = False  
    If File.Exists(fileToTest) Then  
      connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _  
        "Data Source=" & fileToTest & _  
        ";Extended Properties=Excel 12.0"  
      excelConnection = New OleDbConnection(connectionString)  
      excelConnection.Open()  
      excelTables = excelConnection.GetSchema("Tables")  
      For Each excelTable In excelTables.Rows  
        currentTable = excelTable.Item("TABLE_NAME").ToString  
        If currentTable = tableToTest Then  
          Dts.Variables("ExcelTableExists").Value = True  
        End If  
      Next  
    End If  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class  

public class ScriptMain  
{  
    public void Main()  
        {  
            string fileToTest;  
            string tableToTest;  
            string connectionString;  
            OleDbConnection excelConnection;  
            DataTable excelTables;  
            string currentTable;  
  
            fileToTest = Dts.Variables["ExcelFile"].Value.ToString();  
            tableToTest = Dts.Variables["ExcelTable"].Value.ToString();  
  
            Dts.Variables["ExcelTableExists"].Value = false;  
            if (File.Exists(fileToTest))  
            {  
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +  
                "Data Source=" + fileToTest + ";Extended Properties=Excel 12.0";  
                excelConnection = new OleDbConnection(connectionString);  
                excelConnection.Open();  
                excelTables = excelConnection.GetSchema("Tables");  
                foreach (DataRow excelTable in excelTables.Rows)  
                {  
                    currentTable = excelTable["TABLE_NAME"].ToString();  
                    if (currentTable == tableToTest)  
                    {  
                        Dts.Variables["ExcelTableExists"].Value = true;  
                    }  
                }  
            }  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
  
        }  
}

Příklad 3 Popis: Získejte seznam Excel souborů ve složce

Tento příklad vyplní pole seznamem Excel souborů nalezených ve složce uvedené v hodnotě proměnné ExcelFolder a poté pole zkopíruje do proměnnéExcelFiles. Můžete použít Foreach from Variable enumerátor k iteraci souborů v poli.

Pro konfiguraci tohoto příkladu Script Task

  1. Přidejte do balíčku novou skriptovou úlohu a změňte její název na GetExcelFiles.

  2. Otevřete Editor úloh skriptu na záložce Script , klikněte na ReadOnlyVariables a zadejte hodnotu vlastnosti pomocí jedné z následujících metod:

    • Napište ExcelFolder

      nebo

    • Klikněte na tlačítko s tečkou (...) vedle pole vlastností a v dialogovém okně Vybrat proměnné vyberte proměnnou ExcelFolder.

  3. Klikněte na ReadWriteVariables a zadejte hodnotu vlastnosti pomocí jedné z následujících metod:

    • Napište ExcelFiles.

      nebo

    • Klikněte na tlačítko s tečkou (...) vedle pole vlastností a v dialogovém okně Výběr proměnných vyberte proměnnou ExcelFiles.

  4. Klikněte na Edit Script pro otevření editoru skriptu.

  5. Přidejte příkaz Imports pro jmenný prostor System.IO nahoře ve skriptovém souboru.

  6. Přidejte následující kód.

Příklad 3 Kód

Public Class ScriptMain  
  Public Sub Main()  
    Const FILE_PATTERN As String = "*.xlsx"  
  
    Dim excelFolder As String  
    Dim excelFiles As String()  
  
    excelFolder = Dts.Variables("ExcelFolder").Value.ToString  
    excelFiles = Directory.GetFiles(excelFolder, FILE_PATTERN)  
  
    Dts.Variables("ExcelFiles").Value = excelFiles  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class  

public class ScriptMain  
{  
  public void Main()  
  {  
    const string FILE_PATTERN = "*.xlsx";  
  
    string excelFolder;  
    string[] excelFiles;  
  
    excelFolder = Dts.Variables["ExcelFolder"].Value.ToString();  
    excelFiles = Directory.GetFiles(excelFolder, FILE_PATTERN);  
  
    Dts.Variables["ExcelFiles"].Value = excelFiles;  
  
    Dts.TaskResult = (int)ScriptResults.Success;  
  }  
}

Alternativní řešení

Místo použití skriptové úlohy pro shromáždění seznamu Excel souborů do pole můžete také použít enumerátor ForEach File k iteraci všech Excel souborů ve složce. Pro více informací viz Procházet Excel soubory a tabulky pomocí kontejneru Foreach Loop.

Popis příkladu 4: Získejte seznam tabulek v Excel souboru

Tento příklad vyplní pole seznamem pracovních listů a pojmenovaných rozsahů nalezených v souboru Excel workbooku určeným hodnotou proměnné ExcelFile a poté pole zkopíruje do proměnnéExcelTables. Můžete použít Foreach z Variable Enumerator k iteraci přes tabulky v poli.

Note

Seznam tabulek v Excel pracovním sešitu zahrnuje jak pracovní listy (které mají příponu $), tak pojmenované rozsahy. Pokud musíte filtrovat seznam pouze na pracovní listy nebo pouze na pojmenované rozsahy, možná budete muset přidat další kód pro tento účel.

Pro konfiguraci tohoto příkladu Script Task

  1. Přidejte do balíčku nový skriptový úkol a změňte jeho název na GetExcelTables.

  2. Otevřete Editor úloh skriptu na záložce Script , klikněte na ReadOnlyVariables a zadejte hodnotu vlastnosti pomocí jedné z následujících metod:

    • Napište ExcelFile.

      nebo

    • Klikněte na tlačítko s elipse (...) vedle pole vlastností a v dialogovém okně Vybrat proměnné vyberte proměnnou ExcelFile.

  3. Klikněte na ReadWriteVariables a zadejte hodnotu vlastnosti pomocí jedné z následujících metod:

    • Napište ExcelTables.

      nebo

    • Klikněte na tlačítko s elipse (...) vedle pole vlastností a v dialogovém okně Vybrat proměnné vyberte ExcelTablesvariable.

  4. Klikněte na Edit Script pro otevření editoru skriptu.

  5. Přidejte odkaz na jmenný prostor System.XML v projektu skriptu.

  6. Přidejte příkaz Imports pro jmenný prostor System.Data.OleDb nahoře do souboru skriptu.

  7. Přidejte následující kód.

Příklad 4 Kód

Public Class ScriptMain  
  Public Sub Main()  
    Dim excelFile As String  
    Dim connectionString As String  
    Dim excelConnection As OleDbConnection  
    Dim tablesInFile As DataTable  
    Dim tableCount As Integer = 0  
    Dim tableInFile As DataRow  
    Dim currentTable As String  
    Dim tableIndex As Integer = 0  
  
    Dim excelTables As String()  
  
    excelFile = Dts.Variables("ExcelFile").Value.ToString  
    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _  
        "Data Source=" & excelFile & _  
        ";Extended Properties=Excel 12.0"  
    excelConnection = New OleDbConnection(connectionString)  
    excelConnection.Open()  
    tablesInFile = excelConnection.GetSchema("Tables")  
    tableCount = tablesInFile.Rows.Count  
    ReDim excelTables(tableCount - 1)  
    For Each tableInFile In tablesInFile.Rows  
      currentTable = tableInFile.Item("TABLE_NAME").ToString  
      excelTables(tableIndex) = currentTable  
      tableIndex += 1  
    Next  
  
    Dts.Variables("ExcelTables").Value = excelTables  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class  

public class ScriptMain  
{  
  public void Main()  
        {  
            string excelFile;  
            string connectionString;  
            OleDbConnection excelConnection;  
            DataTable tablesInFile;  
            int tableCount = 0;  
            string currentTable;  
            int tableIndex = 0;  
  
            string[] excelTables = new string[5];  
  
            excelFile = Dts.Variables["ExcelFile"].Value.ToString();  
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +  
                "Data Source=" + excelFile + ";Extended Properties=Excel 12.0";  
            excelConnection = new OleDbConnection(connectionString);  
            excelConnection.Open();  
            tablesInFile = excelConnection.GetSchema("Tables");  
            tableCount = tablesInFile.Rows.Count;  
  
            foreach (DataRow tableInFile in tablesInFile.Rows)  
            {  
                currentTable = tableInFile["TABLE_NAME"].ToString();  
                excelTables[tableIndex] = currentTable;  
                tableIndex += 1;  
            }  
  
            Dts.Variables["ExcelTables"].Value = excelTables;  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
        }  
}

Alternativní řešení

Místo použití skriptové úlohy pro shromáždění seznamu Excel tabulek do pole můžete také použít ForEach ADO.NET Schema Rowset Enumerator k iteraci všech tabulek (tedy pracovních listů a pojmenovaných rozsahů) v souboru Excel workbooku. Pro více informací viz Procházet Excel soubory a tabulky pomocí kontejneru Foreach Loop.

Zobrazení výsledků vzorků

Pokud máte všechny příklady v tomto tématu nakonfigurované ve stejném balíčku, můžete všechny skriptové úlohy připojit k dalšímu skriptovému úkolu, který zobrazí výstup všech příkladů.

Pro konfiguraci skriptové úlohy pro zobrazení výstupu příkladů v tomto tématu

  1. Přidejte do balíčku novou skriptovou úlohu a změňte její název na DisplayResults.

  2. Spojte všechny čtyři ukázkové skriptové úlohy navzájem, aby každá úloha splstila po úspěšném dokončení předchozí, a čtvrtou ukázkovou úlohu připojte k úkolu DisplayResults .

  3. Otevřete úkol DisplayResults v editoru skriptových úloh.

  4. Na záložce Script klikněte na ReadOnlyVariables a použijte jednu z následujících metod k přidání všech sedmi proměnných uvedených v Configuring a Package to Test the Samples:

    • Zadejte název každé proměnné oddělený čárkami.

      nebo

    • Klikněte na tlačítko s elipse (...) vedle pole vlastností a v dialogovém okně Výběr proměnných vyberte proměnné.

  5. Klikněte na Edit Script pro otevření editoru skriptu.

  6. Přidat příkazy Import pro Microsoft. VisualBasic a System.Windows. Jmenné prostory Forms jsou v horní části souboru skriptu.

  7. Přidejte následující kód.

  8. Spusť balíček a prohlédni výsledky zobrazené v okně zprávy.

Kód pro zobrazení výsledků

Public Class ScriptMain  
  Public Sub Main()  
    Const EOL As String = ControlChars.CrLf  
  
    Dim results As String  
    Dim filesInFolder As String()  
    Dim fileInFolder As String  
    Dim tablesInFile As String()  
    Dim tableInFile As String  
  
    results = _  
      "Final values of variables:" & EOL & _  
      "ExcelFile: " & Dts.Variables("ExcelFile").Value.ToString & EOL & _  
      "ExcelFileExists: " & Dts.Variables("ExcelFileExists").Value.ToString & EOL & _  
      "ExcelTable: " & Dts.Variables("ExcelTable").Value.ToString & EOL & _  
      "ExcelTableExists: " & Dts.Variables("ExcelTableExists").Value.ToString & EOL & _  
      "ExcelFolder: " & Dts.Variables("ExcelFolder").Value.ToString & EOL & _  
      EOL  
  
    results &= "Excel files in folder: " & EOL  
    filesInFolder = DirectCast(Dts.Variables("ExcelFiles").Value, String())  
    For Each fileInFolder In filesInFolder  
      results &= " " & fileInFolder & EOL  
    Next  
    results &= EOL  
  
    results &= "Excel tables in file: " & EOL  
    tablesInFile = DirectCast(Dts.Variables("ExcelTables").Value, String())  
    For Each tableInFile In tablesInFile  
      results &= " " & tableInFile & EOL  
    Next  
  
    MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information)  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class  

public class ScriptMain  
{  
  public void Main()  
        {  
            const string EOL = "\r";  
  
            string results;  
            string[] filesInFolder;  
            //string fileInFolder;  
            string[] tablesInFile;  
            //string tableInFile;  
  
            results = "Final values of variables:" + EOL + "ExcelFile: " + Dts.Variables["ExcelFile"].Value.ToString() + EOL + "ExcelFileExists: " + Dts.Variables["ExcelFileExists"].Value.ToString() + EOL + "ExcelTable: " + Dts.Variables["ExcelTable"].Value.ToString() + EOL + "ExcelTableExists: " + Dts.Variables["ExcelTableExists"].Value.ToString() + EOL + "ExcelFolder: " + Dts.Variables["ExcelFolder"].Value.ToString() + EOL + EOL;  
  
            results += "Excel files in folder: " + EOL;  
            filesInFolder = (string[])(Dts.Variables["ExcelFiles"].Value);  
            foreach (string fileInFolder in filesInFolder)  
            {  
                results += " " + fileInFolder + EOL;  
            }  
            results += EOL;  
  
            results += "Excel tables in file: " + EOL;  
            tablesInFile = (string[])(Dts.Variables["ExcelTables"].Value);  
            foreach (string tableInFile in tablesInFile)  
            {  
                results += " " + tableInFile + EOL;  
            }  
  
            MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
        }  
}

Související obsah

  • Poslední aktualizace v