.NET アプリケーションから Microsoft Office データにアクセスする
Christa Carpentiere
2004 年 4 月
適用対象:
Microsoft® Access
Microsoft® Excel
Microsoft® Office
Microsoft® Visual Basic® .NET
Microsoft® Visual Studio® .NET
概要: Office ファイルと .NET アプリケーションから Microsoft Access と Excel のデータを取得する方法と、OLE DB プロバイダーを使用して、.xlsまたは .mdb ファイルを簡単に操作できるようにする汎用コードを記述する方法について説明します。 (12ページ印刷)
内容
はじめに
接続の取得
データの取得
汎用データ取得にメタデータを使用する
まとめ
関連書籍
はじめに
Microsoft .NET アプリケーションで Microsoft Office データを操作するために必要なタスクのほとんどは、接続の作成、データの操作に応じた DataReader または DataAdapter の作成、関連データの DataTable をカプセル化するための 1 つ以上の DataSet の作成など、他のデータの使用と同じです。だから、私はここに ADO.NET ドキュメントを書き直すつもりはありません。 むしろ、この記事の目的は、Office データ ソースに対して基本的なデータ取得コードを記述するために必要な情報の欠落または広く分散された部分を提供することです。 Jet の風変わりな点を知っている人は、ADO.NET にあまり慣れていないようで、ADO.NET しているほとんどの人は、Office のプログラミングを支えるテクノロジについてあまり知りません。 ここでは、この分割の両側で見た一般的な質問に答える簡単な 1 回の説明を示します。
まず、Microsoft Excel と Microsoft Access への接続とデータの取得の基本を実行します。 この領域は、私が最大の問題を見てきた場所です。通常、人々がここまで行くと、Office固有の問題が処理され、残りの問題は ADO.NET の使用に焦点を当てています。 次に、OleDbConnection オブジェクトの GetOleDbSchemaTable メソッドを使用して、実行時に Access または Excel データに関するスキーマ情報を取得する方法について説明します。 これにより、データ ソースに関するハードコーディング情報を回避できます。これは、データの取得元の場所を問わず役に立ちます。
接続の取得
Excel スプレッドシートまたは Access データベースに接続するには、 OleDbConnection オブジェクトを作成し、ターゲット データ ソースの詳細を含む接続文字列を渡す必要があります。 Provider プロパティには Jet 4.0 OLE DB プロバイダーを指定する必要があります。
Access への接続
Access データベースの一般的な接続文字列を見てみましょう。
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\Databases\mymusic.mdb"
これは非常に単純です。プロバイダーとデータ ソースのプロパティのみを指定します。 Provider は前述のように Jet 4.0 プロバイダーであり、 Data Source プロパティにはデータベースへの完全修飾パスが含まれています。
もちろん、Access データベースもセキュリティで保護されていますか? そのため、ワークグループ情報ファイル (既定では system.mdw) へのパスと、ユーザー ID とパスワードを指定する必要もあります。
"Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
"Source=C:\Databases\mymusic.mdb; " & _
"Jet OLEDB:System database=" & _
"C:\Databases\system.mdw; " & _
"User ID=UserX;Password=UserXPassword"
Access データベースのセキュリティを制御する .mdw ファイルを指定するには、Jet OLE DB 固有のプロパティを使用する必要があることに注意してください。 データベースの動作をより詳細に制御する場合は、「付録 B: Microsoft Jet 4.0 OLE DB プロパティ リファレンス」の Jet OLE DB プロバイダーによって提供される他のプロパティを調べて、ロック動作や特定の種類のエラーの処理方法などを制御できます。 接続文字列で設定できるプロパティにのみアクセスできることに注意してください。指定する前に接続を開く必要があるプロバイダー プロパティを設定する方法はありません。
Microsoft® Visual Studio® .NET を使用して、すべての Jet OLE DB プロバイダー設定を含む完全なベルとホイッスルの Access 接続文字列のテンプレートを取得できます。 サーバー エクスプローラーで Access データベースへのデータ接続を作成し、ツールボックスの [データ] セクションを使用して OleDbConnection オブジェクトを作成します。 結果のオブジェクトの ConnectionString プロパティには、既定ですべての Jet OLE DB プロバイダー プロパティが含まれます。
Excel への接続
次に、Excel スプレッドシートの一般的な接続文字列を見てみましょう。
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Spreadsheets\calculations.xls;" & _
"Extended Properties=Excel 8.0"
Excel ファイルに接続する場合は、接続文字列の Extended Properties プロパティとプロバイダーとデータ ソースを使用する必要があります。 Excel 8.0 以降の場合は、"Excel 8.0" 設定を使用します。 このプロパティに使用できるその他の値の詳細については、「 ADO プロバイダーのプロパティと設定」の「拡張プロパティ プロパティの設定」セクションを参照してください。
"Aha" と言います。"しかし、Excel のセキュリティはどうでしょうか?さて、そこに素晴らしいニュースはありません、私は恐れています。 Excel でスプレッドシートを既に手動で開いていない限り、パスワードで保護されたスプレッドシートへの接続を開くことはできません (詳細については、「 XL2000: パスワードで保護されたファイルでファイルを復号化できませんでした」エラーを参照してください)。 説明されているエラーは Excel ODBC プロバイダーで発生しますが、Jet 4.0 OLE DB プロバイダーの動作は同じです。 もう 1 つのオプションは、スプレッドシートからパスワードを削除し、他のセキュリティ メカニズム (ファイルが存在するフォルダーに対するアクセス許可の制限など) に依存してアクセスを制御することです。
残念ながら、Visual Studio .NET を使用して Excel 接続文字列のテンプレートを取得することもできません。 少し詳しく説明すると、Excel データ接続を作成できますが、そのプロパティは編集できず、 ConnectionString プロパティは空白になり、IDE (統合開発環境) では少し奇妙です。 詳細については、「 PRB: Visual Studio .NET で Excel ファイルへのデータ接続を構成できない」を参照してください。 したがって、Excel 接続の場合は、自分で行うのはかなり簡単ですが、この状況では、ユーザー インターフェイス (UI) で作成するのと同じくらい簡単にコーディングできます。
データの取得
Office データ ソースへの接続を取得する方法が明確になったので、データを取得するために必要なものを見てみましょう。 わかりやすくするために、 OleDbConnection/OleDbCommand/OleDbDataReader データ取得シナリオを使用します。 少し調整するだけで同じ方法を使用して 、OleDbDataAdapter を構築し、代わりに DataSet を塗りつぶすことができます。 一般的に ADO.NET の詳細については、「.NET Framework開発者ガイド」の「ADO.NET を使用したデータへのアクセス」セクションを参照してください。
アクセス データの取得
Access データ取得コードを記述するときに覚えておく必要がある最初の重要な点は、SQL を指定する必要がある構文にいくつかの特異点があるということです。 グラフィカル UI で Access クエリを作成したり、SQL ビューにポップオーバーしたり、結果の SQL ステートメントをコピーしてコードに貼り付けることはできません。 いいえ、それは簡単すぎます。 通常、生成される SQL コードには、必要な構文の一部が含まれますが、すべてではありません。 Access 開発環境でコードを記述する必要があるユーザーはだれでもこれを知っていますが、平均的な .NET クライアント アプリケーション開発者にとっては、これはニュースになる可能性があります。 大きな違反者は条件式であり、WHERE 句内の特定の種類のデータを特定の方法で区切る必要があります。 日付と時刻の値は、数値記号 (#) で区切る必要があります。 テキスト値は、単一引用符 (') で区切る必要があります。 次に例を示します。
SELECT City, Neighborhood, SalePrice, MonthsOnMarket
FROM RealEstate
WHERE ListingDate > #1/1/04#
これらのバグベアの詳細については、「日付と時刻の 条件式」を参照してください。
他に注意すべき点はかなり明白であるように思われますが、とにかく人を混乱させるようです。したがって、私はそれをメンションします:Accessテーブルが列名に予約語を使用していないことを確認してください。 予約語は、「SQL 予約語」でチェックできます。 これらのいずれかを使用する場合は、可能であれば列の名前を変更するとします。 私はあなたが既にデータベースを使用している場合、これはめったに不可能であることを知っているので、ピンチでクエリを作成し、問題のある列を持つテーブルの代わりにそれを使用することができます。 次のように、AS を使用して列の名前を変更するだけです。
SELECT Artists.ArtistName, Genres.Genre, Labels.Label,
Tracks.Public AS Track, Releases.ReleaseName
FROM (Labels INNER JOIN ((Artists...
これらの項目を念頭に置いて、例を見てみましょう。
Imports System
Imports System.Data
Imports System.Data.OleDb
...
Public Function GetAccessData(ByVal UID As String, _
ByVal pwd As String, ByVal artist As String)
Dim conn As New OleDbConnection
Dim musicReader As OleDbDataReader
Dim cmd As New OleDbCommand
Dim connString As String
Dim i As Integer
Try
' Set the connection string.
connString = "Jet OLEDB:System database=" & _
"C:\Databases\system.mdw;" & _
"Data Source=C:\Databases\mymusic.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User ID=" & UID & ";Password=" & pwd
' Open the connection.
conn.ConnectionString = connString
conn.Open()
'Set the command properties.
cmd.Connection = conn
cmd.CommandText = "SELECT * from music " & _
"WHERE ArtistName = '" & artist & "'"
' Get the OleDbDataReader
' and do some processing with it.
musicReader = _
cmd.ExecuteReader(CommandBehavior.CloseConnection)
Try
While (musicReader.Read)
'Process data.
End While
Finally
musicReader.Close()
End Try
Catch ex As Exception
'Error handling
End Try
End Function 'GetAccessData
Excel データの取得
Excel は、列名に関して Access と同じ予約語禁止を共有します。 一般に、SQL の予約語を念頭に置いて、データ ソースとして使用できるものを作成するときにそれらを避けると、より幸せになります。 心配する必要が少なくなります。
また、Excel には構文に独自の違いがあります。 コードに最も影響する項目は、返す一連のデータを参照するための構文です。
メモ Excel から最も簡単にデータを取得するには、テーブルのような形式を維持するスプレッドシートを使用します。
最初のオプションは、ワークシートと、必要に応じてそのシート上のセルのセットを指定することです。 ワークシート名の後にドル記号が続き、必要に応じてセル セットが続いていることを確認する必要があります。 セル セットは、セットの開始セルと終了セルで指定され、コロンで区切られます。 このデータ識別文字列全体が角かっこで囲まれます。 この種類の構文を使用する SELECT ステートメントは、次のようになります。
SELECT SalesMonth, TotalSales, PercentageChange1Year
FROM [Sheet1$A1:E24]
もう 1 つのオプションは、テーブルアナログとして機能する名前付き範囲を Excel で作成することです。 名前付き範囲を作成するには、「名前 付きセル参照または範囲を作成する」を参照してください。 範囲名は、SELECT ステートメントのテーブル名と同じように使用します。
SELECT SalesMonth, TotalSales, PercentageChange1Year
FROM SalesHighlights
これらの項目を念頭に置いて、例を見てみましょう。
Imports System
Imports System.Data
Imports System.Data.OleDb
...
Public Function GetExcelData()
Dim conn As New OleDbConnection
Dim salesReader As OleDbDataReader
Dim connString As String
Dim cmd As New OleDbCommand
Try
' Set the connection string.
connString = "Data Source=" & _
"C:\Spreadsheets\calculations.xls;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=Excel 8.0;"
' Open the connection.
conn.ConnectionString = connString
conn.Open()
'Set the command properties.
cmd.Connection = conn
cmd.CommandText = "SELECT SalesMonth, " & _
"TotalSales, PercentageChange1Year, " & _
"VolumeDiscounts, Profit from [Sheet1$]"
' Get the OleDbDataReader
' and do some processing with it.
salesReader = _
cmd.ExecuteReader(CommandBehavior.CloseConnection)
Try
While (salesReader.Read)
'Process data.
End While
Finally
salesReader.Close()
End Try
Catch ex As Exception
'Error handling
End Try
End Function 'GetExcelData
汎用データ取得にメタデータを使用する
Excel または Access データ ソースに接続してデータを取得できるようになったので、もう一歩先に進みましょう。 これらのデータ ソースの 1 つからメタデータを取得するために何を行う必要があるかを見てみましょう。これを使用して、データ アクセス コードを構築できます。 このプロセスは、特定のデータ ソースに関連付けたくない一般的な機能を提供するプロシージャを作成する場合に便利です。
OleDbConnection.GetOleDbSchemaTable の使用
必要なメタデータを取得するには、 OleDbConnection.GetOleDbSchemaTable メソッドを使用する必要があります。 このメソッドのコンストラクターは、OLE DB スキーマ行セットを表す OleDbSchemaGuid オブジェクトと、返されるスキーマ情報の基本的な選択条件を表す Objects の配列を受け取ります。
メモ OLE DB スキーマ行セットに慣れていないユーザーにとっては、ANSI SQL-92 で定義されているデータベースコンストラクトの基本的に標準化されたスキーマです。 各スキーマ行セットには、指定されたコンストラクトの定義メタデータを提供する一連の列 (.NET ドキュメントでは "制限列" と呼ばれます) があります。 そのため、列や照合順序などのスキーマ情報を要求すると、返されるデータの種類が正確にわかります。 詳細については、「 付録 B: スキーマ行セット」を参照してください。
Object 配列は、ドキュメントの "制限値の配列" として定義されています。 SQL の WHERE 句のように、返されるデータセットを特定 (つまり制限) するために使用されます。 たとえば、ワークシート Alpha、Beta、Pi を含むブックに接続します。 スキーマ情報を使用して、ワークシート Beta に含まれる列を把握する必要があります。 コードは次のようになります。
schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, _
"Beta", Nothing})
列情報を返す必要があるため、 OleDbSchemaGuid.Columns フィールドを使用して、COLUMNS スキーマを使用する必要があることを示します。 COLUMNS スキーマには、TABLE_CATALOG、TABLE_SCHEMA、TABLE_NAME、およびCOLUMN_NAME制限列が含まれており、配列内のそれぞれの制限値を表す Object を指定する必要があります。 TABLE_NAME値として "Beta" を指定することで、返される列情報をその "テーブル" から返される列情報のみに制限します。
友人の GetOleDbSchemaTable に慣れたので、どのように実践できるかを見てみましょう。 これを使用してデータ ソース内のテーブルと列を反復処理することで、事前にスキーマを理解しなくても、データを取得するために必要なすべての情報を取得できます。 Excel の例を見てみましょう。
Imports System
Imports System.Data
Imports System.Data.OleDb
...
Public Function GetExcelSchema(ByVal xlsPath As String) As DataSet
Dim schemaTable As New DataTable
Dim workAdapter As New OleDbDataAdapter
Dim workSet As New DataSet
Dim conn As New OleDbConnection
Dim i As Integer
Dim x As Integer
Dim charArray As Char() = {",", " "}
Dim charArray2 As Char() = {"$"}
Dim cmdString As String
Dim cmdString2 As String
Dim cmd As New OleDbCommand
Dim tableName As String
workSet.DataSetName = "excelData"
Try
' Set the connection string.
Dim connString As String = _
"Data Source=" & xlsPath & _
";Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=Excel 8.0"
' Open the connection.
conn.ConnectionString = connString
conn.Open()
' Populate the DataTable with schema
' information on the data source tables.
schemaTable = _
conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})
' Populate an array with the table names.
i = schemaTable.Rows.Count - 1
Dim tablesArray(i) As String
For i = 0 To schemaTable.Rows.Count - 1
tablesArray(i) = schemaTable.Rows(i).Item("Table_Name")
Next
' Clear the DataTable
schemaTable.Clear()
' Use the table names and the column schema
' information to construct SELECT statements
' and return data for each table in the data source.
For i = 0 To tablesArray.GetLength(0) - 1
' Populate the DataTable with schema
' information on the data source columns.
schemaTable = _
conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, _
tablesArray(i).ToString(), Nothing})
' Step through the column names and append
' them into a SELECT statement
cmdString = "SELECT "
For x = 0 To schemaTable.Rows.Count - 1
cmdString = cmdString & _
schemaTable.Rows(x).Item("Column_Name") & _
", "
Next
cmdString2 = cmdString.TrimEnd(charArray)
' Note that you don't need to append "$" to
' the table name - it is already included.
cmdString2 = cmdString2 & " FROM [" & _
tablesArray(i).ToString() & "]"
' Use the SELECT command and the
' OleDbDataAdapter to fill the DataSet.
cmd.CommandText = cmdString2
workAdapter.SelectCommand = cmd
workAdapter.SelectCommand.Connection = conn
tableName = _
tablesArray(i).ToString().TrimEnd(charArray2)
workAdapter.Fill(workSet, tableName)
schemaTable.Clear()
Next
Catch ex As Exception
'Error handling
Finally
conn.Close()
End Try
Return workSet
End Function 'GetExcelSchema
ご覧のように、関連するコードは非常に簡単です。 Access データベースで同じことを行う場合、実際の違いは接続文字列だけであり、テーブル名をワークシートとして書式設定する必要がないため、SELECT ステートメントで使用できます。
ところで、この方法は、SQL Server ™ データベースと同様に使用することも、データ ソースから取得したスキーマ情報に基づいて他の操作を実行するように変更することもできます。 これは、あらゆる種類の検出やドキュメントを実行しようとしている場合に便利です。
まとめ
Office データと ADO.NET を一緒に使用するこの簡単なツアーにご参加いただき、ありがとうございます。 それは珍しい仕事ではなく、私はそれに関する基本的な情報の一部が容易に利用できないという理由だけで、これまでは必要以上に難しいと思います。 その部門の Access データベースから情報を取得するだけの場合でも、すべてのユーザーの毎月の経費スプレッドシートのデータを集計する場合でも、始める場所を提供できたらと思います。