DataGrid with Excel Copy and Paste

I coudn''t blog last couple of days due to my busy schedule. So I am back !!!! Y'day and today i was tring to create a control with the excel copy and past it in the datagrid type of a comtrol. first it was a disster, with the help of winformsfaq site i managed to do it. I have paste the full code in the blog. i hope one day it will help you 2

'--------------------------------------------------------------------------------------------------
' Waha gini awelana sului
'------------------------------------------------------------------------------------------

Imports System
Imports System.Drawing
Imports System.Collections
Imports System.ComponentModel
Imports System.Windows.Forms
Imports System.Data
Imports System.IO

Public Delegate Sub DataGridSelectionChangingEventHandler(ByVal sender As Object, ByVal e As DataGridSelectionChangingEventArgs)


Public Class iOMSelectionDataGrid
Inherits DataGrid

'fired when a selection is about to change
Public Event SelectionChanging As DataGridSelectionChangingEventHandler

'(top,left,bottom,right) of selection
Private _selectedRange As GridRange

'used in drawing
Private _selectionRectangle As Rectangle
Private _clipRectangle As Rectangle

'used to record row-col of click
Private _mouseDownRow As Integer
Private _mouseDownCol As Integer
Private _mouseUpRow As Integer
Private _mouseUpCol As Integer

'used in autoscroll
Private lastMoveHorz As Boolean

Friend WithEvents ContextPasteMenu As System.Windows.Forms.ContextMenu
Friend WithEvents MenuItemPaste As System.Windows.Forms.MenuItem
Friend WithEvents MenuItemClear As System.Windows.Forms.MenuItem

Public Sub New()
_selectedRange = New GridRange
_clipRectangle = Rectangle.Empty

'used to redraw selection during a scroll
AddHandler Me.Scroll, AddressOf HandleScroll

'used to get a clipping rectange for the initial display
AddHandler Me.Paint, AddressOf FirstPaint

Me.ContextPasteMenu = New System.Windows.Forms.ContextMenu
Me.MenuItemPaste = New System.Windows.Forms.MenuItem
Me.MenuItemClear = New System.Windows.Forms.MenuItem
'
'ContextPasteMenu
'
Me.ContextPasteMenu.MenuItems.AddRange(New System.Windows.Forms.MenuItem() {Me.MenuItemClear, Me.MenuItemPaste})
'
'MenuItemPaste
'
Me.MenuItemPaste.Index = 0
Me.MenuItemPaste.Text = "&Paste"
'
'MenuItemClear
'
Me.MenuItemClear.Index = 1
Me.MenuItemClear.Text = "&Clear"
'
'set up double buffering to minimize flashing during draws
Me.SetStyle(ControlStyles.AllPaintingInWmPaint, True)
Me.SetStyle(ControlStyles.UserPaint, True)
Me.SetStyle(ControlStyles.DoubleBuffer, True)
End Sub 'New


#Region "Paint & Scroll Event Handlers"
Private Sub FirstPaint(ByVal sender As Object, ByVal e As PaintEventArgs)
'used to mark the size of original clientsize
RemoveHandler Me.Paint, AddressOf FirstPaint
If Not Me.DesignMode Then
_clipRectangle = Me.GetCellBounds(0, 0)
_clipRectangle.Height = Me.Height - _clipRectangle.Y - SystemInformation.HorizontalScrollBarHeight
_clipRectangle.Width = Me.Width - _clipRectangle.X - SystemInformation.VerticalScrollBarWidth
End If
End Sub 'FirstPaint


'helper method that gets the left column from knowing the initial cliprectangle
Public Function LeftColumn() As Integer
Dim hti As DataGrid.HitTestInfo = Me.HitTest(New Point(_clipRectangle.X, _clipRectangle.Y))
Return hti.Column
End Function 'LeftColumn

Private Sub HandleScroll(ByVal sender As Object, ByVal e As EventArgs)
DrawRange(True)
End Sub 'HandleScroll
#End Region

#Region "Selected Range property"

Public Property SelectedRange() As GridRange
Get
Return _selectedRange
End Get
Set(ByVal Value As GridRange)
'If Not (SelectionChanging Is Nothing) Then
Dim e As New DataGridSelectionChangingEventArgs(_selectedRange, Value)
RaiseEvent SelectionChanging(Me, e)
If e.Canceled Then
Return
End If
'End If
_selectedRange = Value
End Set
End Property

