VB実用⑯　印刷Ⅵ（Excel）

プログラムの概要

事務処理に於いて、集計結果等を印刷して、確 認等を行う事も多い為、ペーパーレスオフィス が推奨される昨今に於いても、矢張り印刷に関 する技術は必要で有る。

Visual Basic から印刷を行う方法は、.NET 以 降、PrintDocument オブジェクトを使用する 方法が、標準機能と仕て一般的で有るが、 Professional 版 等 で は ReportView や CrystalReport を利用する事も可能で有る。亦、 Power Packs 2 で は 視 覚 的 確 認 が 容 易 な FormPrint が機能を向上させて復活して居る。 此処では、Access のデータを、条件を設定し て抽出し、各種書式を設定したExcel シートに、 出力位置を記述したデータファイルを利用し て、吐き出し、Excel の印刷機能を使用して印 刷を行う。

印刷Ⅴ

VB 2005 ⑤ □ System.Data.OleDb 名前空間（Access データベースの操作） □ データベースの接続（OleDbConnection オブジェクト） □ データベースの操作（OleDbDataAdapter オブジェクト） □ テーブルデータの格納（DataSet オブジェクト） □ Excel オブジェクト（Application、Workbook、Worksheet オブジェクト） □ データと書式の設定（Range オブジェクト、Value、HorizontalAlignment プロパティ） □ レポートのプレビュー（Worksheet オブジェクト、PrintPreview メソッド） □ レポートの印刷（Worksheet オブジェクト、PrintOut メソッド） 今回の課題項目 □ Excel オブジェクト（Application、Workbook、Worksheet オブジェクト） □ データと書式の設定（Range オブジェクト、Value、HorizontalAlignment プロパティ） □ レポートのプレビュー（Worksheet オブジェクト、PrintPreview メソッド） □ レポートの印刷（Worksheet オブジェクト、PrintOut メソッド） 今回の重点項目

■ オブジェクト･プロパティ一覧 ■ コントロールの種類 プロパティ プロパティの設定値 フォーム name ExcelPrint FormBorderStyle FixedSingle StartPosition CenterScreen Text EXCEL プリント グループボックス１ name grpSearch Font ＭＳ明朝、太字、９ Text 検索条件 グループボックス２ name grpMethod Font ＭＳ明朝、太字、９ Text タイトル項目の検索方法

ラベル１～７ name lblItem1 ～ lblItem5

AutoSize False Font ＭＳ明朝、太字、１０ Text lblItem1：タイトル lblItem2：価格 lblItem3：購入日 lblItem4：カテゴリ ID lblItem5：著者 ID lblItem6：～ lblItem7：～ TextAlign MiddleRight ラジオボタン１～ラジオボタン４ グループボックス１ ボタン２ ラベル１ ～ ラベル７ ボタン３ ボタン１ テキストボックス２ テキストボックス１ テキストボックス３ テキストボックス４ テキストボックス５ コンボボックス２ コンボボックス１ グループボックス２

テキストボックス１ name txtTitle Font ＭＳ明朝、太字、９ Text 空白 テキストボックス２ name txtPrice1 Font ＭＳ明朝、太字、９ Text 空白 TextAlign Right テキストボックス３ name txtPrice2 Font ＭＳ明朝、太字、９ Text 空白 TextAlign Right テキストボックス４ name txtDate1 Font ＭＳ明朝、太字、９ Text 空白 テキストボックス５ name txtDate2 Font ＭＳ明朝、太字、９ Text 空白 コンボボックス１ name cboCate Font ＭＳ明朝、太字、９ Text 空白 コンボボックス２ name cboWriter Font ＭＳ明朝、太字、９ Text 空白

ラジオボタン１～４ name radMethod1 ～ radMethod4

Checked radMethod1：True Font ＭＳ明朝、太字、９ Text radMethod1：部分一致 radMethod2：前方一致 radMethod3：後方一致 radMethod4：完全一致 コマンドボタン１ name btnPreview Font ＭＳ明朝、太字、１２ Text プレビュー コマンドボタン２ name btnExecute Font ＭＳ明朝、太字、１２ Text 印刷 コマンドボタン３ name btnFinish Font ＭＳ明朝、太字、１２ Text 終了 参照設定 事前に、下記のライブラリを参照設定して置く。

ActiveX Data Object x.x Library、Microsoft Excel x.x Object Library ※ x.x はバージョンに依り異なる。

■ Excel シートの作成 ■

事前に、下記の様な Excel シートを作成し、プログラムと同じディレクトリに、excelprint.xls と謂う ファイル名で、保存して置く。

