How to Modify Excel Row Heights with Tagsets.Excelxp

Paige — Tue, 18 May 2010 20:02:11 GMT

I am creating an Excel file with Tagsets.Excelxp and PROC REPORT. I want to modify the row heights in the Excel file, make them smaller or larger.

I have played around with a number of option in the ods tagsets.excelxp statement and also with STYLE options (the height= option) in PROC REPORT, and haven't found the right method. Could someone give me an example using either ods tagsets.excelxp or STYLE=?

Thanks

Re: How to Modify Excel Row Heights with Tagsets.Excelxp

Cynthia_sas — Tue, 18 May 2010 21:45:43 GMT

Hi:
I find that row heights are troublesome, no matter what method I use when I open the output file in Excel. Techniques that work perfectly well for HTML fail miserably in EXCEL when I use them with TAGSETS.EXCELXP. In addition the ROW_HEIGHT sub-option that is available with versions of TAGSETS.EXCELXP has NEVER worked for me.

Here's some code to test out -- compare the HTML output created by MSOFFICE2K for the browser with the XML/ExcelXP output created for Excel. As you see, none of the methods work. Excel is very "picky" about setting row heights and I just never bother with row heights. This may be a question for Tech Support -- if there is any kind of workaround, they would be able to find it out.

cynthia
[pre]
ods tagsets.excelxp file='c:\temp\rowheight1a.xls'
style=sasweb
options(doc="help");
ods msoffice2k file='c:\temp\rowheight1a_mso.html' style=sasweb;

proc report data=sashelp.class nowd;
title '1a) Use HEIGHT in CALL DEFINE';
title2 'HTML respected; EXCELXP ignored';
column name age height weight;
compute weight;
if name = 'John' then
call define(_row_,'style','style={height=100px}');
endcomp;
run;

ods _all_ close;

ods tagsets.excelxp file='c:\temp\rowheight1b.xls'
style=sasweb;
ods msoffice2k file='c:\temp\rowheight1b_mso.html' style=sasweb;

proc report data=sashelp.class nowd;
title '1b) Use HTMLSTYLE attribute';
title2 'HTML respected; EXCELXP ignored';
column name age height weight;
compute weight;
if name = 'John' then
call define(_row_,'style','style={htmlstyle="height:100px"}');
endcomp;
run;

ods _all_ close;

ods tagsets.excelxp file='c:\temp\rowheight2.xls'
style=sasweb;
ods msoffice2k file='c:\temp\rowheight2_mso.html' style=sasweb;

proc report data=sashelp.class nowd
style(column)={height=2in};
title '2) Use HEIGHT in PROC REPORT statement';
title2 'HTML respected; EXCELXP ignored';
column name age height weight;
run;

ods _all_ close;

ods tagsets.excelxp file='c:\temp\rowheight3.xls'
options(row_height='0,50,0,0,0,0,0')
style=sasweb;

proc report data=sashelp.class nowd;
title '3) Use ROW_HEIGHT sub-option for TAGSETS.EXCELXP (no HTML equiv)';
title2 'EXCELXP ignored';
column name age height weight;
run;

ods _all_ close;

ods tagsets.excelxp file='c:\temp\rowheight4.xls'
options(row_height='0,50,0,0,0,0,0')
style=sasweb;
ods msoffice2k file='c:\temp\rowheight4_mso.html' style=sasweb;
ods escapechar='~';

proc report data=sashelp.class nowd;
title '4) Now add some some newlines (line feeds) to the data';
title2 'HTML respected; EXCELXP ignored (have to widen column to see values)';
column name prtname age height weight;
define name / noprint;
define prtname / computed;
compute prtname / character length=20;
prtname = catx(' ',name,'~1n','~1n','~1n');
endcomp;
run;

ods _all_ close;

data class;
set sashelp.class(obs=3);
length longvar $150;
longvar='Twas brillig and the slithy toves did gyre and gimble in the wabe.'||
' All mimsy were the borogroves and the mome raths outgrabe.';
run;

ods tagsets.excelxp file='c:\temp\rowheight5.xls'
style=sasweb;
ods msoffice2k file='c:\temp\rowheight5_mso.html' style=sasweb;
ods escapechar='~';

proc report data=class nowd;
title '5) Use cellwidth with a LONG text variable';
title2 'HTML respected; EXCELXP ignored (have to widen column to long string)';
column longvar name age height weight;
define longvar / display style(column)={cellwidth=1.25in};
run;

ods _all_ close;
[/pre]

Re: How to Modify Excel Row Heights with Tagsets.Excelxp

boschy — Mon, 24 May 2010 07:49:35 GMT

Row heights are a nightmare. We're using a modified version of the tagset that fixes various problems like this. The end result is print-ready Excel output (using XML in the background) that has frozen headers, page numbers, run dates, footers, etc.

I set the AUTOFIT_HEIGHT option to 'yes' to exand rows and force text to wrap. However, I believe that this does not work in the standard tagset. It has been a few years.

-Bosch-

Re: How to Modify Excel Row Heights with Tagsets.Excelxp

boschy — Mon, 24 May 2010 07:56:29 GMT

I should say we're using and modified version of:

Excel XP tagset (SAS 9.1.3, v1.70, 06/05/07).

Re: How to Modify Excel Row Heights with Tagsets.Excelxp

Peter_C — Mon, 31 May 2010 16:06:30 GMT

Cynthia
instead of [pre] options(row_height='0,50,0,0,0,0,0')[/pre]try:[pre] options(row_heightS='0,50,0,0,0,0,0')[/pre]
just a thought this suggestion relates to excelXP Message was edited by: Peter.C

Re: How to Modify Excel Row Heights with Tagsets.Excelxp

Cynthia_sas — Mon, 31 May 2010 20:42:55 GMT

My bad. It did work for that simple example!
Thanks for the catch.
cynthia

topic Re: How to Modify Excel Row Heights with Tagsets.Excelxp in ODS and Base Reporting

How to Modify Excel Row Heights with Tagsets.Excelxp

Re: How to Modify Excel Row Heights with Tagsets.Excelxp

Re: How to Modify Excel Row Heights with Tagsets.Excelxp

Re: How to Modify Excel Row Heights with Tagsets.Excelxp

Re: How to Modify Excel Row Heights with Tagsets.Excelxp

Re: How to Modify Excel Row Heights with Tagsets.Excelxp