Copy/Past from Excel/Calc to xBrowse

[Marco Turco]

Hi,
as subject, do you think it could be possible to past one or more column from Excel/Calc to xBrowse ?
Thanks in advance.

[RAMESHBABU]

Hi Marco,

Yes it is possible and the code is here.

Code: Select all  Expand view

#include "fivewin.ch"
#include "xbrowse.ch"

* A Small Demonistration by Ramesh Babu to show Copied data from MsExcel in xBrowse

STATIC oClipBoard

FUNCTION main()

LOCAL cClipText, aText := {}, oDlg

SET DATE BRIT
SET CENT ON

DEFINE DIALOG oDlg TITLE "Test to show Copied Data from Excel in XBrowse"

DEFINE CLIPBOARD oClipBoard OF oDlg FORMAT TEXT

ACTIVATE DIALOG oDlg CENTERED ON INIT ( cClipText := oClipBoard:GetText()      ,;
                                        oClipBoard:Clear()                     ,;
                                        oClipBoard:End()                       ,;                
                                        aText := ParsePasteText(oDlg,cClipText) )
IF LEN(aText) >0
   XBROWSER aText
ENDIF

RETURN nil

*******************************************************************************
*** STATIC FUNCTION ParsePasteText( cText ) to Parse the Text from Windows  ***
*** Clipboard to the Required data type                                     ***
*******************************************************************************

STATIC FUNCTION ParsePasteText( oDlg, cText, cDelim)

LOCAL n, j, nCols, aTemp_Array := {}, uValue := "", lOk := .F., aText := {}

DEFAULT cDelim := Chr(9)

IF LEN(cText) = 0
   MsgInfo("You do not have any data in Clipboard to Import into xBrowse.")
   oDlg:End()
   RETURN {}
ENDIF

cText := StrTran( cText, CRLF, Chr(10) )
aText := hb_aTokens( cText, Chr(10) )

FOR n := 1 TO LEN( aText )
    aText[ n ] := hb_aTokens( aText[ n ], cDelim)
NEXT

IF ! EMPTY( aText )

   CursorWait()

   nCols       := LEN( aText[ 1 ] )
   aTemp_Array := {}

   FOR n := 1 TO LEN( aText )-1
       FOR j := 1 TO nCols
           uValue := uCharToVal( aText[ n ][ j ])
           aText[n,j] := uValue  
       NEXT j
   NEXT n

   CursorArrow()

ENDIF

MsgInfo("Your data has been sucessfully copied from Excel into xBrowse.")

oDlg:End()

RETURN aText

*******************************************************************************
*** STATIC FUNCTION uCharToVal( cText, cType ) to Convert Text Data to the  ***
*** Specified Data type                                                     ***
*******************************************************************************

STATIC FUNCTION uCharToVal( cText )

LOCAL uVal

cText := ALLTRIM( cText )

IF ( IsDigit( cText ) .OR. LEFT( cText, 1 ) == '-' ) .AND. ;
     LTRIM( STR( Val( cText ) ) ) == cText
     uVal := Val( cText )
ELSEIF UPPER( cText ) $ "T|TRUE|YES"
     uVal := .t.
ELSEIF UPPER( cText ) $ "F|FALSE|NO"
     uVal := .f.
ELSEIF EMPTY( uVal := CharToDate( cText ) )
     uVal := cText
ENDIF

RETURN uVal

*******************************************************************************
*** STATIC FUNCTION CharToDate( cDate ) to Convert Character to Date        ***
*******************************************************************************

STATIC FUNCTION CharToDate( cDate )

LOCAL cFormat, cc
LOCAL dDate

cFormat := Lower( Set( _SET_DATEFORMAT ) )
dDate   := CToD( cDate )

IF EMPTY( dDate )
   cc := LEFT( cFormat, 2 )
   Set( _SET_DATEFORMAT, If( cc == 'dd', 'mm-dd-yy', 'dd-mm-yy' ) )
   dDate := CToD( cDate )
   IF cc == 'yy' .AND. EMPTY( dDate )
      SET DATE AMERICAN
      dDate := CToD( cDate )
   ENDIF
