to Excel : using Array and "paste" it

[Jimmy]

hi,

i´m not sure if this Technique is know : you can "paste" a Array into RANGE of a Excel Sheet

to build a Array from DBF is no Problem
than you need to "calculate" RANGE to fit Array Data

Code: Select all | Expand

  cEnde := ZAHL2CHR( nColCount )
  oSheet:range( "A1:" + cEnde+ + LTRIM( STR( nLen ) ) ) :value := aArray

as you can see it is easy and very quick

Code: Select all | Expand

FUNCTION ZAHL2CHR( nLFcount )
LOCAL nMal
LOCAL cEnde

   IF nLFcount > 26
      nMal := INT( nLFcount / 26 )
      IF nMal = nLFcount / 26
         cEnde := CHR( nMal + 64 - 1 ) + CHR( 90 )
      ELSE
         cEnde := CHR( nMal + 64 ) + CHR( ( nLFcount - ( nMal * 26 ) ) + 64 )
      ENDIF
   ELSE
      cEnde := CHR( nLFcount + 64 )
   ENDIF
RETURN cEnde
 

[nageswaragunupudi]

Yes. Very well known.

aArray := oRange:Value for reading and
oRange:Value := aArray for assigning
"should" work.
And work perfectly with VB.

When it comes to Harbour and xHarbour there are some issues.
We need to be aware of the differences between xHarbour and Harbour and also between older and current versions of Harbour.

These are the reasons, why we advise using
aData := RsGetRows( oRs ) instead of aData := oRs:GetRows()
and
aData := xlRangeValue( oRange ) instead of aData := oRange:Value.

Now about assignment:

Code: Select all | Expand

oSheet:Range( "A1:C1" ):Value := { 1, 2, 3 } //WORKS.
oSheet:Range( "A1:C2" ):Value := { {1,2,3},{4,5,6} } // FAILS, though works with VB
 

The following code works:

Code: Select all | Expand

aData := {{1,2,3},{4,5,6}}
oRange := oSheet:Range( "A1:C2" )
for i := 1 to len( aData )
  oRange:Rows( i ):Value := aData[ i ]
next
 

This is how FW_DbfToExcel() function is implemented.

[nageswaragunupudi]

It gets trickier when we write code for library functions. Our functions should work with all language installations of Excel.

While "A1:D9" works with some languege installations of Excel, we need to write as "A1;D9" for some other language installations.

So, it is safer to write it as :

Code: Select all | Expand

oRange := oShee:Range( oSheet:Cells( 1,1 ), oSheet:Cells( nLastRow, nLastCol ) )
 

[nageswaragunupudi]

Code:
FUNCTION ZAHL2CHR( nLFcount )
LOCAL nMal
LOCAL cEnde

IF nLFcount > 26
nMal := INT( nLFcount / 26 )
IF nMal = nLFcount / 26
cEnde := CHR( nMal + 64 - 1 ) + CHR( 90 )
ELSE
cEnde := CHR( nMal + 64 ) + CHR( ( nLFcount - ( nMal * 26 ) ) + 64 )
ENDIF
ELSE
cEnde := CHR( nLFcount + 64 )
ENDIF
RETURN cEnde

We may consider using this simplified codeblock:

Code: Select all | Expand

{|n|n--,If(n<26,Chr(n+65),Chr(Int(n/26)+64)+Chr(n%26+65))}

[MarcoBoschi]

Dear Nage

Code: Select all | Expand

oSheet:Range( "A1:C1" ):Value := { 1, 2, 3 } //WORKS.
oSheet:Range( "A1:C2" ):Value := { {1,2,3},{4,5,6} } // FAILS, though works with VB <<<<<<<<<<<<<<< this problem 
 

this problem has been resolved?

Many thanks

Marco

[nageswaragunupudi]

There is no problem to be resolved.
We just need to be aware of the differences of behavior between VB vs. (x)Harbour and write our code accordingly.
Please use the sample code I provided above and that works well

[MarcoBoschi]

Many Thanks Nage
Happy New Year

Marco