topic Formatting Issue: Cell Heights in ODS and Base Reporting

Formatting Issue: Cell Heights

deleted_user — Mon, 22 Sep 2008 20:58:36 GMT

Hi, before I burn any more brain cells on this one I thought I'd ask. I'm trying to perfect a spreadsheet format but can't seem to affect Row / Cell height at all. I was able to work around by showing Observations and making them a massive sized font, but that's too unprofessional looking.

This is the code I'm using for the moment. There's some junk in there.. remnants of different things I've tried. Autofit, row_heights, nothing seems to work. It seems like I can only properly use the Column settings with HTML too... am I mistaken?

proc import out = testing
datafile="..."
dbms=excel2000 replace;
run;

proc template;
define style XLsansPrinter;
parent = styles.sansPrinter;
style header_id from header /
font_weight = bold
background= light blue
just = c
vjust = top
borderwidth=2
font_face=arial;
style data_currency from data /
font_size = 11pt
just = c
borderwidth=2
cellheight=20;
style data_text from data /
cellwidth=10
font_size = 40pt
cellheight=10;
end;

define column char_var;
generic=on;
style=data_text;
header=colhd;
end;

run;

ods listing close;
ods tagsets.excelXP path='C:\Documents and Settings\leach\Desktop' file="test! %sysfunc(date(),yymmddn8.).xls" style=XLsansPrinter;

ods tagsets.ExcelXP
options(
row_heights='20,20,20,20,20,20'
absolute_column_width='10'
embedded_titles='yes'
suppress_bylines='yes'
sheet_interval='none'
sheet_name='SheetSheet!');
title 'Testing Continues!';

proc print data=testing noobs label style(header)=header_id style(data)=data_currency style(obs)=data_text;
run;

ods tagsets.ExcelXP close;

Thanks so much.

Re: Formatting Issue: Cell Heights

deleted_user — Tue, 23 Sep 2008 13:59:15 GMT

Is this more Microsoft's problem than anything?

(I am a SAS Newbie, if there's some glaring error please tell me!) Message was edited by: Alai

Re: Formatting Issue: Cell Heights

Eric_SAS — Tue, 23 Sep 2008 15:35:54 GMT

In answer to Alai, this is more of a microsoft problem, but we are stuck with making it work.
autofit doesn't work. - that is microsoft's problem. I keep trying anyway...

First I am wondering which version of the tagset you are using. Later versions of the tagset have greatly improved height calculations. I wouldn't be surprised if a newer version made
all of your problems go away.

Version 1.86 is available on the web at http://support.sas.com/rnd/base/ods/odsmarkup/

The next thing is that you could use row_height_fudge, It's in points, and it is added to the height of each row after the calculations are done. You do not want autofit_height on, that will turn off any height specifications. Autofit_height would be great if Excel actually did what it should.

As it is, heights must be calculated and specified. The row_heights option can help also, but since you appear to want them all the same height, row_height_fudge may be what you need, although the heights will vary according to the font size and number of rows the text needs.