Desktop productivity for business analysts and programmers

Image overlapping data table.

Reply
Contributor
Posts: 34

Image overlapping data table.

Hi All,
I am trying to export a logo to the excel sheet using the msoffice2k ODS destination and I use the following code to do the export.The code exports the image but it overlaps with a couple of rows of data.
Are thee any options in the msoffice2k tagset where i can specify it to start the data after the image?
I tried looking into the doc="help" option but couldnt figure out anything straightforward that would fix things.


ods escapechar="~";
ods tagsets.msoffice2k file="G:\worksheet_msoffice2k_1.xls" style=default
options( doc="help"
);

proc print data=sashelp.prdsale(obs=50);
title j=c " ~S={preimage='G:\sample_reports\logo.gif'} ";
run;

ods tagsets.msoffice2k close;

Thanks
Sapper
SAS Super FREQ
Posts: 8,721

Re: Image overlapping data table.

Hi:
When you use TAGSETS.MSOFFICE2K, you are not creating a true, binary Excel (.XLS) file. You are actually creating an HTML file that Excel knows how to open and render. Naming the file with the ".XLS" extension only "fools" the Windows registry into launching Excel when you double click on the file name.

So, for example, if you open G:\worksheet_msoffice2k_1.xls with Notepad, or any Text Editor, you should see HTML tags in the file. And if you search for the name of your image file, you should soon find an <IMG> tag that pointed to the GIF file.

Personally, I am never happy with the look of logos when I have Excel open and render an HTML file. Excel seems to want to "float" the image over the table. This never looks the way I want. The behavior you observe seems to be inherent in how Excel treats HTML <IMG> tags.

You might want to work with Tech Support to see whether they have any workarounds for this behavior.

cynthia
Contributor
Posts: 34

Re: Image overlapping data table.

hi Cynthia,
Thanks for the reply.
Is there any other ODS destinations that would support exporting images perfectly to excel other than msoffice2k?

The problem with using other Html ODS tagset destinations they donot create a true or pesudo ".xls" sheet as an output and we at the site require that the output file be truly .xls and not .html or .xml
So I chose the msoffice2k ODS tagset.

Can anyone let me know if there is any other way of exporting a logo onto excel and creating a true or pseudo excel (.xls) sheet?

Thanks
Sapper
Super Contributor
Posts: 356

Re: Image overlapping data table.

Have a look at

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

it talks about msoffice2k_x might do what you need....
SAS Super FREQ
Posts: 8,721

Re: Image overlapping data table.

As cool as the new TAGSETS.MSOFFICE2K_X is, it is still producing HTML -- so if you follow the instructions and download the template code and run the template code, if you look down inside the file that's created, it contains:
[pre]
<html xmlns:x="urn:schemas-microsoft-comSmiley Surprisedffice:excel">
<head>
<meta name="Generator" content="SAS Software Version 9.2, see www.sas.com">
<meta http-equiv="Content-type" content="text/html; charset=windows-1252">
[/pre]

....HTML tags, even when the file extension is .XLS -- so if the desire is to have a "true, binary" .XLS proprietary file, this tagset still creates HTML.

I have had folks write their output to a directory location and then write a VB script to go out and open the HTML files in that directory with Excel and explicitly resave them as Excel proprietary format files (either XLS or XLSX).

The only mechanisms I know of in SAS to create proprietary Excel files from SAS data are to:
1) use PROC EXPORT (with SAS/ACCESS to PC File Formats)
2) use the SAS Excel LIBNAME Engine (needs SAS/Access to PC File Formats)
3) use the SAS Add-in to Microsoft Office (requires the SAS Platform for Business Analytics)

Otherwise, when you use ODS MSOFFICE2K or ODS TAGSETS.MSOFFICE2K_X -- you are still creating Microsoft HTML compliant files that Excel knows how to open and render. When you use ODS TAGSETS.EXCELXP, you are creating Microsoft XML Spreadsheet 2003 compliant files that Excel knows how to open and render.

cynthia
Super Contributor
Posts: 356

Re: Image overlapping data table.

Fully understand cynthia, but I think the issues is also OP wants an image in the excel file which can't be done by ExcelXP, or export or any other easy way
Contributor
Posts: 34

Re: Image overlapping data table.

Thanks cynthia and twocanbazza for the replies.
I have created xml files with excelXP that were directly opened with excel but then they included the requirement that the excel sheet should contain the logo in it.
So had to switch to msoffice2k from excel XP.

We donot have SAS Access to PC files installed on site and i don't see it to be installed in the near future.

I have found another ODS tagset "The tableeditor tagset ".
It says that I can apply the logos in the documentation

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

If you go down the page and look in the "Exporting to Excel" section there seems to be an option called "excel_save_file" .

