How to insert image in ODS EXCELXP

[gyambqt]

Hello Experts,

I want to know if there is way to insert image when you use ODS tagset EXCELXP.

I think tagset excelxp will generate xml file which can not incorporate any graph or images and wondering if there is any alterative way to insert image in excel in addition to the table produced. like ods msoffice2k...but not sure how to achieve the result.

Thanks

[Cynthia_sas · Accepted solution]

Hi:

  By Microsoft design, the type of XML that is produced by TAGSETS.EXCELXP does NOT support the inclusion of images. The XML spec is for Excel 2003 and there is no way to put images into the file unless you create the XML file with SAS, open and save the file as an XLSX file or XLS file and then, after you have saved the file to a proprietary Excel format, you can include your image.
         

  The alternative to this, as you noted is ODS MSOFFICE2K (which makes an HTML file) or ODS MSOFFICE2K_X (which makes an HTML file with some XML embedded). Since HTML supports image insertion, you do have the ability to use images with these HTML files. But you are really "inserting" an image into HTML. An HTML file uses an <IMG> tag to point to the external location of the image. And that doesn't change if you use either of these 2 destinations. If you are familiar with the basic "sandwich" technique of ODS, then you use the same technique with either of these destinations. Instead of:

ods tagsets.excelxp file='c:\temp\myreport1.xml';

... sas code ...

ods tagsets.excelxp close;

You would have something like:

ods msoffice2k file='c:\temp\myreport2.xls';

... sas code ...

ods msoffice2k close;

ods tagsets.msoffice2k_x file='c:\temp\myreport3.xls';

... sas code ...

ods tagsets.msoffice2k_x close;

Do note that both of the above files are really HTML files. Naming them as .XLS just fools the Windows registry into launching Excel when you click on the file instead of launching a browser.

Or if you have SAS 9.4, you can try the new ODS EXCEL destination, as described here: http://blogs.sas.com/content/sasdummy/2014/08/29/experimenting-with-ods-excel-to-create-spreadsheets... in which Chris Hemedinger shows how to insert an histogram into an Excel worksheet created with the new ODS EXCEL destination.
       

cynthia

[gyambqt]

Thanks a lot! very very useful.