Page 1 of 1
to Excel : using Array and "paste" it
Posted: Sun Apr 23, 2023 4:51 pm
by 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
Re: to Excel : using Array and "paste" it
Posted: Sun Apr 23, 2023 7:39 pm
by 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.
Re: to Excel : using Array and "paste" it
Posted: Sun Apr 23, 2023 8:31 pm
by 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 ) )
Re: to Excel : using Array and "paste" it
Posted: Wed Apr 26, 2023 4:50 am
by 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))}
Re: to Excel : using Array and "paste" it
Posted: Fri Jan 03, 2025 5:47 pm
by 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
Re: to Excel : using Array and "paste" it
Posted: Mon Jan 06, 2025 9:04 am
by 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
Re: to Excel : using Array and "paste" it
Posted: Tue Jan 07, 2025 9:33 am
by MarcoBoschi
Many Thanks Nage
Happy New Year
Marco