Работа с файлами Excel в задаче «Сценарий»

Службы Integration Services предоставляют диспетчер соединений Excel, источник «Excel» и назначение «Excel» для работы с данными, хранящимися в электронных таблицах в формате Microsoft Excel. Технологии, описанные в этом разделе, используют задачу «Сценарий» для получения сведений о доступных базах данных Excel (файлах книги) и таблицах (листах и именованных диапазонах). Эти образцы можно легко изменить для работы с любыми другими источниками данных на основе файлов, поддерживаемыми поставщиком OLE DB Microsoft Jet.

Настройка пакета для проверки образцов

Пример 1. Проверка существования файла Excel

Пример 2. Проверка существования таблицы Excel

Пример 3. Получение списка файлов Excel в папке

Пример 4. Получение списка таблиц в файле Excel

Отображение результатов образцов

Примечание
Если нужно создать задачу, которую будет удобно использовать в нескольких пакетах, рекомендуется начать разработку пользовательской задачи с этого образца задачи «Сценарий». Дополнительные сведения см. в разделе Разработка пользовательской задачи.

Настройка пакета для проверки образцов

Для тестирования всех образцов этого раздела можно настроить отдельный пакет. В них используется много одинаковых переменных пакета и классов платформы .NET Framework.

Настройка пакета для использования с примерами этого раздела

1. Создайте новый проект служб Integration Services в среде Business Intelligence Development Studio и откройте пакет по умолчанию для изменения.

2. Переменные. Откройте окно Переменные и определите следующие переменные:

   • ExcelFile типа String. Введите полный путь и имя файла существующей книги Excel.

   • ExcelTable типа String. Введите имя существующего листа или именованного диапазона в книге, имя которой было указано в качестве значения переменной ExcelFile. Это значение учитывает регистр.

   • ExcelFileExists типа Boolean.

   • ExcelTableExists типа Boolean.

   • ExcelFolder типа String. Введите полный путь к папке, содержащей, по меньшей мере, одну книгу Excel.

   • ExcelFiles типа Object.

   • ExcelTables типа Object.

3. Инструкции импорта. Для большинства образцов кода необходимо импортировать одно из следующих пространств имен платформы .NET Framework, либо оба пространства в начале файла сценария:

   • System.IO для операций с файловой системой.

   • System.Data.OleDb для открытия файлов Excel как источников данных.

4. Ссылки. Для образцов кода, выполняющих чтение данных схемы из файлов Excel, требуется дополнительная ссылка на проект сценария пространства имен System.Xml.

5. Установите язык сценария по умолчанию для компонента сценария с помощью параметра Язык сценария на странице Общие диалогового окна Параметры. Дополнительные сведения см. в разделе Страница «Общие».

Описание примера 1. Проверка существования файла Excel

В этом примере определяется, существует ли файл книги Excel, указанной в переменной ExcelFile, а затем присваивается логическое значение переменной ExcelFileExists в соответствии с результатом. С помощью этого логического значения можно реализовать ветвление в рабочем процессе пакета.

Настройка этого образца задачи «Сценарий»

1. Добавьте в пакет новую задачу «Сценарий» и измените ее имя на ExcelFileExists.

2. В Редакторе задачи «Сценарий» на вкладке Сценарий щелкните свойство ReadOnlyVariables и введите значение свойства одним из следующих способов.

   • Введите ExcelFile.

     — или —

   • Нажмите кнопку с многоточием (…) рядом с полем свойства и в диалоговом окне Выбор переменных выберите переменную ExcelFile.

3. Щелкните свойство ReadWriteVariables и введите значение свойства одним из следующих способов.

   • Введите ExcelFileExists.

     — или —

   • Нажмите кнопку с многоточием (…) рядом с полем свойства и в диалоговом окне Выбор переменных выберите переменную ExcelFileExists.

4. Нажмите кнопку Изменить сценарий, чтобы открыть редактор сценариев.

