One of the most common user scenario faced by the developers is to support/provide copy/paste feature from the clipboard. With the Spread for COM product, it is with ease to copy paste data from Excel file. However, user cannot copy paste more than 500 rows of data to Spread for COM in a single paste transaction.
By default Spread has 500 rows in a sheet. When user copies more than 500 rows from another sheet/excel sheet and try to paste it in Spreadsheet, it only paste 500 rows and rest of the rows are dropped.So user never knows how many rows the end user is going to copy from Excel sheet and paste it into SpreadSheet. This comes to as a restriction in Spread where user cannot set the number of rows at design time for a SpreadSheet.The same applies to Columns. Spread does not automatically increase the Column/Row count on data paste.
The approach to resolve this problem involves
- Find the length of data (number of rows copied from Excel file) in Clipboard,
- Before pasting starts, set the MaxRows property of the Spread to take the new value, and then allow the pasting.
- Once the pasting is done, set MaxRows property should be set back to the original.
Hence, for the implementation, follow the steps mentioned below :
- Declare the API’s in the code, and then create a function that will help in finding the total number of rows/columns in the clipboard data. This function will return an array, with first element referring to the row count, and the second referring to the column count.
- Set AutoClipBoard property to True
- Handle KeyPress Event
The following code (in Visual Basic 6) demonstrates on the usage of Clipboard API's along with Spread for COM product, to achieve the desired result.
Option Explicit Const CF_SYLK = 4 Const CF_DSPTEXT = &H81 Declare Function OpenClipboard Lib "user32" (ByVal Hwnd As Long) As Long Declare Function CloseClipboard Lib "user32" () As Long Declare Function IsClipboardFormatAvailable Lib "user32" (ByVal wFormat As Long) As Long Declare Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As Long Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" (ByVal lpString As String) As Long Function ClipBoard_RangeSize() Dim lhCB&, lpCB&, lRet&, lSize&, sText$ Dim aTmp, sTmp$, nRow&, nCol& If IsClipboardFormatAvailable(CF_SYLK) Then If OpenClipboard(0&) <> 0 Then lhCB = GetClipboardData(CF_DSPTEXT) If lhCB <> 0 Then lpCB = GlobalLock(lhCB) If lpCB <> 0 Then lSize = GlobalSize(lpCB) sText = Space$(lSize) lRet = lstrcpy(sText, lpCB) lRet = GlobalUnlock(lhCB) sText = Left(sText, InStr(1, sText, Chr$(0), 0) - 1) End If End If CloseClipboard End If aTmp = Split(sText, " ") If UBound(aTmp) > 2 Then sTmp = aTmp(UBound(aTmp) - 2) nRow = Left(sTmp, Len(sTmp) - 1) sTmp = aTmp(UBound(aTmp)) nCol = Left(sTmp, Len(sTmp) - 1) End If End If ClipBoard_RangeSize = Array(nRow, nCol) End Function
Set AutoClipBoard Property
Make sure that Spread’s AutoClipBoard property is set to True so that it has access to the clipboard. If not set at the design time, it is recommend to do so in the Form Load event.
Private Sub Form_Load() fpSpread1.AutoClipboard = True End Sub
Handle KeyPress Event
The best way to trap the Ctrl+V (for paste) is using the KeyPress event of the Spread for COM. In this event, get the number of rows from clipboard through ClipBoard_RangeSize() function. and set that to the MaxRows property of Spread for COM product.
Private Sub fpSpread1_KeyPress(KeyAscii As Integer) If KeyAscii = 22 Then 'paste(ctrl+ v) numRows = ClipBoard_RangeSize(0) fpSpread1.MaxRows = numRows fpSpread1.ClipboardPaste End If End Sub
This way, irrespective of the rows/columns copied to Clipboard from the Excel, can be pasted in the Spread for COM product. Please download the working sample here in VB6. Download Sample