スクリプトタスクを使用した Excel ファイルの操作

[アーティクル]
05/07/2013

Integration Services には Excel 接続マネージャー、Excel ソース、Excel 変換先が用意されており、Microsoft Excel ファイル形式のスプレッドシートに保存されているデータを操作できます。このトピックで説明する方法では、スクリプトタスクを使用して、使用可能な Excel のデータベース (ワークブックファイル) およびテーブル (ワークシートおよび名前付き範囲) に関する情報を取得します。これらのサンプルに簡単な変更を加えて、Microsoft Jet OLE DB プロバイダーによってサポートされる他のすべてのファイルベースデータソースを操作することができます。

サンプルをテストするためのパッケージの構成

例 1 : Excel ファイルが存在するかどうかを確認する

例 2 : Excel テーブルが存在するかどうかを確認する

例 3 : フォルダー内の Excel ファイルの一覧を取得する

例 4 : Excel ファイル内のテーブルの一覧を取得する

サンプルの結果の表示

注
複数のパッケージでより簡単に再利用できるタスクを作成する場合は、このスクリプトタスクサンプルのコードを基にした、カスタムタスクの作成を検討してください。詳細については、「カスタムタスクの開発」を参照してください。

サンプルをテストするためのパッケージの構成

このトピックのすべてのサンプルをテストする単一のパッケージを構成することができます。これらのサンプルでは、同じパッケージ変数と同じ .NET Framework クラスを数多く使用します。

このトピックの例で使用するパッケージを構成するには

SQL Server データツール (SSDT) で新しい Integration Services プロジェクトを作成し、編集のために既定のパッケージを開きます。
変数。 [変数] ウィンドウを開き、次の変数を定義します。
- String 型の ExcelFile。既存の Excel ワークブックの完全なパスとファイル名を入力します。
- String 型の ExcelTable。 ExcelFile 変数の値で指定されたワークブック内の既存のワークシートまたは名前付き範囲の名前を入力します。この値では大文字と小文字が区別されます。
- Boolean 型の ExcelFileExists。
- Boolean 型の ExcelTableExists。
- String 型の ExcelFolder。少なくとも 1 つの Excel ワークブックを含むフォルダーの完全なパスを入力します。
- Object 型の ExcelFiles。
- Object 型の ExcelTables。
Imports ステートメント。ほとんどのコードサンプルでは、スクリプトファイルの先頭で次の .NET Framework 名前空間のいずれかまたは両方をインポートする必要があります。
- ファイルシステム操作用の System.IO。
- Excel ファイルをデータソースとして開くための System.Data.OleDb。
参照。 Excel ファイルからスキーマ情報を読み取るコードサンプルでは、スクリプトプロジェクトで System.Xml 名前空間への追加の参照が必要です。
[オプション] ダイアログボックスの [全般] ページにある [スクリプト言語] オプションを使用して、スクリプトコンポーネントの既定のスクリプト言語を設定します。詳細については、「[全般] ページ」を参照してください。

例 1 の説明 : Excel ファイルが存在するかどうかを確認する

この例では、ExcelFile 変数で指定された Excel ワークブックファイルが存在するかどうかを判断し、その結果を ExcelFileExists 変数のブール値に設定します。このブール値は、パッケージのワークフローを分岐させるために使用することができます。

このスクリプトタスクの例を構成するには

パッケージに新しいスクリプトタスクを追加し、名前を ExcelFileExists に変更します。
[スクリプトタスクエディター] の [スクリプト] タブで [ReadOnlyVariables] をクリックし、次のいずれかの方法でプロパティ値を入力します。
- 「ExcelFile」と入力します。
  
  - または -
- プロパティフィールドの横にある参照ボタン ([...]) をクリックし、[変数の選択] ダイアログボックスで [ExcelFile] 変数を選択します。
[ReadWriteVariables] をクリックし、次のいずれかの方法でプロパティ値を入力します。
- 「ExcelFileExists」と入力します。
  
  - または -
- プロパティフィールドの横にある参照ボタン ([...]) をクリックし、[変数の選択] ダイアログボックスで [ExcelFileExists] 変数を選択します。
[スクリプトの編集] をクリックして、スクリプトエディターを開きます。
スクリプトファイルの先頭に、System.IO 名前空間の Imports ステートメントを追加します。
次のコードを追加します。

例 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 テーブルが存在するかどうかを確認する

この例では、ExcelTable 変数で指定された Excel ワークシートまたは名前付き範囲が ExcelFile 変数で指定された Excel ワークブックファイル内に存在するかどうかを判断し、その結果を ExcelTableExists 変数のブール値に設定します。このブール値は、パッケージのワークフローを分岐させるために使用することができます。

