Hi,
I have a report that currently uses msoffice2k_x to output a multisheet workbook. There are a few issues I have.
1) If I try to use excelxp output instead or msoffice2k_x the XML contains no data when using the ODSOUT object. The structure is there, but the data is missing.
2) The file when opened in excel (using msoffice2k_x) has a few issues.
a. The spacing for the image isn't perfect, what can be done to improve this?
b. The second tab for the workbook reports and error, it gets recovered and the data is present, however the sheet name is missing ('Male')
3) If the below is ran through EG the Enterprise Guide with crash upon completion. The job runs fine and outputs are created, no crash occurs when using SAS interactivly through Linux.
4) The color styling applied through the BMI format in the GKPI Proc is not applied to the Label as would be expected.
I have created the following code as a example:
ods listing close;
filename tmp '/temp';
ods tagsets.msoffice2k_x path=tmp file='temp.html' newfile=output style=listing options(graph_height='74' graph_width='180');
ods escapechar='~';
goptions reset=all device=javaimg xpixels=180 ypixels=74;
proc format;
value $gndr
'F'='F - Female'
'M'='M - Male';
picture bmi (round fuzz=.09)
low - 18.4 = '00.0)' (prefix='~S={color=black}Underweight (')
18.5 - 24.9 = '00.0)' (prefix='~S={color=green}Normal (')
25 - 29.9 = '00.0)' (prefix='~S={color=orange}Overweight (')
30 - high = '00.0)' (prefix='~S={color=red}Obese (');
run;
proc sql;
create view class as
select * from sashelp.class order by sex;
quit;
data _null_;
if _n_=1 then do;
declare odsout o();
call execute('ods tagsets.msoffice2k_x close;');
call execute('ods html file="/temp/gkpi.html" gpath=tmp style=listing;');
call execute('proc gkpi mode=raised;');
end;
set class end=eof;
by sex;
if first.sex then do;
tot_ht=0;
tot_wt=0;
cnt=0;
o.table_start();
o.head_start();
o.row_start();
o.format_cell(data:'This is a spanned header',column_span:5,overrides:'font_weight=bold font_size=16pt just=c');
o.row_end();
o.row_start();
o.format_cell(data:'Name',overrides:'just=l fontweight=bold');
o.format_cell(data:'Age',overrides:'just=r fontweight=bold');
o.format_cell(data:'Height',overrides:'just=r fontweight=bold');
o.format_cell(data:'Weight',overrides:'just=r fontweight=bold');
o.format_cell(data:'BMI',overrides:'just=c fontweight=bold cellwidth=1.9in');
o.head_end();
end;
o.row_start();
tot_ht+height;
tot_wt+weight;
cnt+1;
if sex='M' then do;
o.format_cell(data:name,overrides:'just=l color=blue');
end;
else do;
o.format_cell(data:name,overrides:'just=l color=purple');
end;
o.format_cell(data:age,overrides:'just=r');
o.format_cell(data:height,overrides:'just=r');
o.format_cell(data:weight,overrides:'just=r');
bmi=(weight*703)/(height**2);
call execute('slider actual=' || put(bmi,5.2) || ' bounds=(0 18.5 25 30 40) / noavalue target=21.7 colors=(white yellow green red) name="' || strip(name) || '" label="' || put(bmi,bmi.) || '"; run;');
o.cell_start(overrides:'vjust=c just=c cellwidth=1.9in cellheight=.8in');
o.image(text:cats(name,'.png'));
o.cell_end();
o.row_end();
if last.sex then do;
o.row_start();
o.format_cell(data:'Average',column_span:2,overrides:'just=r fontweight=bold');
o.format_cell(data:floor(tot_ht/cnt),overrides:'just=r');
o.format_cell(data:floor(tot_wt/cnt),overrides:'just=r');
call execute('slider actual=' || put(bmi,5.2) || ' bounds=(0 18.5 25 30 40) / noavalue target=21.7 colors=(white yellow green red) name="average" label="' || put(bmi,bmi.) || '"; run;');
o.cell_start(overrides:'vjust=c just=c cellwidth=1.9in cellheight=.8in');
o.image(text:'average.png');
o.cell_end();
o.row_end();
o.body_end();
o.foot_start();
o.row_start();
o.format_cell(data:'This is a spanned footer',column_span:5,overrides:'just=c');
o.row_end();
o.row_start();
o.format_cell(data:'Here is some other information:',column_span:5,overrides:'just=c color=white background=black font_size=16pt');
o.row_end();
o.row_start();
o.format_cell(data:"~S={URL='http:\\www.sas.com'}I Made This With SAS",column_span:5);
o.row_end();
o.foot_end();
o.table_end();
end;
if eof then do;
call execute('quit;');
call execute('ods html close;');
stop;
end;
run;
ods tagsets.msoffice2k_x file='/temp/temp.xls'
options( worksheet_source="Female#temp.html,Male#temp1.html" );
ods tagsets.msoffice2k_x close;
I have attached a copy of the final output, as I see it, converted to XLS in Excel after opening the temp.xls file produced in the final step above (to avoid uploading the 19 image files, 2 html files and msoffice2k_x generated xls file).
Click image to register for webinar