#End Region

#Region "mouse events that handle making selections"

Protected Overrides Sub OnMouseDown(ByVal e As System.Windows.Forms.MouseEventArgs)
'remember the initial click
If e.Button = MouseButtons.Left Then
Dim hti As DataGrid.HitTestInfo = Me.HitTest(New Point(e.X, e.Y))
_mouseDownRow = hti.Row
_mouseDownCol = hti.Column
_mouseUpRow = _mouseDownRow
_mouseUpCol = _mouseDownCol

'clear any existing selection
SelectedRange.Clear()
DrawRange(False)
ElseIf e.Button = MouseButtons.Right Then
'// Dispaly the menu on right click
ContextPasteMenu.Show(Me, New Point(e.X, e.Y))
End If

End Sub 'OnMouseDown


Protected Overrides Sub OnMouseMove(ByVal e As System.Windows.Forms.MouseEventArgs)
MyBase.OnMouseMove(e)

If e.Button = MouseButtons.Left Then
Dim hti As DataGrid.HitTestInfo = Me.HitTest(New Point(e.X, e.Y))

Select Case hti.Type
'move to a visible cell with no scrolling
Case HitTestType.Cell
If hti.Column <> Me._mouseUpCol OrElse hti.Row <> Me._mouseUpRow Then
lastMoveHorz = Me._mouseUpCol <> hti.Column

DrawRange(False)
Me._mouseUpCol = hti.Column
Me._mouseUpRow = hti.Row
SelectedRange = New GridRange(Me._mouseUpRow, Me._mouseUpCol, Me._mouseDownRow, Me._mouseDownCol)
DrawRange(True)
End If



Case HitTestType.ColumnHeader
If Me._mouseDownRow = -1 Then
Exit Select
End If
If Me.VertScrollBar.Value > 0 Then
Me.VertScrollBar.Value = Me.VertScrollBar.Value - 1
Me.CurrentRowIndex = Me.VertScrollBar.Value

Me._mouseUpCol = hti.Column
Me._mouseUpRow = Me._mouseUpRow - 1
SelectedRange = New GridRange(Me._mouseUpRow, Me._mouseUpCol, Me._mouseDownRow, Me._mouseDownCol)
DrawRange(True)
End If 'this.Invalidate();
Case HitTestType.RowHeader
If (True) Then
If Me._mouseDownCol = -1 Then
Exit Select
End If
Dim r As Rectangle = Me.GetCellBounds(0, Me._mouseUpCol)
If Me.HorizScrollBar.Value >= r.Width Then
Me.HorizScrollBar.Value -= r.Width
Me._mouseUpCol = Me._mouseUpCol - 1
Me.CurrentCell = New DataGridCell(Me.CurrentRowIndex, Me._mouseUpCol)

Me._mouseUpRow = hti.Row
SelectedRange = New GridRange(Me._mouseUpRow, Me._mouseUpCol, Me._mouseDownRow, Me._mouseDownCol)
DrawRange(True)
End If 'this.Invalidate();
End If
Case HitTestType.None
'Console.WriteLine("HitTestType.None");
If lastMoveHorz Then
Console.WriteLine("lastMoveHorz")
If Me._mouseUpCol < rectangle =" Me.GetCellBounds(0," _mouseupcol =" Me._mouseUpCol" currentcell =" New"> -1 Then
Me._mouseUpRow = hti.Row
End If
SelectedRange = New GridRange(Me._mouseUpRow, Me._mouseUpCol, Me._mouseDownRow, Me._mouseDownCol)
DrawRange(True)
End If
End If 'this.Invalidate();
Else
If Me._mouseUpRow < _mouseuprow =" Me._mouseUpRow"> 0 Then
Me._mouseUpCol = hti.Column
End If
Me.CurrentCell = New DataGridCell(Me._mouseUpRow, Me._mouseUpCol)

SelectedRange = New GridRange(Me._mouseUpRow, Me._mouseUpCol, Me._mouseDownRow, Me._mouseDownCol)
DrawRange(True)
End If 'this.Invalidate();
End If

