Microsoft Excel 操作 Excel ファイルにアクセス.NET Frameworks には COM 相互運用 と呼ばれる機能が有り COM コンポーネントを手軽に呼び出す事が出来る 一方 Excel を初めとする Office 製品は 其の機能をマクロ (VBA) 等からも活用出来る様に COM コンポーネントと仕て実装されて居る 此の為 COM 相互運用を使えば.NET アプリケーションから容易に Excel や Word のファイルを開き 其れを様々に操作する事が可能だ 本稿では其の基本的な記述方法と仕て Excel ファイルをオープンしてワークシート上のセルの内容を参照する例を紹介する 参照の追加 アプリケーションから Excel にアクセスするには 先ず Excel が公開する COM コンポーネントへの参照をプロジェクトに追加する 此れには [ 参照の追加 ] ウィンドウで [COM] タブを選択し Microsoft Excel 12.0 Object Library ( Excel 2007 の場合 Excel 2003 の場合は Microsoft Excel 11.0 Object Library を選択 ) 此れに依り ソリューションエクスプローラの 参照設定 のツリーには Microsoft.Office.Core と Microsoft.Office.Interop.Excel ( Excel 2003 の場合は Excel ) が追加される 此れで アプリケーションから Excel ファイルにアクセス可能に成る Excel がインストールされて居るにも拘らず [ 参照の追加 ] ウィンドウで Excel のコンポーネントが見付からない場合は Office のセットアップで.NET プログラミングのサポート がチェックされて居るかを確認して観ると良い 若しもチェックされて居ない様ならチェックして置く 猶 以降で示して居るコード例は Excel 2007 の COM コンポーネントを使用した場合の物で有る -1-
Excel ファイルのオープン 先ずは Microsoft.Office.Interop.Excel.Application オブジェクトを生成する 此れは Windows のスタートメニューから Excel を起動するのと同じ様な物だ Application オブジェクトを新規作成するには次の様に記述する Dim Ex As Excel.Application ' Excel オブジェクト Ex = New Excel.Application( ) Excel.Application ex; // Excel オブジェクト ex = new Excel.Application( ); 新規作成した変数 oxls を使用して 次は目的のファイルをオープンする Excel でファイルをオープンすると ワークブックを表す Workbook オブジェクトが返され 此れ以降の操作は此のオブジェクトに対して行う 猶 本稿で示して居るコードを使用する場合には ファイルの先頭部分で次の様にして名前空間をインポートして置く必要が有る Imports Microsoft.Office.Interop using Excel = Microsoft.Office.Interop.Excel; Excel ファイルのオープンは Workbooks.Open メソッドを使うのだが 此のメソッドのパラメータが非常に多い 通常の Excel ファイル (.xls) を開く場合は ファイル名丈を指定すれば良いので 省略可能な他のパラメータには Type.Missing を指定して空のパラメータを渡す様にする Workbooks.Open メソッドの詳細に付いては オンラインヘルプ等を参照され度い Dim ExcelName As String = "C: sample.xls" Dim Ex As Excel.Application Dim Wb As Excel.Workbook ' Excel オブジェクト ' Workbook オブジェクト Ex = New Excel.Application( ) Ex.Visible = True ' 確認の為 Excel のウィンドウを表示 ' Excel ファイルのオープン Wb = DirectCast(( Ex.Workbooks.Open( _ ExcelName, _ Type.Missing, _ Type.Missing, _ Type.Missing, _ Type.Missing, _ Type.Missing, _ Type.Missing, _ -2-
Type.Missing, _ Type.Missing, _ Type.Missing, _ Type.Missing, _ Type.Missing, _ Type.Missing, _ Type.Missing, _ Type.Missing )), _ Excel.Workbook ) string excelname = "C: sample.xls"; Excel.Application ex; Excel.Workbook wb; // Excel オブジェクト // workbook オブジェクト ex = new Excel.Application( ); ex.visible = true; // 確認の為 Excel のウィンドウを表示する // Excel ファイルのオープン wb = ( Excel.Workbook )( ex.workbooks.open( excelname, // オープンする Excel ファイル名 Type.Missing, // ( 省略可能 )UpdateLinks (0 / 1 / 2 / 3) Type.Missing, // ( 省略可能 )ReadOnly (True / False ) Type.Missing, // ( 省略可能 )Format // 1: タブ / 2: カンマ (,) / 3: スペース / 4: セミコロン (;) // 5: なし / 6: 引数 Delimiter で指定された文字 Type.Missing, // ( 省略可能 )Password Type.Missing, // ( 省略可能 )WriteResPassword Type.Missing, // ( 省略可能 )IgnoreReadOnlyRecommended Type.Missing, // ( 省略可能 )Origin Type.Missing, // ( 省略可能 )Delimiter Type.Missing, // ( 省略可能 )Editable Type.Missing, // ( 省略可能 )Notify Type.Missing, // ( 省略可能 )Converter Type.Missing, // ( 省略可能 )AddToMru Type.Missing, // ( 省略可能 )Local Type.Missing // ( 省略可能 )CorruptLoad )); ワークシートの選択 Workbooks.Open メソッドの実行に依りワークブックで有る Workbook オブジェクトが取得出来たので 続いて 此れに含まれるワークシートを取得する ワークブックには複数のワークシートを格納出来るので 目的のワークシートをワークシート名に依り探し出す必要が有る 本稿の例では 単純にワークブックに含まれるワークシートを順番に観て行き ワークシート名が一致すれば 其れが目的のワークシートで有ると判断して居る 猶 ワークシートのインデックスは 1 から始まって居るので注意が必要で有る -3-
' 指定されたワークシート名のインデックスを返すメソッド Private Function getsheetindex( ByVal SheetName As String, ByVal Wss As Excel.Sheets ) _ As Integer Dim I As Integer = 0 For Each Ws As Microsoft.Office.Interop.Excel.Worksheet In Wss If SheetName = Sh.Name Then Return I + 1 I += 1 Next Return 0 End Function ' 与えられたワークシート名から Worksheet オブジェクトを得る Dim SheetName As String = "Sheet2" Dim Ws As Excel.Worksheet ' Worksheet オブジェクト Ws = DirectCast( Wb.Sheets( _ getsheetindex( SheetName, Wb.Sheets )), Excel.Worksheet ) // 指定されたワークシート名のインデックスを返すメソッド private int getsheetindex( string sheetname, Excel.Sheets wss ) { int i = 0; foreach ( Excel.Worksheet ws in wss ) { if ( sheetname == ws.name ) { return i + 1; } i += 1; } return 0; } // 与えられたワークシート名から Worksheet オブジェクトを得る string sheetname = "Sheet2"; Excel.Worksheet ws; // Worksheet オブジェクト ws = ( Excel.Worksheet ) wb.sheets[ getsheetindex( sheetname, wb.sheets )]; セルの内容を読み込む 此処迄の操作でワークシートを得る事が出来たので 最後に目的のセルの内容を参照する セルの内容は Worksheet オブジェクトの Cells プロパティで取得出来るが Cells プロパティが返す Range オブジェクトから内容を取得する方が解り易い -4-
Range オブジェクトはワークシート上の指定された範囲のセルを管理するのだが 1 つのセルを Range オブジェクトに割り当てて 其の Range オブジェクトからセルの中身がアクセス出来る Range オブジェクトには セルの内容を取り出す Text プロパティや計算式を示す Formula プロパティ セルの表示形式を表す NumberFormat プロパティ等が提供されて居る セルの指定は Cells[ row, column ] の様に行うが row column は共に 1 始まりで有る 従って A1 のセルは Cells[ 1, 1 ] と成る Dim CellVal As String Dim Rn As Excel.Range ' Range オブジェクト Rn = DirectCast( Ws.Cells( 1, 1 ), Excel.Range ) CellVal = Rn.Text.ToString( ) ' A1 セルの内容 string cellval; Excel.Range rn; // Range オブジェクト rn = ( Excel.Range ) ws.cells[ 1, 1 ]; cellval = rn.text.tostring( ); // A1 セルの内容 Range オブジェクトに含まれるセルの値を取得するには Value プロパティか Text プロパティを用いる Text プロパティは セルの値が文字列化された物を取得出来るが セルの表示形式で指定されて居る形式に依って得られる内容が異なる 此処ではセルの内容が文字列か数値の判断が付かないと仮定して居るで Text プロパティを使って居るが 状況に依っては Value プロパティの方が妥当な場合も有る セルの内容を参照するのではなく セルに値をセットする場合は Value プロパティを使用する必要が有る 総ての処理が終了すれば Workbook オブジェクトをクローズし Excel を終了して置く Wb.Close( Type.Missing, Type.Missing, Type.Missing ) Ex.Quit( ) wb.close( Type.Missing, Type.Missing, Type.Missing ); ex.quit( ); 本稿は Excel ファイルの内容を読み取る方法而巳を紹介したが Application オブジェクトや Workbook オブジェクト等に含まれる他のメソッドを使って 新規にワークシートを作成してファイルに保存したり セルの内容を修正して保存したりする事も出来る ワークシート上のセルにアクセスする処迄を理解すれば Excel ファイルの処理は意外と簡単に行える筈で有る -5-
プログラム例 事前に参照を追加して Excel を操作する事前バインディングのプログラム例を 下記に示す 猶 事前バインディングでは コーディングにインテリセンスを利用したり Excel の定数を使用したり出来る等 開発効率が良いが Excel のバージョンが異なれば 動作しない 異なるバージョンの Excel がインストールされて居る複数のコンピュータで動作させるには 遅延バインディングを用いる Public Class ExcelOperation 6.0 Private Sub btnopen_click( ByVal sender As System.Object, ByVal e As System.EventArgs ) _ Handles btnopen.click ' Excel ファイルのパス Dim P As String = Application.StartupPath If Not P.EndsWith( " " ) Then P &= " " P &= "data.xls" ' Excel のクラスのタイプとインスタンスを取得 Dim Ex As Excel.Application = CreateObject( "Excel.Application" ) ' ワークブックコレクションオブジェクト Dim Wbs As Excel.Workbooks = Ex.Workbooks ' Excel ファイルのオープン Dim Wb As Excel.Workbook = Wbs.Open( P ) ' Excel ファイルの表示 Ex.Visible = True ' 変更の保存を確認しない様に設定 Ex.DisplayAlerts = False ' ワークシートコレクションオブジェクト ( 例外が発生する ) ' Dim Wss As Excel.Worksheets = Wb.Worksheets ' ワークシートオブジェクト ' Dim Ws As Excel.Worksheet = Wss.Item( 1 ) Dim Ws As Excel.Worksheet = Wb.Worksheets.Item( 1 ) ' レンジオブジェクト Dim Rn As Excel.Range ' セル A1 の値の取得 Rn = Ws.Range("A1") Dim A1 As String = Rn.Value.ToString( ) MessageBox.Show( A1, " セル A1 の値 " ) Call DisposeComObject( Rn ) ' 使用の都度に解放した方が無難 -6-
' セル B1 の値の取得 Rn = Ws.Range( "B1" ) Dim B1 As String = Rn.Value.ToString( ) MessageBox.Show( B1, " セル B1 の値 " ) Call DisposeComObject( Rn ) ' 使用の都度に解放した方が無難 ' セル A2 の振り仮名の取得 Rn = Ws.Range( "A2" ) Dim A2 As String = Rn.Phonetics( 1 ).Text.ToString( ) MessageBox.Show( A2, " セル A2 の振り仮名 " ) Ex.Speech.Speak( A2 ) Call DisposeComObject( Rn ) ' 使用の都度に解放した方が無難 ' ハイパーリンクの設定 Rn = Ws.Range( "B2" ) Dim Lns As Excel.Hyperlinks = Ws.Hyperlinks Dim Ln As Excel.Hyperlink = Lns.Add( Rn, B1 ) Call DisposeComObject( Rn ) ' 使用の都度に解放した方が無難 ' ワークブックの保存 If MessageBox.Show( " 保存しますか?", " 確認 ", _ MessageBoxButtons.YesNo, MessageBoxIcon.Question ) = _ System.Windows.Forms.DialogResult.Yes Then Wb.Save( ) MessageBox.Show( " 保存しました ", " 保存 " ) ' 終了 If MessageBox.Show( " エクセルを終了しますか?", " 確認 ", _ MessageBoxButtons.YesNo, MessageBoxIcon.Question ) = _ System.Windows.Forms.DialogResult.Yes Then ' オブジェクトの開放 If Ln IsNot Nothing Then Call DisposeComObject( Ln ) If Lns IsNot Nothing Then Call DisposeComObject( Lns ) If Rn IsNot Nothing Then Call DisposeComObject( Rn ) If Ws IsNot Nothing Then Call DisposeComObject( Ws ) ' If Wss IsNot Nothing Then Call DisposeComObject( Wss ) If Wb IsNot Nothing Then Wb.Close( ) ' ワークブックのクローズ Call DisposeComObject( Wb ) If Wbs IsNot Nothing Then Call DisposeComObject( Wbs ) If Ex IsNot Nothing Then Ex.Quit( ) ' エクセルの終了 Call DisposeComObject( Ex ) -7-
' COM オブジェクトを解放するジェネラルプロシージャ Private Sub DisposeComObject( Of T As Class )( ByRef Obj As T ) If Obj IsNot Nothing Then Try If System.Runtime.InteropServices.Marshal.IsComObject( Obj ) Then System.Runtime.InteropServices.Marshal.FinalReleaseComObject( Obj ) Finally Obj = Nothing End Try End Class using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; namespace ExcelOperation { public partial class ExcelOperation : Form { public ExcelOperation( ) { InitializeComponent( ); } private void btnopen_click( object sender, EventArgs e ) { // Excel ファイルのパス string p = Application.StartupPath; if (!p.endswith( @" " )) p += @" "; p += "data.xls"; // Excel クラスのインスタンス生成 Excel.Application ex = new Excel.Application( ); // ワークブックコレクションオブジェクト Excel.Workbooks wbs = ex.workbooks; // Excel ファイルのオープン Excel.Workbook wb = wbs.open( p, -8-
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // Excel ファイルの表示 ex.visible = true; // 変更の保存を確認しない様に設定 ex.displayalerts = false; // ワークシートコレクションオブジェクト ( 例外が発生する ) // Excel.Worksheets wss = ( Excel.Worksheets ) wb.sheets; // ワークシートオブジェクト // Excel.Worksheet ws = ( Excel.Worksheet ) wss.get_item( 1 ); Excel.Worksheet ws = ( Excel.Worksheet ) wb.worksheets[ 1 ]; // レンジオブジェクト Excel.Range rn; // セル A1 の値の取得 rn = ( Excel.Range ) ws.cells[ 1, 1 ]; string a1 = rn.text.tostring( ); MessageBox.Show( a1, " セル A1 の値 " ); DisposeComObject( rn ); // 使用の都度に解放した方が無難 // セル B1 の値の取得 rn = ( Excel.Range ) ws.cells[ 1, 2 ]; string b1 = rn.text.tostring( ); MessageBox.Show( b1, " セル B1 の値 " ); DisposeComObject( rn ); // 使用の都度に解放した方が無難 ' セル A2 の振り仮名の取得 rn = ( Excel.Range ) ws.cells[ 2, 1 ]; string a2 = rn.phonetics( 1 ).Text.ToString( ); MessageBox.Show( a2, " セル A2 の振り仮名 " ); Ex.Speech.Speak( a2 ); DisposeComObject( rn ); // 使用の都度に解放した方が無難 // ハイパーリンクの設定 rn = ( Excel.Range ) ws.cells[ 2, 2 ]; Excel.Hyperlinks lns = ws.hyperlinks; Excel.Hyperlink ln = ( Excel.Hyperlink ) lns.add( rn, b1, "", "", "" ); DisposeComObject( rn ); // 使用の都度に解放した方が無難 -9-
// ワークブックの保存 if ( MessageBox.Show( " 保存しますか?", " 確認 ", MessageBoxButtons.YesNo, MessageBoxIcon.Question ) == System.Windows.Forms.DialogResult.Yes ) { wb.save( ); MessageBox.Show( " 保存しました ", " 保存 " ); } } // 終了 if ( MessageBox.Show( " エクセルを終了しますか?", " 確認 ", MessageBoxButtons.YesNo, MessageBoxIcon.Question ) == System.Windows.Forms.DialogResult.Yes ) { // オブジェクトの開放 if ( ln!= null ) ln = null; if ( lns!= null ) lns = null; if ( rn!= null ) rn = null; if ( ws!= null ) ws = null; // if ( wss!= null ) wss = null; if ( wb!= null ) { wb.close( null, null, null ); // ワークブックのクローズ wb = null; } if ( wbs!= null ) wbs = null; if ( ex!= null ) { ex.quit( ); // エクセルの終了 ex = null; } } } } // COM オブジェクトを解放するメソッド private void DisposeComObject( Of T As Class )( ByRef obj As T ) { if ( obj!= null ) { try { if ( System.Runtime.InteropServices.Marshal.IsComObject( Obj )) System.Runtime.InteropServices.Marshal.FinalReleaseComObject( Obj ); } Finally { obj = null } } } -10-
注意事項 Excel のプロセスが正常に終了しない理由.NET 以降では 6.0 の様に Range オブジェクトを使用すると Excel のプロセスが終了しないと謂う事は認知されて居るが 実際に使用して観るとプロセスが終了しないので 困惑する事も多いと思う 其処で.NET 以降から Excel を操作する上での注意事項を纏めて観た 1. 先ず 起動 終了丈の基本的なプログラムを書いた時点でテストして プロセスが終了しているかを確認する 2. コードは マクロ等を其の儘ペーストせず キーボードから入力する ( 然うする事に依り自動メンバー表示やパラメータヒントが表示される ) 3. 自動メンバー表示等が表示されない場合は コードの使い方が間違って居る場合が有るので ヘルプ等で確認する 4. 今迄と違った使い方や使った事の無いプロパティ等を使用した場合は 其の時点でプロセスが終了するか確認する 5. コードの区切りの時点で 其処迄の動作でプロセスが正常に終了する事を確認し乍ら進める 以上の 5 項目を守れば 少なく共 他人に間違い探しを依頼する事は無い筈で有る 作成してからプロセスが終了して居ない事に気が付いた場合は 確認出来て居る部分を除いてコメント化し 確認し乍ら順次コメントを外して原因箇所を見付ける様にすれば良い Excel のマクロからコードを使用した場合 6.0 と同様に から Excel のプロパティやメソッド オブジェクト等を操作する場合は 必ずオブジェクトを指定する 例 : 誤り ActiveSheet 正解 xlbook.activesheet VBA のヘルプで ActiveSheet プロパティの [ 対象 ] の項を見ると Application オブジェクト Window オブジェクト Workbook オブジェクトの 3 種のオブジェクトで使用可能だが 此の場合は Sheet1 や Sheet2 と謂うワークシートを対象に仕て居るので Workbook オブジェクトを指定する様にする 例えば xlapp xlbook xlsheet が xlapp xlsheet では 何の xlbook の ActiveSheet かが不明で有る 但し 使用するシート名が判って居る場合は xlbook.worksheets("sheet1") や xlsheet の様にシート名を直接指定する様にする 上記の他 VBA のコードではオブジェクト名が省略されて居る場合が多々有るが からは VBA のコードと のコードを区別する上で 省略して書くとエラーが発生したり エラーが発生しなくても Excel 内部で問題に成り プロセスが終了しない原因に成る 従って コードをコピー & ペーストする様な事をせず キーボードから自動メンバ表示等のインテリセンス機能を利用して確認し乍 コードを記入する様にする 猶 VBA のヘルプは Excel 上の Editor からでも観る事が出来る -11-
各オブジェクトを変数と仕て宣言する時の 6.0 との違い Dim Ex As Excel.Application Dim Wb As Excel.Workbook Dim Ws As Excel.Worksheet 6.0 Set Ex = CreateObject( "Excel.Application" ) Set Wb = xlapp.workbooks.add Set Ws = xlbook.worksheets( 1 ).NET 以降 Dim Ex As New Excel.Application Dim Wbs As Excel.Workbooks = Ex.Workbooks Dim Wb As Excel.Workbook = Wbs.Add Dim Wss As Excel.Sheets = Wb.Worksheets Dim Ws As Excel.Worksheet = Wss.Item( 1 ) 上記で WorkBook や WorkSheet を使用する場合は 必ず 一旦 Workbooks 等のコレクションから受ける方法を推奨する オートメーションオブジェクトに対して Marshal.ReleaseComObject メソッドを実行し オブジェクトインスタンスの解放を行う必要が有る為 此の様な方法でないと解放出来ない 此の事は 次の Range オブジェクト等でも同じ理由で有る 下記は Microsoft サポート技術情報よりの抜粋で有る Visual Studio.NET でマネージコードから COM オブジェクトを呼び出すと 自動的に RCW( ランタイム呼出可能ラッパー ) が作成される RCW は.NET アプリケーションと COM オブジェクト間の呼出をマーシャリング ( 整列 ) する RCW は COM オブジェクトへの参照カウントを保持する 従って RCW での総ての参照が解放されて居ない場合 COM オブジェクトは終了しない COM オブジェクトの解放忘れ 1. 基本的に 下記の様に ドット (. ) が 2 個連続で使用されゝば解放出来ない 誤 正 xlrange = xlcells( 1,1 ) xlrange.interior.color = RGB( 0, 255, 0 ) ' 使用変数の解放処理 MRComObject( xlcells ) MRComObject( xlrange ) xlrange = xlcells( 1, 1 ) Dim xlinterior As Excel.Interior xlinterior = xlrange.interior xlinterior.color = RGB( 0, 255, 0 ) MRComObject( xlinterior ) MRComObject( xlcells ) MRComObject( xlrange ) 此の使用法が問題と成る! -12-
必ず 上記の様に分解して 変数に受けて 解放処理を行う 下記の様な場合も当然解放されない xlrange = xlcells( 1, 1 ) xlrange.font.bold = True 2. 下記の様なオブジェクトを返すプロパティ等は Marshal.ReleaseComObject メソッドを実行し オブジェクトインスタンスの解放を行う必要が有る Range オブジェクト Selection プロパティ Location メソッド等 Cells プロパティ Rows プロパティ Columns プロパティ Borders プロパティ等の様に最後に s が付くプロパティ ( コレクション ) 等 猶 上記に記載した以外にも色々有るので VBA のヘルプで戻り値や対象に付いて調べて プロセスが終了するか何うかテストしてから使用する様にする 3. ループ処理等で複数回使用した場合 下記の様に 其の都度 解放処理をする For I = 1 To 255 xlrange = xlsheet.range( R1ToA1( 1, I, 1, I )) xlrange.value = I MRComObject( xlrange ) Next 4. 遅延バインディングで Object 型の変数を使用した場合 遅延バインディングで 下記の様に Object 型の変数を使用すると 参照カウントが通常より多くカウントされ 通常の解放処理では解放出来ない ( アーリーバインディングでは解放される ) Dim xlrange As Object Dim xlhyperlinks As Object Dim xlhyperlink As Object xlrange = xlsheet.range( "A1" ) xlrange.value = "http://www.squid.ne.jp" xlhyperlinks = xlsheet.hyperlinks xlhyperlink = xlhyperlinks.add( xlrange, xlrange.value ) System.Runtime.InteropServices.Marshal.ReleaseComObject( xlrange ) System.Runtime.InteropServices.Marshal.ReleaseComObject( xlhyperlink ) System.Runtime.InteropServices.Marshal.ReleaseComObject( xlhyperlinks ) 上記の場合 xlrange のカウントが 2 に成る様で有る 解放出来ない場合は 下記の様に仕て調べ 0 以上の数値が返って来れば カウントがアップして居るので 其の分 解放処理が必要で有る Debug.WriteLine(System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange)) System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange) System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange) -13-
上記の様に 2 度続けて解放処理をしても 一応解放出来るが 下記の様にする事を推奨する Public Shared Sub MRComObject ( Of T As Class )( ByRef objcom As T, _ Optional ByVal force As Boolean = False ) If objcom Is Nothing Then Return Try If System.Runtime.InteropServices.Marshal.IsComObject( objcom ) Then If force Then Dim count As Integer = _ System.Runtime.InteropServices.Marshal.FinalReleaseComObject( objcom ) ' 此の部分は 動作確認用コードで有る ( 実使用では必要無い ) If Not count = 0 Then MessageBox.Show( "COM オブジェクトが解放されて居ません!" ) Else Dim count As Integer = _ System.Runtime.InteropServices.Marshal.ReleaseComObject( objcom ) ' 此の部分は 動作確認用コードで有る ( 実使用では必要無い ) If Not count = 0 Then MessageBox.Show( "COM オブジェクトが解放されて居ません!" ) Finally objcom = Nothing End Try 5. 2005 と.NET 2003 に依る違い 2005/Excel 2002 では 下記でもプロセスは解放されるが.NET 2003/Excel 2002 では 下記ではプロセスが解放されない xlapp.activewindow.displayhorizontalscrollbar = False xlapp.activewindow.displayverticalscrollbar = False 孰れも 下記の様に 変数に受けて解放処理を仕た方が良い Dim xlwindow As Excel.Window xlwindow = xlapp.activewindow xlwindow.displayhorizontalscrollbar = False xlwindow.displayverticalscrollbar = False MRComObject( xlwindow ) 上記の様に バージョンに依る違いも有る 一度目のテストでは 解放されて居ても 処理を繰り返すと解放されない場合も有るので 充分テスト (2 回以上連続使用 ) を行う必要が有る -14-
補足.NET 以降から Excel を扱う場合は 6.0 とは異なり 総ての COM オブジェクトを System.Runtime.InteropServices.Marshal.ReleaseComObject メソッドを使用して解放する必要が有るが 此の事は MS のサンプル等でも明記されて居らず 其処に記述されて居る ガベージコレクションを実行する為 GC.Collect メソッドを使用してガベージコレクタを強制的に実行し RCW が保持して居る参照を解放する と謂う説明に勘違いして GC.Collect メソッドを実行して居るコードを観掛ける事が有る 併し プロセスが残存しない様にするには COM オブジェクトを夫々れ変数に受け 使用後 ReleaseComObject メソッドを実行する必要が有る 猶 6.0 の時の様に ExcelApplication = Nothing は 必ずしも必要が無く 実行しなくてもプロセスが終了しないと謂う事は無い 寧ろ ReleaseComObject の方が重要で 此方は必ず実行しなけば プロセスが正しく終了しない 亦 上記 MS のサンプルでは Private Sub NAR( ByVal o As Object ) の様に値渡しに成って居るが 参照渡しに変更して居る 此れは ExcelApplication = Nothing が特に必要が無い為 孰れの方法でも実用上問題が発生して居ないが 使用目的に鑑み 参照渡しに仕た方が良いと思われる 基本的に Excel の操作方法は.NET 以降も 6.0 も同じで有る 只 指定方法が若干上記の様に異なる事を注意すれば 6.0 から Excel を操作するプログラムを.NET 以降用に一部変更すれば使用可能で有る FinalReleaseComObject は.NET Framework version 2.0 で新しく追加された物で 一度の呼出で 参照カウントを 0 に設定する 下記の様に 0 を返す迄 ループ内で ReleaseComObject を呼び出す事と同じで有る Dim I As Integer Do I = System.Runtime.InteropServices.Marshal.ReleaseComObject( Obj ) Loop While I > 0-15-
各種 Excel 操作 各種 Excel 操作を 下記に示す 此等は 前述のプログラム例 (ExcelOperation) の実際の処理 ( セル A1 の値の取得 ~ ハイパーリンクの設定 ) の部分に記述する セルにデータの設定 ( 一括 ) ' セル A5:A7 へデータの入力 ( 一括 ) Rn = Ws.Range( "A5:A7" ) Dim D( 2, 0 ) As Object D( 0, 0 ) = "10" D( 1, 0 ) = "20" D( 2, 0 ) = "=Sum( A5:A6 )" Rn.Value = D Call DisposeComObject( Rn ) セルにデータの設定 ( 個別 ) ' セル C5:C7 へデータの入力 ( 個別 ) Dim Cl As Excel.Range = Ws.Cells Rn = DirectCast( Cl( 5, 3 ), Excel.Range ) Rn.Value = "12" Call DisposeComObject( Rn ) Rn = DirectCast( Cl( 6, 3 ), Excel.Range ) Rn.Value = "34" Call DisposeComObject( Rn ) Rn = DirectCast( Cl( 7, 3 ), Excel.Range ) Rn.Value = "=C5+C6" Call DisposeComObject( Rn ) Call DisposeComObject( Cl ) Microsoft Excel を起動 ( 既存のファイルを開く ) -16-
Excel のデータ入力例色々 1.R1C1 形式で個々のセルへの入力例 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click 'R1C1 形式で個々のセルへの入力例 xlapp.visible = True Dim i, j As Integer For i = 5 To 12 '5 行目 ~12 行目 For j = 3 To 8 'C 列 ~ H 列 Call SetdataCell(i, j, i + j) Next j Next i 'C13 に合計を表示 Call SetdataCell(13, 3, "=SUM(C5:C12") Private Sub SetdataCell(ByVal r As Integer, ByVal c As Integer, ByVal d As Object) ' 個々のセルへ値を入力するためのプロシージャ Dim xlcells1 As Excel.Range Dim xlrange1 As Excel.Range xlcells1 = xlsheet.cells xlrange1 = xlcells1(r, c) xlrange1.value = d MRComObject(xlCells1) MRComObject(xlRange1) 2.R1C1 形式で範囲を指定してセルへの入力例 Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click -17-
'R1C1 形式で範囲を指定してセルへの入力例 xlapp.visible = True Dim xlrange As Excel.Range Dim strdat(2, 0) As Object strdat(0, 0) = "10" ' データの作成 strdat(1, 0) = "20" ' セル (B15,B16) strdat(2, 0) = "=SUM(" & R1ToA1(15, 2, 16, 2) & ")" ' 計算式 ' セル (B15,B17) xlrange = xlsheet.range(r1toa1(15, 2, 17, 2)) ' データの入力セル範囲 xlrange.value = strdat ' セルへデータの入力 MRComObject(xlRange) Private Function R1ToA1(ByVal r1 As Integer, ByVal c1 As Integer, _ ByVal r2 As Integer, ByVal c2 As Integer) As String 'A1 形式のアドレスを R1C1 形式に変換する関数 Dim i1, i2 As Integer Dim d1, d2 As String i1 = Math.Floor((c1-1) 26) d1 = IIf(c1 > 26, Strings.Chr(64 + i1), "") i1 = c1-26 * i1 d1 &= Strings.Chr(64 + i1) & CStr(r1) i2 = Math.Floor((c2-1) 26) d2 = IIf(c2 > 26, Strings.Chr(64 + i2), "") i2 = c2-26 * i2 d2 &= Strings.Chr(64 + i2) & CStr(r2) Return d1 & ":" & d2 End Function 注意上記関数は Excel 2007 で 703 列以上を指定すると正しい値を返さないようです Excel 2007 以前は 列数が 255 列までに制限されているので問題ありません 3.R1ToA1 関数を使って個別のセルに入力する場合 Private Sub Button3_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button3.Click 'R1ToA1 関数を使って個別のセルに入力する場合 Dim i As Integer Dim xlrange As Excel.Range -18-
For i = 1 To 255 'A1 ~ IU1 の範囲に連番を入力 xlrange = xlsheet.range(r1toa1(1, i, 1, i)) xlrange.value = i MRComObject(xlRange) Next i xlapp.visible = True Private Function R1ToA1(ByVal r1 As Integer, ByVal c1 As Integer, _ ByVal r2 As Integer, ByVal c2 As Integer) As String 'A1 形式のアドレスを R1C1 形式に変換する関数 Dim i1, i2 As Integer Dim d1, d2 As String i1 = Math.Floor((c1-1) 26) d1 = IIf(c1 > 26, Strings.Chr(64 + i1), "") i1 = c1-26 * i1 d1 &= Strings.Chr(64 + i1) & CStr(r1) i2 = Math.Floor((c2-1) 26) d2 = IIf(c2 > 26, Strings.Chr(64 + i2), "") i2 = c2-26 * i2 d2 &= Strings.Chr(64 + i2) & CStr(r2) Return d1 & ":" & d2 End Function 4.Address プロパティを使っての入力例 Private Sub Button4_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button4.Click 'Address プロパティを使って個別入力 Dim i As Integer Dim xlrange As Excel.Range Dim xlcells As Excel.Range Dim xlrange1 As Excel.Range xlcells = xlsheet.cells Dim Col As String For i = 1 To 255 'A2 ~ IU2 の範囲に連番を入力 xlrange1 = xlcells(2, i) Col = xlrange1.address(false, False) -19-
xlrange = xlsheet.range(col) xlrange.value = i MRComObject(xlRange1) MRComObject(xlRange) Next i MRComObject(xlCells) xlapp.visible = True 5.Address プロパティを使って範囲入力 Private Sub Button5_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button5.Click 'Address プロパティを使って範囲入力 xlapp.visible = True Dim xlrange As Excel.Range Dim strdat(2, 0) As Object strdat(0, 0) = "10" strdat(1, 0) = "20" strdat(2, 0) = "=SUM(A4:A5)" Dim Col1, Col2 As String Dim xlcells As Excel.Range Dim xlrange1 As Excel.Range xlcells = xlsheet.cells xlrange1 = xlcells(4, 1) 'A4 Col1 = xlrange1.address(false, False) MRComObject(xlRange1) xlrange1 = xlcells(6, 1) 'A6 Col2 = xlrange1.address(false, False) 'A4 ~ A6 の範囲に入力 xlrange = xlsheet.range(col1 & ":" & Col2) xlrange.value = strdat MRComObject(xlCells) MRComObject(xlRange1) MRComObject(xlRange) 6.Rows / Columns プロパティの使用方法 -20-
Private Sub Button6_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button6.Click ' xlsheet.rows(6).select() ' これではプロセスが終了しない '----------------------------------------------------------------- Dim xlrange As Excel.Range Dim xlrows As Excel.Range xlrows = xlsheet.rows xlrange = xlrows(6) xlrange.select() MRComObject(xlRows) MRComObject(xlRange) '----------------------------------------------------------------- ' xlsheet.columns(6).select() ' これではプロセスが終了しない '----------------------------------------------------------------- Dim xlrange1 As Excel.Range Dim xlcolumns As Excel.Range xlcolumns = xlsheet.columns xlrange1 = xlcolumns(6) xlrange1.select() MRComObject(xlColumns) MRComObject(xlRange1) '------------------------------------------------------------------------------ 'xlsheet.rows(1). ' この時点で候補がでてこない 'xlsheet.rows.select() ' これなら候補も表示されるがプロセスは終了しない '-------------------------------------------------------------------------------- Dim xlrows2 As Excel.Range xlrows2 = xlsheet.rows xlrows2.select() MRComObject(xlRows2) 7.Excel を Private な変数で宣言しての起動処理 '---------- Private な変数の宣言 ----------------------------------- -21-
Private xlapp As Excel.Application Private xlbooks As Excel.Workbooks Private xlbook As Excel.Workbook Private xlsheets As Excel.Sheets Private xlsheet As Excel.Worksheet '---------- Excel ファイルの Open 処理 ------------------------------ Private Sub Button7_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button7.Click 'Excel の既存ファイルのオープン Call ExcelOpen(IO.Path.GetFullPath(".... Test.xls"), "Sheet1") Private Sub ExcelOpen(ByVal FilePath As String, ByVal SheetName As String) ' 既存のファイルのオープン処理用プロシージャ xlapp = New Excel.Application xlbooks = xlapp.workbooks xlbook = xlbooks.open(filepath) xlsheets = xlbook.worksheets xlsheet = xlsheets(sheetname) xlapp.visible = True 8.Excel ファイルを上書き保存して終了処理 '---------- Excel ファイルの終了時の処理 ------------------------------ Private Sub Button8_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button8.Click 'Excel ファイルを上書き保存 (True 又省略すれば ) して終了処理を実行 Call ExcelClose(IO.Path.GetFullPath(".... Test.xls"), False) 'False の場合保存しないで終了 Private Sub ExcelClose(ByVal FilePath As String, Optional ByVal CancelSave As Boolean = True) 'Excel ファイルを上書き保存して終了処理用プロシージャ xlapp.displayalerts = False ' 保存時の問合せのダイアログを非表示に設定 If CancelSave Then xlsheet.saveas(filepath) ' ファイルに保存 MRComObject(xlSheet) 'xlsheet の解放 MRComObject(xlSheets) 'xlsheets の解放 xlbook.close() 'xlbook を閉じる -22-
MRComObject(xlBook) 'xlbook の解放 MRComObject(xlBooks) 'xlbooks の解放 xlapp.quit() 'Excel を閉じる MRComObject(xlApp) 'xlapp を解放 ' 今まで使っていた方法では Option Strict On の時にエラーとなったので 下記の ' 魔界の仮面弁士さんの投稿を使用させて頂きました 詳しくは 下記リンク参照願います ' デクリメントするだけが目的なら その場で ReleaseComObject だけを実施して下さい 'http://hanatyan.sakura.ne.jp/vbnetbbs/wforum.cgi?mode=allread&no=6370#6374 Public Shared Sub MRComObject(Of T As Class)(ByRef objcom As T, Optional ByVal force As Boolean = False) If objcom Is Nothing Then Return Try If System.Runtime.InteropServices.Marshal.IsComObject(objCom) Then If force Then Dim count As Integer = System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objCom) If count <> 0 Then ' この部分は 動作確認用コードです ( 実使用では必要ありません ) MessageBox.Show("COM オブジェクトが解放されていません ") Else Dim count As Integer = System.Runtime.InteropServices.Marshal.ReleaseComObject(objCom) If count <> 0 Then ' この部分は 動作確認用コードです ( 実使用では必要ありません ) MessageBox.Show("COM オブジェクトが解放されていません ") '0 になる事を期待 ( 使い終った ) していたなら force = True で試して見て下さい Finally objcom = Nothing End Try Private Sub Form1_Closed(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles MyBase.Closed 'Excel が終了していない場合は Excel を終了 If Not xlapp Is Nothing Then Call ExcelClose(IO.Path.GetFullPath(".... Test.xls"), False) -23-
Excel にデータを送りグラフを表示 '======= Excel の起動処理 終了処理の部分を省略します ==================== ' プロジェクト 参照の追加 COM Microsoft Excel *.* ObjectLibrary を参照して下さい ' 基本的な操作部分は [.NET から Excel の基本的な操作方法 ] と同じです '============== グラフデータを作成及びセルに値を代入 =================== 'Excel のセルに値を代入します Dim i, j As Integer Dim kamokurange, simeirange, datarange As Excel.Range Dim datadat(4, 4) As Integer, simeidat(0, 4), kamokudat(4, 0) As String kamokurange = xlsheet.range("a2:a6") simeirange = xlsheet.range("b1:f1") datarange = xlsheet.range("b2:f6") For i = 0 To 4 For j = 0 To 4 '30~100 の範囲のランダムなデータを作成 datadat(j, i) = CInt(70 * Rnd() + 31) Next j Next i ' 系列名の設定 kamokudat(0, 0) = " 国語 " kamokudat(1, 0) = " 数学 " kamokudat(2, 0) = " 英語 " kamokudat(3, 0) = " 社会 " kamokudat(4, 0) = " 体育 " '' 項目名の設定 simeidat(0, 0) = " 石原 " simeidat(0, 1) = " 小泉 " simeidat(0, 2) = " 田中 " simeidat(0, 3) = " 平沼 " simeidat(0, 4) = " 森山 " ' セルに各データを設定 datarange.value = datadat kamokurange.value = kamokudat simeirange.value = simeidat MRComObject(dataRange) 'datarange の解放 MRComObject(kamokuRange) 'kamokurange の解放 MRComObject(simeiRange) 'datarange の解放 -24-
'=============== グラフの表示設定 ======================= Dim MyCharts As Excel.ChartObjects Dim MyChart As Excel.ChartObject Dim MyChart1 As Excel.Chart ' 表示位置 グラフの大きさを指定して新しい埋め込みグラフを作成 MyCharts = xlsheet.chartobjects MyChart = MyCharts.Add(10, 90, 550, 300) Dim xlrange As Excel.Range xlrange = xlsheet.range("a1:f6") ' データの入力セル範囲 MyChart1 = MyChart.Chart With MyChart1 ' 系列を列に変更行は xlrows.setsourcedata(xlrange, Excel.XlRowCol.xlColumns) MRComObject(xlRange) 'xlrange の解放 '' 縦棒グラフを指定.ChartType = Excel.XlChartType.xlColumnClustered '' グラフのタイトルを表示.HasTitle = True Dim xlcharttitle As Excel.ChartTitle xlcharttitle =.ChartTitle xlcharttitle.text = " 中間テスト結果 " MRComObject(xlChartTitle) 'xlcharttitle の解放 '' 目盛りの設定 Dim xlaxes As Excel.Axes Dim xlaxis As Excel.Axis xlaxes = MyChart1.Axes xlaxis = xlaxes.item(excel.xlaxistype.xlvalue) '-------------------------------------------------------------- ' データラベルの表示 ( 全て表示の場合 ).ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue) ' 個別に表示する場合 Dim xlsc As Excel.Series xlsc =.SeriesCollection(1) xlsc.applydatalabels(excel.xldatalabelstype.xldatalabelsshowvalue) MRComObject(xlSC) ' その他詳細設定は Excel 上でマクロを取ってそのマクロを参考に上記のように ' コードを書き直して試して見て下さい '-------------------------------------------------------------- -25-
With xlaxis.majorunit = 20.MaximumScale = 120 End With MRComObject(xlAxis) MRComObject(xlAxes) ' 目盛り間隔 ' 目盛りの最大値 'xlaxis の解放 'xlaxes の解放 ' 作業中のシートにグラフを表示する場合 'Dim location1 As Excel.Chart 'location1 =.Location(Excel.XlChartLocation.xlLocationAsObject, xlsheet.name) 'MRComObject(location1) '' グラフを縮小表示 Dim xlshapes As Excel.Shapes Dim xlshape As Excel.Shape xlshapes = xlsheet.shapes xlshape = xlshapes.item(1) ' 縦横を 70% に縮小表示 xlshape.scalewidth(0.7, Office.MsoTriState.msoFalse, _ Office.MsoScaleFrom.msoScaleFromTopLeft) xlshape.scaleheight(0.7, Office.MsoTriState.msoFalse, _ Office.MsoScaleFrom.msoScaleFromTopLeft) MRComObject(xlShape) 'xlshape の解放 MRComObject(xlShapes) 'xlshapes の解放 End With ' おまけ上記がうまく表示されたらコメントを外して試して下さい ' 3D 表示でグラフが回転しなが表示します 'MyChart.Chart.ChartType = Excel.XlChartType.xl3DColumn 'For i = 0 To 360 Step 10 ' MyChart.Chart.Rotation = i ' System.Threading.Thread.Sleep(1000) 'Next i MRComObject(MyChart1) MRComObject(MyChart) MRComObject(MyCharts) 'MyChart1 の解放 'MyChart の解放 'MyCharts の解放 '=========================== ここまで ======================================= '====== 以下は [.NET から既存の Excel ファイルを開く ] と同じです ======== Excel の定数の調べ方 VB6.0 では ( 定数 ).SetSourceData xlsheet.range("a1:f6"), xlcolumns -26-
VB.NET では (Excel. クラス名. 定数 ).SetSourceData(xlSheet.Range("A1:F6"), Excel.XlRowCol.xlColumns) 上記定数を調べる場合 VBAXL9.CHM 等のヘルプで [SetSourceData] をキーワードに検索すると SetSourceData メソッドがヒットします その解説文に [ 使用できる定数は XlRowCol クラスの xlcolumns または xlrows です ] と記載されています Excel. と入力するとインテリセンス機能により XlRowCol クラスを選択し 使用する定数を選んで下さい ヘルプファイルは バージョンにより VbaXl8.hlp Vbaxl9.chm Vbaxl10.chm VBAXL10.chm 等があります 標準のインストールでは インストールされませんので 入っていない方は CD より追加でインストールして下さい インストール方法は VBA のヘルプを参照するを見て下さい 又 定数名が判っていれば オブジェクトブラウザから定数名を検索すれば簡単に探せます Vbaxl9.chm でしたら 単独で開いても 検索機能が付いておりますので便利です Vbaxl10.chm は単独で開いた場合 キーワード等の検索はできません 又は Private Const xlcolumns As Short = 2 のように定数を宣言して使用する方法もあります [XL95] 組込み定数一覧 (1/3) http://support.microsoft.com/default.aspx?scid=kb;ja;407881 [XL95] 組込み定数一覧 (2/3) http://support.microsoft.com/default.aspx?scid=kb;ja;407882 [XL95] 組込み定数一覧 (3/3) http://support.microsoft.com/default.aspx?scid=kb;ja;407883 Excel のグラフをクリップボード経由で PictureBox に貼付 Excel にデータを送りグラフを表示するに下記の部分を追加して下さい '-------------------------------------------------------------------------- ' このグラフをファイルに保存する場合.Export(FileName:="c: test001.gif", FilterName:="GIF") '--------------------------------------------------------------------------- MRComObject(xlShape) MRComObject(xlShapes) 'xlshape の解放 'xlshapes の解放 -27-
End With -------------------------------------------------------------------------------- '=========================== ここからが追加分 ============================= '------ Excel のグラフをクリップボード経由で PictureBox に貼付 ------------------ ' クリップボードにコピー 'xlsheet.chartobjects(" グラフ 1").Copy() ' これでは.NET では取得できない MyChart1.CopyPicture(Appearance:=Excel.XlPictureAppearance.xlScreen, _ Size:=Excel.XlPictureAppearance.xlScreen, _ Format:=Excel.XlCopyPictureFormat.xlBitmap) '' 現在システムクリップボードにあるデータを取得します Dim idata As IDataObject = Clipboard.GetDataObject() ' クリップボードに Bitmap ファイルがあれば If idata.getdatapresent(system.windows.forms.dataformats.bitmap) Then 'PictureBox1 にクリップボードの画像を貼り付け PictureBox1.Image = CType(iData.GetData(DataFormats.Bitmap), Image) '=========================== ここまで ======================================= -------------------------------------------------------------------------------- MRComObject(MyChart1) MRComObject(MyChart) MRComObject(MyCharts) 'MyChart1 の解放 'MyChart の解放 'MyChart1s の解放 '====== 以下は [.NET から既存の Excel ファイルを開く ] と同じです ======== 通常 Excel のグラフをコピーすると CF_METAFILEPICT : Windows メタファイル CF_ENHMETAFILE : 拡張メタファイルがクリップボードにコピーされ それらのファイルのフォーマット形式は.NET では取得する事ができず 従ってクリップボード経由で PictureBox に貼付できない為に 掲示板等への書き込みが散見されます そこで 色々調べていて解ったのですが Excel のグラフを BMP 形式でコピーできることを知り その動作をマクロにとって.NET 用のコードにしたものです Excel 上では グラフを選択して [Shift] キーを押しながら メニューバーの [ 編集 ] のメニューの [ 図のコピー ] をクリックすると下記のようなダイアログがでてきます -28-
又 Excel 上でグラフをファイルに保存する事も可能です 別途 クリップボード関係の API を使用すればメタファイル形式で取得することもできます ADO を使って Excel のシート名を高速に取得する及び従来の方法 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click ' プロジェクト 参照の追加 COM Microsoft ActiveX Data Objects *.* Library を参照して下さい ''ADO を使って Excel のシート名を高速に取得 Dim CN As New ADODB.ConnectionClass, RS As New ADODB.RecordsetClass Dim ncount As Long, xlfilename As String, SheetNeme As String ListBox1.Items.Clear() xlfilename = System.IO.Path.GetFullPath(".... Sample.xls") ' 下記の Excel 8.0 は Excel のバージョンによって 特に書換える必要はありません CN.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & xlfilename & ";Extended Properties=Excel 8.0;") RS = CN.OpenSchema(ADODB.SchemaEnum.adSchemaTables) Do Until RS.EOF SheetNeme = RS.Collect("TABLE_NAME").ToString() ' 名前ボックス等に表示される範囲名等を除く ' ワークシート名には自動的にシート名の後ろに $ が付くので If SheetNeme.EndsWith("$") Or SheetNeme.EndsWith("'") Then ncount = ncount + 1 ListBox1.Items.Add(SheetNeme) RS.MoveNext() Loop RS.Close() System.Runtime.InteropServices.Marshal.ReleaseComObject(RS) RS = Nothing CN.Close() System.Runtime.InteropServices.Marshal.ReleaseComObject(CN) CN = Nothing -29-
上記は VB6.0 で使っていたコードを.NET でも使えるのかと試してみたものです 本格的に ADO を使用される場合は ADO.NET の方をお使い下さい 因みに ADO.NET を使ったサンプルを作って比べて見ましたが 使っている分にはどちらで取得しているのか区別がつきません ( 処理速度も 機能等も ) Excel ワンポイントテクニック集 1.Excel のシート名を取得する ''VB6.0 の時は下記でも問題がなかったが 'Dim i As Long 'For i = 1 To xlbook.worksheets.count 'Debug.WriteLine(xlBook.Worksheets(i).Name) 'Next i ''VB6.0 の時も本来は Worksheets はコレクションなので下記のように書くべきだった 'Dim Sheet As Excel.Worksheet 'For Each Sheet In xlbook.worksheets ' Debug.WriteLine(Sheet.Name) 'Next ' そうすれば.NET 用にはどのように書けばよいかが解ったはず Dim sht As Excel.Worksheet For Each sht In xlsheets Debug.WriteLine(sht.Name) ' 当然代入したシート 1 個づつについて ReleaseComObject が必要です MRComObject(sht) Next -------------------------------------------------------------------------------- Sheet1 とかに読み書きするときは ちゃんと変数に受けて 使った変数を ReleaseComObject している人が 上記のようにシート名を取得する時には そのような使い方をせず プロセスが終了しない事に悩んでいるケースがあるようです 他人のコード ( マクロ VB6.0 のコード ) を丸写しするのではなく キーボードから 1 文字づつ入力するようにして少しでも理解を深めて下さい 2. 罫線の描画 -30-
Dim xlrange As Excel.Range Dim xlborders As Excel.Borders Dim xlborder As Excel.Border xlrange = xlsheet.range("b2:f6") xlborders = xlrange.borders ' 枠線を実線で表示 xlborders.linestyle = Excel.XlLineStyle.xlContinuous ' 下側の線を指定 xlborder = xlborders(excel.xlbordersindex.xledgebottom) ' 下側の線を 2 重線で表示 xlborder.linestyle = Excel.XlLineStyle.xlDouble ' 一旦 ReleaseComObject MRComObject(xlBorder) ' 右側の線を指定 xlborder = xlborders(excel.xlbordersindex.xledgeright) ' 右側の線を太線で表示 xlborder.weight = Excel.XlBorderWeight.xlThick ' 一旦 ReleaseComObject MRComObject(xlBorder) ' 左側の線を指定 xlborder = xlborders(excel.xlbordersindex.xledgeleft) ' 左側の線を太線で表示 xlborder.linestyle = Excel.Constants.xlGray75 ' 一旦 ReleaseComObject MRComObject(xlBorder) MRComObject(xlBorders) MRComObject(xlRange) 3. 罫線の描画 ( 自作関数を使って ) Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click ' 開始点 終了点 線種 線の太さ 位置 ( 定数値 ) を指定 ' 位置が 0 の場合は格子状に引く ( 省略可 ) Call SetLine(2, 2, 8, 8, _ Excel.XlLineStyle.xlContinuous, _ Excel.XlBorderWeight.xlThin, 0) ' 外枠を太線で描画 Call SetLine(2, 2, 8, 8, _ Excel.XlLineStyle.xlContinuous, _ Excel.XlBorderWeight.xlThick, 34) -31-
'2 重線で下の上側に引く Call SetLine(8, 2, 8, 8, _ Excel.XlLineStyle.xlDouble, _ Excel.XlBorderWeight.xlThick, _ Excel.XlBordersIndex.xlEdgeTop) ' 罫線を引く自作関数 ' 開始点 終了点 線種 線の太さ 位置 ( 定数値 ) を指定 'SetLine(2, 2, 8, 8, は (B2:H8) になります ' 位置が 0 の場合は格子状に引く ( 省略可 ) ' 位置が 34 の場合は外枠線を描く ' 5= 左上から右下への罫線 ' 6= 左下から右上への罫線 ' 7= セルの左辺の罫線 ' 8= セルの上辺の罫線 ' 9= セルの下辺の罫線 ' 10= セルの右辺の罫線 ' 11= 内側の垂直線 ' 12= 内側の水平線 Private Sub SetLine(ByVal r1 As Integer, ByVal c1 As Integer, _ ByVal r2 As Integer, ByVal c2 As Integer, _ ByVal ls As Integer, ByVal lw As Integer, _ Optional ByVal lps As Integer = 0) Dim xlrange As Excel.Range Dim xlborders As Excel.Borders Dim xlborder As Excel.Border ' 線を引く範囲を A1 形式で取得 xlrange = xlsheet.range(r1toa1(r1, c1, r2, c2)) xlborders = xlrange.borders If lps = 0 Then ' 格子状に罫線を引く xlborders.linestyle = ls xlborders.weight = lw ElseIf lps = 34 Then ' 外枠線を描く For i As Int32 = 7 To 10 xlborder = xlborders(i) ' 罫線の表示位置を設定 xlborder.linestyle = ls ' 罫線の線種を設定 xlborder.weight = lw ' 罫線の太さを設定 MRComObject(xlBorder) Next i Else ' 個別に罫線を引く xlborder = xlborders(lps) ' 罫線の表示位置を設定 xlborder.linestyle = ls ' 罫線の線種を設定 xlborder.weight = lw ' 罫線の太さを設定 MRComObject(xlBorder) MRComObject(xlBorders) MRComObject(xlRange) -32-
Private Function R1ToA1(ByVal r1 As Integer, ByVal c1 As Integer, _ ByVal r2 As Integer, ByVal c2 As Integer) As String 'A1 形式のアドレスを R1C1 形式に変換する関数 Dim i1, i2 As Integer Dim d1, d2 As String i1 = Microsoft.VisualBasic.Int((c1-1) 26) d1 = Microsoft.VisualBasic.IIf(c1 > 26, Microsoft.VisualBasic.Strings.Chr(64 + i1), "") i1 = c1-26 * i1 d1 &= Microsoft.VisualBasic.Strings.Chr(64 + i1) & CStr(r1) i2 = Microsoft.VisualBasic.Int((c2-1) 26) d2 = Microsoft.VisualBasic.IIf(c2 > 26, Microsoft.VisualBasic.Strings.Chr(64 + i2), "") i2 = c2-26 * i2 d2 &= Microsoft.VisualBasic.Strings.Chr(64 + i2) & CStr(r2) Return d1 & ":" & d2 End Function 4.Excel の Speech.Speak メソッドを使っての音声読み上げ Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click 'Excel の Speech.Speak メソッドを使っての音声読み上げ Dim xlapp As Object Dim xlspeech As Object xlapp = CreateObject("Excel.Application") xlspeech = xlapp.speech ''Excel の Speech.Speak で読み上げ ' コントロールパネルの音声認識の音声の選択で LH Kenji か LH Naoko を ' 選んでおいて下さい Excel2002 の CD 内に入っています ' 下記コードを実行して入っていない場合はウィザードが表示されます xlspeech.speak(textbox1.text) 'xlspeech 及び xlapp を解放 System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSpeech) xlspeech = Nothing System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp) xlapp = Nothing 5.Excel の GetPhonetic メソッドを使ってのふりがなを取得 -33-
Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click 'Excel の GetPhonetic メソッドを使ってのふりがなを取得 Dim xlapp As Object Dim myname As String Dim furigana As String xlapp = CreateObject("Excel.Application") ' 文字列中の空白を除去 (Microsoft.VisualBasic.Strings. を省略しております ) myname = StrConv(" 山田花子 ", VbStrConv.Wide).Replace(" ", "") 'Excel の GetPhonetic 関数を使ってふりがなを取得 furigana = xlapp.getphonetic(myname) ' 取得したふりがなをひらがなに変換 ( お好みで ) Label2.Text = " 山田花子 :" & StrConv(furigana, VbStrConv.Hiragana) 'xlapp を解放 System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp) xlapp = Nothing これらのサンプルをご利用になる場合は 必要により Excel の起動 終了処理を追加して下さい (No.4 及び No.5 はそのままで使用頂けます ) 又 コメント等の注意書きは よく読んでから使用願います 自動化した Office アプリケーションの終了 現象 Microsoft.NET または Microsoft Visual.NET で Microsoft Office アプリケーションを自動化した場合に Quit メソッドを呼び出しても Office アプリケーションが終了しません 原因 Visual Studio.NET でマネージコードから COM オブジェクトを呼び出すと 自動的にランタイム呼び出し可能ラッパー (RCW) が作成されます RCW は.NET アプリケーションと COM オブジェクトの間の呼び出しをマーシャリングします RCW は COM オブジェクトへの参照カウントを保持します したがって RCW でのすべての参照が解放されていない場合 COM オブジェクトは終了しません 解決方法 Office アプリケーションを終了するには オートメーションコードが以下の条件を満たしていることを確認してください -34-
各オブジェクトを新しい変数として宣言しています たとえば コードに次の行があるとします obook = oexcel.workbooks.add() この行を次のように変更します dim obooks as Excel.Workbooks obooks = oexcel.workbooks obook = obooks.add() オブジェクトの使用が終了したら System.Runtime.InteropServices.Marshal.ReleaseComObject を使用します このメソッドによって RCW の参照カウントを減らします 変数を Nothing または Null に設定して 変数への参照を解放します Office アプリケーションオブジェクトの Quit メソッドを使用して サーバーにシャットダウンするように指示します 現象の再現手順 1.Visual Studio.NET を起動します 2.[ ファイル ] メニューの [ 新規作成 ] をポイントし [ プロジェクト ] をクリックします [ プロジェクト ] をクリックし [Windows アプリケーション ] をクリックして [OK] をクリックします Form1 がデフォルトで作成されます 3.Microsoft Excel Object Library への参照を追加します この操作を行うには 以下の手順を実行します a.[ プロジェクト ] メニューの [ 参照の追加 ] をクリックします b.[com] タブで Excel 用のオブジェクトライブラリを探してクリックし [ 選択 ] をクリックします Microsoft Excel 2002 の場合 : Microsoft Excel 10.0 Object Library 注 : Microsoft Office XP プライマリ相互運用機能アセンブリ (PIA) をまだ入手していない場合は ダウンロードしてインストールすることをお勧めします Office XP 用の PIA の関連情報を参照するには 以下の サポート技術情報 (Microsoft Knowledge Base) をクリックしてください 328912 (http://support.microsoft.com/kb/328912/ ) [INFO] Microsoft Office XP 用の PIA のダウンロード Microsoft Office Excel 2003 の場合 : Microsoft Excel 11.0 Object Library c.[ 参照の追加 ] ダイアログボックスで [OK] をクリックして選択内容を確定します 4.[ 表示 ] メニューの [ ツールボックス ] をクリックし Button コントロールを Form1 にドラッグします -35-
5.[Button1] をダブルクリックします フォームのコードウィンドウが表示されます 6.Form1.vb の先頭に次のコードを追加します Imports Microsoft.Office.Interop 7. コードウィンドウには 次のコードが表示されています Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click これを次のコードで置き換えます Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim oapp As New Excel.Application() Dim obook As Excel.Workbook = oapp.workbooks.add Dim osheet As Excel.Worksheet = oapp.activesheet osheet = Nothing obook.close(false) obook = Nothing oapp.quit() oapp = Nothing Debug.WriteLine("Sleeping...") System.Threading.Thread.Sleep(5000) Debug.WriteLine("End Excel") 8.F5 キーを押してアプリケーションを実行します 9.Windows タスクマネージャを起動します Visual Studio で [ 出力 ] ウィンドウを表示して デバッグメッセージを参照します コマンドボタンをクリックすると Excel.exe のインスタンスが [ プロセス ] タブの一覧に表示されることを確認します A. アプリケーションが休止を終了した後も タスクマネージャの一覧で Excel のインスタンスが実行されています ダイアログボックスを閉じると Excel が [ プロセス ] タブの一覧に表示されなくなることを確認します B. 解決方法 に記載した手順を実行すると Office アプリケーションは最後の変数の解放後 終了します 手順 5. の関数を次のコードで置き換えます Private Sub NAR(ByVal o As Object) Try System.Runtime.InteropServices.Marshal.ReleaseComObject(o) Catch Finally -36-
o = Nothing End Try Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim oapp As New Excel.Application() Dim obooks As Excel.Workbooks = oapp.workbooks Dim obook As Excel.Workbook = obooks.add Dim osheet As Excel.Worksheet = oapp.activesheet NAR(oSheet) obook.close(false) NAR(oBook) NAR(oBooks) oapp.quit() NAR(oApp) Debug.WriteLine("Sleeping...") System.Threading.Thread.Sleep(5000) Debug.WriteLine("End Excel") Visual.NET を使用している場合は NAR() 関数のコードを参照します private void NAR(object o) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(o); } catch {} finally { o = null; } } トラブルシューティング 現象の再現手順 で説明した手順を実行してもサーバーがシャットダウンしない場合は 最後のオブジェクトの解放後 GC.Collect() メソッドと GC.WaitForPendingFinalizers() メソッドを使用します ランタイムは RCW でガベージコレクションを実行するため GC.Collect() メソッドはガベージコレクタを強制的に実行し RCW が保持している参照を解放します GC.Collect() メソッドは 使用可能なメモリの最大メモリを要求しますが すべてのメモリが利用可能になるわけではないことに注意してください MS のサポート技術情報等で紹介されている Excel 関係のサンプル一覧 (20 件 ) http://hanatyan.sakura.ne.jp/dotnet/jyohou.htm -37-
-38-