Page 1 of 1 1
Topic Options
#125009 - 2004-08-16 08:35 AM Excel.Application: Write an Array vertically (column)
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
Guys,

this should be easy as pie one should think.
I was used to write my arrays horizontally (Rowwise) to my Excel log files, but then came the need to do that Vertically (Columnwise) as for the huge amount of data. So I thought this would be as braindead as the horizontal write and could be done with .Range.Value.

Wrong !

The following sample writes only the first item of the Array 20 times

Code:
break on

$file = @scriptdir + '\test.xls'
if exist($file) del $file endif

global $Array[19] ;20 elements !!

for $i = 0 to ubound($Array)
$Array[$i] = "Item " + $i
next

$xl = createobject("Excel.Application")
$_ = $xl.WorkBooks.Add
$xl.Range("A2:A"+(ubound($Array)+2)).Value = $Array ;Range should translate to A2:A21 = 20 cells
$_ = $xl.ActiveWorkbook.SaveAs("$file")
$xl.UserControl = 1
$_ = $xl.quit


exit 1



Anyone knows how to accomplish that ?
_________________________



Top
#125010 - 2004-08-16 08:55 AM Re: Excel.Application: Write an Array vertically (column)
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
k,

I know, the workaround is to loop the array and write single cells, but still I want to know the better solution
_________________________



Top
#125011 - 2004-08-16 09:11 AM Re: Excel.Application: Write an Array vertically (column)
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
well, it might be time for you to start using the basics
$workbook.sheets($sheetcount).cells($row,$column).value
_________________________
!

download KiXnet

Top
#125012 - 2004-08-16 09:13 AM Re: Excel.Application: Write an Array vertically (column)
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
lol, I was searching the docs too long before decided to click submit
_________________________
!

download KiXnet

Top
#125013 - 2004-08-16 09:27 AM Re: Excel.Application: Write an Array vertically (column)
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
Hmm ... and I should have waited with my reply to myself until after the first 'single cell loop approach' - reply was posted ... One could think it is solved otherwise ... well, this reply should reset the 'problem solved'-Flag

Edited by Jochen (2004-08-16 09:31 AM)
_________________________



Top
#125014 - 2004-08-16 09:45 AM Re: Excel.Application: Write an Array vertically (column)
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
so, I'm not sure what is your goal anyways...

instead of your one write data going per row, you write per column.
so, to replace your above code, it would be something like:
$column=$.sheets($1).columns.count+1
for $=0 to ubound($array)
$workb.sheets(1).cells($+2,$column).value=$array[$]
next

or did I miss something? (me really not sure of your goals in there...)
_________________________
!

download KiXnet

Top
#125015 - 2004-08-16 09:49 AM Re: Excel.Application: Write an Array vertically (column)
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
heh, if you have nothing to do, try this:
Quote:

$file = @scriptdir + '\test.xls'
if exist($file) del $file endif

global $Array[0,19] ;20 elements !!

for $i = 0 to ubound($Array,1)
$Array[0,$i] = "Item " + $i
next

$xl = createobject("Excel.Application")
$_ = $xl.WorkBooks.Add
$xl.Range("A2:A"+(ubound($Array,1)+2)).Value = $Array ;Range should translate to A2:A21 = 20 cells
$_ = $xl.ActiveWorkbook.SaveAs("$file")
$xl.UserControl = 1
$_ = $xl.quit



_________________________
!

download KiXnet

Top
#125016 - 2004-08-16 10:06 AM Re: Excel.Application: Write an Array vertically (column)
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
hmmm ... as said, I'd like to write that array to a column in one go

Tried the snippet from your last reply and initially thought that this could work but as I am an enemy of those kind of arrays it might be not suitable for me ... Anyway it doesn't work either:

this produces one cell (A2) with the value of :

Quote:

瑉浥〠





_________________________



Top
#125017 - 2004-08-16 10:33 AM Re: Excel.Application: Write an Array vertically (column)
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
for me it showed pure cryptic chars...
to you too it seems.
I hate this board screwing things up.


hmm... need a ciggy.
think this is little harder one.
I already got excel screaming with:
$xl.sheets(1).Columns(1).Value = $Array

it seems that Excel does not support this.
like, the programmers never though someone would like to do it this way
_________________________
!

download KiXnet

Top
#125018 - 2004-08-16 11:31 AM Re: Excel.Application: Write an Array vertically (column)
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
Quote:

for me it showed pure cryptic chars...
to you too it seems.
I hate this board screwing things up.




-> Yep, for me too, there were 3 squares in that cell


Quote:


hmm... need a ciggy.
think this is little harder one.
I already got excel screaming with:
$xl.sheets(1).Columns(1).Value = $Array

it seems that Excel does not support this.
like, the programmers never though someone would like to do it this way




-> Me too needs a cig ... I too never thought I would need to do it this way round but the amount of data forces me to do so.

Think we have to wait for Shawn (again) on this one
_________________________



Top
#125019 - 2004-08-16 11:56 AM Re: Excel.Application: Write an Array vertically (column)
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
the amount of data does not matter.
you have the same space available in both ways.
you have just some weird requirement why you wanna do it this way.
_________________________
!

download KiXnet

Top
#125020 - 2004-08-16 11:57 AM Re: Excel.Application: Write an Array vertically (column)
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
btw, how many sheets one workbook can hold?
_________________________
!

download KiXnet

Top
#125021 - 2004-08-16 12:04 PM Re: Excel.Application: Write an Array vertically (column)
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
I don't think so ..

There are 2^16 (65536) rows possible but only 2^8 (256) columns

Normally I have Servers in 1st column listed and whatever in 1st row but as whatever is this time greater than 256 items I need to do it this way

about the sheets in one workbook: I don't know exactly but I think it is 256
_________________________



Top
#125022 - 2004-08-16 12:14 PM Re: Excel.Application: Write an Array vertically (column)
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
k, next question is why you use excel?
CSV would be a lot easier and portable solution.
without those limits.
_________________________
!

download KiXnet

Top
#125023 - 2004-08-16 12:28 PM Re: Excel.Application: Write an Array vertically (column)
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
Why ?

because my admin scripts are using the principle of Click'n'finished.

No matter how often the single task runs it will always re-run only on missed Servers/clients and always produce a ready to report complete Excel Log file...

Why should I produce a csv file when I can have an excel file...

Ok, untill there is either the proof that one can't write an array direectly to a column or that it is possible, I will go (temporarily) with the 'loop <-> single cell' approach...

Hey, thanx for looking at it by the way
_________________________



Top
Page 1 of 1 1


Moderator:  Shawn, ShaneEP, Ruud van Velsen, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart 
Hop to:
Shout Box

Who's Online
2 registered (morganw, mole) and 414 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.134 seconds in which 0.095 seconds were spent on a total of 12 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org