The SAS Output Delivery System and reporting techniques

Adding Logos to Excel Report

Reply
Contributor
Posts: 37

Adding Logos to Excel Report

I have used ODS with tagsets to create an excel report.  Now I need to have the title reflect the logo of the company.

Any ideas?

Contributor
Posts: 37

Adding Logos to Excel Report

The logo needs to be at the top of all the excel sheets.  So I would like to put in into the proc template as a header but don't know how.

I started with the following code;

proc template;

define style logostyle;

notes "Putting the logo in the heading of all excel sheets";

class header.footer

style = {preimage = 'companylogo.jpg'};

run;

ods listing close;

ods tagset.excelxp file = filters.xls style = logostyle options (autofilter='all' embedded_titles='yes');

proc print data = test_logo label split = "_" noobs style=logostyle;

run;

ods taset.excelxp close;

However, I don't even think I am close to figuring out how to put the company logo at the top of the excel sheets with titles of the report.

Thanks for any help you can give

Super Contributor
Posts: 394

Adding Logos to Excel Report

Microsoft's Excel XP XML format does not permit the use of images. See this earlier thread: http://communities.sas.com/message/13007

SAS Super FREQ
Posts: 8,743

Adding Logos to Excel Report

Hi:

  ODS TAGSETS.EXCELXP is creating an Office 2003 Spreadsheet Markup Language XML file. So, by Microsoft design, you cannot put images into the XML tags. If Microsoft does not allow images in the 2003 XML, there is nothing that SAS can do to put images into the XML file. In fact, if you tried to use SAS/GRAPH with TAGSETS.EXCELXP, you would see this note in the log:

Excel XML does not support output from Proc:Gchart

Output will not be created.

You can also prove this to yourself by creating a worksheet in Excel (outside of SAS completely) and the put a logo in the Worksheet header. When you save the file do NOT save as XLSX or XLS, instead, save the file as XML Spreadsheet 2003 (.xml) file type. Then you should see a warning from Excel that:

The file XXX.YYY may contain features that are not compatible with XML Spreadsheet 2003. Do you want to keep the workbook in this format?

So, then if you investigate, you will discover that, by Microsoft design, the following types of objects and data are not saved with the file:

--OLE objects (such as embedded documents or objects from another program)

--Pictures

--Drawings and AutoShapes

--Charts

--Microsoft Visual Basic for Applications (VBA) projects

--Group and outline information

--Custom e-mail envelope information

--Custom named styles that are not being used by any cell in the workbook

So, the only way to get a logo into your file would be to create most of the content with TAGSETS.EXCELXP -- then explicitly save the file as an Excel proprietary format, such as .XLS or .XLSX -- and then to insert the logo from inside the Excel end of things. Or, you might have some success creating HTML output (as long as you don't need multi-sheet workbooks) and insert the logo. But, an ODS HTML file will only populate 1 sheet and it would be harder to touch the header area of the HTML file with SAS -- since there's not a lot of choices for HTML as far as controlling the header inside Excel. Excel has been able to open HTML files since Excel 97 -- but most of the HTML content goes straight into 1 worksheet.

If you used the new HTML-based destination, ODS MSOFFICE2K_X, you might be able to have more control over the headers and footers when Excel opened the file. This type of output is Microsoft "flavor" of HTML -- with a bit of Microsoft "XML" thrown into the mix. Documentation on MSOFFICE2K_X destination is here:

http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html

If you have issues with figuring out the suboptions and whether you can insert a logo into the HTML file, you might want to open a track with Tech Support.

cynthia

Contributor
Posts: 37

Adding Logos to Excel Report

Ok,

I have created the logo as an xml file logo.xml 

Can I include this in in my report and then export to excel?

The following code is creating the report without a logo.

ODS listing close;

ods tagset.excelxp file='report.xml' style= statical options (autofilter='all' embedded_titles='Yes');

proc pring data = test_logo label split= '_' noobs;

Title1 'put Company Logo Here';      :smileyinfo: can I change the style of title 1 to mirror the xml code or can I insert the xml code here or something

Title2 'name of report';

Title3 'as of:' today;

run;

ods tagsets.excelxp close;

ods listing;

Ask a Question
Discussion stats
  • 4 replies
  • 1490 views
  • 0 likes
  • 3 in conversation