是 我的部落格

 

一個偷懶資料編輯器 ，從資料庫抓出來後，可以在上面編資料，存檔；從Excel copy 資料貼到 DataGirdView

 

    Private Sub setDBSource()
        dbBuilder = New OleDbConnectionStringBuilder()
        dbBuilder("Provider") = "Microsoft.Jet.OLEDB.4.0"
        dbBuilder("Data Source") = dbStr
        dbCon = New OleDbConnection(dbBuilder.ConnectionString)
        dbCon.Open()
        For count As Integer = 0 To sqlStr.Count - 1
            command(count) = New OleDbCommand(sqlStr(count))  'sql statment
            command(count).Connection = dbCon
            adapter(count) = New OleDbDataAdapter(command(count))
            builder(count) = New System.Data.OleDb.OleDbCommandBuilder(adapter(count))

            dataTable(count) = New System.Data.DataTable()   'assign data
            adapter(count).Fill(dataTable(count))

            Dim source As BindingSource = New BindingSource() 'bridge
            source.DataSource = dataTable(count)
            viewer(count).DataSource = source
            controller(count).BindingSource = source
        Next
        dbCon.Close()
    End Sub

 

 Private Sub saveTabe()    

        Try           
            viewer(Me.TabControl1.SelectedIndex - 1).EndEdit()
            viewer(Me.TabControl1.SelectedIndex - 1).CurrentCell = Nothing
            adapter(Me.TabControl1.SelectedIndex - 1).Update(dataTable(Me.TabControl1.SelectedIndex - 1))
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

 End Sub

 

   Private Sub PasteClipboard()  ' 阿肚仔寫的，忘了從那看來的
        Dim index As Integer = Me.TabControl1.SelectedIndex - 1
        Try
            Dim s As String = Clipboard.GetText()
            Dim lines As String() = s.Split(ControlChars.Lf)
            Dim iFail As Integer = 0, iRow As Integer = viewer(index).CurrentCell.RowIndex
            Dim iCol As Integer = viewer(index).CurrentCell.ColumnIndex
            Dim oCell As DataGridViewCell

            For Each line As String In lines
                If iRow < viewer(index).RowCount AndAlso line.Length > 0 Then
                    Dim sCells As String() = line.Split(ControlChars.Tab)
                    For i As Integer = 0 To sCells.GetLength(0) - 1
                        If iCol + i < Me.viewer(index).ColumnCount Then
                            oCell = viewer(index)(iCol + i, iRow)
                            If Not oCell.[ReadOnly] Then
                                If oCell.Value.ToString() <> sCells(i) Then
                                    oCell.Value = Convert.ChangeType(sCells(i), oCell.ValueType)
                                    oCell.Style.BackColor = Color.Tomato  '改過的區塊變色
                                Else
                                    iFail += 1
                                    'only traps a fail if the data has changed and you are pasting into a read only cell
                                End If
                            End If
                        Else
                            Exit For
                        End If
                    Next
                    iRow += 1
                Else
                    Exit For
                End If
                If iFail > 0 Then
                    MessageBox.Show(String.Format("{0} updates failed due to read only column setting", iFail))
                End If
            Next
        Catch generatedExceptionName As FormatException
            MessageBox.Show("The data you pasted is in the wrong format for the cell")
            Exit Sub
        End Try
    End Sub

 

    'key down event call  PasteClipboard
    Private Sub GradeDataGridView_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) _
        Handles GradeDataGridView.KeyDown, CoolingPatternDataGridView.KeyDown, ScriptPatternDataGridView.KeyDown, _
        StartProfileDataGridView.KeyDown, SegmentDataGridView.KeyDown, CzParaDataGridView.KeyDown, _
        CzSprayDataGridView.KeyDown, CzFaceDataGridView.KeyDown, MoldDataGridView.KeyDown, _
        RatioDataGridView.KeyDown, SprayDataDataGridView.KeyDown, StrandDataGridView.KeyDown
        If (e.Control AndAlso e.KeyCode = Keys.V) OrElse (e.Shift AndAlso e.KeyCode = Keys.Insert) Then
            PasteClipboard()
        End If
    End Sub