Formatting html ods output for Excel

[Breego]

I am using the code below to output an Excel file. I am using Sas 8 but I can also use Sas 9. I am using Sas for Unix. I can't figure out how to do three things:

1. Get it to output gridlines instead of solid white background when I open it in Excel. Manually selecting "show gridlines" within Excel tools,options,show gridlines doesn't even work for some reason.

2. Get it to output titles without wrapping text - keep each title on one line, while retaining the specified column widths within the tables below. Right now it's wrapping text and widening the table column below.

3. Change the title color from blue to black. Color=black in the title statement actually increases the font size and doesn't change the color. That seems like a bug. ?

I've searched and searched. Any advice?

ods html file = '/projects/tndcs/regional08/reg_out/purpose 1.xls' style=sasweb;
ods listing close;
ods escapechar="^";

proc report data=sasdata.inv_sub nowindows headline headskip split='*'
style(header)=[font_weight=bold background=ltgray foreground=black];

column iy
(&REGION
("Sample Header"
("Total" inv_tot sub_tot pct_sub_tot)
("Under 1" inv_1 sub_1 pct_sub_1)
("Age 1-13" inv_2 sub_2 pct_sub_2)
("Age 14+" inv_3 sub_3 pct_sub_3 ) ) );

define iy / display center 'Cohort|Year' width=10;
[SERIES OF MORE DEFINE STATEMENTS HERE, ALL ARE WIDTH=10]

ods escapechar="^";

title "^S={font_weight=bold font_size=10 pt} Sample Title";
run;

ods html close;
ods listing;

[Cynthia_sas]

Hi:
When you use ODS HTML, you are not actually creating a .XLS file. Even though you provide a file extension of .XLS, if you opened the resulting file with a text editor (like Notepad or VI), you would see HTML markup tags in the file.

Excel KNOWS how to open the HTML file and how to render the HTML tags.

In SAS 8, there was only the ODS HTML method for creating formatted (cosmetically pretty) files that Excel could open and render (generally respecting the formatting, colors, fonts, etc.).

In SAS 9, there are 4 methods to create files that Excel can open and render:
1) Use ODS HTML -- I do NOT recommend this method because Excel is not happy rendering HTML 4.01 tags.

2) Use ODS HTML3 -- which creates HTML 3.2 tags -- and Excel is fairly happy rendering these HTML tags. (This is the same HTML as you create with SAS 8 -- between SAS 8 and SAS 9 the "flavor" of HTML that ODS created changed from HTML 3.2 to HTML 4.01).

3) Use ODS MSOFFICE2K -- I DO recommend this method, because this HTML file is composed of Microsoft-flavored HTML tags -- which Excel is happier about rendering.

4) Use ODS TAGSETS.EXCELXP -- which creates Microsoft Spreadsheet Markup Language XML tags -- which Excel is happy about rendering. I also recommend this method.

In SAS 8, you only have the equivalent of #2. There are a few other methods (like ODS CSV) but they mostly do not create "pretty" output in Excel.

As for your questions.
#3: The style attribute is Foreground ... try this:
[pre]
title "^S={font_weight=bold font_size=10 pt foreground=black} Sample Title";
[/pre]

#2: I think that wrapping happens differently for each of the 4 methods. One thing you can do for HTML is to insert a non-breaking space character in the title -- but I can't remember whether Excel honors the   character. I believe that TAGSETS.EXCELXP and MSOFFICE2K handle the title more gracefully in Microsoft flavored markup than with the vanilla W3C HTML markup methods.

#1: You may have to change the style template in order to get interior table lines. I actually think this is another instance where TAGSETS.EXCELXP works better than the HTML methods.
For example, with the code below, here are the interior table lines results for each method
1) ODS HTML = no interior table lines
2) ODS HTML3 = no interior table lines
3) ODS MSOFFICE2K = no interior table lines(need style template to change)
4) TAGSETS.EXCELXP = do get interior table lines

Sometimes, however, interior table lines are considered part of the HTML background or the table background and it's possible that Excel may not print them. (A tick mark against the HTML method.)

My recommendation would be to switch to SAS 9 and use either MSOFFICE2K or TAGSETS.EXCELXP. If you decide to go down the Spreadsheet Markup Language road, then you will certainly want to read the site for the ExcelXP information:
http://support.sas.com/rnd/base/ods/odsmarkup/
(ExcelXP is in the middle of the web page -- note that the latest and greatest version of the tagset is June 2007, which is available for download.)

If you decide to stick with an HTML-based approach, then you might wish to consult with Tech Support for help with a style template that will draw borders for you. With HTML based methods, you're not really touching the gridline function in Excel, instead, you would be drawing borders around all the cells by changing the borderwidth and bordercolor of the line to be used. The ExcelXP tagset, however, has a GRIDLINES='YES' option that tells Excel to turn on gridlines for printing. Pretty cool! (the option would go inside the "options" parentheses. I didn't put it in my code, because I don't remember if Gridlines was available in the first version of the ExcelXP tagset that was delivered with SAS 9.)

cynthia

*** some SAS 9 test code;
[pre]
ods html file = 'purpose1_ht4.xls' style=sasweb;
ods html3 file = 'purpose1_ht3.xls' style=sasweb;
ods msoffice2k file = 'purpose1_mso.xls' style=sasweb;
ods tagsets.excelxp(id=1) file = 'purpose1_xp.xls' style=sasweb;
ods tagsets.excelxp(id=2) file = 'purpose1_xp2.xls' style=sasweb
options(embedded_titles='yes' doc='help' );

ods listing close;
ods escapechar="^";

proc report data=sashelp.shoes nowindows split='*'
style(header)=[font_weight=bold background=ltgray foreground=black];
title "^S={font_weight=bold font_size=10 pt foreground=black} Sample Title";
column region product sales inventory returns;
define region /group;
define product /group;
define sales /sum;
define inventory /sum;
define returns /sum;
rbreak after / summarize;
run;

ods _all_ close;
ods listing;
[/pre]