VB実用⑨　エクセル操作Ⅲ

プログラムの概要

事務処理に於いて、Microsoft 社の スプレッドシートソフトで有るエク セルは、データベースソフトで有る ア ク セ ス と 共 に 、 業 界 標 準 （De Facto Standard）で有ると謂う事が 出来る。 今回は、エクセルのセルに設定され た書式の取得を、重点的に学ぶ。 前回迄に学んだエクセル操作の為の オブジェクトの生成と、既存のエク セルのデータが実際に入力されて居 る範囲と値の取得を元に、総ての入 力範囲のデータを、フレキシブルグ リッドに書式付きで表示する。 亦、フレキシブルグリッドの書式の設定と、時間の懸る処理の進行状況を表示し、ユーザーの不安を解 消する為に、Windows の標準的なコンポーネントで有るプログレスバーの使用法も、学習する。

VB でエクセル操作Ⅲ

VB 2005 ⑨ □ オブジェクトの参照設定（事前バインディング、実行時バインディング） □ オブジェクト変数の宣言（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 等） 今回の重点項目

■ オブジェクト･プロパティ一覧 ■ コントロールの種類 プロパティ プロパティの設定値 フォーム Name excel3 Text 合唱コンクール審査結果 FormBorderStyle FixedSingle StartPosition CenterScreen フレキシブルグリッド Name fgdData AllowUserResizing flexResizeColumns コマンドボタン１ Name btnFiler Font ＭＳ明朝、太字、１０ コマンドボタン２ Name btnExtract Font ＭＳ明朝、太字、１０ コマンドボタン３ Name btnClose Font ＭＳ明朝、太字、１０ コマンドボタン４ Name btnFinish Text 終了 Font ＭＳ明朝、太字、１２ オープンファイルダイアログ Name dlgFiler FileName 空白 プログレスバー オブジェクト名 prgProceed フレキシブルグリッドに付いては、ツールボックスウィンドウの『アイテムの選択』より『COM コンポーネント』でMicrosoft FlexGrid Control, version 6.0（msflxgrd.ocx）を選択して、追加す る。

亦、プロジェクトメニューの『参照の追加』より『COM』で Microsoft Excel x.x Object Library への参照を追加する。 ボタン４ ボタン1 フレキシブルグリッド ファイルオープンダイアログ ボタン２ ボタン３ プログレスバー

■ プログラムリスト ■ 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 ' エクセルファイル名 ' フォームが読み込まれた時の処理

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 End Sub ' ボタン（エクセルのファイルを指定する）がクリックされた時の処理

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 = "" End If End If End Sub 完成後は、此等のコメントを外し て、Object 型にすると、Excel の バージョンに依存しないプログラ ムにする事が出来る。 製作中は、此の様に事前バインデ ィングすると、コード入力時にイ ンテリセンスが使用出来る。 ボタンのキャプションに改行を入 れ度い時は、此の様に記述すれば 良い（デザイン時にも出来ない事 はないが、面倒で有る）。 エクセルファイル丈を表示する様 に設定する。 ファイルオープンダイアログでキ ャンセルがクリックされたか何う かは、戻り値で判断する。

' ボタン（エクセルのデータを抽出する）がクリックされた時の処理

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 End If Next N = S.Length - 1 For I = 1 To S.Length C += ( Asc( S.Substring(I - 1, 1 )) – 64 ) * ( 26 ^ N ) N -= 1 Next 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 Next 此処で宣言した変数は宣言したサ ブプロシージャ内でしか値の参照 と設定を行う事が出来ない。 CreateObject 関数は、事前バイン ディングと実行時バインディング の孰れでも使用する事が出来る。 指定したファイルで、Workbooks コレクションを開く。 シート名には、操作対象とするシ ートの名称を指定する。 Address プロパティでワークシー トの使用範囲をA1 形式で取得す る（例：A1:AD23）。 右下端のセル位置をアルファベッ ト部分（S）と数字部分（T）に分 割する。 アルファベット部分（S）を序数 に変換する。猶、64 は A の１個 前の@の文字コードで、26 はアル ファベットの文字数を表す。 同一のオブジェクトに対して複数 の処理を行う場合、With ステート メントを用いると、オブジェクト への参照はプロパティを割り当て る度に指定するのではなく、一度 丈指定される為、効率が良く成る。 Chr 関数は、文字コードより文字 を生成する関数で有る。此処では 0 なら A、1 なら B…と謂う様に変 換して、列見出しを表示して居る。

' 行見出しの表示 .Col = 0 For I = 1 To R .Row = I .CellAlignment = flexAlignCenterCenter .Text = I.ToString.Trim Next ' エクセルデータの読込と表示 追加変更 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 If Next Next End With End Sub ' ボタン（エクセルのファイルを終了する）がクリックされた時の処理

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( )

