Praca z plikami programu Excel z zadaniem skryptów

Integration Services Menedżer połączeń programu Excel, urządzenie źródłowe programu Excel i obiekt docelowy programu Excel do pracy z danymi przechowywanymi w arkuszach kalkulacyjnych w Microsoft Excel formatu pliku. Z metod opisanych w tym temacie użyć zadania skryptu w celu uzyskania informacji na temat dostępnych baz danych programu Excel (pliki skoroszytów) i tabelach (arkusze i nazwane zakresy).Pliki te mogą być modyfikowane łatwe do pracy z jednym z innych źródeł danych opartych na plikach, obsługiwane przez Microsoft Dostawca baz danych OLE aparatu Jet.

Konfigurowanie pakiet, aby przetestować próbek

Example1: Sprawdź, czy plik programu Excel istnieje

Przykład 2: Sprawdź, czy tabela programu Excel istnieje

Przykład 3: Uzyskaj listę plików programu Excel, folder

Przykład 4: Uzyskaj listę tabel w plik programu Excel

Wyświetlanie wyniki próbek

Uwaga

Jeśli chcesz utworzyć zadanie, które łatwo można używać w wielu pakietów, należy rozważyć przy użyciu kodu w tym przykładzie skrypt zadanie jako punkt początkowy dla niestandardowego zadania.Aby uzyskać więcej informacji zobaczOpracowywania niestandardowego zadania.

Konfigurowanie pakiet, aby przetestować próbek

Można skonfigurować jeden pakiet w celu przetestowania wszystkich próbek w tym temacie.Próbki za pomocą wielu zmiennych ten sam pakiet i takie same .NET Framework klasy.

Aby skonfigurować pakiet do użytku z przykłady w tym temacie

1. Tworzenie nowej Integration Services Projekt Business Intelligence Development Studio i otwórz pakiet domyślnie do edycji.

2. Zmienne.Otwórz Zmienne okna i definiowania następujących zmiennych:

   • ExcelFile, typu String. Wprowadź pełną ścieżka i nazwę pliku do istniejącego skoroszyt programu Excel.

   • ExcelTable, typu String. Wprowadź nazwę istniejącego arkusza lub nazwany zakres skoroszyt o nazwie w wartości ExcelFile Zmienna. Ta wartość jest uwzględniana wielkość liter.

   • ExcelFileExists, typu Boolean.

   • ExcelTableExists, typu Boolean.

   • ExcelFolder, typu String. Wprowadź pełną ścieżka folderu, który zawiera co najmniej jeden skoroszyt programu Excel.

   • ExcelFiles, typu Object.

   • ExcelTables, typu Object.

3. Instrukcje przywozu.Większość przykładach kodu wymagają Importuj jedną lub obie z następujących czynności .NET Framework obszary nazw w górnej części pliku skryptu:

   • System.IO, dla operacji systemu plików.

   • System.Data.OleDb, aby otworzyć pliki Excel jako źródła danych.

4. Odwołania.Przykładowy kod, który odczytać informacji o schemacie z plików programu Excel wymagają dodatkowych odwołanie bezwzględne w projekcie skryptu System.Xml obszar nazw.

5. Ustawić domyślny język wykonywanie wykonywanie skryptów dla składnika wykonywanie wykonywanie skryptów przy użyciu Język wykonywanie wykonywanie skryptów Opcja Ogólne stronaOpcje -okno dialogowe.Aby uzyskać więcej informacji zobaczStrona ogólnych.

Opis przykład 1: Sprawdź, czy plik programu Excel istnieje

W poniższym przykładzie określa, czy plik skoroszyt programu Excel określony w ExcelFile Zmienna istnieje, a następnie ustawia wartość logiczny ExcelFileExists Zmienna wynik. Umożliwia to logiczny dla branching przebiegu pracy z pakiet.

Aby skonfigurować ten przykład zadania skryptu

1. Dodać nowe zadanie skryptu do pakiet, a następnie zmienić jego nazwę do ExcelFileExists.

2. W Edytor zadań skryptu, on the Skrypt karcie, kliknij przycisk ReadOnlyVariables i wprowadź wartość właściwość przy użyciu jednej z następujących metod:

   • Typ ExcelFile.

     - lub -

   • Kliknij przycisk wielokropka (…) przycisk obok pole właściwość, a w Umożliwia wybór zmiennych okno dialogowe wybierzExcelFile zmiennej.

3. Kliknij przycisk ReadWriteVariables i wprowadź wartość właściwość przy użyciu jednej z następujących metod:

   • Typ ExcelFileExists.

     - lub -

   • Kliknij przycisk wielokropka (…) przycisk obok pole właściwość, a w Umożliwia wybór zmiennych okno dialogowe wybierzExcelFileExists zmiennej.

4. Kliknij przycisk Edytowanie skryptu , aby otworzyć Edytor skryptów.

