VB実用⑯　印刷Ⅵ（Excel）

Size: px

Start display at page:

Download "VB実用⑯　印刷Ⅵ（Excel）"

みさきよどぎみ
4 years ago
Views:

印刷 Ⅴ VB 2005 5 プログラムの概要事務処理に於いて集計結果等を印刷して確認等を行う事も多い為ペーパーレスオフィスが推奨される昨今に於いても矢張り印刷に関する技術は必要で有る Visual Basic から印刷を行う方法は.

1 印刷 Ⅴ VB プログラムの概要事務処理に於いて集計結果等を印刷して確認等を行う事も多い為ペーパーレスオフィスが推奨される昨今に於いても矢張り印刷に関する技術は必要で有る Visual Basic から印刷を行う方法は.NET 以降 PrintDocument オブジェクトを使用する方法が標準と仕て一般的で有るが Professional 版等では ReportView や CrystalReport を利用する事も可能で有る亦 Power Packs 2 では視覚的確認が容易な FormPrint がを向上させて復活して居る此処では Access のデータを条件を設定して抽出し各種を設定した Excel シートに出力位置を記述したデータファイルを利用して吐き出し Excel の印刷を使用して印刷を行う今回の課題項目 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 メソッド ) -1-

2 ~ オブジェクトプロパティ一覧グループボックス 1 テキストボックス1 テキストボックス2 テキストボックス3 テキストボックス 4 テキストボックス 5 ラベル 1 ラベル 7 グループボックス 2 コンボボックス 1 コンボボックス 2 ラジオボタン 1~ ラジオボタン 4 ボタン 1 ボタン 2 ボタン 3 コントロールの種類プロパティプロパティの設定値フォーム name ExcelPrint FormBorderStyle FixedSingle StartPosition CenterScreen EXCEL プリントグループボックス1 name grpsearch 検索条件グループボックス2 name grpmethod タイトル項目の検索方法ラベル1~7 name lblitem1 ~ lblitem5 AutoSize False Font MS 明朝太字 10 lblitem1: タイトル lblitem2: 価格 lblitem3: 購入日 lblitem4: カテゴリ ID lblitem5: 著者 ID lblitem6:~ lblitem7:~ Align MiddleRight -2-

3 テキストボックス1 name txttitle テキストボックス2 name txtprice1 Align Right テキストボックス3 name txtprice2 Align Right テキストボックス4 name txtdate1 テキストボックス5 name txtdate2 コンボボックス1 name cbocate コンボボックス2 name cbowriter ラジオボタン1~4 name radmethod1 ~ radmethod4 Checked radmethod1:true radmethod1: 部分一致 radmethod2: 前方一致 radmethod3: 後方一致 radmethod4: 完全一致コマンドボタン1 name btnpreview Font MS 明朝太字 12 プレビューコマンドボタン2 name btnexecute Font MS 明朝太字 12 印刷コマンドボタン3 name btnfinish Font MS 明朝太字 12 終了参照設定事前に下記のライブラリを参照設定して置く ActiveX Data Object x.x Library Microsoft Excel x.x Object Library x.x はバージョンに依り異なる -3-

4 Excel シートの作成事前に下記の様な Excel シートを作成しプログラムと同じディレクトリに excelprint.xls と謂うファイル名で保存して置く帳票類の印刷では設定等の容易な Excel で帳票の雛型を作成して置く方法が便利で有る -4-

5 プログラムリスト 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 ) 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 )) FileClose( 1 ) StartupPath はプログラムを起動した EXE ファイルのパス ( 位置 ) を表す Excel のセルへの書込位置は此の様に別ファイルに仕た方がメンテナンスが容易に成るファイルからデータ単位で読み込むには従来からのファイル操作を行うと便利で有る Object 変数は New キーワードを用いてインスタンスを生成する ' コネクションの確立 CursorLocation プロパティの設 F = SD & "data.mdb" 定は Access 2000 以降では必須 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( ) -5-

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