このスクリプトタスクの例を構成するには

パッケージに新しいスクリプトタスクを追加し、名前を ExcelTableExists に変更します。
[スクリプトタスクエディター] の [スクリプト] タブで [ReadOnlyVariables] をクリックし、次のいずれかの方法でプロパティ値を入力します。
- 「ExcelTable」および「ExcelFile」をコンマで区切って入力します。
  
  - または -
- プロパティフィールドの横にある参照ボタン ([...]) をクリックし、[変数の選択] ダイアログボックスで [ExcelTable] と [ExcelFile] の変数を選択します。
[ReadWriteVariables] をクリックし、次のいずれかの方法でプロパティ値を入力します。
- 「ExcelTableExists」と入力します。
  
  - または -
- プロパティフィールドの横にある参照ボタン ([...]) をクリックし、[変数の選択] ダイアログボックスで [ExcelTableExists] 変数を選択します。
[スクリプトの編集] をクリックして、スクリプトエディターを開きます。
スクリプトプロジェクトに System.Xml アセンブリへの参照を追加します。
スクリプトファイルの先頭に、System.IO 名前空間と System.Data.OleDb 名前空間の Imports ステートメントを追加します。
次のコードを追加します。

例 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 ファイルの一覧を取得する

この例では、ExcelFolder 変数の値で指定されたフォルダー内で検索された Excel ファイルの一覧を配列に代入し、その配列を ExcelFiles 変数にコピーします。 Foreach from Variable 列挙子を使用して、配列内のファイルを繰り返し処理することができます。

このスクリプトタスクの例を構成するには

パッケージに新しいスクリプトタスクを追加し、名前を GetExcelFiles に変更します。
[スクリプトタスクエディター] を開き、[スクリプト] タブで [ReadOnlyVariables] をクリックし、次のいずれかの方法でプロパティ値を入力します。
- 「ExcelFolder」と入力します。
  
  - または -
- プロパティフィールドの横にある参照ボタン ([...]) をクリックし、[変数の選択] ダイアログボックスで [ExcelFolder] 変数を選択します。
[ReadWriteVariables] をクリックし、次のいずれかの方法でプロパティ値を入力します。
- 「ExcelFiles」と入力します。
  
  - または -
- プロパティフィールドの横にある参照ボタン ([...]) をクリックし、[変数の選択] ダイアログボックスで [ExcelFiles] 変数を選択します。
[スクリプトの編集] をクリックして、スクリプトエディターを開きます。
スクリプトファイルの先頭に、System.IO 名前空間の Imports ステートメントを追加します。
次のコードを追加します。

例 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 ファイルの一覧を配列に集める代わりに、ForEach File 列挙子を使用してフォルダー内のすべての Excel ファイルを繰り返し処理することもできます。詳細については、「Foreach ループコンテナーを使用して Excel のファイルおよびテーブルをループ処理する」を参照してください。

例 4 の説明 : Excel ファイル内のテーブルの一覧を取得する

この例では、ExcelFile 変数の値で指定された Excel ワークブックファイル内で検索されたワークシートまたは名前付き範囲の一覧を配列に代入し、その配列を ExcelTables 変数にコピーします。 Foreach from Variable 列挙子を使用して、配列内のテーブルを繰り返し処理することができます。

注
Excel ワークブック内のテーブルの一覧には、ワークシート ($ というサフィックスが付きます) と名前付き範囲の両方が含まれます。ワークシートまたは名前付き範囲のみを一覧からフィルター選択する必要がある場合は、そのためのコードを追加する必要があります。

このスクリプトタスクの例を構成するには

パッケージに新しいスクリプトタスクを追加し、名前を GetExcelTables に変更します。
[スクリプトタスクエディター] を開き、[スクリプト] タブで [ReadOnlyVariables] をクリックし、次のいずれかの方法でプロパティ値を入力します。
- 「ExcelFile」と入力します。
  
  - または -
- プロパティフィールドの横にある参照ボタン ([...]) をクリックし、[変数の選択] ダイアログボックスで [ExcelFile] 変数を選択します。
[ReadWriteVariables] をクリックし、次のいずれかの方法でプロパティ値を入力します。
- 「ExcelTables」と入力します。
  
  - または -
