topic inline style in excel output? ODS tagset in ODS and Base Reporting

inline style in excel output? ODS tagset

Eva — Tue, 25 Jan 2011 14:03:49 GMT

Dear all,

I use inline style in my html output. I do this by adding a data field to my data in the data step:

ods escapechar = '~';

data work.newfile;
set myoldfile;
newfield = "~S={font_weight=bold}blablabla~S={}~1n~S={font_size=-1}more text";
run;

After that I run a simple proc report.

So I have inline style for a font_weight, line break and font_size.
Can I do that in excel as well? The above code does not work in excel output.

Best wishes
Eva

Re: inline style in excel output? ODS tagset

Cynthia_sas — Tue, 25 Jan 2011 17:15:46 GMT

Hi:
I am not sure that you can use negative numbers to specify font size. The numbers you specify when you use the font_size attribute can be a relative font size (such as an HTML font size of 1 through 7) or a PT size or other units of measure, as identified here (where it explicitly says that the dimension for font-size must be a non-negative number):
http://support.sas.com/documentation/cdl/en/odsug/61723/HTML/default/a002972093.htm

When I tried this, then the second line of "more text" was in an 8pt font.
[pre]
newfield = "~S={font_weight=bold}blablabla~S={}~1n~S={font_size=8pt}more text";
[/pre]
I used the MSOFFICE2K destination which creates "Microsoft friendly" HTML tag for my HTML file. I find that Excel is happiest opening this type of HTML.

cynthia

Re: inline style in excel output? ODS tagset

Eva — Wed, 26 Jan 2011 08:46:00 GMT

Dear Cythia,

The font_size=-1 works perfect with ods html. That's why I used it. I first did my report for HTML and now I have to add excel as output and try to fit in as much as I can what I did for the html output.

For Excel I used the tagset.excelxp
Here everything I needed worked except the inline style I mentioned in this thread.

I now tried your suggestion with tagset.msoffice2k and the inline style now works just as in html. Even the font_size=-1 which indeed is relative value.

With this tagset everything works I used with the excelxp tagset except the ods options column_width nor frozen_headers = 'yes' nor sheet_name = 'xy'

Do I have to choose now or is there a way to get everything? 🙂

Best wishes
Eva

Re: inline style in excel output? ODS tagset

Cynthia_sas — Wed, 26 Jan 2011 21:25:55 GMT

Hi:
since the doc says that font size must be a non-negative number, I am baffled by how the '-1' even works. That would be a question for Tech Support.

TAGSETS.EXCELXP has different sub-options than other destinations. For example, TAGSETS.EXCELXP has frozen_headers and sheet_name sub-options that are just not built in to the "regular" HTML-based ODS destinations like ODS HTML or ODS HTML3. That's because TAGSETS.EXCELXP creates Microsoft Office 2003 Spreadsheet Markup Language XML tags and NOT HTML tags. So sub-options that work with TAGSETS.EXCELXP will not work with ODS HTML, ODS HTML3 or ODS MSOFFICE2K.

There is, however, a new HTML-based destination, TAGSETS.MSOFFICE2K_X, that may offer you some solution. The information about it is described here:
http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html

The exciting thing about TAGSETS.MSOFFICE2K_X is that it adds sub-options similar to the TAGSETS.EXCELXP suboptions except to an HTML-based destination. For example, there are extensive print setup sub-options and (the good news for you) a SHEET_NAME sub-option. It looks like there might be a FROZEN_HEADERS sub-option too. So maybe you can have everything you want.

Or, it may be easier to change your font_size from a negative number to a point size and keep using TAGSETS.EXCELXP with the existing sub-options. The point size should work for ODS HTML instead of the -1.

cynthia

Re: inline style in excel output? ODS tagset

Eva — Mon, 31 Jan 2011 07:22:39 GMT

Dear Cynthia,

Unfortunately, there is no tagset that has everything so the programmer has to choose which fits best.

The problem with the excelxp tagset was not only the font size -1 but that it didn't display anything I did with the inline style (no bold font no line break e.g.).

When I use the msoffice2k tagset I don't have the options sheet_name and frozen_headers.

When I use the msoffice2k_x tagset I can't use any line break. I had e.g. two title statements. Which resulted in a code with . As SAS didn't use I got the error in excel that the
didn't have a closing tag.

So no tagset with everything. But now I have a selection and try to choose the best tagset for my needs.

Thanx a lot for the idscussion!
Eva Message was edited by: Eva