2013年12月16日月曜日

[VB.NET]CSVやTXTをそのまま取込むバッチに汎用性を持たせる。


使いまわせそうなのでメモ。文字化け対策もやった。(こっちの方が重要かも)
取込先のテーブル情報を先に読んでからセット。


Dim objSR As New StreamReader(strFilePath, Encoding.GetEncoding("shift-jis"))
Dim Strtbl As String = "[取り込みテーブル名]"
'テーブルの項目名をとっておく。
Dim DS As New DataSet
Std.ADONET.FillDataset(objTR, DS, "test", & _
 "SELECT name,type_name(user_type_id) AS kata FROM Sys.Columns WHERE object_id = object_id('" & strtbl & "') ")
If DS.Tables("test").Rows.Count = 0 Then
Throw New Exception("ERR:テーブル項目がありません。エラーが発生しました。")
End If


While (objSR.Peek() >= 0)
Dim strSQL As New System.Text.StringBuilder
Dim stBuffer As String = objSR.ReadLine()
'1列ずつ中身読込
If InStr(stBuffer, ",") = 0 Then
   Exit While
End If
Nakami = Split(stBuffer, ",")
'項目数がなにかの理由で(主に文字化け)合わない時
If Nakami.length - 1 <> DS.Tables("test").Rows.Count Then
Throw New Exception(cnt + 1 & "行:ERR:元データの項目数がおかしい。エラーが発生しました。")
End If
With DS.Tables("test")
strSQL.Append("INSERT INTO " & strtbl & _
                  " ( ")
                  For i As Integer = 0 To .Rows.Count - 1
                       strSQL.Append("[" & .Rows(i).Item("name") & "]")
                       If i <> .Rows.Count - 1 Then strSQL.Append(",")
                  Next
                  strSQL.Append(")")
                  strSQL.Append(" VALUES ")
                  strSQL.Append("(")
                  For k As Integer = 0 To .Rows.Count - 1
                      If .Rows(k).Item("kata") = "varchar" Then
                                '文字化け対策やってみる
                                If AscW(Nakami(k)) = 0 Then
                                    Messagebox.show( .Rows(k).Item("name") & "[" & Nakami(k).ToString & "]が文字化けしていたようなのでスペースに置き換えました。")
                                    Nakami(k) = ""
                                End If
                                strSQL.Append("'" & Nakami(k).ToString & "'")
                          Else
                                '文字化け対策やってみる
                                If AscW(Nakami(k)) = 0 Then
                                    Messagebox.show(.Rows(k).Item("name") & "[" & Nakami(k).ToString & "]が文字化けしていたようなので0に置き換えました。"
                                    Nakami(k) = 0
                                End If
                                strSQL.Append("" & Nakami(k).ToString & "")
                         End If
                         If k <> .Rows.Count - 1 Then strSQL.Append(",")
                  Next
                  strSQL.Append(")")
End With
Call Std.ADONET.ExecuteNonQuery(objTR, strSQL.ToString)
End While
objSR.Close()

2013年2月26日火曜日

[VB.NET]DataGridViewでのドラッグアンドドロップ

以下のサイトにあったC#のソースコードをVB.NETに修正&一部変更しました。
http://social.msdn.microsoft.com/forums/ja-JP/csharpgeneralja/thread/e59a4043-6298-4653-809f-5c8fcf04f2e6

変更点として、
行セレクトモード⇒セルセレクトモードにして、
特定のカラム(ここではColumn0のImage)上でしかドラッグアンドドロップが起動しないようにしています。




Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        'テストの準備
        'データグリッド
        Dim objTable As New DataTable
        Dim objDR As DataRow
        objTable.Columns.Add(New DataColumn("項目1", GetType(String)))
        objTable.Columns.Add(New DataColumn("項目2", GetType(Integer)))
        For i As Integer = 0 To 10
            objDR = objTable.NewRow
            objDR("項目1") = i.ToString & i.ToString & i.ToString & i.ToString
            objDR("項目2") = i
            objTable.Rows.Add(objDR)
        Next
        Me.DataGridViewUe.DataSource = objTable


        For Each col As DataGridViewColumn In Me.DataGridViewUe.Columns
            col.SortMode = DataGridViewColumnSortMode.NotSortable
        Next
        Me.DataGridViewUe.AllowDrop = True

    End Sub

Private Sub DataGridViewUe_MouseDown(sender As System.Object, e As MouseEventArgs) Handles DataGridViewUe.MouseDown

        OwnBeginGrabRowIndex = -1

        If (e.Button <> MouseButtons.Left And MouseButtons.Left <> MouseButtons.Left) Then Return


        Dim hit As DataGridView.HitTestInfo = DataGridViewUe.HitTest(e.X, e.Y)

    If hit.ColumnIndex <> 0 Then Return
        If (hit.Type <> DataGridViewHitTestType.Cell) Then Return


        'ドラッグ&ドロップの開始
        'クリック時などは -1 に戻らないが問題なし
        OwnBeginGrabRowIndex = hit.RowIndex


        'Me.TextBox1.Text = OwnBeginGrabRowIndex
        'Me.TextBox2.Text = "すたーと"
        DataGridViewUe.DoDragDrop(OwnBeginGrabRowIndex, DragDropEffects.Move)



    End Sub
  
 
    Private Sub DataGridViewUe_DragOver(sender As System.Object, e As DragEventArgs) Handles DataGridViewUe.DragOver


        'Integer型の場合は、エフェクトをMoveに変更(ここはカラムによるのかな?)
        If e.Data.GetDataPresent(GetType(Integer)) Then
            e.Effect = DragDropEffects.Move
        Else
            e.Effect = DragDropEffects.None
        End If

        Me.DataGridViewUe.Rows(OwnBeginGrabRowIndex).Selected = True


        Dim kara, made As Integer
        Dim tugi As Boolean
        Dim valid As Boolean = DecideDropDestinationRowIndex(DataGridViewUe, e, kara, made, tugi)


        'ドロップ先マーカーの表示・非表示の制御
        Dim needRedraw As Boolean = (valid <> DropDestinationIsValid)
        If (valid) Then
            needRedraw = (needRedraw Or (made <> DropDestinationRowIndex) Or (tugi <> DropDestinationIsNextRow))
        End If

        If (needRedraw) Then
            If (DropDestinationIsValid) Then
                DataGridViewUe.InvalidateRow(DropDestinationRowIndex)
            End If
            If (valid) Then
                DataGridViewUe.InvalidateRow(made)
            End If
        End If

        DropDestinationIsValid = valid
        DropDestinationRowIndex = made
        DropDestinationIsNextRow = tugi

    End Sub

    Private Sub DataGridViewUe_DragLeave(sender As System.Object, e As DragEventArgs) Handles DataGridViewUe.DragLeave
        If (DropDestinationIsValid) Then
            DropDestinationIsValid = False
            DataGridViewUe.InvalidateRow(DropDestinationRowIndex)
        End If
    End Sub

    Private Sub DataGridViewUe_DragDrop(sender As System.Object, e As DragEventArgs) Handles DataGridViewUe.DragDrop

        Dim kara, made As Integer
        Dim tugi As Boolean

        If (DecideDropDestinationRowIndex(DataGridViewUe, e, kara, made, tugi) = False) Then
            Return
        End If

        DropDestinationIsValid = False

        'データの移動
        made = MoveDataValue(kara, made, tugi)

        Me.DataGridViewUe.CurrentCell = Me.DataGridViewUe(Me.DataGridViewUe.CurrentCell.ColumnIndex, made)


        Me.DataGridViewUe.Invalidate()


    End Sub


    Private Sub DataGridViewUe_RowPostPaint(sender As System.Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewUe.RowPostPaint

        'ドロップ先のマーカーを描画
        If (DropDestinationIsValid And e.RowIndex = DropDestinationRowIndex) Then
            Using objpen As New Pen(Color.Red, 4)
                Dim y As Integer = IIf(DropDestinationIsNextRow = False, e.RowBounds.Y + 2, e.RowBounds.Bottom - 2)
                e.Graphics.DrawLine(objpen, e.RowBounds.X, y, e.RowBounds.X + 50, y)
            End Using
        End If
    End Sub



    'ドロップ先の行の決定
    Private Function DecideDropDestinationRowIndex(grid As DataGridView, e As DragEventArgs, ByRef kara As Integer, ByRef made As Integer, ByRef tugi As Boolean) As Boolean


        'If Not e.Data.GetDataPresent(GetType(Integer)) Then Return
        kara = DirectCast(e.Data.GetData(GetType(Integer)), Integer)

        '元の行が追加用の行であれば、常に false
        If (grid.NewRowIndex <> -1 And grid.NewRowIndex = kara) Then
            made = 0
            tugi = False
            Return False
        End If



        Dim clientPoint As Point = grid.PointToClient(New Point(e.X, e.Y))
        '上下のみに着目するため、横方向は無視する
        clientPoint.X = 1
        Dim hit As DataGridView.HitTestInfo = grid.HitTest(clientPoint.X, clientPoint.Y)

        made = hit.RowIndex
        If (made = -1) Then

            Dim top As Integer = IIf(grid.ColumnHeadersVisible, grid.ColumnHeadersHeight, 0)
            top += 1  '// ...

            If (top > clientPoint.Y) Then
                'ヘッダへのドロップ時は表示中の先頭行とする
                made = grid.FirstDisplayedCell.RowIndex
            Else
                '最終行へ
                made = grid.Rows.Count - 1
            End If

        End If

        '追加用の行は無視
        If (made = grid.NewRowIndex) Then
            made -= made
        End If

        'Me.NumericTextBox1.Text = made

        tugi = (made > kara)
        Return (kara <> made)

    End Function



    'データの移動
    Private Function MoveDataValue(ByVal kara As Integer, ByVal made As Integer, ByVal tugi As Boolean) As Integer

        Dim table As DataTable = DirectCast(DataGridViewUe.DataSource, DataTable)

        '移動するデータの退避(計算列があればたぶんダメ)
        Dim rowData = table.Rows(kara).ItemArray
        Dim row As DataRow = table.NewRow()
        row.ItemArray = rowData

        '移動元から削除
        table.Rows.RemoveAt(kara)
        If (made > kara) Then made = made - 1
        '移動先へ追加
        If (tugi) Then made = made + 1

        If (made <= table.Rows.Count) Then
            table.Rows.InsertAt(row, made)
        Else
            table.Rows.Add(row)
        End If

        Return table.Rows.IndexOf(row)
    End Function



End Class