■ プログラムリスト ■

Public Class ExcelPrint

Private Structure PrnPos Public Pcol As String Public Prow( ) As Integer End Structure

Private Pos( 7 ) As PrnPos Private SD As String Private Cn As ADODB.Connection Private Rs As ADODB.Recordset Private Ex As Excel.Application Private Wb As Excel.Workbook Private Ws As Excel.Worksheet ' フォームが読み込まれた時の処理

Private Sub ExcelPrint_Load( ByVal sender As Object, ByVal e As System.EventArgs ) _ Handles Me.Load Dim F As String Dim I As Integer Dim J As Integer ' 起動パスの取得

SD = Application.StartupPath : If Not SD.EndsWith( "¥" ) Then SD &= "¥"

' セル位置の読込 For I = 0 To 7

ReDim Pos( I ).Prow( 4 ) Next

F = SD & "excelprint.dat" FileOpen( 1, F, OpenMode.Input ) For I = 0 To 7

Input( 1, Pos( I ).Pcol ) For J = 0 To 4

Input( 1, Pos( I ).Prow( J )) Next Next FileClose( 1 ) ' コネクションの確立 F = SD & "data.mdb" Cn = New ADODB.Connection Cn.CursorLocation = ADODB.CursorLocationEnum.adUseClient Cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & F & ";Persist Security Info=False;"

Cn.Open( ) 此処で宣言した変数は、宣言した クラス内の総てのメソッド（プロ シージャ）で値の参照と設定を行 う事が出来る。 複数のデータ型を含む構造体を宣 言する。構造体には、配列も含め る事が出来る。 ユーザ定義型も、通常のデータ型 同様に、変数や配列の宣言に使用 する事が出来る。 此処で宣言した変数は宣言したプ ロシージャ内でしか値の参照と設 定を行う事が出来ない。 Excel のセルへの書込位置は、此 の様に別ファイルに仕た方が、メ ンテナンスが容易に成る。 StartupPath は、プログラムを起 動したEXE ファイルのパス（位 置）を表す。 ファイルからデータ単位で読み込 むには、従来からのファイル操作 を行うと便利で有る。 Object 変数は New キーワードを 用いてインスタンスを生成する。 CursorLocation プロパティの設 定はAccess 2000 以降では必須。

' コンボボックスへフィールド名の設定 Rs = New ADODB.Recordset

Rs.Open( "SELECT * FROM カテゴリ", Cn ) If Rs.RecordCount > 0 Then

For I = 1 To Rs.RecordCount

cboCate.Items.Add( Rs.Fields( "カテゴリ名" ).Value & "" ) Rs.MoveNext( )

Next End If Rs.Close( )

Rs.Open( "SELECT * FROM 著者", Cn ) If Rs.RecordCount > 0 Then

For I = 1 To Rs.RecordCount

cboWriter.Items.Add( Rs.Fields( "著者名" ).Value & "" ) Rs.MoveNext( ) Next End If Rs.Close( ) ' エクセルの設定 F = SD & "excelprint.xls" Ex = New Excel.Application Wb = Ex.Workbooks.Open( F ) Ws = Wb.Worksheets( "Sheet1" ) Ex.Visible = True End Sub ' フォームが破棄された時の処理

Private Sub ExcelPrint_Disposed( ByVal sender As Object, ByVal e As System.EventArgs ) _ Handles Me.Disposed ' オブジェクトの解放 Cn.Close( ) Rs = Nothing Cn = Nothing ' エクセルの終了 Ex.Application.DisplayAlerts = False Ex.Application.Quit( ) Ws = Nothing Wb = Nothing Ex = Nothing End Sub ' ボタン（終了）がクリックされた時の処理

Private Sub Button1_Click( ByVal sender As System.Object, ByVal e As System.EventArgs ) _ Handles Button1.Click ' フォーム自体をメモリから消去 Me.Dispose( ) ' プログラムを強制的に終了 End End Sub null データを文字列型と仕て操作 したり、数値化するとエラーが発 生する為、ヌルストリングを連結 して防いで居る。 MoveNext メソッドは、レコード セット内でカレントレコードを１ 行後に進めるメソッドで有る。 RecordCount プロパティには、抽 出したレコード数が格納される為 其の数丈のループで、総てのレコ ードを処理する事が出来る。 CreateObject ( "Excel.Application" ) でもオブジェクトを生成す る事が出来る。 デバック後に、Excel シートへの 書込状況を隠し度い場合は、此の 行のコメントアウトする。 此等の Object 変数は終了する時 にNothing を代入して破棄しない と、プログラム終了後もメモリに 残存する。 DisplayAlerts を False に設定す ると、変更点の保存を促すメッセ ージボックスが表示されない。 Dispose メソッドを実行すると、 自動的にフォームのDisposed イ ベントが発生する。

