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 view  RUN

 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 view  RUN

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 view  RUN

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 view  RUN

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 view  RUN

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 view  RUN

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