
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