Edited at

ADO.NETでExcelファイルに接続する

More than 1 year has passed since last update.

ExcelファイルにSQL投げられるよ!よ!

対象のファイルをExcelで開いていると、お互いにリアルタイムで反映されて楽しいかもしれない。


ランタイム

今回はAccessデータベースエンジン(ACEエンジン)を使います。Accessのコンポーネントなので、インストールされていない環境であればMicrosoft Access データベース エンジン 2010 再頒布可能コンポーネントあたりを入れるとよいです。Access全体のランタイムもありますが、でかいです。


注:Access2013は Microsoft.ACE.OLEDB.12.0 として呼び出すと例外吐くようなので、15.0として呼び出すか、上のコンポーネントを入れます。2016は12.0 or 16.0どちらでも。

注:既にAccess2010がインストールされている環境に2016Runtimeをインストール・アンインストールしたところ、2010付属のACE12も消え、修復インストールが必要になりました。後になって思えば上のコンポーネントだけインストールすればよかったのかも。


インストールされていれば OleDbEnumerator.GetElements() でそれらしいのが見れるはず。あと、例によってx86とx64別です。

なおExcel2003以前の形式(.xls)だけなら、Windows付属(7まで?)の Microsoft.Jet.OLEDB.4.0 というプロバイダでも基本的に同じように使えます。


接続する

接続文字列に必要なパラメータは以下の通り。


  • Provider: Microsoft.ACE.OLEDB.12.0

  • Data Source: ファイル名

  • Extended Properties: ISAM形式; オプション


ISAM形式は Excel 12.0 Excel 12.0 Xml Excel 12.0 Macro Excel 8.0 など。 HKLM\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access\Access Connectivity Engine\ISAM Formats (もしくはそれに準ずる場所)で一覧が見れます。オプションは・・・



  • HDR=NO:ヘッダ行なし(F1, F2, ...といった列名になる)


  • MAXSCANROWS=[1..16]:データ型の自動判定に使われる行数

var constr = new OleDbConnectionStringBuilder

{
["Provider"] = "Microsoft.ACE.OLEDB.12.0",
["Data Source"] = @"r:\hoge.xls",
["Extended Properties"] = "Excel 12.0 Xml"
}.ToString();
var con = new OleDbConnection(constr);


何ができるか



  • [名前](Excelで定義した領域名)


  • [シート名$](データが入力されている左上から右下までの範囲)

  • [シート名$名前]


  • [シート名$セル範囲][シート名$] の範囲内のみ)

をテーブル名として、SELECT/INSERT/UPDATEが普通に使えます。ただしDELETEはできない。


  • CREATE TABLE

    既存の領域を指定すると見出しを変更できます。

    $を含まない文字列を指定するとシート名として扱われ、新しいシートが作成されます。

  • DELETE TABLE

    列見出しも含めて消去します。定義済みの名前で指定すると、その名前も削除されるようです。


参考