End Sub

' ボタン（終了）がクリックされた時の処理

Private Sub btnFinish_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnFinish.Click

' エクセルファイルが開いて居ればクローズ If WS IsNot Nothing Then

' エクセルを終了するサブルーチン呼出 Call CloseExcel( ) End If ' フォームをメモリから消去して終了 Me.Dispose( ) End End Sub ' エクセルファイルを閉じるジェネラルプロシージャ Private Sub CloseExcel( )

' エクセルの終了 EX.application.displayalerts = False EX.application.quit() ' オブジェクトとの関連付けの解除 WS = Nothing WB = Nothing EX = Nothing End Sub 行見出しは、数値で表されるので 此処では、単純に、数値を文字列 に変換して、前後の空白を取り除 いた物を表示して居る。 エクセルの各セルに設定されて居 る値は、Worksheet オブジェクト のCells プロパティで取得する事 が出来る。第１引数で行を、第２ 引数で列を、1 から始まる数値で 指定する。 オブジェクトの比較は、Is 演算子 （IsNot 演算子）を用いて行う。 ジェネラルプロシージャを呼び出 す（制御を移す）には、Call ステ ートメントを用いる。 実際には、此処迄丁寧に記述する 必要は無いのだが、使用した総て のフォームをメモリから消去した 上で、プログラムを正しく終了す ると謂う癖を付けて置く事が望ま しい。 使用済みの Excel.Application オ ブジェクトやWorkbook オブジェ クトや Worksheet オブジェクト は、必ず、閉じて、メモリから開 放して置く。 Quit メソッドは、エクセルを終了 する丈で、メモリから削除するに はNothing を設定する。 Worksheet オブジェクトのインス タンスが存在すればApplication、 Workbook、Worksheet オブジェ クトを正しく終了するプロシージ ャを呼び出す。

' セルの属性を設定するジェネラルプロシージャ 追加

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 As Boolean Static Mstr As String 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 End If ' 数値書式の設定

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 End If End If ' 文字色の設定（値 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 ) End If

' 背景色の設定（値 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 ) Static ステートメントは、静的変 数を宣言する。 単一のオブジェクトに対して複数 の処理を行う場合、With 制御構造 内に記述すると、効率が良い。 Format 関数は、式を指定した書 式に変換する。 IsNumeric 関数は、式が数値とし て評価出来るか何うかを調べ、結 果をブール型で返す。 ToLower メソッドは、アルファベ ットの大文字を小文字に変換す る。 Range オブジェクトの Font プロ パティは、指定されたRange オブ ジェクトに設定されて居るフォン ト（Font オブジェクト）を返す。 Font オブジェクトのプロパティ で、文字色や文字装飾を指定する が、フレキシブルグリッドでは、 表現出来ない装飾も有る。 Interior オブジェクトは、オブジ ェクトの内部の状態等を表すオブ ジェクトで有る。

' フォントの設定

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 End If ' 列幅の設定 If R = 1 Then fgdData.set_ColWidth( C, .Width * 20 ) End If ' セル結合の設定（行方向而巳） If Not Mflag Then

If .MergeCells Then fgdData.MergeCells = flexMergeRestrictRows fgdData.set_MergeRow( R, True ) Mflag = True Mstr = .Value End If Else If .MergeCells Then fgdData.set_TextMatrix( R, C, Mstr ) Ret = False Else Mflag = False Mstr = "" End If End If End With Return Ret End Function End Class 文字列操作 ※ Visual Basic では、文字列は、ダブルクォーテーションでクォートする。 ※ 長さ 0 の文字列をヌルストリングと謂い、対のダブルクォーテーション而巳を記述する。 猶、Visual Basic では、vbNullString と謂う定数も用意されて居る。

※ 文字列を結合（連結）するには、+ 演算子か & 演算子を使用する。 列の幅（横幅）１列総て同じなの で、各セル毎に設定する必要は無 い。猶、エクセルの列の幅はポイ ントで、フレキシブルグリッドの 列の幅はTwips で指定する為、変 換が必要で有る。 MergeCells プロパティで、同じ内 容のセルを単一のセルにグループ 化するか何うかを指定する。 フ レ キ シ ブ ル グ リ ッ ド で は MergeCol と MergeRow プロパ ティでマージ（結合）を許可した 列や行で、連接するセルの内容が 等しい場合に、セルが結合され る。 此処では、コードが煩雑と成る 為、行方向（横方向）のマージし か許可して居ないが、列方向（縦 方向）のマージにも挑戦して観て 欲しい。 Visual Basic 6.0 では、色の表現 がBBGGRR（16 進数表現）で有 る為、一旦、4 桁の 16 進数に変 換した後、夫々れの要素を抽出し て居る。猶、CellBackColore に 0 を設定すると標準色と看做され る為、1 を設定して居る。