7 ' ボタン ( プレビュー印刷 ) がクリックされた時の処理 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( ) Recordset オブジェクトの EOF Do Until Rs.EOF はレコードポインタがレコー F = False ドの終端を超えた時に True と成 ' データのエクセルへの書込るプロパティで有る 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 = ' xlleft C += 1 1ページ分のデータ (8 件分 ) を ' 8 件分書込み時点で吐出セルに書込めば一旦吐き出す If C > 7 Then If sender.equals( btnpreview ) Then Ex.Visible = True : Ws.PrintPreview( ) 1ページ分のデータを吐き出した Else 後次処理の為にセルをクリア Ws.PrintOut( ) する自作メソッドを呼び出す C = 0 : F = True Call ClearWorksheet( ) Move メソッドはレコードセット内でカレントレコードを1 Rs.Move( ) 行後に進めるメソッドで有る Loop ' 未処理分の吐出 If Not F Then ループ終了後に未だ吐き出して If sender.equals( btnpreview ) Then 居ないデータが有れば吐き出し Ex.Visible = True : Ws.PrintPreview( ) を行う Else Ws.PrintOut( ) Ex.Visible = False Else MsgBox( " 抽出データは有りません!", MsgBoxStyle.Information, " 結果 " ) レコードセットが開いて居る状態 Rs.Close( ) で再オープンする事は出来ない End Sub -7-

8 ' ワークシートをクリアするジェネラルプロシージャ ( 自作メソッド ) Private Sub ClearWorksheet( ) Sub プロシージャは戻り値を返 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 = "" End Sub ' SQL を生成するジェネラルプロシージャ ( 自作メソッド ) Private Function SetSQL( ) As String Dim W As String Dim S As String = "" Dim P1, P2 As String Function プロシージャは戻り値を返すプロシージャで有る ' 条件節格納用文字列の初期化 W = "" ' 条件 ( タイトル ) の設定文字列データは ' でクォートする If Not txttitle. = "" Then S = txttitle. If radmethod1.checked Then S = "%" & S & "%" ElseIf radmethod2.checked Then S = S & "%" ElseIf radmethod3.checked Then S = "%" & S If InStr( S, "%" ) > 0 Then W = " タイトル LIKE '" & S & "'" Else W = " タイトル ='" & S & "'" ADOBD の SQL でワイルドカードの表記は * では無く % で有る事に注意を要するワイルドカードとの比較を行う場合の演算子は = では無く LIKE で有る事に注意を要する ' 条件 ( 価格 ) の設定数値型データはクォートしない P1 = txtprice1. : P2 = txtprice2. If Not P1 = "" Then If Not P2 = "" Then S = " 価格 >=" & P1 & " AND 価格 <=" & P2 Else S = " 価格 >=" & P1 If W = "" Then W = S Else W = W & " AND " & S -8- 両方の価格欄に入力が為されて居る時は上限と下限が指定された物と看做す左側の価格欄而巳の場合は下限が指定された物と看做す

9 ElseIf Not P2 = "" Then S = " 価格 <=" & P2 If W = "" Then W = S Else W = W & " AND " & S Date 型の値をクォートする場合 ' 条件 ( 購入日 ) の設定日付型データは # でクォートするクォーテーションでも良いが一 P1 = txtdate1. : P2 = txtdate2. 般にシャープを使用する If Not P1 = "" Then If Not P2 = "" Then S = " 購入日 >=#" & P1 & "# AND 購入日 <=#" & P2 & "#" Else 上限と下限の指定に付いては価 S = " 購入日 >=#" & P1 & "#" 格の場合と同様に処理する 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 If 文を 1 行で記述する場合は末尾には付けない ' 条件 ( カテゴリ ) の設定文字列データは ' でクォートする If Not cbocate.selectedindex < 0 Then S = " カテゴリ名 ='" & cbocate.selecteditem.tostring & "'" If W = "" Then W = S Else W = W & " AND " & S ' 条件 ( 著者 ) の設定文字列データは ' でクォートする If Not cbowriter.selectedindex < 0 Then S = " 著者名 ='" & cbowriter.selecteditem.tostring & "'" If W = "" Then W = S Else W = W & " AND " & S 右側の価格欄而巳の場合は上限が指定された物と看做す ' SQL 文の設定 S = "SELECT 本. タイトル, 著者. 著者名, カテゴリ. カテゴリ名, 本. 価格, 本. 購入日 " & _ "FROM 本, カテゴリ, 著者 " & _ "WHERE 本. カテゴリ ID= カテゴリ.ID AND 本. 著者 ID= 著者.ID" If Not W = "" Then S = S & " AND " & W ORDER BY 句ではソートに使用するフィールドを指定する S &= " ORDER BY 本.ID;" ' 戻り値を設定して復帰 Return S End Function Return は呼出元に復帰するメソッドで Function プロシージャの場合は戻り値を設定する事が出来る End Class -9-

