ODS EXCEL lacks autofit_height

RichardDeVen — Thu, 07 Oct 2021 10:05:18 GMT

Dissatisfied with how ODS EXCEL will wrap text by adding a newline to the text in a cell

Suppose you have some data and a custom style to render values in data cells in 10pt Arial

ods _all_ close;

data a;
s1 = 'aaaaa per AAAAAAA/BBBB ccccccccccc ii ttt SSSSSSSS ii cvvvvvvvvv within the ttttt lllllll to rrrrrrr wwwww [SSSSSSSS && NULL].';
s2 = 'The aaaaaaaa bbbb is not consistent per dddd eeeeeeee combination';
g_1 = 'xyzzy abba';
g_2 = g_1;
g_3 = g_2;
g_4 = g_3;
output;
output;
s1='aaa'; s2='bbb'; output;
output;

proc template;
  define style special;
  parent = styles.statistical;
  style fonts from fonts /
    'HeadingFont'= ('Arial', 10pt, bold)
    'DocFont'    = ('Arial', 10pt)
  ;
  end;
run;

The simplest ODS EXCEL will wrap text in narrow cells by inserting newline characters into the data value and will set the row height to show the 'wrapped' text. The number of rows is too many and the wrapping is not 'clean' later when a person viewing the Excel widens a column because of those added newlines

ods excel file="!temp\a%sysfunc(monotonic()).xlsx"   style=special 
  options( zoom='85' autofilter = 'ALL' sheet_name='one') ;

proc report data=a;
  columns s1 s2 g_: z;
  define s1  / style(column)=[cellwidth = 2in];
  define s2  / style(column)=[cellwidth = 1in];
  define g_: / style=[cellwidth = 0.5in];
run;
ods excel close;

The ODS EXCEL options flow='TABLES' will tell Excel to wrap text (by turning on the cells format Alignment/Wrap Text) -- no embedded newlines are added to the value by ODS EXCEL. This means when a viewer widens or narrows a column, the text will properly wrap.

ods excel file="!temp\a%sysfunc(monotonic()).xlsx" style=special 
  options( zoom='85' autofilter = 'ALL' flow = 'TABLES' sheet_name='one');

proc report data=a;
  columns s1 s2 g_: ;
  define s1  / style(column)=[cellwidth = 2in];
  define s2  / style(column)=[cellwidth = 1in];
  define g_: / style=[cellwidth = 0.5in];
run;
ods excel close;

How ever, the initial blank space in a cell is now worse. The EXCEL destination is calculating a row height that is too much, or maybe Excel is not autofitting well (hard to tell where the culprit is).

Any ideas how to fix that excess empty space induced by excessive row height ?

In windows I might use CBTBL and CALL MODULE to use OS routines for computing needed space, but using ODS EXCEL on a Linux host.

If a nominal render length of a value rendered in Arial 10pt could be computed in Proc REPORT, a CALL DEFINE (_row_, 'STYLE', 'STYLE=[height=xx.ddin]') could be issued to set the row height. nominal height xx.dd would be (Arial 10pt inches/line) * (1 + int(nominal length (inches) / column width (inches)))

Any ideas on computing a nominal render length of a text string without using a graphics drawing context ?

Re: ODS EXCEL lacks autofit_height

Cynthia_sas — Thu, 07 Oct 2021 14:12:49 GMT

Hi:
I think this is a question best addressed by Tech Support because they could involve the PROC REPORT and the ODS EXCEL teams to address your question.
Cynthia

topic Re: ODS EXCEL lacks autofit_height in ODS and Base Reporting

ODS EXCEL lacks autofit_height

Re: ODS EXCEL lacks autofit_height