5. Dodawanie Imports Instrukcja System.IO obszar nazw w górnej części pliku skryptu.

6. Dodaj następujący kod.

Przykład 1, kod

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;
  }
}

Opis przykład 2: Sprawdź, czy tabela programu Excel istnieje

W poniższym przykładzie określa, czy arkusz programu Excel lub nazwany zakres określony w ExcelTable Zmienna istnieje w określonym w pliku skoroszyt programu Excel ExcelFile Zmienna, a następnie ustawia wartość logiczny ExcelTableExists Zmienna wynik. Umożliwia to logiczny dla branching przebiegu pracy z pakiet.

Aby skonfigurować ten przykład zadania skryptu

1. Dodać nowe zadanie skryptu do pakiet, a następnie zmienić jego nazwę do ExcelTableExists.

2. W Edytor zadań skryptu, on the Skrypt karcie, kliknij przycisk ReadOnlyVariables i wprowadź wartość właściwość przy użyciu jednej z następujących metod:

   • Typ ExcelTable and ExcelFile rozdzielone przecinkami .

     - lub -

   • Kliknij przycisk wielokropka (…) przycisk obok pole właściwość, a w Umożliwia wybór zmiennych okno dialogowe wybierzExcelTable and ExcelFile zmiennych.

3. Kliknij przycisk ReadWriteVariables i wprowadź wartość właściwość przy użyciu jednej z następujących metod:

   • Typ ExcelTableExists.

     - lub -

   • Kliknij przycisk wielokropka (…) przycisk obok pole właściwość, a w Umożliwia wybór zmiennych okno dialogowe wybierzExcelTableExists zmiennej.

4. Kliknij przycisk Edytowanie skryptu , aby otworzyć Edytor skryptów.

5. Dodaj odwołanie do System.Xml wirtualny plik dziennika w projekcie skryptu.

6. Dodawanie Imports instrukcje dla System.IO i System.Data.OleDb obszary nazw w górnej części pliku skryptu.

7. Dodaj następujący kod.

Przykład 2, kod

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.Jet.OLEDB.4.0;" & _
        "Data Source=" & fileToTest & _
        ";Extended Properties=Excel 8.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.Jet.OLEDB.4.0;" +
                "Data Source=" + fileToTest + ";Extended Properties=Excel 8.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;

        }
}

Opis przykład 3: Uzyskaj listę plików programu Excel, folder

W tym przykładzie wypełnia tablicę z listą programu Excel pliki znajdujące się w folderze określonym w wartości ExcelFolder Zmienna, a następnie kopiuje tablicy do ExcelFiles Zmienna. Foreach z modułu wyliczającego zmienna może być iterować nad plikami w tablicy.

Aby skonfigurować ten przykład zadania skryptu

1. Dodać nowe zadanie skryptu do pakiet, a następnie zmienić jego nazwę do GetExcelFiles.

2. Otwórz Edytor zadań skryptu, on the Skrypt karcie, kliknij przycisk ReadOnlyVariables i wprowadź wartość właściwość przy użyciu jednej z następujących metod:

   • Typ ExcelFolder

     - lub -

   • Kliknij przycisk wielokropka (…) przycisk obok pole właściwość, a w Umożliwia wybór zmiennych w oknie dialogowym wybierz zmienną ExcelFolder.

3. Kliknij przycisk ReadWriteVariables i wprowadź wartość właściwość przy użyciu jednej z następujących metod:

   • Typ ExcelFiles.

     - lub -

   • Kliknij przycisk wielokropka (…) przycisk obok pole właściwość, a w Umożliwia wybór zmiennych w oknie dialogowym wybierz zmienną ExcelFiles.

4. Kliknij przycisk Edytowanie skryptu , aby otworzyć Edytor skryptów.

5. Dodawanie Imports Instrukcja System.IO obszar nazw w górnej części pliku skryptu.

6. Dodaj następujący kod.

Przykład 3, kod

Public Class ScriptMain
  Public Sub Main()
    Const FILE_PATTERN As String = "*.xls"

    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 = "*.xls";

    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;
  }
}

Alternatywne rozwiązanie

Zamiast korzystać z zadań skryptów zebrać listę plików programu Excel do tablicy, można również użyć modułu wyliczającego pliku ForEach do iterować za pośrednictwem wszystkich plików programu Excel w folderze.Aby uzyskać więcej informacji zobaczJak Pętla za pomocą programu Excel pliki, a tabele przy użyciu kontenera Foreach pętli.

Opis przykład 4: Uzyskaj listę tabel w plik programu Excel

W tym przykładzie wypełnia tablicę z listą arkusze i nazwane zakresy można znaleźć w pliku skoroszyt programu Excel, określony przez wartość ExcelFile Zmienna, a następnie kopiuje tablicy do ExcelTables Zmienna. Foreach ze zmiennej moduł wyliczający umożliwia iterować w tabelach w tablicy.