ENDIF

Set( _SET_DATEFORMAT, cFormat )

RETURN dDate

************
 

[nageswaragunupudi]

Hi,
as subject, do you think it could be possible to past one or more column from Excel/Calc to xBrowse ?
Thanks in advance.

Yes.
After defining the xbrowse in normal way, (1) please ensure all columns are editable (oCol:nEditType := 1) and (2) set oBrw:lCanPaste := .t. ( default .f. )

Then we can copy any block of cells from Excel (or any other source) and paste into the xbrowse. Copy and Paste from Excel to XBrowse works exactly like copy and paste from one sheet of excel to another sheet of excel.

Please test this simple example.

Code: Select all  Expand view

#include "fivewin.ch"

function ExcelCopyPaste

   SET DATE ITALIAN
   SET CENTURY ON
   SET EPOCH TO 1950

   XBROWSER "CUSTOMER.DBF" FASTEDIT SETUP ( oBrw:nMarqueeStyle := 3, oBrw:lCanPaste := .t. )

return nil
 

Now try copy from an excel sheet blocks of similar data and paste into xbrowse.

Please see this video:
http://autode.sk/1lSGKnn

[Marco Turco]

Hi Rao,
I'm testing your sample but after the Ctrl-V the following error appear. Any ideas ?

Application
===========
Path and name: K:\fwh\samples\test.exe (32 bits)
Size: 3,121,152 bytes
Compiler version: xHarbour 1.2.3 Intl. (SimpLex) (Build 20150830)
FiveWin Version: FWHX 15.09
Windows version: 6.1, Build 7601 Service Pack 1

Time from start: 0 hours 0 mins 12 secs
Error occurred at: 30-11-2015, 19:19:42
Error description: Error BASE/1132 Bound error: array access
Args:
[ 1] = A { ... } length: 0
[ 2] = N 1

Stack Calls
===========
Called from: .\source\classes\XBROWSE.PRG => TXBROWSE:PASTE( 7131 )
Called from: .\source\classes\XBROWSE.PRG => TXBROWSE:KEYCHAR( 2530 )
Called from: => TWINDOW:HANDLEEVENT( 0 )
Called from: .\source\classes\CONTROL.PRG => TCONTROL:HANDLEEVENT( 1733 )
Called from: .\source\classes\XBROWSE.PRG => TXBROWSE:HANDLEEVENT( 12964 )
Called from: .\source\classes\WINDOW.PRG => _FWH( 3556 )
Called from: => DIALOGBOXINDIRECT( 0 )
Called from: .\source\classes\DIALOG.PRG => TDIALOG:ACTIVATE( 293 )
Called from: .\source\function\XBROWSER.PRG => XBROWSE( 212 )
Called from: test.prg => MAIN( 9 )

[nageswaragunupudi]

Many thanks for pointing out this bug. So you are using xharbour. I did not notice the change in the functionality of hb_atokens() of xharbour over a period of time.

Can you help me by making this change in xbrowse.prg and testing again?
Please locate this function in xbrowse. ( line 13144 in fwh15.09)

Code: Select all  Expand view

static function ClipTextAsArray( cText )

   local aText, n

   cText       := StrTran( cText, CRLF, Chr(10) )

#ifndef __XHARBOUR__
   if Right( cText, 1 ) == Chr(10)
      cText    := Left( cText, Len( cText ) - 1 )
   endif
#endif
   aText       := hb_aTokens( cText, Chr(10) )
   for n := 1 to Len( aText )
      aText[ n ]  := hb_aTokens( aText[ n ], Chr(9) )
   next

return aText

//----------------------------------------------------------------------------//
 

Please comment out or remove #ifndef __XHARBOUR__ and #endif
The revised code should like this:

Code: Select all  Expand view

