BookmarkSubscribeRSS Feed
SAS_Doctor
Calcite | Level 5
Can anybody tell me if banner_color_even="lightyellow" and banner_color_odd="palegoldenrod" works for them when exporting a table to excel?

I am producing an html page that has an export to excel button. The colors show correctly in html but when I export it to excel the colors go weird...

The col_color_odd & even works but not the banner.

I am using the tableeditor tagset:

%include 'D:\SSC\SAS Source Code\SSC_Op_Analysis_Prod\TableEditor.tpl' / nosource2;
%let _ODSDEST=tagsets.tableeditor;
%let _ODSOPTIONS=options(frozen_HEADERs="yes"
title_size = "10pt"
gridline_color = "blue"
HEADER_fgcolor="black"
HEADER_bgcolor="lightyellow"
HEADER_SIZE="8pt"
rowHEADER_fgcolor="black"
rowHEADER_bgcolor="lightyellow"
rowHEADER_SIZE="8pt"
DATA_SIZE="8pt"
col_color_even="lightyellow"
col_color_odd="palegoldenrod"
/*banner_color_even="lightyellow"*/
/*banner_color_odd="palegoldenrod"*/
/*alert_text="Report uses MMS Test Database"*/
sort="YES"
reorder_cols="yes"
zoom_table="80%"
gridline="YES"
file_format="Excel" );
%let _ODSSTYLE=SASWEB;
%let _DEBUG=log;
4 REPLIES 4
SAS_Doctor
Calcite | Level 5
This worked yesterday morning and then I applied some MS updates. That's when it started acting funny.

I am using MS Office Excel 2007 (12.0.6550.500) SP2 MSO(12.6554.5001) running on Window XP Professional Professional 2002 SP 3.
SAS Information Delivery Portal 4.2
SAS Server 9.2 running in the Stored Process Server (TS2M3)
Andre
Obsidian | Level 7
For me through IE only
this code opens under Excel 2007 with the internal content completely in blue

[pre]
ODS tagsets.tableeditor file="d:\temp\tableeditor.htm"
options(frozen_HEADERs="yes"
title_size = "10pt"
gridline_color = "blue"
HEADER_fgcolor="black"
HEADER_bgcolor="lightyellow"
HEADER_SIZE="8pt"
rowHEADER_fgcolor="black"
rowHEADER_bgcolor="lightyellow"
rowHEADER_SIZE="8pt"
DATA_SIZE="8pt"
col_color_even="lightyellow"
col_color_odd="palegoldenrod"
banner_color_even="lightyellow"
banner_color_odd="palegoldenrod"
/*alert_text="Report uses MMS Test Database"*/
sort="YES"
reorder_cols="yes"
zoom_table="80%"
gridline="YES"
file_format="Excel" );

proc print data=sashelp.class;run;
ods tagsets.tableeditor close;
[/pre]

but it seems that there is a new solution for odd even with the tagsets.excelxp in the recent paper of Chevell Parker at page 5/ 6 SGF2011
(not tested already)

Paper 250-2011
The Perfect Marriage: The SAS Output Delivery System (ODS) and Microsoft Office
Chevell Parker, SAS Institute

Andre
Cynthia_sas
SAS Super FREQ
Hi:
In prior versions of Office, I think that you only have a palette of 56 colors with Excel (like 2000, 2003 and even 2007). It's not until Excel 2010 that you have all 16.7 million colors. So that means browsers will show more "accurate" colors than earlier versions of Excel. For example -- a color that is defined in hex as #6495ED (light blue) will show as blue in the browser, but is mapped to a more purple/lavender color in Excel 2003 and 2007.

You'll have to look up the 56 colors that are acceptable to Excel. This site isn't a Microsoft site, but they have some history that goes back to Excel 97 and colors:
http://www.mvps.org/dmcritchie/excel/colors.htm

This page talks about how to change the palette, but doesn't show the default palette:
http://support.microsoft.com/kb/288412

cynthia
SAS_Doctor
Calcite | Level 5
Andre,

Tanks for reconfirming my results of all data in blue. I'll readup on the paper you discussed from SGF2011 and excelxp. I hope it gives the users a way to dowload from html to excel.

Cynthia,

It's odd that those colors work for col_color_even="lightyellow" & col_color_odd="palegoldenrod", but when I comment those two lines out and uncomment banner_color_even="lightyellow" & banner_color_odd="palegoldenrod" it doesn't work. So I tried banner_color_even="#FFFF99" & banner_color_odd="#FFFFCC" (color 20 & 36) and everything still comes out in blue like it did for Andre. So I just tried it with yellow and green and still got all blue.

In fact the header background works with lightyellow - HEADER_bgcolor="lightyellow"!!!! And this all worked two days ago...

It's just some with the banner collors.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 966 views
  • 0 likes
  • 3 in conversation