DataGridViewによる取引データ入力 VB.NET
環境 Windows11 23H2 & Visual Studio 2022 & SQLServer 2022 ( & WebView2 )
DataGridViewを変更してクラス CustomDataGridView_Ent を作成。EnterキーでTabキー動作をする。テーブル内のコピー＆貼り付けが可能。
メイン画面
設定メニュー
設定メニュー
取引先検索
該当行をクリック選択ボタンでセルへ値をセット。あるいはダブルクリック。
コピーと貼り付け
コピー、貼り付けを行う場合は、EditModeをEditMode切替ボタンでEditOnEnterからEditOnKeystrokeへ切替ます。入力時は、EditOnEnterへ切替。
（Ctrl）＋ C でコピー
（Ctrl）+ V で貼り付け
ソリューション
Form1.vb
Imports System.Data.OleDb
Imports System.Drawing.Drawing2D
Imports System.Drawing.Printing
Imports System.IO
Imports System.Net.WebRequestMethods
Imports System.Reflection
Imports System.Windows.Forms.VisualStyles.VisualStyleElement
Imports PdfGeneratorNetFree
Imports PdfGeneratorNetFree.PdfContentItem
Imports PdfGeneratorNetFree.PgnEnum
Imports PdfGeneratorNetFree.PgnStyle
Imports File = System.IO.File
Imports PaperSize = PdfGeneratorNetFree.PgnStyle.PaperSize
Public Class Form1
'入力値
Private intTextBox1 As Integer = 0
Private intTextBox2 As Integer = 0
Private rsRet As Boolean = False
Private _editingColumn As Integer
'-------------------------------------------------------
' フォーム初期処理
'-------------------------------------------------------
Private Sub Form1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
INI()
Me.KeyPreview = True
'EditModeの指定
CustomDataGridView_Ent1.EditMode = DataGridViewEditMode.EditOnEnter
'column_headerの色指定
CustomDataGridView_Ent1.EnableHeadersVisualStyles = False
CustomDataGridView_Ent1.ColumnHeadersDefaultCellStyle.ForeColor = Color.Black
CustomDataGridView_Ent1.ColumnHeadersDefaultCellStyle.BackColor = Color.AliceBlue
CustomDataGridView_Ent1.Columns(6).HeaderCell.Style.ForeColor = Color.Black
CustomDataGridView_Ent1.Columns(6).HeaderCell.Style.BackColor = Color.Aqua
CustomDataGridView_Ent1.Columns(8).HeaderCell.Style.ForeColor = Color.Black
CustomDataGridView_Ent1.Columns(8).HeaderCell.Style.BackColor = Color.Aqua
CustomDataGridView_Ent1.Columns(12).HeaderCell.Style.ForeColor = Color.Black
CustomDataGridView_Ent1.Columns(12).HeaderCell.Style.BackColor = Color.Aqua
'row_headerの幅指定
CustomDataGridView_Ent1.RowHeadersWidth = 60
'row_headerの色＆位置指定
CustomDataGridView_Ent1.RowHeadersDefaultCellStyle.BackColor = Color.AliceBlue
CustomDataGridView_Ent1.RowHeadersDefaultCellStyle.ForeColor = Color.Black
CustomDataGridView_Ent1.RowHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
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
End
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
'-------------------------------------------------------
' keyPres開始取引日処理 Enter = tab
'-------------------------------------------------------
Private Sub TextBox1_Leave(sender As Object, e As EventArgs) Handles TextBox1.Leave
Dim ret As Boolean = Integer.TryParse(TextBox1.Text, intTextBox1)
If IsDate(Format(intTextBox1, "####/##/##")) = False And intTextBox1 <> 0 Then
MsgBox("取引日エラー")
TextBox1.Focus()
Exit Sub
End If
End Sub
'-------------------------------------------------------
' keyPres終了取引日処理 Enter = tab
'-------------------------------------------------------
Private Sub TextBox2_Leave(sender As Object, e As EventArgs) Handles TextBox2.Leave
Dim ret As Boolean = Integer.TryParse(TextBox2.Text, intTextBox2)
If IsDate(Format(intTextBox2, "####/##/##")) = False And intTextBox2 <> 0 Then
MsgBox("取引日エラー")
TextBox2.Focus()
Exit Sub
End If
If intTextBox2 < intTextBox1 And intTextBox1 <> 0 And intTextBox2 <> 0 Then
MsgBox("取引日範囲エラー")
TextBox2.Focus()
Exit Sub
End If
Call Begin_sub()
End Sub
'-------------------------------------------------------
'textBox1 GotFocusの処理
'-------------------------------------------------------
Private Sub textBox1_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.GotFocus
TextBox1.SelectionStart = 0
TextBox1.SelectionLength = Len(TextBox1.Text)
End Sub
'-------------------------------------------------------
'textBox2 GotFocusの処理
'-------------------------------------------------------
Private Sub textBox2_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox2.GotFocus
TextBox2.SelectionStart = 0
TextBox2.SelectionLength = Len(TextBox2.Text)
End Sub
'-------------------------------------------------------
'取消ボタンの処理
'-------------------------------------------------------
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
CustomDataGridView_Ent1.Rows.Clear()
TextBox1.Focus()
End Sub
'-------------------------------------------------------
' 終了ボタン処理
'-------------------------------------------------------
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
End
End Sub
'-------------------------------------------------------
' 終了メニュー処理
'-------------------------------------------------------
Private Sub 終了ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 終了ToolStripMenuItem.Click
End
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 Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
Dim iform4 As New Form4
iform4.ShowDialog()
CustomDataGridView_Ent1.Focus()
End Sub
'-------------------------------------------------------
'検索値のセット
'-------------------------------------------------------
Private Sub DataGridView1_CellEnter(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs) Handles CustomDataGridView_Ent1.CellEnter
If Gsentaku = 1 Then
CustomDataGridView_Ent1.CurrentCell.Value = Gtorihikisaki_code
Gsentaku = 0
End If
End Sub
'-------------------------------------------------------
' 登録ボタン処理
'-------------------------------------------------------
Private Sub Button1_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Button1.Click
Dim Count As Integer = CustomDataGridView_Ent1.RowCount
Dim retCount As Integer = 0
Dim fld00 As Integer '取引日
Dim fld01 As Integer '連番
Dim fld02 As String '摘要
Dim fld03 As Integer '入金額
Dim fld04 As Integer '出金額
Dim fld05 As Integer '取引先
Dim fld06 As Integer '支払先
Dim fld07 As Integer '支払日
Dim fld08 As Integer '取引区分
Dim fld09 As Boolean '削除
Dim fld10 As Integer '明細番号
Dim sqlStr As String = ""
cmd.Connection() = co
tr = co.BeginTransaction(IsolationLevel.ReadCommitted)
cmd = co.CreateCommand
cmd.Transaction = tr
Try
For i = 0 To Count - 1
Dim ret00 As Boolean = Integer.TryParse(CustomDataGridView_Ent1.Rows(i).Cells(0).Value, fld00)
If IsDate(Format(fld00, "####/##/##")) = True Then
Dim ret01 As Boolean = Decimal.TryParse(CustomDataGridView_Ent1.Rows(i).Cells(1).Value, fld01)
fld02 = CustomDataGridView_Ent1.Rows(i).Cells(2).Value
Dim ret03 As Boolean = Decimal.TryParse(CustomDataGridView_Ent1.Rows(i).Cells(3).Value, fld03)
Dim ret04 As Boolean = Decimal.TryParse(CustomDataGridView_Ent1.Rows(i).Cells(4).Value, fld04)
Dim ret05 As Boolean = Decimal.TryParse(CustomDataGridView_Ent1.Rows(i).Cells(5).Value, fld05)
Dim ret06 As Boolean = Decimal.TryParse(CustomDataGridView_Ent1.Rows(i).Cells(7).Value, fld06)
Dim ret07 As Boolean = Decimal.TryParse(CustomDataGridView_Ent1.Rows(i).Cells(9).Value, fld07)
Dim ret08 As Boolean = Decimal.TryParse(CustomDataGridView_Ent1.Rows(i).Cells(10).Value, fld08)
fld09 = CustomDataGridView_Ent1.Rows(i).Cells(11).Value
Dim ret10 As Boolean = Decimal.TryParse(CustomDataGridView_Ent1.Rows(i).Cells(12).Value, fld10)
If fld10 = 0 Then
'新規
sqlStr = " insert into 取引明細 "
sqlStr += " (明細番号,取引日,連番,摘要,入金額,出金額,取引先,支払先,支払日,取引区分) "
sqlStr += " values( " + "(select coalesce(max(明細番号),0)+1 from 取引明細)" + ","
sqlStr += Str(fld00) + "," + Str(fld01) + ",'" + fld02 + "'," + Str(fld03) + "," + Str(fld04) + "," + Str(fld05) + ","
sqlStr += Str(fld06) + "," + Str(fld07) + "," + Str(fld08) + ")"
Else
'既存
If fld09 = "false" Then
sqlStr = " update 取引明細 set "
sqlStr += " 取引日=" + Str(fld00)
sqlStr += ",連番=" + Str(fld01)
sqlStr += ",摘要='" + fld02 + "'"
sqlStr += ",入金額=" + Str(fld03)
sqlStr += ",出金額=" + Str(fld04)
sqlStr += ",取引先=" + Str(fld05)
sqlStr += ",支払先=" + Str(fld06)
sqlStr += ",支払日=" + Str(fld07)
sqlStr += ",取引区分=" + Str(fld08)
sqlStr += " where 明細番号=" + Str(fld10)
Else
sqlStr = " delete from 取引明細 "
sqlStr += " where 明細番号=" + Str(fld10)
End If
End If
cmd.CommandText = sqlStr
cmd.ExecuteNonQuery()
retCount += 1
End If
Next
tr.Commit()
MsgBox(Str(retCount) + "件更新しました。")
Catch ex As Exception 'OleDbException
If IsDBNull(tr) = False Then
tr.Rollback()
End If
MsgBox("書き込みエラーです。" & vbCr _
& ex.Message & vbCr _
& cmd.CommandText & vbCr _
& "確認してください。")
Exit Sub
End Try
CustomDataGridView_Ent1.Rows.Clear()
TextBox1.Focus()
End Sub
'-------------------------------------------------------
' データ照会処理
'-------------------------------------------------------
Private Sub Begin_sub()
Dim Count As Integer
Try
CustomDataGridView_Ent1.Rows.Clear()
cmd.Connection() = co
cmd.CommandText = "select count(*) as count from 取引明細"
cmd.CommandText += " where 取引日 >= " + Str(intTextBox1)
cmd.CommandText += " and 取引日 <= " + Str(intTextBox2)
rs = cmd.ExecuteReader()
Count = 0
While rs.Read()
Count = rs.Item("count").ToString
End While
rs.Close()
cmd.CommandText = "select 取引明細.*,coalesce(T.取引先名,'') as 取引先名称,coalesce(S.取引先名,'') as 支払先名称 from 取引明細"
cmd.CommandText += " left outer join 取引先マスター as T on 取引明細.取引先=T.取引先コード"
cmd.CommandText += " left outer join 取引先マスター as S on 取引明細.支払先=S.取引先コード"
cmd.CommandText += " where 取引日 >= " + Str(intTextBox1)
cmd.CommandText += " and 取引日 <= " + Str(intTextBox2)
cmd.CommandText += " order by 取引日,連番"
rs = cmd.ExecuteReader()
Dim i As Integer = 0
CustomDataGridView_Ent1.Rows.Add(Count + 20)
While rs.Read()
'row_headerの行番号表示
CustomDataGridView_Ent1.Rows(i).HeaderCell.Value = Str(i + 1)
'rowの色指定
If (i + 1) Mod 2 = 0 Then
CustomDataGridView_Ent1.Rows(i).DefaultCellStyle.BackColor = Color.Ivory
End If
'追加した行に値をセット
CustomDataGridView_Ent1.Rows(i).Cells(0).Value = rs.Item("取引日").ToString
CustomDataGridView_Ent1.Rows(i).Cells(1).Value = rs.Item("連番").ToString
CustomDataGridView_Ent1.Rows(i).Cells(2).Value = rs.Item("摘要").ToString
CustomDataGridView_Ent1.Rows(i).Cells(3).Value = rs.Item("入金額").ToString
CustomDataGridView_Ent1.Rows(i).Cells(4).Value = rs.Item("出金額").ToString
CustomDataGridView_Ent1.Rows(i).Cells(5).Value = rs.Item("取引先").ToString
CustomDataGridView_Ent1.Rows(i).Cells(6).Value = rs.Item("取引先名称").ToString
CustomDataGridView_Ent1.Rows(i).Cells(7).Value = rs.Item("支払先").ToString
CustomDataGridView_Ent1.Rows(i).Cells(8).Value = rs.Item("支払先名称").ToString
CustomDataGridView_Ent1.Rows(i).Cells(9).Value = rs.Item("支払日").ToString
CustomDataGridView_Ent1.Rows(i).Cells(10).Value = rs.Item("取引区分").ToString
CustomDataGridView_Ent1.Rows(i).Cells(11).Value = "false"
CustomDataGridView_Ent1.Rows(i).Cells(12).Value = rs.Item("明細番号").ToString
i += 1
End While
rs.Close()
'追加した行にフォーカスを移す
CustomDataGridView_Ent1.CurrentCell = CustomDataGridView_Ent1(0, i)
Catch ex As OleDbException
MsgBox("データベース接続エラーです。" & vbCr _
& ex.Message & vbCr _
& "終了します")
Return
End Try
End Sub
Private Sub 照会ToolStripMenuItem1_Click(sender As Object, e As EventArgs) Handles 照会ToolStripMenuItem1.Click
Dim iform3 As New Form3
iform3.ShowDialog()
End Sub
Private Sub CustomDataGridView_Ent1_CellEndEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles CustomDataGridView_Ent1.CellEndEdit
If e.ColumnIndex = 5 Then
Dim ShTorihikisakinew = New ShTorihikisaki(CustomDataGridView_Ent1.CurrentCell.Value)
CustomDataGridView_Ent1.Rows(e.RowIndex).Cells(6).Value = ShTorihikisakinew.GTorihikisaki_name
End If
If e.ColumnIndex = 7 Then
Dim ShTorihikisakinew = New ShTorihikisaki(CustomDataGridView_Ent1.CurrentCell.Value)
CustomDataGridView_Ent1.Rows(e.RowIndex).Cells(8).Value = ShTorihikisakinew.GTorihikisaki_name
End If
End Sub
'EditingControlShowingイベントハンドラ
Private Sub CustomDataGridView_Ent1_EditingControlShowing(ByVal sender As Object,
ByVal e As DataGridViewEditingControlShowingEventArgs) _
Handles CustomDataGridView_Ent1.EditingControlShowing
'表示されているコントロールがDataGridViewTextBoxEditingControlか調べる
If TypeOf e.Control Is DataGridViewTextBoxEditingControl Then
Dim dgv As DataGridView = CType(sender, DataGridView)
'編集のために表示されているコントロールを取得
Dim tb As DataGridViewTextBoxEditingControl =
CType(e.Control, DataGridViewTextBoxEditingControl)
'イベントハンドラを削除
RemoveHandler tb.KeyPress, AddressOf dataGridViewTextBox1_KeyPress
'該当する列か調べる
If dgv.CurrentCell.OwningColumn.Name = "xxColumn1" Then
'KeyPressイベントハンドラを追加
AddHandler tb.KeyPress, AddressOf dataGridViewTextBox1_KeyPress
End If
If dgv.CurrentCell.OwningColumn.Name = "xxColumn10" Then
'KeyPressイベントハンドラを追加
AddHandler tb.KeyPress, AddressOf dataGridViewTextBox1_KeyPress
End If
End If
End Sub
'DataGridViewに表示されているテキストボックスのKeyPressイベントハンドラ
Private Sub dataGridViewTextBox1_KeyPress(ByVal sender As Object,
ByVal e As KeyPressEventArgs)
'数字しか入力できないようにする
If e.KeyChar < "0"c Or e.KeyChar > "9"c Then
e.Handled = True
End If
End Sub
'CellValidatingイベントハンドラ
Private Sub CustomDataGridView_Ent1_CellValidating(ByVal sender As Object, ByVal e As DataGridViewCellValidatingEventArgs) Handles CustomDataGridView_Ent1.CellValidating
Dim dgv As DataGridView = DirectCast(sender, DataGridView)
'新しい行のセルでなく、セルの内容が変更されている時だけ検証する
If e.RowIndex = dgv.NewRowIndex OrElse Not dgv.IsCurrentCellDirty Then
Exit Sub
End If
If dgv.Columns(e.ColumnIndex).Name = "Column1" Then
Dim outData = 0
Dim ret As Boolean = Integer.TryParse(e.FormattedValue.ToString(), outData)
If IsDate(Format(outData, "####/##/##")) = False And outData > 0 Then
'行にエラーテキストを設定
dgv.Rows(e.RowIndex).ErrorText = "値が正しくありません。"
'入力した値をキャンセルして元に戻すには、次のようにする
'dgv.CancelEdit()
'キャンセルする
e.Cancel = True
End If
End If
If dgv.Columns(e.ColumnIndex).Name = "Column6" Then
Dim code As Integer
Dim ret As Boolean = Integer.TryParse(e.FormattedValue.ToString(), code)
If code > 0 Then
Dim ShTorihikisakinew = New ShTorihikisaki(code)
If ShTorihikisakinew.GTorihikisaki_Inv = 1 Then
'行にエラーテキストを設定
dgv.Rows(e.RowIndex).ErrorText = "取引先コードエラー"
'入力した値をキャンセルして元に戻すには、次のようにする
'dgv.CancelEdit()
'キャンセルする
e.Cancel = True
End If
End If
End If
If dgv.Columns(e.ColumnIndex).Name = "Column8" Then
Dim code As Integer
Dim ret As Boolean = Integer.TryParse(e.FormattedValue.ToString(), code)
If code > 0 Then
Dim ShTorihikisakinew = New ShTorihikisaki(code)
If ShTorihikisakinew.GTorihikisaki_Inv = 1 Then
'行にエラーテキストを設定
dgv.Rows(e.RowIndex).ErrorText = "支払先コードエラー"
'入力した値をキャンセルして元に戻すには、次のようにする
'dgv.CancelEdit()
'キャンセルする
e.Cancel = True
End If
End If
End If
If dgv.Columns(e.ColumnIndex).Name = "Column10" Then
Dim outData = 9
Dim ret As Boolean = Integer.TryParse(e.FormattedValue.ToString(), outData)
If IsDate(Format(outData, "####/##/##")) = False And outData > 0 Then
'行にエラーテキストを設定
dgv.Rows(e.RowIndex).ErrorText = "値が正しくありません。"
'入力した値をキャンセルして元に戻すには、次のようにする
'dgv.CancelEdit()
'キャンセルする
e.Cancel = True
End If
End If
End Sub
'CellValidatedイベントハンドラ
Private Sub CustomDataGridView_Ent1_CellValidated(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs) Handles CustomDataGridView_Ent1.CellValidated
Dim dgv As DataGridView = DirectCast(sender, DataGridView)
'エラーテキストを消す
dgv.Rows(e.RowIndex).ErrorText = Nothing
End Sub
Private Sub DateTimePicker1_ValueChanged(sender As Object, e As EventArgs) Handles DateTimePicker1.ValueChanged
TextBox1.Text = Replace((DateTimePicker1.Value).ToShortDateString, "/", "")
TextBox1.Focus()
End Sub
Private Sub DateTimePicker2_ValueChanged(sender As Object, e As EventArgs) Handles DateTimePicker2.ValueChanged
TextBox2.Text = Replace((DateTimePicker2.Value).ToShortDateString, "/", "")
TextBox2.Focus()
End Sub
Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
If CustomDataGridView_Ent1.EditMode = DataGridViewEditMode.EditOnEnter Then
CustomDataGridView_Ent1.EditMode = DataGridViewEditMode.EditOnKeystroke
Label7.Text = "EditOnKeystroke"
Else
CustomDataGridView_Ent1.EditMode = DataGridViewEditMode.EditOnEnter
Label7.Text = "EditOnEnter"
End If
End Sub
End Class
CustomDataGridView_Ent
Imports System.IO
Imports System.Xml.Serialization
Imports System.Text
Public Class CustomDataGridView_Ent
Inherits DataGridView
' ----------------------------------------------------------------------------------
' DataGridViewのカラム幅をファイルへ保存／読込するための実装↓↓↓↓↓↓↓↓↓↓↓↓
' ----------------------------------------------------------------------------------
''' <summary>
''' DataGridViewのカラム幅をXML形式でシリアライズするためのクラス
''' </summary>
''' <remarks></remarks>
Public Class ColWidths
''' <summary>
''' カラム幅s
''' </summary>
Public Widths As Integer()
End Class
''' <summary>
''' DataGridViewのカラム幅をファイルへ保存
''' </summary>
''' <remarks>DataGridViewのカラム幅をXML形式でシリアライズ</remarks>
Public Sub SaveColWidths()
Try
' EXEファイルのPATH
Dim ExePath As String = System.AppDomain.CurrentDomain.BaseDirectory
' XMLファイルのPATH
Dim XmlPath As String = ExePath & "\" & Me.Parent.Name & "_" & Me.Name & ".xml"
' XMLファイルオープン
Dim sw As StreamWriter = New StreamWriter(XmlPath, False, Encoding.Default)
Try
' シリアライザー
Dim serializer As New XmlSerializer(GetType(ColWidths))
' DataGridViewのカラム幅取得
Dim colw As New ColWidths
ReDim colw.Widths(Me.Columns.Count - 1)
For i As Integer = 0 To Me.Columns.Count - 1
colw.Widths(i) = Me.Columns(i).Width
Next
' XMLファイル保存
serializer.Serialize(sw, colw)
Catch ex As Exception
Finally
' XMLファイルクローズ
If sw Is Nothing = False Then sw.Close()
End Try
Catch ex As Exception
End Try
End Sub
''' <summary>
''' DataGridViewのカラム幅を前回保存したファイルから読込
''' </summary>
''' <remarks>DataGridViewのカラム幅をXML形式でデシリアライズ</remarks>
Public Sub ReadColWidths()
Try
' EXEファイルのPATH
Dim ExePath As String = System.AppDomain.CurrentDomain.BaseDirectory
' XMLファイルのPATH
Dim XmlPath As String = ExePath & "\" & Me.Parent.Name & "_" & Me.Name & ".xml"
' XMLファイルオープン
Dim sr As StreamReader = New StreamReader(XmlPath, Encoding.Default)
Try
' シリアライザー
Dim serializer As New XmlSerializer(GetType(ColWidths))
' XMLファイル読込
Dim colw As New ColWidths
colw = CType(serializer.Deserialize(sr), ColWidths)
' DataGridViewにカラム幅設定
For i As Integer = 0 To Me.Columns.Count - 1
Me.Columns(i).Width = colw.Widths(i)
Next
Catch ex As Exception
Finally
' XMLファイルクローズ
If sr Is Nothing = False Then sr.Close()
End Try
Catch ex As Exception
End Try
End Sub
' ----------------------------------------------------------------------------------
' DataGridViewのカラム幅をファイルへ保存／読込するための実装↑↑↑↑↑↑↑↑↑↑↑↑
' ----------------------------------------------------------------------------------
' ----------------------------------------------------------------------------------
' DataGridViewの各カラムへの入力可能文字を制限するための実装↓↓↓↓↓↓↓↓↓↓↓↓
' ----------------------------------------------------------------------------------
''' <summary>
''' カラムへの入力可能文字を指定するための配列
''' </summary>
''' <remarks>ColumnChars(0)="1234567890"</remarks>
Public ColumnChars() As String
''' <summary>
''' 編集中のカラム番号
''' </summary>
''' <remarks></remarks>
Private _editingColumn As Integer
''' <summary>
''' 編集中のTextBoxEditingControl
''' </summary>
''' <remarks></remarks>
Private _editingCtrl As DataGridViewTextBoxEditingControl
''' <summary>
''' セルが編集中になった時の処理
''' </summary>
''' <param name="sender">イベントの発生元</param>
''' <param name="e">イベントの情報</param>
''' <remarks>編集中のTextBoxEditingControlにKeyPressイベント設定</remarks>
Private Sub DataGridViewPlus_EditingControlShowing(
ByVal sender As Object, ByVal e As DataGridViewEditingControlShowingEventArgs) _
Handles Me.EditingControlShowing
' 編集中のカラム番号を保存
_editingColumn = CType(sender, DataGridView).CurrentCellAddress.X
Try
' 編集中のTextBoxEditingControlにKeyPressイベント設定
_editingCtrl = CType(e.Control, DataGridViewTextBoxEditingControl)
AddHandler _editingCtrl.KeyPress, AddressOf DataGridViewPlus_CellKeyPress
Catch
End Try
End Sub
''' <summary>
''' セルの編集が終わった時の処理
''' </summary>
''' <param name="sender">イベントの発生元</param>
''' <param name="e">イベントの情報</param>
''' <remarks>編集中のTextBoxEditingControlからKeyPressイベント削除</remarks>
Private Sub DataGridViewPlus_CellEndEdit(ByVal sender As Object,
ByVal e As DataGridViewCellEventArgs) Handles Me.CellEndEdit
If _editingCtrl Is Nothing = False Then
' 編集中のTextBoxEditingControlからKeyPressイベント削除
RemoveHandler _editingCtrl.KeyPress, AddressOf DataGridViewPlus_CellKeyPress
_editingCtrl = Nothing
End If
End Sub
''' <summary>
''' 編集中のTextBoxEditingControlのKeyPressの処理
''' </summary>
''' <param name="sender">イベントの発生元</param>
''' <param name="e">イベントの情報</param>
''' <remarks>力可能文字の判定</remarks>
Private Sub DataGridViewPlus_CellKeyPress(
ByVal sender As Object, ByVal e As KeyPressEventArgs)
' カラムへの入力可能文字を指定するための配列が指定されているかチェック
If IsArray(ColumnChars) Then
' カラムへの入力可能文字を指定するための配列数チェック
If ColumnChars.GetLength(0) - 1 >= _editingColumn Then
' カラムへの入力可能文字が指定されているかチェック
If ColumnChars(_editingColumn) <> "" Then
' カラムへの入力可能文字かチェック
If InStr(ColumnChars(_editingColumn), e.KeyChar) <= 0 AndAlso
e.KeyChar <> Chr(Keys.Back) Then
' カラムへの入力可能文字では無いので無効
e.Handled = True
End If
End If
End If
End If
End Sub
' ----------------------------------------------------------------------------------
' DataGridViewの各カラムへの入力可能文字を制限するための実装↑↑↑↑↑↑↑↑↑↑↑↑
' ----------------------------------------------------------------------------------
' ----------------------------------------------------------------------------------
' DataGridViewでCtrl-Vキー押下時にクリップボードから貼り付けるための実装↓↓↓↓↓↓
' DataGridViewでDelやBackspaceキー押下時にセルの内容を消去するための実装↓↓↓↓↓↓
' ----------------------------------------------------------------------------------
Private Sub DataGridViewPlus_KeyDown(
ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) _
Handles Me.KeyDown
Dim dgv As DataGridView = CType(sender, DataGridView)
Dim x As Integer = dgv.CurrentCellAddress.X
Dim y As Integer = dgv.CurrentCellAddress.Y
If e.KeyCode = Keys.Delete Or e.KeyCode = Keys.Back Then
' セルの内容を消去
dgv(x, y).Value = ""
ElseIf (e.Modifiers And Keys.Control) = Keys.Control And e.KeyCode = Keys.V Then
' クリップボードの内容を取得
Dim clipText As String = Clipboard.GetText()
' 改行を変換
clipText = clipText.Replace(vbCrLf, vbLf)
clipText = clipText.Replace(vbCr, vbLf)
' 改行で分割
Dim lines() As String = clipText.Split(vbLf)
Dim r As Integer
Dim nflag As Boolean = True
For r = 0 To lines.GetLength(0) - 1
' 最後のNULL行をコピーするかどうか
If r >= lines.GetLength(0) - 1 And
"".Equals(lines(r)) And nflag = False Then Exit For
If "".Equals(lines(r)) = False Then nflag = False
' タブで分割
Dim vals() As String = lines(r).Split(vbTab)
' 各セルの値を設定
Dim c As Integer = 0
Dim c2 As Integer = 0
For c = 0 To vals.GetLength(0) - 1
' セルが存在しなければ貼り付けない
If Not (x + c2 >= 0 And x + c2 < dgv.ColumnCount And
y + r >= 0 And y + r < dgv.RowCount) Then
Continue For
End If
' 非表示セルには貼り付けない
If dgv(x + c2, y + r).Visible = False Then
c = c - 1
Continue For
End If
'' 貼り付け処理(入力可能文字チェック無しの時)------------
'' 行追加モード&(最終行の時は行追加)
'If y + r = dgv.RowCount - 1 And _
' dgv.AllowUserToAddRows = True Then
' dgv.RowCount = dgv.RowCount + 1
'End If
'' 貼り付け
'dgv(x + c2, y + r).Value = vals(c)
' ------------------------------------------------------
' 貼り付け処理(入力可能文字チェック有りの時)------------
Dim pststr As String = ""
For i As Long = 0 To vals(c).Length - 1
_editingColumn = x + c2
Dim tmpe As KeyPressEventArgs =
New KeyPressEventArgs(vals(c).Substring(i, 1))
tmpe.Handled = False
DataGridViewPlus_CellKeyPress(sender, tmpe)
If tmpe.Handled = False Then
pststr = pststr & vals(c).Substring(i, 1)
End If
Next
' 行追加モード＆最終行の時は行追加
If y + r = dgv.RowCount - 1 And
dgv.AllowUserToAddRows = True Then
dgv.RowCount = dgv.RowCount + 1
End If
' 貼り付け
dgv(x + c2, y + r).Value = pststr
' ------------------------------------------------------
' 次のセルへ
c2 = c2 + 1
Next
Next
End If
End Sub
' ----------------------------------------------------------------------------------
' DataGridViewでCtrl-Vキー押下時にクリップボードから貼り付けるための実装↑↑↑↑↑↑
' DataGridViewでDelやBackspaceキー押下時にセルの内容を消去するための実装↑↑↑↑↑↑
' ----------------------------------------------------------------------------------
Protected Overrides Function ProcessDialogKey(
ByVal keyData As Keys) As Boolean
'Enterキーが押された際に、タブキーが押されたことにし、
'それ以外のキーの場合は、通常どおり実行します。
If (keyData And Keys.KeyCode) = Keys.Enter Then
Return Me.ProcessTabKey(keyData)
Else
Return MyBase.ProcessDialogKey(keyData)
End If
End Function
Protected Overrides Function ProcessDataGridViewKey(
ByVal e As KeyEventArgs) As Boolean
'Enterキーが押された際に、タブキーが押されたことにし、
'それ以外のキーの場合は、通常どおり実行します。
If e.KeyCode = Keys.Enter And e.Handled = False Then
e.Handled = True
If Me.CurrentCell IsNot Nothing Then
' 右下セルのときは次のコントロールにフォーカス移動
If Me.CurrentCell.RowIndex = Me.Rows.GetLastRow(DataGridViewElementStates.Visible) AndAlso
Me.CurrentCell.ColumnIndex = Me.Columns.GetLastColumn(DataGridViewElementStates.Visible, DataGridViewElementStates.None).Index AndAlso
e.Modifiers <> Keys.Shift Then
Return Me.FindForm().SelectNextControl(Me.FindForm().ActiveControl, True, True, True, True)
End If
' 左上のセルでShift + Enterが押されたときは前のコントロールにフォーカス移動
If Me.CurrentCell.RowIndex = 0 AndAlso
Me.CurrentCell.ColumnIndex = 0 AndAlso
e.Modifiers = Keys.Shift Then
Return Me.FindForm().SelectNextControl(Me.FindForm().ActiveControl, False, True, True, True)
End If
End If
' Enterキーが押されらTabキーの処理を行う
Return Me.ProcessTabKey(e.KeyData)
Else
Return MyBase.ProcessDataGridViewKey(e)
End If
End Function
End Class
module.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 OutDir As String
Public F2Str As String
Public strUrl As String = ""
Public n As Integer = FreeFile()
Public co As New OleDb.OleDbConnection
Public rs As OleDb.OleDbDataReader
Public cmd As New OleDb.OleDbCommand
Public tr As OleDb.OleDbTransaction
Public Gtorihikisaki_code As String
Public Gsentaku As Integer
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)
OutDir = 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 & ";"
Debug.WriteLine(CoString)
GCoOpen()
End Sub
Public Sub GCoOpen()
Try
co.ConnectionString = CoString
co.Open()
Catch ex As Exception
MsgBox("データベース接続エラーです。" & vbCr _
& ex.Message & vbCr _
& "終了します")
End
End Try
End Sub
Public Sub GCoClose()
Try
co.Close()
Catch ex As Exception
MsgBox("データベース接続エラーです。" & vbCr _
& ex.Message & vbCr _
& "終了します")
End
End Try
End Sub
End ModuleShTorihikisaki.vb
Imports System.Data.OleDb
Public Class ShTorihikisaki
Public GTorihikisaki_Inv As Long
Public GTorihikisaki_code As Long
Public GTorihikisaki_name As String
'取引先マスター索引
Public Sub New(ByVal Temp1 As Integer)
'Temp1 取引先コード
Dim rs As OleDb.OleDbDataReader
Dim cmd As New OleDb.OleDbCommand
GTorihikisaki_Inv = 1
GTorihikisaki_code = 0
GTorihikisaki_name = ""
'
'***************************************
'* 初期処理
'***************************************
Try
cmd.Connection() = co
cmd.CommandText = "select * FROM 取引先マスター " _
& " WHERE 取引先コード=" & Temp1
rs = cmd.ExecuteReader()
While rs.Read()
GTorihikisaki_Inv = 0
GTorihikisaki_code = rs.Item("取引先コード")
GTorihikisaki_name = rs.Item("取引先名")
End While
rs.Close() 'クローズが必要
'***************************************
'* 終了処理
'***************************************
'coole.Close()
Catch ex As Exception
MsgBox("取引先マスター：データベース接続エラーです。" & vbCr _
& ex.Message & vbCr _
& "継続します")
End Try
End Sub
End Class
Form4.vb
Imports System.Reflection
Imports System.Windows.Forms.VisualStyles.VisualStyleElement
Public Class Form4
Private Sub textBox1_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.GotFocus
TextBox1.SelectionStart = 0
TextBox1.SelectionLength = Len(TextBox1.Text)
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Gtorihikisaki_code = TextBox1.Text
Gsentaku = 1
Me.Close()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.Close()
End Sub
Private Sub Form4_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 Form4_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.KeyPreview = True
ListView1.View = View.Details
ListView1.Columns.Add("コード", 100, HorizontalAlignment.Left)
ListView1.Columns.Add("取引先名", 200, HorizontalAlignment.Left)
'ListView1.Items.Clear()
Gsentaku = 0
Dim sel As Long
sel = 1
If sel = 1 Then
'Dim rs As OleDb.OleDbDataReader
'Dim cmd As New OleDb.OleDbCommand
'Dim rs As iDB2DataReader
'Dim cmd As New iDB2Command
'***************************************
'* 初期処理
'***************************************
Try
cmd = co.CreateCommand
'***************************************
'* データの取得
'***************************************
cmd.CommandText = "select * from 取引先マスター ORDER BY 取引先コード"
rs = cmd.ExecuteReader()
While rs.Read()
Dim LV As New ListViewItem(rs.Item("取引先コード").ToString)
LV.SubItems.Add(rs.Item("取引先名"))
ListView1.Items.Add(LV)
End While
rs.Close()
'***************************************
'* 終了処理
'***************************************
Catch ex As Exception
MsgBox("データベース接続エラーです。" & vbCr &
ex.Message & vbCr &
"検索終了します。")
'Exit Sub
End Try
If ListView1.Items.Count = 0 Then
Dim LV As New ListViewItem("")
LV.SubItems.Add("明細がありません")
ListView1.Items.Add(LV)
End If
Else
Dim LV As New ListViewItem("0")
ListView1.Items.Add(LV)
End If
End Sub
Private Sub ListView1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView1.Click
If ListView1.FocusedItem.Index >= 0 Then
TextBox1.Text = ListView1.FocusedItem.Text()
End If
End Sub
Private Sub ListView1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView1.DoubleClick
If ListView1.FocusedItem.Index >= 0 Then
TextBox1.Text = ListView1.FocusedItem.Text()
End If
Gtorihikisaki_code = TextBox1.Text
Me.Close()
GSentaku = 1
End Sub
Private Sub ListView1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles ListView1.KeyDown
If e.KeyCode = Keys.Enter And ListView1.FocusedItem.Index >= 0 Then
If ListView1.FocusedItem.Index >= 0 Then
TextBox1.Text = ListView1.FocusedItem.Text()
End If
Gtorihikisaki_code = TextBox1.Text
Me.Close()
GSentaku = 1
End If
If e.KeyCode = Keys.End Then
Gsentaku = 1
Gtorihikisaki_code = TextBox1.Text
Me.Close()
End If
End Sub
Private Sub Form4_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles MyBase.KeyDown
If e.KeyCode = Keys.End Then
Gsentaku = 1
Gtorihikisaki_code = TextBox1.Text
Me.Close()
End If
End Sub
End Class