static function ClipTextAsArray( cText )

   local aText, n

   cText       := StrTran( cText, CRLF, Chr(10) )

//#ifndef __XHARBOUR__
   if Right( cText, 1 ) == Chr(10)
      cText    := Left( cText, Len( cText ) - 1 )
   endif
//#endif
   aText       := hb_aTokens( cText, Chr(10) )
   for n := 1 to Len( aText )
      aText[ n ]  := hb_aTokens( aText[ n ], Chr(9) )
   next

return aText

//----------------------------------------------------------------------------//
 

Please make this change and test again.
If you can please confirm it is working well, then I shall include a suitable fix for the next release.

[Marco Turco]

Hi Rao,
this problem is solved now but there is another problem.
Essentially I can only past a column from Excel. It's impossible to past (ctrl-v) further columns on the right.
Any ideas ? Thank you in advance.

[nageswaragunupudi]

Not just one column.

You can paste a block

For example, you can select a Range with 3 columns and 4 rows in Excel.

When you press Ctrl-V when you are in any cell of xbrowse (example 4th column 5th row of xbrowse), all 3 columns and 4 rows will be pasted into xbrowse into cells from 4th to 7th column and 5th to 8th row of xbrowse.

In other words, the copy and paste work just like copying and pasting from one sheet of Excel to another sheet of Excel.

I hope you can see the video I linked in the above posting.

[Marco Turco]

I know I can copy more columns at the same time but if I try to copy single columuns it doesn't runs after the first one.

[nageswaragunupudi]

That is also working properly for me.
Can you please check again?
Please ensure that the columns nEditType is set to EDIT_GET.

[Marco Turco]

Hi Rao,
this video show you the problem adding the second column http://www.softwarexp.co.uk/beta/XBROWSE.html

Please note I added two messageinfo as log to check the problem in the xbrowse keychar method (line 2507)

case ::lCanPaste .and. nKey == 22 // Ctrl-V
msginfo("paste") && FIRST LOG
if ::SelectedCol():cDataType == 'P'
DEFINE CLIPBOARD oClp OF ::oWnd FORMAT BITMAP
uClip := oClp:GetBitmap()
if uClip != 0 .and. ::SelectedCol():nEditType > 0
::SelectedCol():VarPut( BmpToStr( uClip ) )
oClp:Clear()
endif
oClp:End()
endif

DEFINE CLIPBOARD oClp OF ::oWnd FORMAT TEXT
if FW_SetUnicode()
uClip := oClp:GetUnicodeText()
else
uClip := oClp:GetText()
endif
if ! Empty( uClip )
oClp:Clear()
endif
oClp:End()
msginfo(uclip) && SECOND LOG
if ! Empty( uClip )
::Paste( uClip )
endif

[nageswaragunupudi]

I assume you are starting with XBrowse of an Empty Array.
Please confirm if I am right.

If you start with an empty array, after paste, the browse array will have the same number of columns and rows that are pasted. In the above case the copied array has only one column. You can not later "add" columns or "rows" by pasting.

If you want to copy into an array, column after column then I advise creating an array with the required number of columns and rows first and then copy.

[Marco Turco]

Great !! It is fine now. Thank you Rao.

[nageswaragunupudi]

this video show you the problem adding the second column http://www.softwarexp.co.uk/beta/XBROWSE.html

Actually this program also should work as you expected.

But we need to make a little correction to xbrowse.

Please locate Method Paste( cText ) of TXBrwColumn.
Please note that is the Paste method of column not xbrowse. You may find this method around line 12925 of fwh15.09.

In this method please locate the line:

Code: Select all  Expand view

if ::cDataType == nil .or. ::cDataType == cType

please modify this line as

Code: Select all  Expand view

if ::cDataType == nil .or. ::cDataType == 'U' .or. ::cDataType == cType

After making this correction, please try your original example (as in the Video)

The modifications I suggested here apply to most previous versions.

[nageswaragunupudi]

viewtopic.php?f=3&t=31686

[Marco Turco]

Thank you Rau.