VB実用⑨　エクセル操作Ⅲ

Size: px

Start display at page:

Download "VB実用⑨　エクセル操作Ⅲ"

ありみちとべ
5 years ago
Views:

VB でエクセル操作 Ⅲ VB 2005 9 プログラムの概要事務処理に於いて Microsoft 社のスプレッドシートソフトで有るエクセルはデータベースソフトで有るアクセスと共に業界標準 ( De Facto Standard) で有ると謂う事が出来る今回はエクセルのセルに設定された書式の取得を重点的に学ぶ前回迄に学んだエクセル操作の為のオブジェクトの生成と

1 VB でエクセル操作 Ⅲ VB プログラムの概要事務処理に於いて 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 フレキシブルグリッド

2 オブジェクトプロパティ一覧フレキシブルグリッドボタン 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-

3 Public Class excel3 プログラムリスト ' フォームレベルでグローバルな定数の宣言 ( エクセル定数 ) Private Const xla1 As Integer = 1 Private Const xlhaligngeneral As Integer = 1 Private Const xlhalignleft As Integer = Private Const xlhaligncenter As Integer = Private Const xlhalignright As Integer = ' フォームレベルでグローバルな定数の宣言 ( フレキシブルグリッド定数 ) 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- ボタンのキャプションに改行を入れ度い時は此の様に記述すれば良い ( デザイン時にも出来ない事はないが面倒で有る ) エクセルファイル丈を表示する様に設定するファイルオープンダイアログでキャンセルがクリックされたか何うかは戻り値で判断する

4 ' ボタン ( エクセルのデータを抽出する ) がクリックされた時の処理 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-

5 ' 行見出しの表示.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 を設定する

6 ' セルの属性を設定するジェネラルプロシージャ追加 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-

7 ' フォントの設定 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-

8 フレキシブルグリッドの 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-

9 フレキシブルグリッドの 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-

10 フレキシブルグリッドの 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-

VB実用⑧　エクセル操作Ⅱ

VB実用⑧　エクセル操作Ⅱ VB でエクセル操作 Ⅱ VB 2005 8 プログラムの概要事務処理に於いて Microsoft 社のスプレッドシートソフトで有るエクセルはデータベースソフトで有るアクセスと共に業界標準 ( De Facto Standard) で有ると謂う事が出来る今回はエクセルを Visual Basic から操作する為に最も基本と成るセルに設定された値の取得を重点的に学ぶ前回学んだエクセル操作の為のオブジェクトの生成と

VB実用⑨ エクセル操作Ⅲ

VB実用⑨　エクセル操作Ⅲ