End Select
End If
End Sub 'OnMouseMove


Protected Overrides Sub OnMouseUp(ByVal e As System.Windows.Forms.MouseEventArgs)
'if normal click clear things & click
If Me._mouseUpCol = Me._mouseDownCol AndAlso Me._mouseUpRow = Me._mouseDownRow Then
DrawRange(False)
_selectionRectangle = Rectangle.Empty
MyBase.OnMouseDown(e)
MyBase.OnMouseUp(e)
End If
End Sub 'OnMouseUp


#End Region

#Region "Drawing Code"

Private Sub DrawRange(ByVal showRange As Boolean)
'if removing selection just redraw
If Not showRange Then
_selectionRectangle = Rectangle.Empty
Me.Invalidate()

Return
End If

If SelectedRange.Left < _selectionrectangle =" Rectangle.Empty" rectangle =" Me.GetCellBounds(SelectedRange.Top," rectangle =" Me.GetCellBounds(SelectedRange.Bottom," x =" rect.Left" w =" rect1.Left" x =" rect1.Left" w =" rect.Left" y =" rect.Top" h =" rect1.Top" y =" rect1.Top" h =" rect.Top" _selectionrectangle =" New" height =" Me.Height" width =" Me.Width" datatable =" getExcelCopiedCells()" datatable =" CType(MyBase.DataSource," integer =" SelectedRange.Top" integer =" SelectedRange.Bottom" integer =" (SelectedRange.Bottom" integer =" SelectedRange.Left" integer =" SelectedRange.Right" integer =" (SelectedRange.Right" integer =" SelectedTop" integer =" SelectedLeft" selectedvalue =" Me(currentRowPos," selectedvalue = "NI" selectedvalue = "NP" selectedvalue = "NC" selectedvalue = "NF" selectedvalue = "ND" selectedvalue = "NT" selectedvalue = "NS" idataobject =" Clipboard.GetDataObject()" char =" {"> 0)
'Array to hold the split data for each row
Dim arrSplitData As Array

'Multipurpose Loop Counter
Dim iLoopCounter As Integer = 0

'Read a line of data from the StreamReader object
sFormattedData = srReadExcel.ReadLine()

'Split the string contents into an array
arrSplitData = sFormattedData.Split(charDelimiterArray)

If tblExcel2WinData.Columns.Count <= 0 Then
For iLoopCounter = 0 To arrSplitData.GetUpperBound(0)
tblExcel2WinData.Columns.Add()
Next
iLoopCounter = 0
End If

'Row to hold a single row of the Excel Data
Dim rowNew As DataRow
rowNew = tblExcel2WinData.NewRow()


For iLoopCounter = 0 To arrSplitData.GetUpperBound(0)
rowNew(iLoopCounter) = arrSplitData.GetValue(iLoopCounter)
Next
iLoopCounter = 0

'Add the row back to the DataTable
tblExcel2WinData.Rows.Add(rowNew)

rowNew = Nothing
End While

'Close the StreamReader object
srReadExcel.Close()

'Bind the data to the DataGrid
Return tblExcel2WinData
Else
MsgBox("Clipboard data does not seem to be copied from Excel!", MsgBoxStyle.Information)
Return Nothing
End If
Else
MsgBox("Clipboard is empty!", MsgBoxStyle.Information)
Return Nothing
End If

Catch exp As Exception
MsgBox(exp.Message, MsgBoxStyle.Information)
Return Nothing
End Try

End Function

Private Sub MenuItemClear_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles MenuItemClear.Click


Dim SelectedTop As Integer = SelectedRange.Top
Dim SelectedBottom As Integer = SelectedRange.Bottom
Dim SelectedTopToBottom As Integer = (SelectedRange.Bottom - SelectedRange.Top) + 1
Dim SelectedLeft As Integer = SelectedRange.Left
Dim SelectedRight As Integer = SelectedRange.Right
Dim SelectedLeftToRight As Integer = (SelectedRange.Right - SelectedRange.Left) + 1
Dim selectedValue As String

'// Row Navigation
For currentRowPos As Integer = SelectedTop To SelectedBottom

'//Column Navigation
For CurrentColPos As Integer = SelectedLeft To SelectedRight