Uwaga

Listy tabel skoroszyt programu Excel zawiera zarówno arkuszy, (które mają sufiks $) i nazwane zakresy.Jeżeli trzeba przefiltrować listę tylko arkusze lub tylko nazwane zakresy, należy dodać dodatkowy kod w tym celu.

Aby skonfigurować ten przykład zadania skryptu

1. Dodać nowe zadanie skryptu do pakiet, a następnie zmienić jego nazwę do GetExcelTables.

2. Otwórz Edytor zadań skryptu, on the Skrypt karcie, kliknij przycisk ReadOnlyVariables i wprowadź wartość właściwość przy użyciu jednej z następujących metod:

   • Typ ExcelFile.

     - lub -

   • Kliknij przycisk wielokropka (…) przycisk obok pole właściwość, a w Umożliwia wybór zmiennych w oknie dialogowym wybierz zmienną ExcelFile.

3. Kliknij przycisk ReadWriteVariables i wprowadź wartość właściwość przy użyciu jednej z następujących metod:

   • Typ ExcelTables.

     - lub -

   • Kliknij przycisk wielokropka (…) przycisk obok pole właściwość, a w Umożliwia wybór zmiennych okno dialogowe ExcelTables wybierzZmienna.

4. Kliknij przycisk Edytowanie skryptu , aby otworzyć Edytor skryptów.

5. Dodaj odwołanie do System.Xml obszar nazw w projekcie skryptu.

6. Dodawanie Imports Instrukcja System.Data.OleDb obszar nazw w górnej części pliku skryptu.

7. Dodaj następujący kod.

Przykład 4, kod

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.Jet.OLEDB.4.0;" & _
        "Data Source=" & excelFile & _
        ";Extended Properties=Excel 8.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.Jet.OLEDB.4.0;" +
                "Data Source=" + excelFile + ";Extended Properties=Excel 8.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;
        }
}

Alternatywne rozwiązanie

Zamiast korzystać z zadań skryptów zebrać listę tabel programu Excel do tablicy, umożliwia także moduł wyliczający ForEach ADO.NET zestaw zestaw wierszy schematu, aby przejść przez wszystkie tabele (to znaczy, arkusze i nazwane zakresy) w pliku skoroszyt programu Excel.Aby uzyskać więcej informacji zobaczJak Pętla za pomocą programu Excel pliki, a tabele przy użyciu kontenera Foreach pętli.

Wyświetlanie wyniki próbek

Jeśli w tym temacie, w tym samym pakiecie został skonfigurowany każdy z przykładów, można połączyć wszystkie zadania skryptu dodatkowe zadania skryptu, który wyświetla dane wyjściowe w przykładach.

Aby skonfigurować zadania skryptu do wyświetlania danych wyjściowych w przykładach w tym temacie

1. Dodać nowe zadanie skryptu do pakiet, a następnie zmienić jego nazwę do DisplayResults.

2. Łączenie każdej z czterech przykład zadania skryptu ze sobą, tak aby każde zadanie jest uruchamiane po pomyślnym zakończeniu poprzedniego zadania i Połącz z czwartego zadania przykład DisplayResults zadanie.

3. Otwórz DisplayResults zadaniaEdytor zadań skryptu.

4. Na Skrypt karcie, kliknij przycisk ReadOnlyVariables i użyj jednej z następujących metod, aby dodać wszystkie zmienne siedem na liścieKonfigurowanie pakiet, aby przetestować próbek:

   • Wpisz nazwę każdej zmiennej, oddzielając je średnikami.

     - lub -

   • Kliknij przycisk wielokropka (…) przycisk obok pole właściwość, a w Umożliwia wybór zmiennych , okno dialogowe Wybieranie zmiennych.

5. Kliknij przycisk Edytowanie skryptu , aby otworzyć Edytor skryptów.

6. Dodawanie Imports instrukcje dla Microsoft.VisualBasic i System.Windows.Forms obszary nazw w górnej części pliku skryptu.

7. Dodaj następujący kod.

8. Należy uruchomić pakiet i przejrzeć wyniki wyświetlane w oknie komunikatu.

Kod, aby wyświetlić wyniki

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;
        }
}

Konfiguracja w aktualizacji z usług integracja Services pobytu Najnowsze pliki do pobrania, artykuły, próbki, i pliki wideo firmy Microsoft, jak również wybranego rozwiązania od Wspólnoty, odwiedź witrynę Integration Services strona w witrynie MSDN lub TechNet: Odwiedź strona usług integracja Services w witrynie MSDN Odwiedź strona usług integracja Services w witrynie TechNet Automatycznego powiadomienie tych aktualizacji należy subskrybować źródła danych RSS, które jest dostępne strona.

See Also

Other Resources

Menedżer połączeń programu Excel

Jak Pętla za pomocą programu Excel pliki, a tabele przy użyciu kontenera Foreach pętli