The SAS Output Delivery System and reporting techniques

Insert Image Using ExcelXP ODS

Reply
Contributor
Posts: 53

Insert Image Using ExcelXP ODS

I have some code that creates an .xml excel file that contains 4 different worksheets. I'm trying to create a "standard" excel template out of SAS that I can use for multiple reports. I'd like the header in excel to have the company logo, and report title on it. Is there any way to have my proc template or proc print insert an image?

Thanks in advance...
SAS Super FREQ
Posts: 8,868

Re: Insert Image Using ExcelXP ODS

Posted in reply to mdavidson
Hi:
By Microsoft design, Spreadsheet Markup Language XML (for Excel 2003) does not support image insertion. You can prove this to yourself, by going to Excel, typing into some cells in the spreadsheet, inserting an image and then try to save the sheet from Excel as an XML file -- you will get an error message -- from Excel.

So, without SAS being involved, you can see that Microsoft does not support images in SpreadsheetML -- there's nothing that SAS can do to change the Microsoft behavior. You can put images into HTML files-- but then you lose some of the capabilities of ExcelXP and Spreadsheet ML XML.

I do not know what the new Office 2007/2008 Excel XML does with images or whether it allows them.

cynthia
Contributor
Posts: 53

Re: Insert Image Using ExcelXP ODS

Posted in reply to Cynthia_sas
Thanks Cynthia. I was not aware of that regarding .xml files.
Contributor
Posts: 53

Re: Insert Image Using ExcelXP ODS

Posted in reply to mdavidson
Cynthia,

Say I were to change my output to a .xls file, is there any way I can insert a logo into the .xls file? I've attempted to use the preimage/postimage functions, but excel is unable to load my .xls file once it is created with a preimage.
SAS Super FREQ
Posts: 8,868

Re: Insert Image Using ExcelXP ODS

Posted in reply to mdavidson
Hi:
Some background....the only way to create a "true, binary" XLS file is with PROC EXPORT or the SAS Libname Engine for Excel. These methods take a SAS dataset and use it to create an Excel workbook in binary .XLS format -- but without any colors or font or styles.

Once you move into the world of SpreadsheetML (TAGSETS.EXCELXP) and/or HTML and/or TAGSETS.TABLEEDITOR (HTML with Javascript), then you may or may not be able to put images into your file. Keep in mind that merely giving your file a file extension of .XLS does NOT make the file an Excel file. This is only a convenience to "fool" the Windows registry into opening Excel -- the fact that Excel can render HTML or XML is what makes it at all possible for you to use ODS to create your file.

For example...consider this program:
[pre]
ods msoffice2k file='useimg.html' style=sasweb;
ods msoffice2k(2) FILE='useimg2.xls' style=sasweb;
ods tagsets.excelxp file='useimg_xp.xls'
style=sasweb options(embedded_titles='yes');
ods tagsets.tableeditor file='useimg_te.html'
style=sasweb
options(saveas="yes" default_file="c:\\temp\\save_img_te.xls" );

proc print data=sashelp.class
style(table)={preimage="c:\temp\kermit.jpg"};
title 'Use an Image';
run;
ods _all_ close;
[/pre]

The fact is that naming the second file with .XLS does NOT make it an Excel file. If you open the file with Notepad, you will see EXACTLY the same type of HTML file as that created with the first ODS MSOFFICE2K statement. The same is true of the ExcelXP file -- naming it .XLS does NOT make it a "binary" Excel file -- if you open that file with Notepad, you will see an XML file that Excel knows how to render.

If you have a file called "kermit.jpg" or replace that image name with your own image name, you will see that the HTML file, when opened in the browser, looks different from the file when opened in Excel. You can get an image or logo IN the HTML file, but you may not like the way it looks when Excel tries to render the file. And, as you can see, the ExcelXP file does not include any image at all.

So, my answer to your question is: It depends.

If you use ODS HTML or ODS MSOFFICE2K or TAGSETS.TABLEEDITOR, then you can create an HTML file that contains an image. You may or may not like the way that image looks when you open the file with Excel.

If you use TAGSETS.EXCELXP to create an XML file then even if you try to put an image into a file, the XML spreadsheet will NOT use/reveal/show you the image.

You may or may not be able to insert an image if you do this:
1) create an HTML or XML file without the image using ODS (let's call this file1.html)
2) Open the .HTML or .XML file with Excel and then use an Excel macro or a VB script to insert the logo into the file using Microsoft technology.
3) Resave the file as an XLS file Excel's file/save as (let's call this file2.xls -- and in the Save AS choices, you select or program to save as .XLS)

How did you envision creating your file??? With ODS HTML, with PROC EXPORT, with ODS TAGSETS.EXCLEXP?????

cynthia
Contributor
Posts: 27

Re: Insert Image Using ExcelXP ODS

Posted in reply to Cynthia_sas

Cynthia,

I was trying to do the same thing using the ods tagsets.excelxp would I be able to try your third suggestion or was that specific to a particular output type.

The idea is to create an automated procedure, but I will check into using a macro in excel also.

Thanks,

Martinez

Super Contributor
Posts: 339

Re: Insert Image Using ExcelXP ODS

Posted in reply to Cynthia_sas

If you use the tagsets. to pass images, they are stored in the xml/html as would regular html websites. They provide the path and attempt to render them. This means that if your excel file is meant to be sent to another computer, it will only render the image if either the path to the image provided is the full path on the server AND the folder in which you've stored the image is shared to public - or that you used relative path and provided a copy of the image within the same zip and thus got extracted at the same relative path. It's somewhat tedious.

The only way around which I know of within SAS is using DDE statement to open the created file in excel, insert the image with DDE and then save the excel file again through DDE in a .xls format. It's quite dense since it requires some knowledge of excel commands and I must aknowledge last I did something like this I did not have to tailor around save as formats, my excel sheet was already an xls file. I can try to provide at least some basics (I don't know much more than basics of DDE myself) if you fall back to this option.

The nice thing about this is that you can purposedly copy an image top left anchor in a selected excel cell so you can chose the positioning.

Vincent

Contributor
Posts: 27

Re: Insert Image Using ExcelXP ODS

Posted in reply to Vince28_Statcan

Vince,

DDE sounds like a great solution. I have an excel macro that adds images to a1 cell for every sheet and then apply to all needed workbooks.

If I have already closed the ods output could I use dde to insert the image after my ods close statement?

Thanks,

Martinez

Super Contributor
Posts: 339

Re: Insert Image Using ExcelXP ODS

Posted in reply to Martinez77


As far as I am aware, yes. If you already have coded an excel macro doing this, you can use DDE to copy the macro in new excel files and run it instead of doing a DDE command specific image insert.

Contributor
Posts: 27

Re: Insert Image Using ExcelXP ODS

Posted in reply to Vince28_Statcan

Vince,

Thank you I will start learning about DDE and give it a shot. Appreciate all the help

Martinez

Ask a Question
Discussion stats
  • 9 replies
  • 2757 views
  • 0 likes
  • 4 in conversation