' ボタン（プレビュー、印刷）がクリックされた時の処理

Private Sub Buttons_Click( ByVal sender As System.Object, ByVal e As System.EventArgs ) _ Handles btnPreview.Click, btnPrint.Click

Dim SQL As String Dim C As Integer Dim I As Integer Dim F As Boolean ' データの抽出 SQL = SetSQL( ) Rs.Open( SQL, Cn ) If Rs.RecordCount > 0 Then C = 0 Call ClearWorksheet( ) Do Until Rs.EOF F = False ' データのエクセルへの書込 For I = 0 To 4

Ws.Range( Pos( C ).Pcol & CStr( Pos( C ).Prow( I ))).Value = Rs.Fields(I).Value & "" Ws.Range( Pos( C ).Pcol & CStr( Pos( C ).Prow( I ))).HorizontalAlignment = -4131 ' xlLeft Next

C += 1

' ８件分書込み時点で吐出 If C > 7 Then

If sender.Equals( btnPreview ) Then Ex.Visible = True : Ws.PrintPreview( ) Else Ws.PrintOut( ) End If C = 0 : F = True Call ClearWorksheet( ) End If Rs.MoveNext( ) Loop ' 未処理分の吐出 If Not F Then

If sender.Equals( btnPreview ) Then Ex.Visible = True : Ws.PrintPreview( ) Else Ws.PrintOut( ) End If Ex.Visible = False End If Else MsgBox( "抽出データは有りません！", MsgBoxStyle.Information, "結果" ) End If Rs.Close( ) End Sub 此処で宣言した変数は宣言したプ ロシージャを抜けると、自動的に 破棄される。 複数のトリガでイベントが発生す る様に、イベントハンドラを追加 して居る。 Recordset オブジェクトの EOF は、レコードポインタが、レコー ドの終端を超えた時に True と成 るプロパティで有る。 １ページ分のデータ（８件分）を セルに書込めば、一旦吐き出す。 １ページ分のデータを吐き出した 後、次処理の為に、セルをクリア する自作メソッドを呼び出す。 MoveNext メソッドは、レコード セット内でカレントレコードを１ 行後に進めるメソッドで有る。 ループ終了後に、未だ吐き出して 居ないデータが有れば、吐き出し を行う。 レコードセットが開いて居る状態 で再オープンする事は出来ない。

' ワークシートをクリアするジェネラルプロシージャ（自作メソッド） Private Sub ClearWorksheet( )

Dim I As Integer Dim J As Integer

For I = 0 To 7 For J = 0 To 4

Ws.Range( Pos( I ).Pcol & CStr( Pos( I ).Prow( J ))).Value = "" Next

Next End Sub

' SQL を生成するジェネラルプロシージャ（自作メソッド） Private Function SetSQL( ) As String

Dim W As String Dim S As String = "" Dim P1, P2 As String ' 条件節格納用文字列の初期化 W = "" ' 条件（タイトル）の設定【文字列データは ' でクォートする】 If Not txtTitle.Text = "" Then

S = txtTitle.Text

If radMethod1.Checked Then S = "%" & S & "%"

ElseIf radMethod2.Checked Then S = S & "%"

ElseIf radMethod3.Checked Then S = "%" & S

End If

If InStr( S, "%" ) > 0 Then

W = "タイトル LIKE '" & S & "'" Else W = "タイトル='" & S & "'" End If End If ' 条件（価格）の設定【数値型データはクォートしない】 P1 = txtPrice1.Text : P2 = txtPrice2.Text If Not P1 = "" Then If Not P2 = "" Then

S = "価格>=" & P1 & " AND 価格<=" & P2 Else

S = "価格>=" & P1 End If

If W = "" Then W = S Else W = W & " AND " & S

Sub プロシージャは、戻り値を返 さないプロシージャで有る。 Function プロシージャは、戻り値 を返すプロシージャで有る。 ADOBD の SQL でワイルドカー ドの表記は、*では無く、%で有る 事に注意を要する。 ワイルドカードとの比較を行う場 合の演算子は、=では無く、LIKE で有る事に注意を要する。 両方の価格欄に入力が為されて居 る時は、上限と下限が指定された 物と看做す。 左側の価格欄而巳の場合は、下限 が指定された物と看做す。

ElseIf Not P2 = "" Then S = "価格<=" & P2

