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>.

While the syntax required by APL+Win might be less than straightforward (I
don't know, since I am a Dyalog APL's user), most of the complication stems
from Excel not from APL+Win. The problem is that Excel supports matrixes
which, in the most general case, map to nested arrays and there's no helping
it.

If you can convince the person who builds the Excel spreadsheet to provide
you with one containing ONLY strings, only one column of them, then it's not
too hard. But beware. Anything that looks like a number will be converted to
a number. Not by APL, but by Excel which is trying to be helpful (and in
most cases succeeds in doing so). Anything that looks like a date, will be
converted to a date. Anything that looks like a price will be converted to a
currency number. And so on... Almost always complications in software are
born because the real world is complex.
Anyway, even dealing with all these is simply a matter of adding a "mix
format each" in front of your nested array (plus some little manipulation
before the "mix" since APL+Win, as far as I know, doesn't extend vectors to
make them as long as the longest when building the matrix...)
--
  WildHeart'2k3

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