- プロパティフィールドの横にある参照ボタン ([...]) をクリックし、[変数の選択] ダイアログボックスで [ExcelTables] 変数を選択します。
[スクリプトの編集] をクリックして、スクリプトエディターを開きます。
スクリプトプロジェクトに System.Xml 名前空間への参照を追加します。
スクリプトファイルの先頭に、System.Data.OleDb 名前空間の Imports ステートメントを追加します。
次のコードを追加します。

例 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 テーブルの一覧を配列に集める代わりに、ForEach ADO.NET Schema Rowset 列挙子を使用して Excel ワークブックファイル内のすべてのテーブル (つまり、ワークシートと名前付き範囲) を繰り返し処理することもできます。詳細については、「Foreach ループコンテナーを使用して Excel のファイルおよびテーブルをループ処理する」を参照してください。

サンプルの結果の表示

このトピックの各例を同じパッケージで構成した場合は、すべてのスクリプトタスクを、すべての例の出力を表示する追加のスクリプトタスクに接続することができます。

このトピックの例の出力を表示するスクリプトタスクを構成するには

パッケージに新しいスクリプトタスクを追加し、名前を DisplayResults に変更します。
4 つのスクリプトタスク例のそれぞれを互いに接続し、各タスクが、前のタスクが正常に完了した後に実行されるようにして、4 番目のタスク例を DisplayResults タスクに接続します。
[スクリプトタスクエディター] で DisplayResults タスクを開きます。
[スクリプト] タブで [ReadOnlyVariables] をクリックし、次のいずれかの方法で、「サンプルをテストするためのパッケージの構成」で示した 7 つの変数のすべてを追加します。
- 各変数の名前をコンマで区切って入力します。
  
  - または -
- プロパティフィールドの横にある参照ボタン ([...]) をクリックし、[変数の選択] ダイアログボックスで変数を選択します。
[スクリプトの編集] をクリックして、スクリプトエディターを開きます。
スクリプトファイルの先頭に、Microsoft.VisualBasic 名前空間と System.Windows.Forms 名前空間の Imports ステートメントを追加します。
次のコードを追加します。
パッケージを実行し、メッセージボックスに表示される結果を調べます。

結果を表示するコード

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

最新の Integration Services の入手

マイクロソフトが提供する最新のダウンロード、アーティクル、サンプル、ビデオ、およびコミュニティで選択されたソリューションについては、MSDN の Integration Services のページを参照してください。

MSDN の Integration Services のページを参照する

これらの更新が自動で通知されるようにするには、ページの RSS フィードを定期受信します。

Share via

スクリプトタスクを使用した Excel ファイルの操作

サンプルをテストするためのパッケージの構成

このトピックの例で使用するパッケージを構成するには

例 1 の説明 : Excel ファイルが存在するかどうかを確認する

このスクリプトタスクの例を構成するには

例 1 のコード

例 2 の説明 : Excel テーブルが存在するかどうかを確認する

このスクリプトタスクの例を構成するには

例 2 のコード

例 3 の説明 : フォルダー内の Excel ファイルの一覧を取得する

このスクリプトタスクの例を構成するには

例 3 のコード

代替ソリューション

例 4 の説明 : Excel ファイル内のテーブルの一覧を取得する

このスクリプトタスクの例を構成するには

例 4 のコード

代替ソリューション

サンプルの結果の表示

このトピックの例の出力を表示するスクリプトタスクを構成するには

結果を表示するコード

関連項目

タスク

概念

その他のリソース

Share via

スクリプト タスクを使用した Excel ファイルの操作

サンプルをテストするためのパッケージの構成

このトピックの例で使用するパッケージを構成するには

例 1 の説明 : Excel ファイルが存在するかどうかを確認する

このスクリプト タスクの例を構成するには

例 1 のコード

例 2 の説明 : Excel テーブルが存在するかどうかを確認する

このスクリプト タスクの例を構成するには

例 2 のコード

例 3 の説明 : フォルダー内の Excel ファイルの一覧を取得する

このスクリプト タスクの例を構成するには

例 3 のコード

代替ソリューション

例 4 の説明 : Excel ファイル内のテーブルの一覧を取得する

このスクリプト タスクの例を構成するには

例 4 のコード

代替ソリューション

サンプルの結果の表示

このトピックの例の出力を表示するスクリプト タスクを構成するには

結果を表示するコード

関連項目

タスク

概念

その他のリソース

スクリプトタスクを使用した Excel ファイルの操作

このスクリプトタスクの例を構成するには

このスクリプトタスクの例を構成するには

このスクリプトタスクの例を構成するには

このスクリプトタスクの例を構成するには

このトピックの例の出力を表示するスクリプトタスクを構成するには