APL+Win reading from Excel

Quote:

> I find APL+Win's reading from Excel spreadsheets to be overly complicated.

> A few months ago, with all sorts of kludgy code and tests, I managed to

> read in the values. Now I have a situation, but I can't figure out all the

> hoops I jumped through before. Why can't it be simple? I have a column of

> cells. I want to read it into APL+Win as a literal matrix. Blank cells

> would simply be blank rows. No nesting!! Then with real simple code I

could

> manipulate it and do what I want. Very simple, yet so hard.

> Don <donwiss at panix.com>.

I am not sure from your complaint exactly what it is that you find "overly"

complicated, but I can imagine three areas:

1) Hooking up to Excel as an ActiveX object.

2) Dealing with the nesting

3) Dealing with empty cells.

First of all, let me note that empty cells are not the same as blank cells,

in the same fashion that an empty vector is not the same as a character

vector of spaces in APL. If you want to read blanks (that is, spaces) then

you have to have spaces in Excel. You can't ask the world to give up the

concept of an empty cell, just because you are not interested in it this

week. If you are reading empty cells, then you can substitute in your

desired spaces in APL.

I also am not sure exactly what you mean by a "literal" matrix. If you mean

it in one sense, I would think of a character matrix. If you have numeric

as well as character values, then you might mean a two-dimensional

heterogeneous matrix, which would be somewhat different. I will go with the

guess that you are trying to read character values.

1) Hook up to Excel

'xl' []wi 'New' 'Excel.Application'

'xl' []wi 'xVisible' 1

{Load your Excel worksheet}

('xl' []wi 'xActiveSheet') []wi 'xRange > rng' 'd1' 'd10'

{I use column D and ten rows just as an example}

wissfull {gets} {rotate} 'rng' []wi 'xValue2'

{You have to rotate this because Excel thinks of columns and rows where

APL thinks of rows and columns. We would call this 1D to 10D, right?}

You now have your data in the variable wissfull whose shape is 10 1

2) If you actually have blanks (spaces) in the cells that have no other

characters, all you have to do to deal with the nesting is disclose it and

use indexing with axis

wissful2 {gets} {disclose} wissfull

{to make its shape 10 1 somelength}

wissful3 {gets} 1 {squad}[2] wissful2

{to make its shape 10 somelength;

you can get the squad symbol with Alt+Shft+L}

and there is is your character matrix.

3) If you have empty cells in the data you read from Excel, then you can

substitute in blanks with a simple (or at least a relatively uncomplicated

one-test) function.

empty2blank;row

:for row :in {iota}columnlength

:if 8204='#' []wi 'VT' (wissful[row;1])

wissfull[row;1] {gets} ({enclose} ' {space}{space} ')

:endif

:endfor

where 8204 is the variant array type that I get for a row when I read empty

cells from Excel into an array.

I don't consider this "all sorts of kludgy code" and it is only one test,

but beauty is {fill in the empty vector}.

If you are reading simple numeric values in some cells, you will get an

array that has those rows padded with zeroes. If you are reading other

types of values, then you will have to convert those to whatever you want to

deal with in APL. That would make it more complicated, kludgy, and testing.

Colyn Phillips