データベーステーブルの帳票印刷 VB.NET
環境 Windows11 23H2 & Visual Studio 2022 & SQLServer 2022 テーブルは、取引先マスター を使用。
メイン画面
処理選択メニューバー
設定メニューバー
設定ファイル登録
Printerコンボ選択
PaperSizeコンボ選択
用紙方向コンボ選択
印刷プレビュー
ソリューション
Form1.vb
Imports System.Data.OleDb
Imports System.Drawing.Drawing2D
Imports System.Drawing.Printing
Imports System.IO
Imports System.Text
Imports System.Windows.Forms.VisualStyles.VisualStyleElement
Public Class Form1
'印刷の定義
Private doc As PrintDocument = Nothing
'最大行数( 1ページに印刷する行数 )
Private rowMax As Integer
'ページ数
Private pageCount As Integer = 0
'行数(印字する行位置)
Private prtRow As Integer = 1
' 印字用フォント
Private prtFon12 As Font = New Font("ＭＳ 明朝", 12, FontStyle.Regular)
Private prtFon14 As Font = New Font("ＭＳ 明朝", 14, FontStyle.Regular)
' 印字用テキスト
Private prtText As System.Drawing.Graphics
' マージン
Private TopMargin As Integer
Private LeftMargin As Integer
' 外部データ
Private ReadFile As StreamReader
Private LineText As String
Private co As New OleDb.OleDbConnection
Private rs As OleDb.OleDbDataReader
Private cmd As New OleDb.OleDbCommand
Private rsRet As Boolean = False
'-------------------------------------------------------
' フォーム初期処理
'-------------------------------------------------------
Private Sub Form1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
INI()
Me.KeyPreview = True
'印刷処理実体の作成
doc = New PrintDocument()
'プリンター名セット
TextBox1.Text = doc.PrinterSettings.PrinterName.ToString
ComboBox1.Items.Clear()
Dim PrinterName As String
For Each PrinterName _
In Printing.PrinterSettings.InstalledPrinters
ComboBox1.Items.Add(PrinterName)
Next
'用紙サイズセット
TextBox2.Text = "A4"
ComboBox2.Items.Clear()
Dim PaperSize As System.Drawing.Printing.PaperSize
For Each PaperSize _
In doc.PrinterSettings.PaperSizes
ComboBox2.Items.Add(PaperSize.PaperName)
Next
'用紙方向セット
TextBox3.Text = "縦"
ComboBox3.Items.Clear()
ComboBox3.Items.Add("縦")
ComboBox3.Items.Add("横")
'既定プリンター名のセット
If (Not String.IsNullOrEmpty(prtName)) Then
TextBox1.Text = prtName
Else
' Nothing、もしくは空文字列である
End If
End Sub
'-------------------------------------------------------
' keyDown処理 Endボタンで終了
'-------------------------------------------------------
Private Sub Form1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles MyBase.KeyDown
If e.KeyCode = Keys.End Then
Me.Close()
End If
End Sub
'-------------------------------------------------------
' keyPress処理 Enter = tab
'-------------------------------------------------------
Private Sub Form1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles MyBase.KeyPress
If e.KeyChar = Chr(13) Then
SendKeys.Send("{TAB}")
e.Handled = True
End If
End Sub
'-------------------------------------------------------
' 終了ボタン処理
'-------------------------------------------------------
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Me.Close()
End Sub
'-------------------------------------------------------
' 終了メニュー処理
'-------------------------------------------------------
Private Sub 終了ToolStripMenuItem1_Click(sender As Object, e As EventArgs) Handles 終了ToolStripMenuItem1.Click
Me.Close()
End Sub
'-------------------------------------------------------
' 設定メニュー処理 Para.iniの登録へ
'-------------------------------------------------------
Private Sub 設定ToolStripMenuItem1_Click(sender As Object, e As EventArgs) Handles 設定ToolStripMenuItem1.Click
Dim iform2 As New Form2
iform2.ShowDialog()
End Sub
'-------------------------------------------------------
' 印刷ボタン処理
'-------------------------------------------------------
Private Sub Button1_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Button1.Click
'印刷開始
'印刷処理実体の作成
doc = New PrintDocument()
' プレビューのプロパティにセット
PrintPreviewDialog1.Document = doc
'プリンター指定
doc.PrinterSettings.PrinterName = TextBox1.Text().ToString
' 用紙の向きを設定(横：True、縦：False)
doc.DefaultPageSettings.Landscape = False
If TextBox3.Text = "横" Then
doc.DefaultPageSettings.Landscape = True
End If
'用紙サイズ指定
Dim paperSize As PaperSize
With doc.PrinterSettings
For Each paperSize In .PaperSizes
If paperSize.PaperName = TextBox2.Text().ToString Then
doc.DefaultPageSettings.PaperSize = paperSize
Exit For
End If
Next
End With
'印刷イベントをプレビューから呼び出せるように登録する
'印刷前処理
AddHandler doc.BeginPrint, AddressOf doc_BeginPrint
'印刷 処理
AddHandler doc.PrintPage, AddressOf doc_PrintPage
'印刷後処理
AddHandler doc.EndPrint, AddressOf doc_EndPrint
PrintPreviewDialog1.ShowDialog()
End Sub
'-------------------------------------------------------
' プレビューダイアログの初期処理
'-------------------------------------------------------
Private Sub PrintPreviewDialog1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles PrintPreviewDialog1.Load
'デスクトップ左上
PrintPreviewDialog1.DesktopLocation = New System.Drawing.Point(0, 0)
'ダイアログの大きさ
PrintPreviewDialog1.Width = 800
PrintPreviewDialog1.Height = 600
'倍率
PrintPreviewDialog1.PrintPreviewControl.Zoom = 1
End Sub
'-------------------------------------------------------
'プリンター名コンボ処理
'-------------------------------------------------------
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
TextBox1.Text = ComboBox1.Text
End Sub
'-------------------------------------------------------
'用紙サイズコンボ処理
'-------------------------------------------------------
Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
TextBox2.Text = ComboBox2.Text
End Sub
'-------------------------------------------------------
'用紙方向コンボ処理
'-------------------------------------------------------
Private Sub ComboBox3_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox3.SelectedIndexChanged
TextBox3.Text = ComboBox3.Text
End Sub
'-------------------------------------------------------
' 印刷の前処理
'-------------------------------------------------------
Private Sub doc_BeginPrint(ByVal sender As System.Object,
ByVal e As System.Drawing.Printing.PrintEventArgs)
Dim Count As Integer
Try
co.ConnectionString = CoString
co.Open()
cmd.Connection() = co
cmd.CommandText = "select * from 取引先マスター order by 取引先コード"
Count = cmd.ExecuteScalar()
rs = cmd.ExecuteReader()
Catch ex As OleDbException
MsgBox("データベース接続エラーです。" & vbCr _
& ex.Message & vbCr _
& "終了します")
e.Cancel = True
Return
End Try
' 用意したレコードセットにデータが無い場合印字処理を行わない
If Count = 0 Then
MessageBox.Show("印字データが存在しません")
e.Cancel = True
Return
End If
End Sub
'-------------------------------------------------------
' 印刷の後処理
'-------------------------------------------------------
Private Sub doc_EndPrint(ByVal sender As System.Object,
ByVal e As System.Drawing.Printing.PrintEventArgs)
'接続解除
If Not co Is Nothing Then
co.Close()
End If
End Sub
'-------------------------------------------------------
' 印刷処理
'-------------------------------------------------------
Private Sub doc_PrintPage(ByVal sender As System.Object,
ByVal e As System.Drawing.Printing.PrintPageEventArgs)
'左側余白
LeftMargin = e.MarginBounds.Left
'上側余白
TopMargin = e.MarginBounds.Top
'現在のコンテキストを取得
prtText = e.Graphics
'印刷に必要なデータを取得します
GetPrtData(e)
'１ページの行数セット
rowMax = 55
If TextBox2.Text = "A4" And TextBox3.Text = "横" Then
rowMax = 39
End If
'---------------------------------------
'* 初期処理
'---------------------------------------
pageCount += 1
prtRow = 4
'---------------------------------------
'印字処理
'---------------------------------------
Do
'データが印字できない場合は
'データを読み込まないようにする
If prtRow > rowMax Then
'見出し印字
headerForm(e)
prtRow = 4
'ページ内にデータ印字できないので出る
Exit Do
End If
'次行読み込み
rsRet = rs.Read()
'データがなくなった場合
'データがない場合は、空ページを印字してしまうので
'呼び出す前にチェックする
If rsRet = False Then
'見出し印字
headerForm(e)
pageCount = 0
prtRow = 4
'データ終了なので印刷処理終了
e.HasMorePages = False
Return
End If
'データ印字
PrtString(prtRow, 50, rs.Item("取引先コード"), prtFon12)
PrtString(prtRow, 120, rs.Item("取引先名"), prtFon12)
PrtString(prtRow, 450, rs.Item("取引先区分"), prtFon12)
'行数へ1を加算
prtRow += 1
Loop
' 次ページがあり
e.HasMorePages = True
End Sub
'-------------------------------------------------------
' 印字に必要なデータの取得
'-------------------------------------------------------
Private Sub GetPrtData(ByVal e As System.Drawing.Printing.PrintPageEventArgs)
' 左側余白
LeftMargin = 20
' 上側余白
TopMargin = 20
' 現在のコンテキストを取得
prtText = e.Graphics
End Sub
'-------------------------------------------------------
' 文字列を指定行の先頭から印字
'-------------------------------------------------------
Private Sub PrtString(ByVal row As Integer,
ByVal xPos As Integer,
ByVal str As String,
Optional ByVal TargetFont As Font = Nothing)
If TargetFont Is Nothing Then
TargetFont = prtFon12
End If
Dim yPos As Integer = 0
'1行を20と設定
yPos = TopMargin + (row - 1) * 20
'印字可能な左端から文字列をセットする
prtText.DrawString(str, TargetFont, Brushes.Black, LeftMargin + xPos, yPos)
End Sub
'青印字
Private Sub PrtString_blue(ByVal row As Integer,
ByVal xPos As Integer,
ByVal str As String,
Optional ByVal TargetFont As Font = Nothing)
If TargetFont Is Nothing Then
TargetFont = prtFon12
End If
Dim yPos As Integer = 0
'1行を20と設定
yPos = TopMargin + (row - 1) * 20 '1行を20と設定
' 印字可能な左端から文字列をセットする
prtText.DrawString(str, TargetFont, Brushes.Blue, LeftMargin + xPos, yPos)
End Sub
'--------------------------------------------------------
' 見出しデータ印字
'--------------------------------------------------------
Private Sub headerForm(ByVal e As System.Drawing.Printing.PrintPageEventArgs)
PrtString_blue(1, 200, "＊＊ 取引先マスター一覧表 ＊＊", prtFon14)
Dim StrPage As String = " " + pageCount.ToString("#,0")
StrPage = StrPage.Substring(StrPage.Length - 3)
PrtString(1, 680, "page : " + StrPage, prtFon12)
PrtString_blue(3, 0, "取引先コード", prtFon12)
PrtString_blue(3, 120, "取引先名", prtFon12)
PrtString_blue(3, 400, "取引先区分", prtFon12)
' 横線設定
'Penオブジェクトの作成(幅0.1の黒色)
Dim blackPen As New Pen(Color.Black, 0.1)
'ダッシュ
blackPen.DashStyle = DashStyle.Solid
'明細行へ横線印字
Dim i As Integer
For i = 1 To rowMax - 1
prtText.DrawLine(blackPen, 20, 39 + i * 20, 800, 39 + i * 20)
Next
'縦線印字
prtText.DrawLine(blackPen, 20, 59, 20, (rowMax + 1) * 20)
prtText.DrawLine(blackPen, 130, 59, 130, (rowMax + 1) * 20)
prtText.DrawLine(blackPen, 400, 59, 400, (rowMax + 1) * 20)
prtText.DrawLine(blackPen, 800, 59, 800, (rowMax + 1) * 20)
'次に印字する行
prtRow = 4
End Sub
'-------------------------------------------------------
' 印刷メニュー処理
'-------------------------------------------------------
Private Sub 印刷ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 印刷ToolStripMenuItem.Click
'開始
'印刷処理の実体の作成
doc = New PrintDocument()
' プレビューのプロパティにセット
PrintPreviewDialog1.Document = doc
'プリンター指定
doc.PrinterSettings.PrinterName = TextBox1.Text().ToString
'用紙の向きを設定(横：True、縦：False)
doc.DefaultPageSettings.Landscape = False
If TextBox3.Text = "横" Then
doc.DefaultPageSettings.Landscape = True
End If
'用紙サイズ指定
Dim paperSize As PaperSize
With doc.PrinterSettings
For Each paperSize In .PaperSizes
If paperSize.PaperName = TextBox2.Text().ToString Then
doc.DefaultPageSettings.PaperSize = paperSize
Exit For
End If
Next
End With
'印刷イベントをプレビューから呼び出せるように登録する
'印刷前処理
AddHandler doc.BeginPrint, AddressOf doc_BeginPrint
'印刷 処理
AddHandler doc.PrintPage, AddressOf doc_PrintPage
'印刷後処理
AddHandler doc.EndPrint, AddressOf doc_EndPrint
PrintPreviewDialog1.ShowDialog()
End Sub
End Class
Form2.vb
Imports System.Windows.Forms.VisualStyles.VisualStyleElement
Public Class Form2
Private Sub Form2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim stCurrentDir As String = System.IO.Directory.GetCurrentDirectory()
Dim n2 As Integer = FreeFile()
Dim st As String
Me.KeyPreview = True
Try
n = FreeFile()
FileOpen(n, stCurrentDir & "\PARA.ini", OpenMode.Input)
TextBox1.Text = LineInput(n)
TextBox2.Text = LineInput(n)
TextBox3.Text = LineInput(n)
TextBox4.Text = LineInput(n)
TextBox5.Text = LineInput(n)
TextBox6.Text = LineInput(n)
FileClose(n)
Catch
FileOpen(n2, stCurrentDir & "\PARA.ini", OpenMode.Output)
st = ""
PrintLine(n2, st)
PrintLine(n2, st)
PrintLine(n2, st)
PrintLine(n2, st)
PrintLine(n2, st)
PrintLine(n2, st)
FileClose(n2)
End Try
ComboBox1.Items.Clear()
Dim PrinterName As String
For Each PrinterName _
In Printing.PrinterSettings.InstalledPrinters
ComboBox1.Items.Add(PrinterName)
Next
End Sub
'-------------------------------------------------------
'プリンター名コンボ処理
'-------------------------------------------------------
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
TextBox6.Text = ComboBox1.Text
End Sub
'-------------------------------------------------------
'登録ボタン処理
'-------------------------------------------------------
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim stCurrentDir As String = System.IO.Directory.GetCurrentDirectory()
Dim n2 As Integer = FreeFile()
Dim st As String
FileOpen(n2, stCurrentDir & "\PARA.ini", OpenMode.Output)
st = TextBox1.Text
PrintLine(n2, st)
st = TextBox2.Text
PrintLine(n2, st)
st = TextBox3.Text
PrintLine(n2, st)
st = TextBox4.Text
PrintLine(n2, st)
st = TextBox5.Text
PrintLine(n2, st)
st = TextBox6.Text
PrintLine(n2, st)
FileClose(n2)
End Sub
'-------------------------------------------------------
'終了ボタン処理
'-------------------------------------------------------
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Me.Close()
End Sub
'-------------------------------------------------------
'keyDown処理
'-------------------------------------------------------
Private Sub Form2_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles MyBase.KeyDown
If e.KeyCode = Keys.End Then
Me.Close()
End If
End Sub
'-------------------------------------------------------
'keyPress処理
'-------------------------------------------------------
Private Sub Form2_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles MyBase.KeyPress
If e.KeyChar = Chr(13) Then
SendKeys.Send("{TAB}")
e.Handled = True
End If
End Sub
End ClassModule.vb
Imports System.Data.OleDb
Module Module1
Public F1 As Form1
Public fname As System.IO.StreamReader
Public SYSTEM_NAME As String
Public USER_NAME As String
Public PASSWORD As String
Public SERVER As String
Public LIBL As String
Public prtName As String
Public F2Str As String
Public n As Integer = FreeFile()
Public CoString As String
Public Sub INI()
Try
Dim stCurrentDir As String = System.IO.Directory.GetCurrentDirectory()
n = FreeFile()
FileOpen(n, stCurrentDir & "\PARA.ini", OpenMode.Input)
SYSTEM_NAME = LineInput(n)
USER_NAME = LineInput(n)
PASSWORD = LineInput(n)
SERVER = LineInput(n)
LIBL = LineInput(n)
prtName = LineInput(n)
FileClose(n)
Catch
MsgBox("設定ファイルエラー:エラーＮＯ:" &
Err.Number & vbCr & Err.Description & vbCr &
"設定してください。")
End Try
CoString = "Provider=SQLOLEDB.1;Data Source=" & SYSTEM_NAME _
& ";Initial Catalog=" & LIBL _
& ";Password=" & PASSWORD & ";User ID=" & USER_NAME & ";"
End Sub
End Module
