VB でエクセル操作 Ⅲ VB 2005 9 プログラムの概要 事務処理に於いて Microsoft 社のスプレッドシートソフトで有るエクセルは データベースソフトで有るアクセスと共に 業界標準 ( De Facto Standard) で有ると謂う事が出来る 今回は エクセルのセルに設定された書式の取得を 重点的に学ぶ 前回迄に学んだエクセル操作の為のオブジェクトの生成と 既存のエクセルのデータが実際に入力されて居る範囲と値の取得を元に 総ての入力範囲のデータを フレキシブルグリッドに書式付きで表示する 亦 フレキシブルグリッドの書式の設定と 時間の懸る処理の進行状況を表示し ユーザーの不安を解消する為に Windows の標準的なコンポーネントで有るプログレスバーの使用法も 学習する 今回の課題項目 オブジェクトの参照設定 ( 事前バインディング 実行時バインディング ) オブジェクト変数の宣言 (Object 型 As Object) オブジェクトのインスタンス生成 (CreateObject 関数 ) エクセルのオブジェクト (Excel.Application WorkBook WorkSheet) ワークシートのプロパティ (UsedRange プロパティ Cells プロパティ ) プログレスバーの操作 (Max プロパティ Value プロパティ ) エクセルセルの書式 (HorizontalAlignment NumberFormat Font Interior 等 ) フレキシブルグリッドの書式 (RowHeight CellForeColor CellBackColor 等 ) 今回の重点項目 エクセルのオブジェクト (Excel.Application WorkBook WorkSheet) ワークシートのプロパティ (UsedRange プロパティ Cells プロパティ ) プログレスバーの操作 (Max プロパティ Value プロパティ ) エクセルセルの書式 (HorizontalAlignment NumberFormat Font Interior 等 ) フレキシブルグリッドの書式 (RowHeight CellForeColor CellBackColor 等 ) -1-
オブジェクト プロパティ一覧 フレキシブルグリッド ボタン 1 ボタン 2 ボタン 3 プログレスバー ファイルオープンダイアログ ボタン 4 コントロールの種類 プロパティ プロパティの設定値 フォーム Name excel3 Text 合唱コンクール審査結果 FormBorderStyle FixedSingle StartPosition CenterScreen フレキシブルグリッド Name fgddata AllowUserResizing flexresizecolumns コマンドボタン1 Name btnfiler Font MS 明朝 太字 10 コマンドボタン2 Name btnextract Font MS 明朝 太字 10 コマンドボタン3 Name btnclose Font MS 明朝 太字 10 コマンドボタン4 Name btnfinish Text 終了 Font MS 明朝 太字 12 オープンファイルダイアログ Name dlgfiler FileName 空白 プログレスバー オブジェクト名 prgproceed フレキシブルグリッドに付いては ツールボックスウィンドウの アイテムの選択 より COM コンポーネント で Microsoft FlexGrid Control, version 6.0(msflxgrd.ocx) を選択して 追加する 亦 プロジェクトメニューの 参照の追加 より COM で Microsoft Excel x.x Object Library への参照を追加する -2-
Public Class excel3 プログラムリスト ' フォームレベルでグローバルな定数の宣言 ( エクセル定数 ) Private Const xla1 As Integer = 1 Private Const xlhaligngeneral As Integer = 1 Private Const xlhalignleft As Integer = -4131 Private Const xlhaligncenter As Integer = -4108 Private Const xlhalignright As Integer = -4152 ' フォームレベルでグローバルな定数の宣言 ( フレキシブルグリッド定数 ) Private Const flexalignleftcenter As Integer = 1 Private Const flexalignrightcenter As Integer = 7 Private Const flexaligncentercenter As Integer = 4 Private Const flexaligngeneral As Integer = 9 Private Const flexmergerestrictrows As Integer = 2 ' フォームレベルでグローバルな変数の宣言 ' Private EX As Object ' Excel.Application ' Private WB As Object ' Excel.Workbook ' Private WS As Object ' Excel.Worksheet Private EX As Excel.Application Private WB As Excel.Workbook Private WS As Excel.Worksheet Private SD As String ' 格納場所 ( 起動パス ) Private FN As String ' エクセルファイル名 完成後は 此等のコメントを外して Object 型にすると Excel のバージョンに依存しないプログラムにする事が出来る 製作中は 此の様に事前バインディングすると コード入力時にインテリセンスが使用出来る ' フォームが読み込まれた時の処理 Private Sub excel3_load(byval sender As System.Object, ByVal e As System.EventArgs) _ Handles MyBase.Load ' エクセルファイルの格納場所の設定 ( 起動パス ) SD = Application.StartupPath : If Not SD.EndsWith(" ") Then SD &= " " ' ウィンドウを常に手前に表示 Me.TopMost = True ' ボタンのキャプションの設定 btnfiler.text = " エクセルのファイルを " & vbcrlf & " 指定する " btnextract.text = " エクセルのデータを " & vbcrlf & " 抽出する " btnclose.text = " エクセルのファイルを " & vbcrlf & " 終了する " ' ファイルを開くダイアログの設定 dlgfiler.filter = " エクセルファイル (*.xls) *.xls 総てのファイル (*.*) *.*" dlgfiler.initialdirectory = SD ' ボタン ( エクセルのファイルを指定する ) がクリックされた時の処理 Private Sub btnfiler_click(byval sender As Object, ByVal e As System.EventArgs) _ Handles btnfiler.click If dlgfiler.showdialog = Windows.Forms.DialogResult.OK Then ' エクセルのファイル名の設定 FN = dlgfiler.filename If Not FN.Substring( FN.LastIndexOf(".") + 1 ) = "xls" Then FN = "" -3- ボタンのキャプションに改行を入 れ度い時は 此の様に記述すれば 良い ( デザイン時にも出来ない事 はないが 面倒で有る ) エクセルファイル丈を表示する様に設定する ファイルオープンダイアログでキャンセルがクリックされたか何うかは 戻り値で判断する
' ボタン ( エクセルのデータを抽出する ) がクリックされた時の処理 Private Sub btnextract_click(byval sender As Object, ByVal e As System.EventArgs) _ Handles btnextract.click Dim S As String = "" Dim T As String = "" Dim D As String Dim I As Integer Dim J As Integer Dim N As Integer Dim R As Integer ' 最終行 Dim C As Integer ' 最終列 ' エクセルファイルが指定されて居ない時は強制脱出 If FN = "" Then Exit Sub ' エクセルオブジェクトの新しいインスタンスの生成 EX = CreateObject( "Excel.Application" ) ' ファイルを指定してワークブックのオープン WB = EX.Workbooks.Open( FN ) ' シート名を指定してオブジェクトの定義 WS = WB.Worksheets( "Sheet1" ) ' エクセルの表示 EX.Visible = True ' シートの使用領域の取得 WS.UsedRange.Select( ) S = WS.UsedRange.Address( False, False, xla1 ) D = S.Substring( S.IndexOf(":") + 1 ) For I = 1 To D.Length If D.Substring( I - 1, 1 ) < "A" Then S = D.Substring( 0, I - 1 ) T = D.Substring( I - 1 ) Exit For N = S.Length - 1 For I = 1 To S.Length C += ( Asc( S.Substring(I - 1, 1 )) 64 ) * ( 26 ^ N ) N -= 1 R = Val( T ) ' フレキシブルグリッドに表示 With fgddata.cols = C + 1.Rows = R + 1 ' 列見出しの表示.Row = 0 For I = 0 To ( C - 1 ).Col = I + 1 S = "" : N = I Do S = Chr(( N Mod 26 ) + 65 ) & S N = N 26-1 Loop Until N < 0.CellAlignment = flexaligncentercenter.text = S 此処で宣言した変数は宣言したサブプロシージャ内でしか値の参照と設定を行う事が出来ない CreateObject 関数は 事前バインディングと実行時バインディングの孰れでも使用する事が出来る 指定したファイルで Workbooks コレクションを開く シート名には 操作対象とするシートの名称を指定する Address プロパティでワークシートの使用範囲を A1 形式で取得する ( 例 :A1:AD23) 右下端のセル位置をアルファベット部分 (S) と数字部分 (T) に分割する アルファベット部分 (S) を序数に変換する 猶 64 は A の1 個前の @ の文字コードで 26 はアルファベットの文字数を表す 同一のオブジェクトに対して複数の処理を行う場合 With ステートメントを用いると オブジェクトへの参照はプロパティを割り当てる度に指定するのではなく 一度丈指定される為 効率が良く成る Chr 関数は 文字コードより文字を生成する関数で有る 此処では 0 なら A 1 なら B と謂う様に変換して 列見出しを表示して居る -4-
' 行見出しの表示.Col = 0 For I = 1 To R.Row = I.CellAlignment = flexaligncentercenter.text = I.ToString.Trim ' エクセルデータの読込と表示追加変更 prgproceed.maximum = ( R + 1 ) * C prgproceed.value = 1 For I = 1 To R ' 行の高さの設定 (20 twips = 1 point, 15 twips = 1 pixel).set_rowheight( I, WS.Cells( I, 1 ).rowheight * 20 ).Row = I For J = 1 To C prgproceed.value = I * C + J.Col = J If SetAttribute( I, J ) Then.Text = WS.Cells( I, J ).value End With 行見出しは 数値で表されるので此処では 単純に 数値を文字列に変換して 前後の空白を取り除いた物を表示して居る エクセルの各セルに設定されて居る値は Worksheet オブジェクトの Cells プロパティで取得する事が出来る 第 1 引数で行を 第 2 引数で列を 1 から始まる数値で指定する ' ボタン ( エクセルのファイルを終了する ) がクリックされた時の処理 Private Sub btnclose_click(byval sender As Object, ByVal e As System.EventArgs) _ Handles btnclose.click If WS Is Nothing Then Exit Sub ' エクセルを終了するサブルーチン呼出 Call CloseExcel( ) ' ボタン ( 終了 ) がクリックされた時の処理 Private Sub btnfinish_click(byval sender As Object, ByVal e As System.EventArgs) Handles btnfinish.click ' エクセルファイルが開いて居ればクローズ If WS IsNot Nothing Then ' エクセルを終了するサブルーチン呼出 Call CloseExcel( ) ' フォームをメモリから消去して終了 Me.Dispose( ) End ' エクセルファイルを閉じるジェネラルプロシージャ Private Sub CloseExcel( ) ' エクセルの終了 EX.application.displayalerts = False EX.application.quit() ' オブジェクトとの関連付けの解除 WS = Nothing WB = Nothing EX = Nothing -5- オブジェクトの比較は Is 演算子 (IsNot 演算子 ) を用いて行う ジェネラルプロシージャを呼び出す ( 制御を移す ) には Call ステートメントを用いる Worksheet オブジェクトのインスタンスが存在すれば Application Workbook Worksheet オブジェクトを正しく終了するプロシージャを呼び出す 実際には 此処迄丁寧に記述する必要は無いのだが 使用した総てのフォームをメモリから消去した上で プログラムを正しく終了すると謂う癖を付けて置く事が望ましい 使用済みの Excel.Application オブジェクトや Workbook オブジェクトや Worksheet オブジェクトは 必ず 閉じて メモリから開放して置く Quit メソッドは エクセルを終了する丈で メモリから削除するには Nothing を設定する
' セルの属性を設定するジェネラルプロシージャ追加 Private Function SetAttribute(ByVal R As Integer, ByVal C As Integer) As Boolean Dim S As String Dim Ret As Boolean Dim Clr As String Dim Cr, Cg, Cb As Integer Static Mflag Static Mstr As Boolean As String Static ステートメントは 静的変 数を宣言する Ret = True fgddata.row = R fgddata.col = C With WS.Cells( R, C ) ' 配置の設定 If.HorizontalAlignment = xlhalignleft Then fgddata.cellalignment = flexalignleftcenter ElseIf.HorizontalAlignment = xlhaligncenter Then fgddata.cellalignment = flexaligncentercenter ElseIf. HorizontalAlignment = xlhalignright Then fgddata.cellalignment = flexalignrightcenter Else fgddata.cellalignment = flexaligngeneral ' 数値書式の設定 If IsNumeric(.Value ) Then S =.NumberFormat If Not S.Substring( 0, 1 ).ToLower = "g" Then fgddata.set_textmatrix( R, C, Format(.Value, S )) Ret = False ' 文字色の設定 ( 値 0 は標準色 ) If.Font.Color = 0 Then fgddata.cellforecolor = Color.Black Else Clr = "00000" & Hex(.Font.Color ) Clr = Clr.Substring(Clr.Length - 6 ) Cb = Val( "&H" & Clr.Substring( 0, 2 )) Cg = Val( "&H" & Clr.Substring( 2, 2 )) Cr = Val( "&H" & Clr.Substring( 4, 2 )) fgddata.cellforecolor = Color.FromArgb( Cr, Cg, Cb ) ' 背景色の設定 ( 値 0 は標準色 ) Clr = "00000" & Hex(.Interior.Color ) Clr = Clr.Substring(Clr.Length - 6 ) Cb = Val( "&H" & Clr.Substring( 0, 2 )) Cg = Val( "&H" & Clr.Substring( 2, 2 )) Cr = Val( "&H" & Clr.Substring( 4, 2 )) If Cr + Cg + Cb = 0 Then Cb = 1 ' 黒の場合 fgddata.cellbackcolor = Color.FromArgb( Cr, Cg, Cb ) 単一のオブジェクトに対して複数の処理を行う場合 With 制御構造内に記述すると 効率が良い IsNumeric 関数は 式が数値として評価出来るか何うかを調べ 結果をブール型で返す ToLower メソッドは アルファベットの大文字を小文字に変換する Format 関数は 式を指定した書式に変換する Range オブジェクトの Font プロパティは 指定された Range オブジェクトに設定されて居るフォント (Font オブジェクト ) を返す Font オブジェクトのプロパティで 文字色や文字装飾を指定するが フレキシブルグリッドでは 表現出来ない装飾も有る Interior オブジェクトは オブジェクトの内部の状態等を表すオブジェクトで有る -6-
' フォントの設定 fgddata.cellfontname =.Font.Name fgddata.cellfontsize =.Font.Size If.Font.FontStyle = " 太字 " Then fgddata.cellfontbold = True ElseIf.Font.FontStyle = " 斜体 " Then fgddata.cellfontitalic = True ElseIf.Font.FontStyle = " 太字斜体 " Then fgddata.cellfontbold = True fgddata.cellfontitalic = True ' 列幅の設定 If R = 1 Then fgddata.set_colwidth( C,.Width * 20 ) ' セル結合の設定 ( 行方向而巳 ) If Not Mflag Then If.MergeCells Then fgddata.mergecells = flexmergerestrictrows fgddata.set_mergerow( R, True ) Mflag = True Mstr =.Value Else If.MergeCells Then fgddata.set_textmatrix( R, C, Mstr ) Ret = False Else Mflag = False Mstr = "" End With Return Ret End Function End Class Visual Basic 6.0 では 色の表現が BBGGRR(16 進数表現 ) で有る為 一旦 4 桁の 16 進数に変換した後 夫々れの要素を抽出して居る 猶 CellBackColore に 0 を設定すると標準色と看做される為 1 を設定して居る 列の幅 ( 横幅 )1 列総て同じなので 各セル毎に設定する必要は無い 猶 エクセルの列の幅はポイントで フレキシブルグリッドの列の幅は Twips で指定する為 変換が必要で有る MergeCells プロパティで 同じ内容のセルを単一のセルにグループ化するか何うかを指定する フレキシブルグリッドでは MergeCol と MergeRow プロパティでマージ ( 結合 ) を許可した列や行で 連接するセルの内容が等しい場合に セルが結合される 此処では コードが煩雑と成る為 行方向 ( 横方向 ) のマージしか許可して居ないが 列方向 ( 縦方向 ) のマージにも挑戦して観て欲しい 文字列操作 Visual Basic では 文字列は ダブルクォーテーションでクォートする 長さ 0 の文字列をヌルストリングと謂い 対のダブルクォーテーション而巳を記述する 猶 Visual Basic では vbnullstring と謂う定数も用意されて居る 文字列を結合 ( 連結 ) するには + 演算子か & 演算子を使用する -7-
フレキシブルグリッドの RowHeight プロパティ 機能 書式 解説 指定された行の高さを twip 単位で設定 取得するプロパティ Object.RowHeight( 行番号 ) = 値 値を 0 に設定すると 非表示の行を作成する事が出来る 亦 -1 に設定すると 行の高さが現在のフォントサイズに合わせて設定される既定値に再設定される 行番号には 1 から始まる行の番号を設定する整数値を指定する 此のプロパティは フォームのスケールモードとは関係無く デザイン時には使用する事は出来ない フレキシブルグリッドの ColWidth プロパティ 機能書式解説 指定された列の幅を twip 単位で設定 取得するプロパティ Object.ColWidth( 列番号 ) = 値 値を 0 に設定すると 非表示の列を作成する事が出来る 亦 -1 に設定すると 列の幅が現在のフォントサイズに合わせて設定される既定値に再設定される 列番号には 1 から始まる列の番号を設定する整数値を指定する 此のプロパティは フォームのスケールモードとは関係無く デザイン時には使用する事は出来ない 此のプロパティを使用すると 実行時に列の幅を設定する事が出来る デザイン時に列の幅を設定するには FormatString プロパティを使用する フレキシブルグリッドの CellForeColor プロパティ CellBackColor プロパティ 機能書式書式解説 CellForeColor : 個々のセルや特定範囲のセルの前景色を設定 取得するプロパティ CellBackColor : 個々のセルや特定範囲のセルの背景色を設定 取得するプロパティ Object.CellForeColor = 色 Object.CellBackColor = 色 此のプロパティを変更すると FillStyle プロパティの設定値に基づき カレントセルや現在選択されて居る範囲のセルに変更が反映される 此等のプロパティの孰れかに 0 を設定すると フレキシブルグリッドコントロールでは 標準の背景色と前景色を使用してセルが表示される 此等のプロパティの孰れかに黒色を設定する場合は 両方のプロパティに 0 では無く 1 を設定する 此等のプロパティは デザイン時には 使用する事が出来ない -8-
フレキシブルグリッドの CellFontName プロパティ 機能 カレントセルのテキストにフォント名を設定 取得するプロパティ 書式 Object.CellFontName = フォント名 解説 此のプロパティを変更すると FillStyle プロパティの設定値に基づいて カレントセルや現在選択されて居るセルの範囲に変更が反映される 此等のプロパティは デザイン時には 使用する事が出来ない フレキシブルグリッドの CellFontSize プロパティ 機能 カレントセルのテキストにフォントサイズを設定 取得するプロパティ 書式 Object.CellFontSize = フォントサイズ ( ポイント単位 ) 解説 此のプロパティを変更すると FillStyle プロパティの設定値に基づいて カレントセルや現在選択されて居るセルの範囲に変更が反映される 此等のプロパティは デザイン時には 使用する事が出来ない フレキシブルグリッドの CellFontBold CellFontItalic CellFontUnderline プロパティ 機能 カレントセルのテキストに太字や斜体や下線のスタイルを設定 取得するプロパティ 書式 太字 :Object.CellFontBold = ブール値斜体 :Object.CellFontItalic = ブール値下線 :Object.CellFontUnderline = ブール値 解説 此のプロパティを変更すると FillStyle プロパティの設定値に基づいて カレントセルや現在選択されて居るセルの範囲に変更が反映される 此等のプロパティは デザイン時には 使用する事が出来ない ブール値の設定値は 下記の通りで有る 設定値 True False 内容カレントセルのテキストは太字や斜体や下線に成る カレントセルのテキストは通常のスタイルに成る 猶 FillStyle プロパティは フレキシブルグリッドコントロールのセルの書式設定が変更された時其の変更を 選択された総てのセルに適用するか何うかを指定するプロパティで有る 単独の場合 : 定数 flexfillsingle か値 0 を指定する 反復の場合 : 定数 flexfillrepeat か値 1 を指定する -9-
フレキシブルグリッドの MergeCells プロパティ 機能書式解説 同じ内容のセルを単一のセルにグループ化するか何うかを設定 取得するプロパティ Object.MergeCells = 値 此のセルマージ機能に依り データを明確で解り易い方法で表示する事が出来る セルマージ機能は 並び替え機能と列の順序付け機能と共に使用する事が出来る 値の設定値は 下記の通りで有る 定数 値 内容 flexmergenever 0 同一内容のセルをグループ化しない ( 既定値 ) flexmergefree 1 自由にグループ化する ( 同一内容のセルは常にマージされる ) flexmergerestrictrows 2 行内で左側に隣接する同一内容のセルがマージされる flexmergerestrictcolumns 3 列内で上方に隣接する同一内容のセルがマージされる flexmergerestrictboth 4 行内で左側に隣接 又は 列内で上方に隣接する同一内容のセルがマージされる フレキシブルグリッドコントロールのセルマージ機能を使用する手順は 下記の通りで有る 1.MergeCells プロパティに 0 以外の値を設定する ( 上表参照 ) 2. マージする行と列の配列を示す MergeRow と MergeCol プロパティに真 (True) を設定する セルマージ機能を使用すると 同一内容のセルがマージされ セルの内容を変更すると マージは自動的に更新される MergeCells に 0 以外の値を設定すると 選択範囲を強調表示する設定は自動的に解除される 此れは主に再描画を高速化する為で有るが マージされたセルを含む範囲を選択すると予想しない結果を招く事が有る事に 注意を要する フレキシブルグリッドの MergeCol MergeRow プロパティプロパティ 機能書式解説 内容をマージ出来る行と列を指定する値を設定 取得するプロパティ 列 :Object.MergeCols( 列番号 ) = 値行 :Object.MergeRows( 行番号 ) = 値 MergeCells プロパティが 0 以外の値に設定されて居る場合 同一の値で隣接するセルは MergeRow プロパティが真に設定されて居る行内に有るか MergeCol プロパティが真に設定されて居る列内に有る場合に限りマージされる 値の設定値は 下記の通りで有る 設定値 内容 True 隣接するセルが同一内容の場合には マージされる False 隣接するセルが同一内容の場合でも マージされない ( 既定値 ) -10-