BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bala_pa
Calcite | Level 5

Hello SAS Form,

 

I am currently using a template style to create same report in xlsx file using ods excel and xml using tagsets.  The template works on xml and not on xlsx. For example: The font, borders are as per style in xml format whereas xlsx uses some default of its own. Can someone help me resolve the issue? Below is the sample code that I am working on. I am using SAS 9.4 M1

 

proc template;
define style customnew;
parent=Styles.Normal;

style header /
font_face='Microsoft Sans Serif'
fontsize=11pt
color=black
fontweight=bold
textalign=center
verticalalign=middle
backgroundcolor=lightblue
;
style data /
font_face='Microsoft Sans Serif'
fontsize=10pt
borderbottomcolor=black

textalign=center
verticalalign=middle
;
style Table /
borderwidth = 3px
bordercolor=black
frame = box
rules = all
borderwidth = 1pt
bordertopcolor = black
borderbottomcolor = black
borderleftcolor = black
borderrightcolor = black

;
style Body /
topmargin = _undef_
bottommargin = _undef_
leftmargin = _undef_
rightmargin = _undef_
;
end;
run;

%let outputpath=C:\Users;

ods excel file="&outputpath.\class.xlsx" style=customnew;
proc print data=sashelp.class;
run;
ods excel close;

ods tagsets.excelxp file="&outputpath.\class.xml" style=customnew;
proc print data=sashelp.class;
run;
ods tagsets.excelxp close;

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi:

  I used a slightly modified version of your template and added a very obvious change to the style header (pink and purple) so I could be sure that the style template was being used. I can verify that the style template is being used as you can see in the screen shots below. My template was almost the same as yours except I added an ODS PATH statement so I could control the location of the template and I added the style header section and I cleaned up the indention.

 

  If your issue is with the border lines, that is something of a sticking point with templates and Excel. Microsoft has one way to do borders and border lines and won't really respect what you specify here, easily.

 

  One way to verify that Excel does not respect border lines -- either presence or absence, take a look at the differing outputs created by this code:

ods html(id=1) file='c:\temp\output\class_jour.html' style=journal;
ods tagsets.excelxp(id=2) file='c:\temp\output\class_jour.xml' style=journal;
ods excel(id=3) file="&outputpath.\class_jour.xlsx" style=journal;
proc print data=sashelp.class;
run;
ods excel(id=3) close;
ods tagsets.excelxp(id=2) close;
ods html(id=1) close;

as you can see in the screen shot, the HTML file is lovely, with no interior table lines, as defined by the journal style. Compare that with the ODS EXCEL and ODS TAGSETS.EXCELXP outputs:

 

try_journal.png

 

When I run my version of your code, I do see the style is used for those elements and attributes that Excel respects. For example, it did use the MIcrosoft Sans Serif font and the sizes specified.

 

cynthia

template_used.png

View solution in original post

4 REPLIES 4
Cynthia_sas
SAS Super FREQ

Hi:

  I used a slightly modified version of your template and added a very obvious change to the style header (pink and purple) so I could be sure that the style template was being used. I can verify that the style template is being used as you can see in the screen shots below. My template was almost the same as yours except I added an ODS PATH statement so I could control the location of the template and I added the style header section and I cleaned up the indention.

 

  If your issue is with the border lines, that is something of a sticking point with templates and Excel. Microsoft has one way to do borders and border lines and won't really respect what you specify here, easily.

 

  One way to verify that Excel does not respect border lines -- either presence or absence, take a look at the differing outputs created by this code:

ods html(id=1) file='c:\temp\output\class_jour.html' style=journal;
ods tagsets.excelxp(id=2) file='c:\temp\output\class_jour.xml' style=journal;
ods excel(id=3) file="&outputpath.\class_jour.xlsx" style=journal;
proc print data=sashelp.class;
run;
ods excel(id=3) close;
ods tagsets.excelxp(id=2) close;
ods html(id=1) close;

as you can see in the screen shot, the HTML file is lovely, with no interior table lines, as defined by the journal style. Compare that with the ODS EXCEL and ODS TAGSETS.EXCELXP outputs:

 

try_journal.png

 

When I run my version of your code, I do see the style is used for those elements and attributes that Excel respects. For example, it did use the MIcrosoft Sans Serif font and the sizes specified.

 

cynthia

template_used.png

bala_pa
Calcite | Level 5

Hello Cynthia,

 

I included the ODS path statement and also modified the Proc Template (FONT, its SIZE and HEADER Colors)to check if it worked. Yes all the changed worked for .xml file whereas only the color and size options we applied to the xlsx format. You can see that the xlsx file does not use the MocroSoft Sans Serif font and borders. If this is an issue like you told earlier with MS Excel and templates, can you suggest me a suitable template style which I can make modifications and will still work with MS Excel.

 

Thank you,

Bala 

2017-03-27_14-10-02.png

Cynthia_sas
SAS Super FREQ

Hi:
As I indicated in my posting, "If your issue is with the border lines, that is something of a sticking point with templates and Excel. Microsoft has one way to do borders and border lines and won't really respect what you specify here, easily."

The code I and examples I posted illustrated that point .. the border lines work for RTF, PDF and HTML (or absence of border lines if you use the JOURNAL style) -- but they do NOT work with ODS EXCEL or ODS TAGSETS.EXCELXP. And, I am not sure that you can specify to turn on the border lines with a style template. This would be a question for Tech Support.

I did see the Microsoft Sans Serif font used in my Excel output. If you replace all the occurences of Courier New with Microsoft Sans Serif in your template, you should see that font being used, assuming it is on your system. If your template font is not showing in Excel, then that, along with border lines is a question for Tech Support.

cynthia

bala_pa
Calcite | Level 5

Thank you 

Bala

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 3106 views
  • 1 like
  • 2 in conversation