Try

selectedValue = Me(currentRowPos, CurrentColPos)

If selectedValue = "NI" OrElse selectedValue = "NP" OrElse selectedValue = "NC" OrElse selectedValue = "NF" OrElse selectedValue = "ND" OrElse selectedValue = "NT" OrElse selectedValue = "NS" Then
'// Color cells do nothing
Else
Me(currentRowPos, CurrentColPos) = ""
End If

Catch ex As Exception
End Try

Next CurrentColPos

Next currentRowPos


End Sub

Protected Overrides Function ProcessCmdKey(ByRef msg As System.Windows.Forms.Message, ByVal keyData As System.Windows.Forms.Keys) As Boolean

Dim cellCurrent As DataGridCell
Dim curentCellValue As String

Const WM_KEYDOWN As Integer = &H100
Const WM_SYSKEYDOWN As Integer = &H104

If msg.Msg = WM_KEYDOWN Or msg.Msg = WM_SYSKEYDOWN Then
Select Case keyData
Case Keys.Control Or Keys.V
Call MenuItemPaste_Click(Nothing, Nothing)
End Select
End If

Return MyBase.ProcessCmdKey(msg, keyData)

End Function


End Class 'SelectionDataGrid '


#Region "GridRange class implementation"
'----^--- Pre-processor directives not translated

Public Class GridRange
Private _top As Integer
Private _left As Integer
Private _bottom As Integer
Private _right As Integer


Public Sub New()
_top = -1
_left = -1
_bottom = -1
_right = -1
End Sub 'New


Public Sub New(ByVal t As Integer, ByVal l As Integer, ByVal b As Integer, ByVal r As Integer)
_top = Math.Min(t, b)
_left = Math.Min(l, r)
_bottom = Math.Max(t, b)
_right = Math.Max(l, r)
End Sub 'New


Public Property Top() As Integer
Get
Return _top
End Get
Set(ByVal Value As Integer)
_top = Value
End Set
End Property

Public Property Bottom() As Integer
Get
Return _bottom
End Get
Set(ByVal Value As Integer)
_bottom = Value
End Set
End Property

Public Property Left() As Integer
Get
Return _left
End Get
Set(ByVal Value As Integer)
_left = Value
End Set
End Property

Public Property Right() As Integer
Get
Return _right
End Get
Set(ByVal Value As Integer)
_right = Value
End Set
End Property

Public Sub Clear()
Me.Bottom = -1
Me.Top = -1
Me.Left = -1
Me.Right = -1
End Sub 'Clear


Public Overrides Function ToString() As String
Return "TopLeft:" + Me.Top.ToString() + "," + Me.Left.ToString() + " BottomRight:" + Me.Bottom.ToString() + "," + Me.Right.ToString()
End Function 'ToString
End Class 'GridRange



#End Region

#Region "SelectionChangingEvent class implementation"


Public Class DataGridSelectionChangingEventArgs
Inherits EventArgs
Private _oldRange As GridRange
Private _newRange As GridRange
Private _canceled As Boolean


Public Sub New(ByVal oldValue As GridRange, ByVal newValue As GridRange)
_oldRange = New GridRange(oldValue.Top, oldValue.Left, oldValue.Bottom, oldValue.Right)
_newRange = New GridRange(newValue.Top, newValue.Left, newValue.Bottom, newValue.Right)
_canceled = False
End Sub 'New


Public Property Canceled() As Boolean
Get
Return _canceled
End Get
Set(ByVal Value As Boolean)
_canceled = Value
End Set
End Property

Public Property OldRange() As GridRange
Get
Return _oldRange
End Get
Set(ByVal Value As GridRange)
_oldRange.Bottom = Value.Bottom
_oldRange.Top = Value.Top
_oldRange.Left = Value.Left
_oldRange.Right = Value.Right
End Set
End Property

Public Property NewRange() As GridRange
Get
Return _newRange
End Get
Set(ByVal Value As GridRange)
_newRange.Bottom = Value.Bottom
_newRange.Top = Value.Top
_newRange.Left = Value.Left
_newRange.Right = Value.Right
End Set
End Property
End Class 'DataGridSelectionChangingEventArgs
#End Region