Does this mean that it is saving the created html file in a excel(.xls) format just like the custom VB script that cynthia was talking ??

If anyone has used this tagset to create excel sheets please clarify my question on the "Excel_Save_file" option.
I have never used this tagset and will probabaly try tommorow .

Thanks
Sapper
SAS Super FREQ
Posts: 8,721

Re: Image overlapping data table.

From what was said by the OP (underline mine):

"we at the site require that the output file be truly .xls and not .html or .xml
So I chose the msoffice2k ODS tagset."


The OP chose MSOFFICE2K without understanding that it was HTML "under the covers" -- so if HTML turns out to be OK, then, you're right, the new MSOFFICE2K_X tagset template may offer the ability to insert a "non-floating" logo into the HTML file. But it will still be HTML output from either MSOFFICE2K or TAGSETS.MSOFFICE2K_X.

If the client is not willing to have an HTML file instead of proprietary XLS, then other solutions must be found (OLE, ODBC, DDE, populate an XLT with a CSV file, etc, etc.)

cynthia
Contributor
Posts: 34

Re: Image overlapping data table.

HI cynthia,
If you Can point me towards the "other" solutions that you were talking in your reply, it would be really helpful to check the alternatives to MSOffice2k / 2K_X tagsets.

Thanks
Sapper
SAS Super FREQ
Posts: 8,721

Re: Image overlapping data table.

Hi:
TAGSETS.TABLEEDITOR uses HTML with embedded JavaScript. If you can load your browser with HTML files that have embedded JavaScript to do the creation of an HTML file, this solution may work. However, I believe that the HTML file must load into the browser first before the JavaScript "Export/Save" button becomes active.
Information on TAGSETS.TABLEEDITOR is here:
http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/index.html
and
http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/index.html#exporte (see the suboptions EXCEL_SAVE_FILE and UPDATE_TARGET starting at this location in the description of TAGSETS.TABLEEDITOR)

As for the other methods -- OLE-DB, ODBC, DDE, creating Microsoft XLT files -- those are all Microsoft topics. To the extent that you can use OLE-DB, ODBC and/or DDE with SAS -- you are basically sending Microsoft commands to Excel using a "conversation" between two applications. If you are using SAS in a client/server scenario -- such as EG on Windows and SAS on Unix, then you may not be able to use DDE, etc, because I believe both apps have to be talking to each other on the same system for the "conversation" to take place.

There are some user group papers on DDE, ODBC, OLE-DB, etc -- probably more DDE examples than anything else out there.
(If you do a Google search on:
Koen Vyverman SAS DDE
or
Leroy Bessler SAS DDE
you should find some good papers.)

It looks to me like TAGSETS.MSOFFICE2K_X will allow you to load an Excel macro when the file is opened or closed -- this may be another alternative -- if your clients allow the use of Excel macros -- it might be a simple Excel macro as described here:
http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html#Macro (of course that means the Excel macro would also have to be written and resident on the user's system).
Super Contributor
Posts: 356

Re: Image overlapping data table.

SAPPER.

We are having the same issues here, we have been using the ExcelXP tagset and now we want our logo in the supplied output.

Other issues with the 2k and ExcelXP tagset is as Cynthia rightly pointed out is that the output is not true excel (ie Markup, HTML or XML) which produces large files when there is alot of data.

I'll let you know how we get one, or if you beat us to it let me know how you go.

I think think there is going to be a simple answer to this one.

Regards

Barry
SAS Super FREQ
Posts: 8,721

Re: Image overlapping data table.

Hi:
I don't write VB Script code. But I have had students tell me that a quick way to get "true" Excel files is to write a VB Script to go out, open the HTML or XML file and then do a SAVE AS--> XLS (or whatever Excel version you want) -- before the file was made available for general consumption.

cynthia
Super Contributor
Posts: 356

Re: Image overlapping data table.

Thanks Cynthia.

For the VB script to work does excel have to be on the server running the code?

Barry
SAS Super FREQ
Posts: 8,721

Re: Image overlapping data table.

Hi:
I'm not really sure. The folks who have explained it to me were writing their ODS output to a shared Windows directory, like a Q: drive and then running the VB Script on everything in that directory by opening the file with Excel and doing a file Save AS --> XLS -- so I would imagine that the key is that the VB Script and Excel have to be somewhere in the same vicinity or the files to be saved have to be accessible to Excel, whereEVER it is. Nobody said anything about the mechanics fo the VB Script and Excel, server-wise.

cynthia
Super Contributor
Posts: 356

Re: Image overlapping data table.

Thanks
Ask a Question
Discussion stats
  • 15 replies
  • 210 views
  • 0 likes
  • 3 in conversation