Praca z plikami programu Excel z zadań skryptu

Integration Serviceszapewnia menedżer połączeń w programie Excel, Excel źródło i Excel obiekt docelowy do pracy z danymi przechowywanymi w arkuszach kalkulacyjnych w Microsoft format pliku programu Excel.Technik opisanych w tym temacie umożliwia uzyskanie informacji na temat dostępnych baz danych programu Excel (pliki skoroszyt) i tabel (arkusze i nazwane zakresy) zadania skryptu.Próbki te można modyfikować łatwo pracować z którymkolwiek z innych źródeł na podstawie pliku danych obsługiwanych przez Microsoft Jet OLE DB Provider.

Konfigurowanie pakietu próbek

Example1: Sprawdź, czy istnieje plik programu Excel

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

Przykład 3: Lista plików programu Excel w folderze

Przykład 4: Uzyskiwanie listy tabel w pliku programu Excel

Wyświetlanie wyników próbek

Ostrzeżenie

Aby utworzyć zadanie łatwiej można użyć ponownie w wielu pakietach, należy rozważyć przy użyciu kodu w tym przykładzie zadanie skryptu jako punktu wyjścia dla niestandardowego zadania.Aby uzyskać więcej informacji, zobacz Opracowywania niestandardowego zadania.

Konfigurowanie pakietu próbek

Można skonfigurować pojedynczy pakiet, aby przetestować wszystkie próbki w tym temacie.Próbki używanych jest wiele zmiennych tego samego pakiet i tym samym .NET Framework klasy.

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

1. Utwórz nowy Integration Services projektu w Business Intelligence Development Studio i Otwórz pakiet domyślny do edycji.

2. Zmienne.Otwórz zmiennych okna i zdefiniować następujące zmienne:

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

   • ExcelTable, of type String.Wprowadź nazwę istniejącego arkusza lub nazwanego zakres skoroszyt o nazwie wartości ExcelFile zmiennej.Wartość ta jest przypadek-poufne.

   • ExcelFileExists, of type Boolean.

   • ExcelTableExists, of type Boolean.

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

   • ExcelFiles, of type Object.

   • ExcelTables, of type Object.

3. Importuje instrukcji.Większość przykładów kodu trzeba zaimportować jedną lub obie z następujących .NET Framework obszarów nazw u góry pliku skryptu:

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

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

4. Odwołania do.Przykłady kodu, które przeczytać informacje o schemacie z plików programu Excel wymagają dodatkowych odwołania projektu skryptu w System.Xml obszaru nazw.

5. Ustawić domyślny język wykonywanie skryptów składnik wykonywanie skryptów przy użyciu język skryptowy opcji na Ogólne strona Opcje okno dialogowe.Aby uzyskać więcej informacji, zobacz Strony głównej.

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

W tym przykładzie określa, czy określony plik skoroszyt programu Excel, w ExcelFile zmiennej istnieje, a następnie ustawia wartość wartość logiczna ExcelFileExists Zmienna wynik.To wartość logiczna służy do tworzenia odgałęzień w przepływie pracy pakiet.

Aby skonfigurować ten przykład zadania skryptu

1. Dodać nowe zadanie skryptu do pakiet i zmienić jego nazwę na ExcelFileExists.

2. W Script Editor zadania, na skryptu , kliknij pozycję 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ść i w wybierz zmienne okno dialogowe, wybierz ExcelFile zmiennej.

3. Kliknij 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ść i w wybierz zmienne okno dialogowe, wybierz ExcelFileExists zmiennej.

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

5. Dodaj Imports instrukcja dla System.IO namespace u góry pliku skryptu.

6. Dodaj następujący kod.

Kod przykładowy 1

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 istnieje tabela programu Excel

W tym przykładzie określa, czy arkusz programu Excel lub nazwany zakres określony w ExcelTable Zmienna istnieje w pliku skoroszyt programu Excel, określonego w ExcelFile zmiennej, a następnie ustawia wartość wartość logiczna ExcelTableExists Zmienna wynik.To wartość logiczna służy do tworzenia odgałęzień w przepływie pracy pakiet.

Aby skonfigurować ten przykład zadania skryptu

1. Dodać nowe zadanie skryptu do pakiet i zmienić jego nazwę na ExcelTableExists.

2. W Script Editor zadania, na skryptu , kliknij pozycję ReadOnlyVariables i wprowadź wartość właściwość przy użyciu jednej z następujących metod:

   • Typ ExcelTable i ExcelFile przecinkami.

     - lub -

   • Kliknij przycisk wielokropka (…) przycisk obok pole właściwość i w wybierz zmienne okno dialogowe, wybierz ExcelTable i ExcelFile zmiennych.

3. Kliknij 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ść i w wybierz zmienne okno dialogowe, wybierz ExcelTableExists zmiennej.

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

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

6. Dodaj Imports sprawozdania System.IO i System.Data.OleDb obszarów nazw u góry pliku skryptu.

7. Dodaj następujący kod.

Kod przykładowy 2

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: Lista plików programu Excel w folderze

