Date:         Tue, 7 Aug 2007 10:02:44 -0700
Reply-To:     jasper6294@GMAIL.COM
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         jasper6294@GMAIL.COM
Organization: http://groups.google.com
Subject:      Excel Spreadsheet XML
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset="iso-8859-1"

Given the level of control I prefer to have in my output, I am
creating XML output for Excel directly from the data step without
bothering with ODS. When it comes to the actual formatting in the
output, Excel is displaying the report exactly as I wish. However, I
can't seem to have Excel recognize the options I have set in the
WorksheetOptions element. I am attaching the relevant piece of code
for review.

I am running SAS 9 on Unix SunOS. Is there/are there additional
settings and/or options I need to consider either in the SAS
environment or in the OS? Anything else jump out?

T.I.A.

Perry


%macro XML_worksheet_options(gridlines=N, freeze_panes_rows=,
orientation=Landscape);

    put '    <WorksheetOptions xmlns="urn-schemas-microsoft-
com:office:excel">';
    put '      <PageSetup>';
    put "        <Layout x:Orientation=""&orientation""/>";
    put '        <:Header x:Margin="0"/>';
    put '        <:Footer x:Margin="0"/>';
    put '        <PageMargins x:Bottom="0.25" x:Left="0.25"
x:Right="0.25" x:Top="0.25"/>';
    put '      </PageSetup>';
    put '      <FitToPage/>';
    put '      <Print>';
    put '        <FitHeight>999</FitHeight>';
    put '        <ValidPrinterInfo/>';
    put '      </Print>';
    put '      <Selected/>';

  %if %upcase(&gridlines) eq N %then %do;
    put '      <DoNotDisplayGridlines/>';
  %end;

  %if %upcase(&freeze_panes_rows) gt 0 %then %do;
    put '      <FreezePanes/>';
    put '      <FrozenNoSplit/>';
    put "      <SplitHorizontal>&freeze_panes_rows</SplitHorizontal>";
    put "      <TopRowBottomPane>&freeze_panes_rows</
TopRowBottomPane>";
    put '      <ActivePane>2</ActivePane>';
    put '      <Panes>';
    put '        <Pane>';
    put '          <Number>3</Number>';
    put '        </Pane>';
    put '        <Pane>';
    put '          <Number>2</Number>';
    put '          <ActiveRow>5</ActiveRow>';
    put '        </Pane>';
    put '      </Panes>';
  %end;

    put '      <ProtectObjects>False</ProtectObjects>';
    put '      <ProtectScenarios>False</ProtectScenarios>';
    put '    </WorksheetOptions>';

%mend;