Date:         Thu, 14 Dec 2006 15:11:15 -0800
Reply-To:     Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Subject:      Cell-addressable Excel output using the ExcelXP tagset
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

In data step reporting (often called data _null_ reporting even though
it's not limited to data _null_ steps) to a line printer, you can use
the N=PAGESIZE option of the file statement to make every character
space on the page directly addressable - that it, you can print
something in the upper left hand corner, then something in the bottom
right-hand corner, then something in the middle, and so on.  This is
especially useful when creating multi-column reports.

You can achieve a similar effect - direct cell addressability - in an
Excel "spreadsheet" created with the ExcelXP tagset by storing the cell
values and attributes in arrays, then iterating through the arrays in
row-column order at the end of your processing.  Here's an example:

=====
ods tagsets.excelxp file='c:\temp\bycell.xls' style=sasweb;
ods listing close;

data _null_;

    array celltext {10,10} $60. _temporary_;
    array cellattr {10,10} $60. _temporary_;

    celltext{2, 2} = 'hello';
    cellattr{2, 2} = 'font_weight=bold';

    celltext{5, 5} = 'goodbye';
    cellattr{5, 5} = ' ';

    celltext{8, 10} = ' ';
    cellattr{8, 10} = 'background=red';

    declare odsout ByCell();

    ByCell.table_start();

    do row = 1 to hbound(celltext, 1);
       ByCell.row_start();
       do col = 1 to hbound(celltext, 2);
          if celltext{row, col} ne '' or cellattr{row, col} ne '' then
             do;
             textcell = celltext{row, col};
             attrcell = cellattr{row, col};
             ByCell.format_cell(text: celltext{row, col} || '',
                                overrides: cellattr{row, col} || '');
             end;
          else
             ByCell.format_cell();
       end;
       ByCell.row_end();
    end;

    ByCell.table_end();

run;

ods _all_ close;
ods listing;
=====

You might find it useful to create additional arrays containing the
numeric values of the cells, range names, and so forth.

Three caveats:

- The LISTING destination must be closed when you do this.  Otherwise
   you'll get an error message (in 9.1.3).

- This doesn't work for the PDF destination - entirely blank columns
   are omitted from the output.  It does work for RTF.

- There's a bug that prevents array references from being used
   correctly.  That's why I use:

      ByCell.format_cell(text: celltext{row, col} || '',
                         overrides: cellattr{row, col} || '');

   instead of:

      ByCell.format_cell(text: celltext{row, col},
                         overrides: cellattr{row, col});

   I could also have used:

      ByCell.format_cell(text: trim(celltext{row, col}),
                         overrides: trim(cellattr{row, col}));

This was developed using the 31May2006 version of the ExcelXP tagset in
SAS 9.1.3 SP4 on Windows XP.  I would expect it to work on other
platforms; I am doubtful about earlier releases of the tagset but
(happily) don't have them available to try.