Two questions that I believe are related to the ExcelXP tagset.
1) I have one of those really long legal titles that takes up row 5 of an Excel spreadsheet. I've mimicked the title below and the carriage returns that I need, but can't figure out how to obtain with SAS.
2) The title that is returned isn't half bad, although the client will object. When I open the spreadsheet, the title is partially hidden, and I need to do a manual fix. For that particular title, I almost need an Excel row height of 55. Any options that I've overlooked?
%let title5="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz";
ods html close;
ods tagsets.excelxp file='c:\test long titles.xls';
Well, for some reason, I had to put just=c into a style template. I could not get centering working with the center command in the TITLE statement or in the ESCAPECHAR style override.
I -did- manage to get the HEIGHT working for the TITLE line. If you say autofit_height='no' then you can set a specific row height for the title using the ROW_HEIGHTS suboption.
But then, since the title needs to span ALL the columns, I had odd wrapping for the XXX, YYY and ZZZ -- so I needed to increase the width of every column so that all the columns together would be wide enough to fit the long text.
Last, I had to use the special string that represents Alt+Enter for Excel -- that is the string. Excel only allows you to specify a "return" in a cell by typing Alt+Enter. Apparently, the way you specify the equivalent of Alt+Enter in a Spreadsheet Markup Language XML file is
...and so that special string needed to have protectspecialchars set to off so the & would not get turned into & when it was used. (Just like < gets "protected" by being turned into < and > gets turned into > )
When I used Office 2007 to open the file generated by my code, the title did span the entire table. It must be something different between 2003 vs 2007. I did not have to use the Merge_titles_footnotes suboption -- but it's nice to know that it will work in a situation like this!