W tym przykładzie wypełnia tablicę z listą plików programu Excel w folderze określonym w wartości ExcelFolder zmiennej, a następnie skopiuje tablicy do ExcelFiles zmiennej.Foreach z modułu wyliczającego zmiennej umożliwia iterować przeglądanie plików w tablicy.

Aby skonfigurować ten przykład zadania skryptu

1. Dodać nowe zadanie skryptu do pakiet i zmienić jego nazwę na GetExcelFiles.

2. Otwórz Script Editor zadania, na skryptu , kliknij pozycję 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ść i w wybierz zmienne okno dialogowe Wybierz zmienną ExcelFolder.

3. Kliknij 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ść i w wybierz zmienne okno dialogowe Wybierz zmienną ExcelFiles.

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

5. Dodaj Imports instrukcja dla System.IO namespace u góry pliku skryptu.

6. Dodaj następujący kod.

Kod przykładowy 3

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

Rozwiązaniem alternatywnym

Zamiast zadania skryptu zebrać listę plików programu Excel do tablicy, również można modułu wyliczającego pliku ForEach iterować przeglądanie wszystkich plików programu Excel w folderze.Aby uzyskać więcej informacji, zobacz Jak Pętlę za pomocą programu Excel, plików i tabel przy użyciu kontenera Foreach pętli.

Opis przykład 4: Uzyskiwanie listy tabel w pliku programu Excel

W tym przykładzie wypełnia tablicę listy arkuszy i nazwane zakresy w pliku skoroszyt programu Excel, określony przez wartość ExcelFile zmiennej, a następnie skopiuje tablicy do ExcelTables zmiennej.Foreach z modułu wyliczającego zmiennej umożliwia iterować przeglądanie tabel w tablicy.

Ostrzeżenie

Listy tabel skoroszyt programu Excel zawiera zarówno arkuszy, (które mają sufiks $) i nazwane zakresy.Jeśli filtrowanie listy 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 i zmienić jego nazwę na GetExcelTables.

2. Otwórz Script Editor zadania, na skryptu , kliknij pozycję 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ść i w wybierz zmienne okno dialogowe Wybierz zmienną ExcelFile.

3. Kliknij 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ść i w wybierz zmienne okno dialogowe Wybierz ExcelTables,zmiennej.

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

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

6. Dodaj Imports instrukcja dla System.Data.OleDb namespace u góry pliku skryptu.

7. Dodaj następujący kod.

Kod przykładowy 4

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

Rozwiązaniem alternatywnym

Zamiast zadania skryptu zebrać listę tabel programu Excel do tablicy można również użyć ForEach ADO.NET schematu wierszy wyliczający iterować przeglądanie wszystkich tabel (czyli arkusze i nazwane zakresy) w pliku skoroszyt programu Excel.Aby uzyskać więcej informacji, zobacz Jak Pętlę za pomocą programu Excel, plików i tabel przy użyciu kontenera Foreach pętli.

Wyświetlanie wyników próbek

Jeśli skonfigurowano poszczególne przykłady w tym temacie, w tym samym pakiet, można połączyć wszystkie zadania skryptu dodatkowe zadania skrypt wyświetla wynik wszystkich przykładach.

Aby skonfigurować zadanie skryptu, aby wyświetlić dane wyjściowe przykłady w tym temacie

1. Dodać nowe zadanie skryptu do pakiet i zmienić jego nazwę na DisplayResults.

2. Połączyć każdy z czterech przykład zadania skryptu, tak aby każde zadanie jest uruchamiane po pomyślnym zakończeniu poprzedniego zadania i połączyć zadania przykład czwarty zadania DisplayResults.

3. Otwórz zadanie DisplayResults w Script Editor zadania.

4. Na skryptu , kliknij pozycję ReadOnlyVariables i użyj jednej z następujących metod, aby dodać wszystkie siedem zmienne wymienione w Konfigurowanie pakietu próbek:

   • Wpisz nazwę każdej zmiennej oddzielonych przecinkami.

     - lub -

   • Kliknij przycisk wielokropka (…) przycisk obok pole właściwość i w wybierz zmienne okno dialogowe, wybierając zmiennych.

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

6. Dodaj Imports sprawozdania Microsoft.VisualBasic i System.Windows.Forms obszarów nazw u góry pliku skryptu.

7. Dodaj następujący kod.

8. Uruchom pakiet i badają 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;
        }
}

Bieżąco z usług integracji Najnowsze pliki do pobrania, artykuły, próbki i wideo firmy Microsoft, jak również wybranych rozwiązań ze Wspólnoty, odwiedź witrynę Integration Services strona na MSDN i TechNet: Odwiedź strona usługi integracji w witrynie MSDN Odwiedź strona usługi integracji w witrynie TechNet Aby otrzymywać automatyczne powiadomienia dotyczące tych aktualizacji, zasubskrybuj źródła danych RSS dostępne na tej stronie.

Zobacz także

Zadania

Jak Pętlę za pomocą programu Excel, plików i tabel przy użyciu kontenera Foreach pętli

Koncepcje

Menedżer połączeń programu Excel