topic-43094

Posts: 1772

Joined: Thu Sep 05, 2019 05:32 AM

to Excel : using Array and "paste" it
Posted: Sun Apr 23, 2023 04:51 PM

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 (fw): Select all Collapse

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

as you can see it is easy and very quick

Code (fw): Select all Collapse

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

greeting,

Jimmy

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: to Excel : using Array and "paste" it
Posted: Sun Apr 23, 2023 07:39 PM

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 (fw): Select all Collapse

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 (fw): Select all Collapse

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.

Regards

G. N. Rao.

Hyderabad, India

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: to Excel : using Array and "paste" it
Posted: Sun Apr 23, 2023 08:31 PM

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 (fw): Select all Collapse

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

Regards

G. N. Rao.

Hyderabad, India

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: to Excel : using Array and "paste" it
Posted: Wed Apr 26, 2023 04:50 AM

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 (fw): Select all Collapse

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

Regards

G. N. Rao.

Hyderabad, India

MarcoBoschi

Posts: 1091

Joined: Thu Nov 17, 2005 11:08 AM

Re: to Excel : using Array and "paste" it
Posted: Fri Jan 03, 2025 05:47 PM

Dear Nage

Code (fw): Select all Collapse

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

Marco Boschi
info@marcoboschi.it

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: to Excel : using Array and "paste" it
Posted: Mon Jan 06, 2025 09:04 AM

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

Regards

G. N. Rao.

Hyderabad, India

MarcoBoschi

Posts: 1091