ExcelXP Tagset Underline Spanning Headers

[jardenne]

I want to be able to adjust the underlines for spanning headers in an Excel report using the ExcelXP tagset.  In the document below, there is a description of how to do exactly what I would like to do, but with the RTF tagset:

http://support.sas.com/resources/papers/proceedings11/246-2011.pdf

(see Figure 10, code at bottom of page 😎

When I run this code using the ExcelXP tagset, the inline style statements in the column line have no effect.  The underlines of the spanning headers span the entire table, rather than just the columns spanned by the headers.

Is there ANY way to reproduce this output with the ExcelXP tagset?  I have spent hours searching for a solution (or even a straight answer) and have not found it.

Thanks in advance,

Jon

[Cynthia_sas]

Hi,

  RTF files are meant to be opened and rendered by a Word processor. TAGSETS.EXCELXP files are meant to be opened and rendered by Microsoft Excel. The RTF spec was written a LONG time ago (1987) by Microsoft as a way to exchange files; the XML spec for Spreadsheet Markup Language XML was written/released with Office 2002/2003. So, yes, there are some fundamental, underlying differences between RTF and XML; just as there are some fundamental underlying differences between Word and Excel. For example, I -could- write my next user-group paper in Excel instead of Word, but that would a pain to do because papers are huge paragraphs of text. Or, I could keep my gift list in Word instead of Excel, but I like to add a new column every year, so I can keep track of what I've given family so I don't duplicate a present I've already given.

  It doesn't  surprise me that code that works in Word, when it opens an RTF file doesn't work the same way when you switch destinations. The place that I find Excel being "icky" about formatting is with lines, borders, cell heights and cell widths (and general cell formatting). Excel treats cells in its own way, and frequently the same syntax in another destination looks exactly as I want, but in Excel, no dice. I know this is small comfort to you.

  However, using TEXTDECORATION=UNDERLINE, I don't have quite as many issues as you were observing. If you notice in the screenshot, I put 2 reports in 1 sheet for comparison purposes. TAGSETS.EXCELXP will use TEXTDECORATION=UNDERLINE in some places. Notice how it is not used in example #1, but is used in Example #2. -- But Excel has a mind of its own as far as it "auto-adjusted" the cellheights for #2 and I'm not sure why. There is a suboption that I can fiddle with to try to adjust it, but I just wanted to really show that the underline only goes under the text. That is the difference between trying to underline with borders and using the style attribute for underlining. Borders span whole cells from edge to edge (usually) and underlines underline text. If you have SAS 9.2 or higher, then TEXTDECORATION might be a better approach for you.

cynthia

title; footnote;

ods escapechar='^';

ods tagsets.excelxp file='c:\temp\underspan.xls' style=sasweb

    options(sheet_interval='none' doc='Help');

proc report nowd data=sashelp.class

     style(report)=[rules=none frame=void cellspacing=0]

     style(header)=[background=white foreground=black];

where age = 13;

column (name

             ('^S={textdecoration=underline}1) this is spanned'

              age sex height weight));

define name / order style(column)={textdecoration=underline just=c

                                   cellwidth=1.5in};

compute after _page_ / style={just=c};

   line '^S={textdecoration=underline}1) Short Text Underlined';

endcomp;

run;

proc report nowd data=sashelp.class

     style(report)=[rules=none frame=void cellspacing=0]

     style(header)=[background=white foreground=black];

where age = 13;

column (name dispname

             ('^S={textdecoration=underline}2) this is spanned'

              age sex height weight));

define name / order noprint;

define dispname / computed 'Name'

       style(column)={just=c cellwidth=1.5in};

compute after _page_ / style={just=c};

   line '^S={textdecoration=underline}2) Short Text Underlined';

endcomp;

compute dispname / character length=40;

   dispname = '^S={textdecoration=underline}'||trim(name);

endcomp;

run;

ods _all_ close;