5. В верхней части файла сценария добавьте инструкцию Imports для пространства имен System.IO.

6. Добавьте следующий код.

Код примера 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;
  }
}

Описание примера 2. Проверка существования таблицы Excel

В этом примере определяется, существует ли лист или именованный диапазон Excel, указанный в переменной ExcelTable, в книге Excel, указанной в переменной ExcelFile, а затем присваивается логическое значение переменной ExcelTableExists в соответствии с результатом. С помощью этого логического значения можно реализовать ветвление в рабочем процессе пакета.

Настройка этого образца задачи «Сценарий»

1. Добавьте в пакет новую задачу «Сценарий» и измените ее имя на ExcelTableExists.

2. В Редакторе задачи «Сценарий» на вкладке Сценарий щелкните свойство ReadOnlyVariables и введите значение свойства одним из следующих способов.

   • Введите значения ExcelTable и ExcelFile, разделенные запятыми.

     — или —

   • Нажмите кнопку с многоточием (…) рядом с полем свойства и в диалоговом окне Выбор переменных выберите переменные ExcelTable и ExcelFile.

3. Щелкните свойство ReadWriteVariables и введите значение свойства одним из следующих способов.

   • Введите ExcelTableExists.

     — или —

   • Нажмите кнопку с многоточием (…) рядом с полем свойства и в диалоговом окне Выбор переменных выберите переменную ExcelTableExists.

4. Нажмите кнопку Изменить сценарий, чтобы открыть редактор сценариев.

5. Добавьте ссылку на сборку System.Xml в проект сценария.

6. В верхней части файла сценария добавьте инструкции Imports для пространств имен System.IO и System.Data.OleDb.

7. Добавьте следующий код.

Код примера 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;

        }
}

Описание примера 3. Получение списка файлов Excel в папке

В этом примере массив заполняется списком файлов Excel, найденных в папке, которая была указана в качестве значения переменной ExcelFolder, а затем этот массив копируется в переменную ExcelFiles. Можно использовать перечислитель по объекту из переменной, чтобы выполнить итерацию по файлам в массиве.

Настройка этого образца задачи «Сценарий»

1. Добавьте в пакет новую задачу «Сценарий» и измените ее имя на GetExcelFiles.

2. Откройте Редактор задачи «Сценарий». На вкладке Сценарий щелкните свойство ReadOnlyVariables и введите значение свойства одним из следующих способов.

   • Введите ExcelFolder

     — или —

   • Нажмите кнопку с многоточием (…) рядом с полем свойства и в диалоговом окне Выбор переменных выберите переменную ExcelFolder.

3. Щелкните свойство ReadWriteVariables и введите значение свойства одним из следующих способов.

   • Введите ExcelFiles.

     — или —

   • Нажмите кнопку с многоточием (…) рядом с полем свойства и в диалоговом окне Выбор переменных выберите переменную ExcelFiles.

4. Нажмите кнопку Изменить сценарий, чтобы открыть редактор сценариев.

5. В верхней части файла сценария добавьте инструкцию Imports для пространства имен System.IO.

6. Добавьте следующий код.

Код примера 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;
  }
}

Альтернативное решение

Вместо того чтобы использовать задачу «Сценарий» для сбора списка файлов Excel в массив, можно применить перечислитель с циклом по каждому файлу для выполнения итерации по всем файлам Excel в папке. Дополнительные сведения см. в разделе Как просматривать файлы и таблицы Excel с помощью контейнера «цикл по каждому элементу».

Описание примера 4. Получение списка таблиц в файле Excel

В этом примере массив заполняется списком листов и именованных диапазонов, найденных в файле книги Excel, которая была указана в переменной ExcelFile, а затем этот массив копируется в переменную ExcelTables. Можно использовать перечислитель по объекту из переменной, чтобы выполнить итерацию по таблицам в массиве.