If W = "" Then W = S Else W = W & " AND " & S End If ' 条件（購入日）の設定【日付型データは # でクォートする】 P1 = txtDate1.Text : P2 = txtDate2.Text If Not P1 = "" Then If Not P2 = "" Then

S = "購入日>=#" & P1 & "# AND 購入日<=#" & P2 & "#" Else

S = "購入日>=#" & P1 & "#" End If

If W = "" Then W = S Else W = W & " AND " & S ElseIf Not P2 = "" Then

S = "購入日<=#" & P2 & "#"

If W = "" Then W = S Else W = W & " AND " & S End If

' 条件（カテゴリ）の設定【文字列データは ' でクォートする】 If Not cboCate.SelectedIndex < 0 Then

S = "カテゴリ名='" & cboCate.SelectedItem.ToString & "'" If W = "" Then W = S Else W = W & " AND " & S End If

' 条件（著者）の設定【文字列データは ' でクォートする】 If Not cboWriter.SelectedIndex < 0 Then

S = "著者名='" & cboWriter.SelectedItem.ToString & "'" If W = "" Then W = S Else W = W & " AND " & S End If

' SQL 文の設定

S = "SELECT 本.タイトル,著者.著者名,カテゴリ.カテゴリ名,本.価格,本.購入日 " & _ "FROM 本,カテゴリ,著者 " & _

"WHERE 本.カテゴリ ID=カテゴリ.ID AND 本.著者 ID=著者.ID" If Not W = "" Then

S = S & " AND " & W End If

S &= " ORDER BY 本.ID;" ' 戻り値を設定して復帰 Return S End Function End Class 右側の価格欄而巳の場合は、上限 が指定された物と看做す。 Date 型の値をクォートする場合 クォーテーションでも良いが、一 般に、シャープを使用する。 上限と下限の指定に付いては、価 格の場合と同様に処理する。 Return は、呼出元に復帰するメソ ッドで、Function プロシージャの 場合は、戻り値を設定する事が出 来る。 ORDER BY 句では、ソートに使 用するフィールドを指定する。 If 文を 1 行で記述する場合は、末 尾にEnd If は付けない。

EXCEL の Worksheet オブジェクトの Range プロパティ Range オブジェクトを取得するプロパティ Object.Range( 引数 ) Object で指定したワークシートの特定のセル範囲を指定する。 引数には、マクロの言語のA1 形式での範囲を指定する。 範囲名には、範囲を表す演算子（:）、共通部分を表す演算子（スペース）、複数の範囲を表す演算子（,） を含める事が出来る。猶、ドル記号（$）は、含める事は出来るが、無視される。 亦、範囲の一部にローカルに定義した名前を使用する事も出来る。名前を使用する場合、其の名前はマ クロの言語と看做される。 此のプロパティは、セル範囲の Range オブジェクトの取得に用いられる。下記に、セル範囲の指定例 を示す。 Range( "A3" ) … 単一セルを取得 Range( "B2:E8" ) … セル範囲を取得 Range( Object.Cells( 1, 1 ), Object.Cells( 5, 4 ) ) … セル範囲を取得 Range( "範囲名" ) … セル範囲を取得

上記に於いて、３番目の例では、２個の引数を指定する構文を使用して居る。此の構文では、第１引数

で左上隅のセルを、第２引数で右下隅のセルを指定する。此の構文を利用すると、上記のCells プロパ

ティと併用して、R1C1 参照形式の様にセルを座標と仕て縦横共に数字番地で扱う事が出来る。

実際にセルを操作する場合には、Text プロパティや Value プロパティや Formula プロパティを使用し て、設定や取得する種類を特定する事が多い。

EXCEL の Worksheet オブジェクトの Cells プロパティ （参考）

Range オブジェクトを取得するプロパティ Object.Cells( 第１引数, 第２引数 ) Object で指定したワークシートの特定のセルを指定する。 引数１には、１から始まる行（Row）を表す数値を指定する。 引数２には、１から始まる列（Column）を表す数値を指定する。 R1C1 参照形式の様にセルを座標と仕て縦横共に数字番地で扱う場合に多用されるプロパティで、単一 セルのRange オブジェクトの取得に用いられる。

実際にセルを操作する場合には、Text プロパティや Value プロパティや Formula プロパティを使用し て、設定や取得する種類を特定する事が多い。 機 能 書 式 解 説 機 能 書 式 解 説