フレキシブルグリッドのRowHeight プロパティ 指定された行の高さをtwip 単位で設定・取得するプロパティ Object.RowHeight( 行番号 ) = 値 値を0 に設定すると、非表示の行を作成する事が出来る。亦、-1 に設定すると、行の高さが 現在のフォントサイズに合わせて設定される既定値に再設定される。 行番号には、１から始まる行の番号を設定する整数値を指定する。 此のプロパティは、フォームのスケールモードとは関係無く、デザイン時には使用する事は出来ない。 フレキシブルグリッドのColWidth プロパティ 指定された列の幅をtwip 単位で設定・取得するプロパティ Object.ColWidth( 列番号 ) = 値 値を0 に設定すると、非表示の列を作成する事が出来る。亦、-1 に設定すると、列の幅が現 在のフォントサイズに合わせて設定される既定値に再設定される。 列番号には、１から始まる列の番号を設定する整数値を指定する。 此のプロパティは、フォームのスケールモードとは関係無く、デザイン時には使用する事は出来ない。 此のプロパティを使用すると、実行時に列の幅を設定する事が出来る。デザイン時に列の幅を設定する には、FormatString プロパティを使用する。 フレキシブルグリッドのCellForeColor プロパティ、CellBackColor プロパティ CellForeColor ：個々のセルや特定範囲のセルの前景色を設定・取得するプロパティ CellBackColor ：個々のセルや特定範囲のセルの背景色を設定・取得するプロパティ Object.CellForeColor = 色 Object.CellBackColor = 色 此のプロパティを変更すると、FillStyle プロパティの設定値に基づき、カレントセルや現在 選択されて居る範囲のセルに変更が反映される。 此等のプロパティの孰れかに0 を設定すると、フレキシブルグリッドコントロールでは、標準の背景色 と前景色を使用してセルが表示される。此等のプロパティの孰れかに黒色を設定する場合は、両方のプ ロパティに0 では無く 1 を設定する。 此等のプロパティは、デザイン時には、使用する事が出来ない。 機 能 書 式 解 説 機 能 書 式 解 説 書 式 機 能 書 式 解 説 書 式 書 式 書 式

フレキシブルグリッドの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 を指定する。

フレキシブルグリッドのMergeCells プロパティ 同じ内容のセルを単一のセルにグループ化するか何うかを設定・取得するプロパティ Object.MergeCells = 値 此のセルマージ機能に依り、データを明確で解り易い方法で表示する事が出来る。セルマー ジ機能は、並び替え機能と列の順序付け機能と共に使用する事が出来る。 値の設定値は、下記の通りで有る。 定数 値 内容 flexMergeNever 0 同一内容のセルをグループ化しない（既定値）。 flexMergeFree 1 自由にグループ化する（同一内容のセルは常にマージされる）。 flexMergeRestrictRows 2 行内で左側に隣接する同一内容のセルがマージされる。 flexMergeRestrictColumns 3 列内で上方に隣接する同一内容のセルがマージされる。 flexMergeRestrictBoth 4 行内で左側に隣接、又は、列内で上方に隣接する同一内容のセルが マージされる。 フレキシブルグリッドコントロールのセルマージ機能を使用する手順は、下記の通りで有る。 １．MergeCells プロパティに 0 以外の値を設定する（上表参照）。

２．マージする行と列の配列を示すMergeRow と MergeCol プロパティに真（True）を設定する。

セルマージ機能を使用すると、同一内容のセルがマージされ、セルの内容を変更すると、マージは自動 的に更新される。 MergeCells に 0 以外の値を設定すると、選択範囲を強調表示する設定は自動的に解除される。此れは 主に再描画を高速化する為で有るが、マージされたセルを含む範囲を選択すると予想しない結果を招く 事が有る事に、注意を要する。 フレキシブルグリッドのMergeCol、MergeRow プロパティプロパティ 内容をマージ出来る行と列を指定する値を設定・取得するプロパティ 列：Object.MergeCols( 列番号 ) = 値 行：Object.MergeRows( 行番号 ) = 値 MergeCells プロパティが 0 以外の値に設定されて居る場合、同一の値で隣接するセルは MergeRow プロパティが真に設定されて居る行内に有るか、MergeCol プロパティが真に設 定されて居る列内に有る場合に限りマージされる。 値の設定値は、下記の通りで有る。 設定値 内容 True 隣接するセルが同一内容の場合には、マージされる。 False 隣接するセルが同一内容の場合でも、マージされない（既定値）。 機 能 書 式 解 説 書 式 機 能 書 式 解 説 書 式