topic Draw box (outside boundary for overall table) with style(report) with ODS EXCEL,style = minimal in ODS and Base Reporting

Draw box (outside boundary for overall table) with style(report) with ODS EXCEL,style = minimal

t_ar_taat — Thu, 21 Apr 2022 10:31:15 GMT

I try to add box line when ods excel and proc report with style=minimal.
Code is.

ods excel file= "/home/sasondemand/test_ods_excel.xlsx"
style =minimal
 ;

proc report data=sashelp.class(obs=10)   
style( report ) = [ background=blue frame=box ]
;
    column sex name age height weight;
    define   sex / order;
run;
ods excel close;

After submit, output window seems nice.

But output excel is not match.

What I want is like this, say simple and just outside boundary for "style(report) =[ frame=box ]"

When I check this problem, found that "style=minimal" is occasionalley not good.

https://communities.sas.com/t5/ODS-and-Base-Reporting/Help-with-ODS-ExcelXP-errors-using-STYLE-minimal/m-p/141066#M11203

Is there any way for simple looks and "style(report)" is valid.

I can leave "style=minimal" if it have simple looks.

Regards

Re: Draw box (outside boundary for overall table) with style(report) with ODS EXCEL,style = minimal

Cynthia_sas — Thu, 21 Apr 2022 16:00:19 GMT

Hi:
In my experience, border controls that work in ODS HTML will rarely work for ODS EXCEL with simple style overrides. This might work with a style template change, but I'm not sure. Back in 2008, this user group paper talked about doing custom borders in Excel specifically with an older destination (TAGSETS.EXCELXP) and the author said: "Unfortunately borders are difficult to deal with because Excel doesn't have the concept of a table like HTML or RTF or even LaTeX. In Excel the only thing that can have borders is an individual cell. This complicates things quite a bit."
With ODS EXCEL, you are creating a file that is actually package of XML "mini files" which contain the XML instructions that have to conform to the Microsoft XML standard for an XLSX file. I do not know whether a style template can be designed that does the equivalent of putting a box around all the table cells using the Excel controls. This is probably a question for SAS Tech Support. Just because you can do a box around a table in HTML, doesn't mean it will work for ODS EXCEL. I am surprised that using background=blue worked for you in HTML, though, I would have expected you to need bordercolor=blue, not background=blue.
Cynthia

Re: Draw box (outside boundary for overall table) with style(report) with ODS EXCEL,style = minimal

ballardw — Thu, 21 Apr 2022 21:46:56 GMT

Partially, I think the issue is the style chosen, Minimal.

This code, using style=Meadow, will put vertical borders on the table:

ods excel file= "&outpath.\test_ods_excel.xlsx"
 style=meadow
 ;

proc report data=sashelp.class(obs=10)     
;
    column sex name age height weight;
    define   sex / order style=[borderleftcolor=blue borderleftwidth=2mm];
    define weight /  style=[borderrightcolor=blue borderrightwidth=2mm];
run;

ods excel close;

But style Minimal, Journal and Analysis so likely others, don't show the right/left border. So the style has some impact.

Some additional overrides, with the right style, may get the top part of the frame.

ods excel file= "&outpath.\test_ods_excel.xlsx"
 style=meadow
 ;

proc report data=sashelp.class(obs=10)     
;
    column sex name age height weight;
    define   sex / order style=[borderleftcolor=blue borderleftwidth=2mm]
                       style(header)=[bordertopcolor=blue bordertopwidth=2mm
                                      borderleftcolor=blue borderleftwidth=2mm];
    define name /    style(header)=[bordertopcolor=blue bordertopwidth=2mm];
    define age /     style(header)=[bordertopcolor=blue bordertopwidth=2mm];
    define height/   style(header)=[bordertopcolor=blue bordertopwidth=2mm];
    define weight /  style=[borderrightcolor=blue borderrightwidth=2mm]
                      style(header)=[bordertopcolor=blue bordertopwidth=2mm
                                    borderrightcolor=blue borderrightwidth=2mm];
run;

ods excel close;

Re: Draw box (outside boundary for overall table) with style(report) with ODS EXCEL,style = minimal

t_ar_taat — Fri, 22 Apr 2022 10:48:22 GMT

Hi, Cynthia_sas
Thank you so much for your comment as I can believe that I should avoid "style=minimal" for my desire.

Re: Draw box (outside boundary for overall table) with style(report) with ODS EXCEL,style = minimal

t_ar_taat — Fri, 22 Apr 2022 10:49:22 GMT

Thank you for your detailed examples !!