10 EXCEL の Worksheet オブジェクトの Range プロパティ Range オブジェクトを取得するプロパティ Object.Range( 引数 ) Object で指定したワークシートの特定のセル範囲を指定する引数にはマクロの言語の A1 形式での範囲を指定する範囲名には範囲を表す演算子 (:) 共通部分を表す演算子 ( スペース ) 複数の範囲を表す演算子 (,) を含める事が出来る猶ドル記号 ($) は含める事は出来るが無視される亦範囲の一部にローカルに定義した名前を使用する事も出来る名前を使用する場合其の名前はマクロの言語と看做される此のプロパティはセル範囲の Range オブジェクトの取得に用いられる下記にセル範囲の指定例を示す Range( "A3" ) Range( "B2:E8" ) Range( Object.Cells( 1, 1 ), Object.Cells( 5, 4 ) ) Range( " 範囲名 " ) 単一セルを取得セル範囲を取得セル範囲を取得セル範囲を取得上記に於いて 3 番目の例では 2 個の引数を指定する構文を使用して居る此の構文では第 1 引数で左上隅のセルを第 2 引数で右下隅のセルを指定する此の構文を利用すると上記の Cells プロパティと併用して R1C1 参照形式の様にセルを座標と仕て縦横共に数字番地で扱う事が出来る実際にセルを操作する場合にはプロパティや Value プロパティや Formula プロパティを使用して設定や取得する種類を特定する事が多い EXCEL の Worksheet オブジェクトの Cells プロパティ ( 参考 ) Range オブジェクトを取得するプロパティ Object.Cells( 第 1 引数, 第 2 引数 ) Object で指定したワークシートの特定のセルを指定する引数 1 には 1 から始まる行 (Row) を表す数値を指定する引数 2 には 1 から始まる列 (Column) を表す数値を指定する R1C1 参照形式の様にセルを座標と仕て縦横共に数字番地で扱う場合に多用されるプロパティで単一セルの Range オブジェクトの取得に用いられる実際にセルを操作する場合にはプロパティや Value プロパティや Formula プロパティを使用して設定や取得する種類を特定する事が多い -10-

11 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 オブジェクトを指定する下記の引数を指定する事も出来る ( 孰れも省略可能 ) 第 1 引数で印刷を開始するページの番号を指定する第 2 引数で印刷を終了するページの番号を指定する第 3 引数で印刷部数を指定する第 4 引数で印刷をする前に印刷プレビューを実行するか何うかを指定する (True か False) 第 5 引数でアクティブなプリンタの名前を指定する第 6 引数でファイルへ出力するか何うかを指定する (True か False) 第 7 引数で部単位で印刷するか何うかを指定する (True か False) 第 8 引数で出力するファイルの名前を指定する ( 第 6 引数が True の場合 ) Preview メソッドの場合とは異なり此のメソッドを実行する時必ずしも Excel オブジェクトを表示する必要は無い即ち非表示に仕て Excel オブジェクトをバックグラウンドで利用する事が可能で有る -11-

12 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 メソッドはワークブックを閉じると謂うのが夫々れのメソッドので有ると謂える -12-

VB実用⑦　エクセル操作Ⅰ

VB実用⑦　エクセル操作Ⅰ VB でエクセル操作 Ⅰ VB 2005 7 プログラムの概要事務処理に於いて Microsoft 社のスプレッドシートソフトで有るエクセルはデータベースソフトで有るアクセスと共に業界標準 (De Facto Standard) で有ると謂う事が出来る此処では其のエクセルを Visual Basic から操作する方法を重点的に学ぶ今回は Visual Basic でエクセルを利用する基本と成るオブジェクト生成と

VB実用⑯ 印刷Ⅵ（Excel）

VB実用⑯　印刷Ⅵ（Excel）