(.Net).NetアプリケーションからAccess(MDB)のテーブルをExcelファイルにエクスポート

Accessにはテーブルを各種形式にファイルにエクスポートできる機能があります。

(テーブル選択し、ファイルメニュー→エクスポートのことです。)

さて、これを .Net のアプリケーションからAccessを起動しなくてもExcelファイル(xls)にエクスポートしてやろうと言うのが今回のしたいことです。

●SQLクエリを使う方法

まず JETデータベース(MDB) のSQLクエリ自身にエクスポートする機能があるようです。

下記のようなSQL構文になります。

SELECT * ITNO エクスポート先ファイル名 FROM テーブル名

.Netからは OLEDB 経由で上記構文のSQLを実行すればいいだけです。

下記にサンプルソースを載せます。(C#)

//OLEDB接続オブジェクト生成
System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection();
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"D:\\test.mdb\";";
//OLEDBコマンドオブジェクト生成
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.Connection = cn;

//SQLクエリでエクスポートを指定
string strSQL = "SELECT * INTO [Excel 8.0;Database=D:\\test0.xls].[テストシート] FROM [tblTest]";
cmd.CommandText = strSQL;

cn.Open();
//実行
cmd.ExecuteNonQuery();
cn.Close();

上記を実行すると、D:\test.mdb 内の "tblTest" テーブルが D:\test0.xls ファイルの "テストシート" に出力されます。

この時 JET エンジンは Microsoft Jet 4.0 OLE DB プロバイダを使ってエクスポートするようですね。

対応するExcelファイルファイルのバージョンは下記のとおりです。

Excel 3.0

Excel 4.0

Excel 5.0(Excel95)

Excel 8.0(Excel97-2003)

(なお、Excel 12.0 以降(Excel2007)は JET 4.0 OLE DB じゃサポートしてないっぽいです。Microsoft Office 12.0 Access Database Engine OLE DB Providerをいれれば新形式も扱えるようになということがサンプルプログラム集　[631_ADOでAccess2007用のJETデータベースを扱う]に書かれていました。)

さて、まずこの方法で試みたわけですが、ExecuteNonQuery メソッドで下記のような例外が発生してしまいます。

System.Data.OleDb.OleDbException はハンドルされませんでした。
  Message=数値フィールドがオーバーフローしました。
  Source=Microsoft JET Database Engine
  ErrorCode=-2147467259
  StackTrace:
       場所 System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       場所 System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       場所 System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       場所 System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       場所 System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       場所 System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
       .....

調べてみたんですがエクスポート時におきる現象はどうもあまりないようです。

Accessのファイルメニューからエクスポートした時はうまく行くんですが。。。

簡単な mdb を作るとうまくいきました。

今回ターゲットにしてる mdb は数万件のデータが入っており、mdb にしてはそこそこ大きいので、どっかで型の不整合があるのかもしれません。

原因追究したかったんですが、時間が無いので別の方法をとることにしました。

参考:

きままにスクリプト

サンプルプログラム集　[623_ADOでJETデータベースのエクスポート]

MSサポート:ADO を使用して ADO データ ソースから Excel にデータを転送する方法

●VBScriptを使う方法

VBScript(WSH)から Access の TransferSpreadsheet メソッドを呼び出してもExcel形式にエクスポートできるようなので、その方法を試してみました。

下記のようなスクリプトになります。

'モード指定 1がエクスポートみたい(0だとインポート)
Const acExport = 1
'エクスポートするExcelファイルのバージョン
Const acSpreadsheetTypeExcel9 = 8
'Access操作用オブジェクト生成
Set objAccess = CreateObject("Access.Application")
'対象となるMDB指定
objAccess.OpenCurrentDatabase "D:\test.mdb"
'Excelファイルにエクスポート
objAccess.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblTest", "D:\test1.xls", True

上記のコードはMS:Hey, Scripting Guy! Access データベースのテーブルをワークシートとして保存する方法はありますかまるままです。

ただSQLクエリの時と違って、Excelのシート名の指定はできません。エクスポートしたテーブル名がシート名となります。(Accessファイルメニューで行うときと同じですね。)

後はこのスクリプトを .Net アプリケーションから実行してやればいいだけです。

上記のスクリプトが export.vbs というファイル名で、exe と同じパスにあるとした場合、こんな感じです。(C#)

using System.Diagnostics;

string strScrptPath = System.Windows.Forms.Application.StartupPath + "\\export.vbs";
//VBScript起動(wscriptなのはWaitForInputIdleを使えるようにするため)
Process prc = Process.Start("wscript.exe", "\"" + strScrptPath + "\"");
//↑で起動したプロセスが終わるまで待つ。
while (true)
{
    System.Threading.Thread.Sleep(500);
    try {
        Process.GetProcessById(prc.Id);
    } catch (Exception){
        break;
    }
}
//prc.WaitForInputIdle();

プロセス終了の判定ですが、本当はWaitForInputIdleメソッド使いたかったんですが、どうやらwscriptのようなアクティブウィンドウを持たない場合は使えないようです。

仕方ないので、無限ループで回して、プロセスIDが無くなったらループ抜ける仕組みにしました。

これでようやく要件どおりに動くようになりました。

参考:

Excelへデータを出力する方法 - TransferSpreadsheetメソッド：SampleFile126

[VBA]AccessからExcelにデータをインポートする方法 (ADO編) - DQNEO起業日記 別の方法としてExcelのVBAからmdbひらいてコピペする方法もあるようです。

MSサポート:Visual Basic .NET と ADO.NET を使用して Excel ブックのレコードの取得と変更を行う方法 JET OLE DBプロバイダを使って.NetからExcelを操作する方法もあるようです。