In an xml spreadsheet, how can I give unused cells a transparent background?

AdrianGriffin — Wed, 17 Oct 2012 23:44:59 GMT

If the background is transparent, the screen gridlines will be visible — I think this makes the spreadsheet easier to navigate and reassures the user that there is nothing missing.

But I can’t find a style element that controls unoccupied cells?

Any ideas?

Here is some code that does most of what I want. But when I open the sheet in Excel 2010, the unused cells have a whit-out background, so screen gridlines are not visible.

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

*Generate some data;

data Sales;

do year=2008 to 2012;

do Salesman='Smith ', 'Jones', 'Valdez', 'Lee';

do County='Alameda', 'Sonoma', 'Yolo', 'Lake', 'Merced';

do Industry='Wineries', 'Packers';

do Product='Pump ', 'Hoist', 'Furnace', 'Fan';

Sales=round(10000+ranuni(88)*10000, 10);

Mileage=round(800+ranuni(88)*80);

if ranuni(3)<0.8 then output;

end;

attrib mileage format=comma11. label='Mileage reimbursed';

attrib sales format=comma11. label='Annual sales';

run;

options pageno=1;

proc sort data=sales; by salesman; run;

*ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ;

*Old-school listing table;

proc tabulate noseps data=sales f=comma8.;

class year salesman county product industry; var sales mileage;

table salesman=' ', year=' '*county=' ', sales*sum=' '*Industry=' '*product=' '

/indent=2 rts=16;

title2'Salesman performance trends';

Footnote 'Not for distribution';

run;

*XML output suitable for Excel using tagsets.excel

proc template;

*Style suitable for the plain look that I want;

define style styles.excel_xml_test;

parent=styles.minimal;

style pages/background=transparent;

style document/background=transparent;

style body/background=transparent topmargin=0.5in bottommargin=0.5in leftmargin=0.5in rightmargin=0.5in;

style systemtitle/font_face='Gill Sans MT' font_size=12pt verticalalign=middle;

style systemfooter/font_face='Gill Sans MT' font_size=11pt just=left verticalalign=middle;

style caption/background=transparent cellheight=24pt;

style pageno/font_face='Gill Sans MT' font_size=11pt;

style byline/font_face='Gill Sans MT' font_size=11pt;

style table/font_face='Gill Sans MT' font_size=11pt ;

style header/verticalalign=middle cellheight=24pt bordertopwidth=1pt borderbottomwidth=1pt;

style rowheader/verticalalign=top borderbottomwidth=0pt bordertopwidth=0pt;

style parskip/cellheight=21pt background=transparent just=left;

end;

run;

ods tagsets.excelxp file="&desktop.xmltest.xml" style=excel_xml_test

options(embedded_titles='yes' embedded_footnotes='yes' suppress_bylines='yes'

skip_space='0, 0, 1, 0, 0' row_heights='21, , , , , , 21'

sheet_label=' ' sheet_interval='bygroup'

width_fudge='0.6');

options pageno=1;

proc report nofs headline data=sales;

column (salesman year county sales, industry, product);

define salesman/ group noprint;

define year/group ' ' left;

define county/group ' ';

define industry/across ' ';

define product/across ' ';

define sales/style=[TAGATTR='format:#,##0'];

run;

proc tabulate noseps data=sales f=comma8. style=[TAGATTR='format:#,##0'];

class year salesman county product industry; var sales mileage;

table salesman=' ', year=' '*county=' ', sales*sum=' '*Industry=' '*product=' '

/indent=2 rts=16;

title2'Salesman performance trends';

Footnote 'Not for distribution';

footnote2 'Second footnote line';

by salesman;

run;

ods tagsets.ExcelXP close;

Re: In an xml spreadsheet, how can I give unused cells a transparent background?

Cynthia_sas — Thu, 18 Oct 2012 06:10:20 GMT

Hi:

Look at this Tech Support note. It says that using background=transparent doesn't work with TAGSETS.EXCELXP and the solution illustrates a different method.

http://support.sas.com/kb/46/949.html

cynthia

Re: In an xml spreadsheet, how can I give unused cells a transparent background?

AdrianGriffin — Tue, 23 Oct 2012 17:56:02 GMT

Thanks, Cynthia. I tried it today and it works like a charm.

topic Re: In an xml spreadsheet, how can I give unused cells a transparent background? in ODS and Base Reporting

In an xml spreadsheet, how can I give unused cells a transparent background?

Re: In an xml spreadsheet, how can I give unused cells a transparent background?

Re: In an xml spreadsheet, how can I give unused cells a transparent background?