How to overide style template background color in ODS Excel?

Multipla99 — Tue, 15 Nov 2022 20:06:23 GMT

When running the code below I get everything I want except the background color of the Excel cells that are not in the report area. I suppose that the style template Excel somewhere tells Excel to use #FAFBFE as the background color for alla cells in the sheet.

However I would like to change the background color of whole the Excel sheet to #FFFFFF (white). Any advise how to do this with a minimum effort would be very much appreciated.

libname RESULTS "C:\Temp";
goptions device=png;
ods excel                                                
	file="%sysfunc(pathname(RESULTS))\Exempel SASdata.xlsx" 
	style= styles.excel 
;
ods excel
	options(sheet_name = "Tabell 1" 										 
	sheet_interval='none'                        
	flow='header,data'													 
	start_at = "B2"                              
	absolute_column_width = "6,15,6,6,10,10")    
;

proc odstext;
	p 'Tabell 1. Class'
		/ style =[fontsize=8pt fontfamily=roboto fontweight=bold];
run;

%let dsid=%sysfunc(open(sashelp.class));
%let nobs=%sysfunc(attrn(&dsid.,nlobs));
%let dsid=%sysfunc(close(&dsid.));

proc report
	data = sashelp.class
	style(report)=[backgroundcolor= #ffffff]
	style(header)=[foreground= #1E00BE								
	bordertopcolor = #1E00BE					
	bordertopstyle = solid
	bordertopwidth = 10
	borderbottomcolor = #1E00BE			  
	borderbottomstyle = solid					
	borderbottomwidth = 10
	backgroundcolor= #ffffff					
	fontsize=8pt fontfamily=roboto]
	style(column)=[fontsize=8pt fontfamily=roboto    
	vjust=c														
	foreground= #1E00BE]							
;
	columns Name Sex Age Height Weight;
	define  Name
		/ 	display
		style(header)={just=l 															
		borderrightcolor = #1E00BE						
		borderrightstyle = solid							
		borderrightwidth = 5} 							
		style(column)={just=l 																
		borderrightcolor = #1E00BE						
		borderrightstyle = solid							
		borderrightwidth = 5};
	define  Sex
		/ 	display 
		style(header)={just=l 
		borderrightcolor = #1E00BE
		borderrightstyle = solid
		borderrightwidth = 5} 
		style(column)={just=l 
		borderrightcolor = #1E00BE
		borderrightstyle = solid
		borderrightwidth = 5};
	define  Age
		/ 	analysis
		style(header)={just=r 
		borderrightcolor = #1E00BE							
		borderrightstyle = solid
		borderrightwidth = 5} 
		style(column)={just=r 
		borderrightcolor = #1E00BE
		borderrightstyle = solid
		borderrightwidth = 5};
	define  Height
		/ 	analysis
		style(header)={just=r 
		borderrightcolor = #1E00BE
		borderrightstyle = solid
		borderrightwidth = 5} 
		style(column)={just=r 
		borderrightcolor = #1E00BE
		borderrightstyle = solid
		borderrightwidth = 5};
	define  Weight
		/ 	
		analysis
		style(header)={just=r} 
		style(column)={just=r};

	compute weight;
		count+ 1;

		if count^= &nobs then
			do;
				if mod(count,2)=0 then
					do;
						call define(_row_, "style", "style={background=#EDEDFF}");
					end;
			end;
		else if count= &nobs then
			do;
				if mod(count,2)=0 then
					do;
						call define(_row_, "style", "style={background=#EDEDFF borderbottomcolor= #1E00BE borderbottomwidth=10}");
					end;
				else if mod(count,2)^=0 then
					do;
						call define(_row_, "style", "style={borderbottomcolor= #1E00BE borderbottomwidth=10}");
					end;
			end;
	endcomp;
run;

ods excel close;

Re: How to overide style template background color in ODS Excel?

Ksharp — Wed, 16 Nov 2022 11:45:23 GMT

Maybe you need change the TEMPLATE by this :

/*
Add this PROC TEMPLATE before your code.
*/
proc template;
 define style styles._excel;
  parent=styles.excel;
     class colors / 'docbg' = cxFFFFFF ;
 end;
run;




libname RESULTS "C:\Temp";
goptions device=png;
ods excel                                                
 file="%sysfunc(pathname(RESULTS))\Exempel SASdata.xlsx" 
 style= styles._excel   /*refer to the new style which is at top of code*/
;
ods excel
 options(sheet_name = "Tabell 1"            
 sheet_interval='none'                        
 flow='header,data'              
 start_at = "B2"                              
 absolute_column_width = "6,15,6,6,10,10")    
;
....................................
.....................................
.....................................

Re: How to overide style template background color in ODS Excel?

Multipla99 — Wed, 16 Nov 2022 15:35:07 GMT

Thank you again, Ksharp, for your proposed solutions!

topic Re: How to overide style template background color in ODS Excel? in ODS and Base Reporting

How to overide style template background color in ODS Excel?

Re: How to overide style template background color in ODS Excel?

Re: How to overide style template background color in ODS Excel?