The SAS Output Delivery System and reporting techniques

How can I control row heights in a spreadsheet using the ExcelXP tagset?

Reply
Contributor
Posts: 27

How can I control row heights in a spreadsheet using the ExcelXP tagset?

Colleagues:

I can't seem to control row heights in the main part of the table when using the ExcelXP tagset.

The systemtitle and parskip rows seem to take the row height from my template, but I can't add extra spacing to a line written with the compute statement.

Here is some code to give you a sense of what I am trying to do. I have trolled the web thoroughly and tried switching options everywhare, with no success.

--Any ideas, Adrian

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

*Compile Tagsets.ExcelXP_mod, which makes changes to the ExcelXP tagset to preserve the screen gridlines;

*I'm starting from ExcelXPv1.127, 09/26/2011;

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

proc template;

   define tagset tagsets.excelxp_mod;

      parent=tagsets.excelxp;

      define event sub_body;

         break /if $sub_body;

            set $sub_body "True";

            set $body_class "_body";

            put '<Style ss:ID="_body">' nl;

            put ' <Font ss:FontName="Gill Sans MT" ssSmiley Frustratedize="11" />' NL;

            put "<Interior />" Nl;

            put '</Style>' nl;

            set $contents_class "_contents";

            put '<Style ss:ID="_contents">' nl;

            put ' <Interior ssSmiley Tongueattern="Solid" />' nl;

            put ' <Protection ssSmiley Tonguerotected="1" />' nl;

            put '</Style>' nl;

            set $pages_class "_pages";

            put '<Style ss:ID="_pages">' nl;

            put ' <Interior ssSmiley Tongueattern="Solid" />' nl;

            put ' <Protection ssSmiley Tonguerotected="1" />' nl;

            put '</Style>' nl;

      end;

   end;

run;

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

*Style for a plain spreadsheet;

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

  define style styles.excel_xml;

    parent=styles.minimal;

      style body/background=transparent topmargin=0.5in bottommargin=0.5in leftmargin=0.5in rightmargin=0.5in;

      style systemtitle/font_face='Gill Sans MT' font_size=12pt verticalalign=middle cellheight=27pt;

      style systemfooter/font_face='Gill Sans MT' font_size=11pt just=left verticalalign=middle cellheight=27pt;

      style caption/background=transparent cellheight=24pt;

      style pageno/font_face='Gill Sans MT' font_size=11pt;

      style byline/font_face='Gill Sans MT' font_size=11pt;

      style notecontent/font_face='Gill Sans MT' font_size=11pt verticalalign=middle;

      style table/font_face='Gill Sans MT' font_size=11pt;

      style header/verticalalign=middle cellheight=24pt bordertopwidth=1pt borderbottomwidth=1pt;

      style rowheader/verticalalign=top borderbottomwidth=0pt bordertopwidth=0pt;

      style parskip/cellheight=21pt background=transparent just=left;

  end;

run;

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

*Pull data and do report;

*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;

data cars(keep=combo make model horsepower weight wheelbase);

  set sashelp.cars;

  train=trim(lowcase(drivetrain)) !! '-wheel drive'; cyl=put(cylinders, 1.) !! '-cylinder';

  length combo $48;

  Combo=catx(', ', origin, type, train, cyl);

run;

ods tagsets.ExcelXP_mod style=excel_xml file="cars.xml"

     options (sheet_interval='bygroup' embedded_titles='yes' embedded_footnotes='yes'  suppress_bylines='yes' skip_space='1, 0, 1, 0, 0'

     embed_titles_once='yes' sheet_label=' ' autofit_height='Yes'  width_fudge='0.46');

options pageno=1;

proc report nofs split='|' data=cars;

  columns combo filler make model horsepower weight wheelbase;

  define combo/noprint order;

  define filler/computed style=[width=24pt] ' ';

  define make/order;

  define weight/format=comma7. 'Weight' style=[tagattr='format:##,##0'];

  define horsepower/style=[width=72pt];

  define wheelbase/'Wheelbase|inches' style=[width=72pt];

  title2 'Summary of car data';

 

  compute before combo/style=[just=left height=33pt];

    line combo $48.;

  endcomp;

  compute after combo/style=[just=left height=8pt];

   line ' ';

  endcomp;

  compute filler/character length=2;

    filler=' ';

  endcomp;

run;

title2;

ods tagsets.ExcelXP_mod close;

Post a Question
Discussion Stats
  • 0 replies
  • 472 views
  • 0 likes
  • 1 in conversation