EXCEL の Range オブジェクトの Value プロパティ 指定したセルの値を設定・取得するプロパティ Object.Value = 値 セルの値を、設定する。オブジェクトにはRange オブジェクトを指定する。 値を取得する場合、セルが空の場合はEmpty 値が返される。此れを調べるには、IsEmpty 関数を使用 する。 Range オブジェクトに複数のセルが含まれて居る場合は、値の配列が返される。此れを調べるには IsArray 関数を使用する。

EXCEL の Worksheet オブジェクトの PrintPreview メソッド

印刷プレビュー（印刷時のイメージ）を表示するメソッド

Object.PrintPreview

オブジェクトにはWorksheet オブジェクトを指定する。

Excel オブジェクトを非表示に仕て居る場合は、此のメソッドを実行する前に、必ず、Excel オブジェ クトを表示させる必要が有る。即ち、Excel オブジェクトの Visible プロパティを True に設定する。

EXCEL の Worksheet オブジェクトの PrintOut メソッド

オブジェクトを印刷するメソッド Object.PrintOut オブジェクトにはWorksheet オブジェクトを指定する。 下記の引数を指定する事も出来る（孰れも、省略可能）。 第１引数で、印刷を開始するページの番号を指定する。 第２引数で、印刷を終了するページの番号を指定する。 第３引数で、印刷部数を指定する。 第４引数で、印刷をする前に印刷プレビューを実行するか何うかを指定する（True か False）。 第５引数で、アクティブなプリンタの名前を指定する。 第６引数で、ファイルへ出力するか何うかを指定する（True か False）。 第７引数で、部単位で印刷するか何うかを指定する（True か False）。 第８引数で、出力するファイルの名前を指定する（第６引数がTrue の場合）。 Preview メソッドの場合とは異なり、此のメソッドを実行する時、必ずしも Excel オブジェクトを表示 する必要は無い。即ち、非表示に仕て、Excel オブジェクトを、バックグラウンドで利用する事が可能 で有る。 機 能 書 式 解 説 機 能 書 式 解 説 機 能 書 式 解 説

EXCEL の DisplayAlerts プロパティ マクロの実行中に特定の警告やメッセージの表示を制御するプロパティ Object.DisplayAlerts = 値 値がTrue（既定値）の場合、マクロの実行中に特定の警告やメッセージを表示する。 値には、True か False のブール値を指定する。 マクロを実行して居る間に、ユーザーに入力を促すメッセージや警告メッセージを表示させない場合は 此のプロパティをFalse を設定する。猶、此のプロパティは、False に設定した儘、マクロの実行を終 了しても、自動的にTrue には戻らない。

Visual Basic から操作する場合、Excel シートに加えた変更を保存せずに終了する時には、此のプロパ

ティを False に設定して、『変更を保存しますか？』と謂うダイアログボックスを表示しない様にする 事が多い。 EXCEL の Quit メソッド Excel を終了するメソッド Object.Quit Object で指定した現在開いて居る Excel を終了する。 Quit メソッドを使用すると、開いて居るブックを未だ保存して居ない場合は、変更を保存するか何うか を確認するダイアログボックスが表示される。メッセージを表示させない場合は、Quit メソッドを実行 する前に、総てのブックを保存するか、又は、DisplayAlerts プロパティを False に設定する。 DisplayAlerts プロパティに False が設定されて居ると、確認メッセージは表示されず、変更したブッ クを保存しないで、Excel を終了する。 猶、ブックを保存しなくても、其のブックのSaved プロパティに True を設定すると、確認メッセージ を表示せずにExcel を終了させる事が出来る。 注意：此のメソッドを用いてExcel を正しく終了しない場合、例えば、エラー等が発生し実行が途中で 中断された様な場合には、Excel は、終了しないで、メモリに残存した儘と成る。此れを何度も繰り返 すと、メモリ（リソース）不足と成り、システムがフリーズする事が有る。特に、Excel を非表示に仕 て居る場合は、タスクバーにも表示されない為、注意を要する。此の様な場合には、Ctrl+Alt+Delete で『プログラムの強制終了』ダイアログボックスを開き、Excel が残存して居ないか確認し、残存して 居れば終了する。

参考：Excel を終了するには、上記の Quit メソッドの他に、Close メソッドを使用する方法も有る。両 者の違いは、Quit メソッドが Application オブジェクトを対象に仕て居るのに対し、Close メソッドが Workbooks コレクション、又は、Workbook オブジェクトを対象に仕て居る点で有る。其の為、Quit メソッドは、エクセルを終了し、Close メソッドは、ワークブックを閉じると謂うのが、夫々れのメソ ッドの機能で有ると謂える。 機 能 書 式 解 説 機 能 書 式 解 説