Примечание
Список таблиц в книге Excel включает в себя и листы (которые имеют суффикс $), и именованные диапазоны. Если нужно отфильтровать список только по листам или только по именованным диапазонам, то, возможно, понадобится добавить дополнительный код.

Настройка этого образца задачи «Сценарий»

1. Добавьте в пакет новую задачу «Сценарий» и измените ее имя на GetExcelTables.

2. Откройте Редактор задачи «Сценарий». На вкладке Сценарий щелкните свойство ReadOnlyVariables и введите значение свойства одним из следующих способов.

   • Введите ExcelFile.

     — или —

   • Нажмите кнопку с многоточием (…) рядом с полем свойства и в диалоговом окне Выбор переменных выберите переменную ExcelFile.

3. Щелкните свойство ReadWriteVariables и введите значение свойства одним из следующих способов.

   • Введите ExcelTables.

     — или —

   • Нажмите кнопку с многоточием (…) рядом с полем свойства и в диалоговом окне Выбор переменных выберите переменную ExcelTables.

4. Нажмите кнопку Изменить сценарий, чтобы открыть редактор сценариев.

5. Добавьте ссылку на пространство имен System.Xml в проект сценария.

6. В верхней части файла сценария добавьте инструкцию Imports для пространства имен System.Data.OleDb.

7. Добавьте следующий код.

Код примера 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;
        }
}

Альтернативное решение

Вместо того чтобы использовать задачу «Сценарий» для сбора списка таблиц Excel в массив, можно применить перечислитель по набору строк схемы ADO.NET для перебора всех таблиц (т.е. листов и именованных диапазонов) в файле книги Excel. Дополнительные сведения см. в разделе Как просматривать файлы и таблицы Excel с помощью контейнера «цикл по каждому элементу».

Отображение результатов образцов

Если все образцы в этом разделе были настроены для использования с одним пакетом, можно соединить все задачи «Сценарий» с дополнительной задачей «Сценарий», отображающей выход всех образцов.

Настройка задача «Сценарий» для отображения выхода всех образцов в этом разделе

1. Добавьте в пакет новую задачу «Сценарий» и измените ее имя на DisplayResults.

2. Соедините каждую задачу «Сценарий» всех четырех образцов друг с другом, чтобы каждая задача выполнялась после успешного завершения предыдущей, и соедините задачу четвертого образца с задачей DisplayResults.

3. Откройте задачу DisplayResults в редакторе задачи «Сценарий».

4. На вкладке Сценарий щелкните ReadOnlyVariables и используйте один из следующих методов, чтобы добавить все семь переменных, перечисленных в разделе Настройка пакета для тестирования образцов:

   • Введите имена переменных, разделенные запятыми.

     — или —

   • Нажмите кнопку с многоточием (…) рядом с полем свойства и в диалоговом окне Выбор переменных выберите переменные.

5. Нажмите кнопку Изменить сценарий, чтобы открыть редактор сценариев.

6. В верхней части файла сценария добавьте инструкции Imports для пространств имен Microsoft.VisualBasic и System.Windows.Forms.

7. Добавьте следующий код.

8. Выполните пакет и просмотрите результаты, отобразившиеся в окне сообщения.

Код для отображения результатов

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

Будьте в курсе новых возможностей cлужб Integration Services Чтобы загружать новейшую документацию, статьи, образцы и видеоматериалы от корпорации Майкрософт, а также лучшие решения от участников сообщества, посетите страницу служб Integration Services на сайтах MSDN или TechNet: Посетить страницу «Службы Integration Services» на сайте MSDN Посетить страницу «Службы Integration Services» на сайте TechNet Чтобы получать автоматические уведомления об этих обновлениях, подпишитесь на RSS-каналы, предлагаемые на этой странице.

См. также

Задания

Как просматривать файлы и таблицы Excel с помощью контейнера «цикл по каждому элементу»

Основные понятия

Диспетчер соединений с Excel