Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SHARING] Embedded Images in Excel
Go
New
Search
Notify
Tools
Reply
  
[SHARING] Embedded Images in Excel
 Login/Join
 
Expert
posted
Here is a utility that takes advantage of the single file web page format to add a static image to an EXL2K format report.

The reason for doing this was due to a very complex spreadsheet, that comes from a compound report that created multiple sheets, that also will be delivered externally. Having the spreadsheet completely self contained seemed a good thing to do.

I guess this could have been done with macros, but that would create another dimension of complexity.

utlxlsimg.fex is the utility that adds the image to the EXL2K report.

utlbase64.fex is the utility that encodes the image into a format that can be added to the EXL2K report.

exl2k_image.fex is a sample program that shows the use of these utilities.

xls_image.xls is the output from the sample program.

exl2k_image.zip is a zip file of the above files.

The programs have been tested on Windows and Unix with WebFOCUS 7.1.6, 7.6.6 and 7.6.9.

The generated output has beed tested against Excel 2003 (Works), Excel 2007 (Image does not show), and Excel 2010 (Works).

This message has been edited. Last edited by: Waz,


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6069 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
If you notice any problems with the code, or excel versions, please let me know, thanks.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6069 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Thanks Waz for sharing the info with all, can I add this to our Tips and Techniques collection?

Cheers,

Kerry


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1950 | Location: New York | Registered: November 16, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
Hi Kerry,

Yes, of course you can.

Thanks for offering.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6069 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hi All,

I am not able see the linked fex files. Please let me know how can I get all these files.

Thanks and Regards,
Neelima


WebFocus 8104,8204
Excel/PDF/HTML/HTMLTABLE/XML/ALPHA/GIF file/GRAPH/Active technologies
 
Posts: 182 | Location: INDIA | Registered: July 11, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
I'll take look and see what going on.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6069 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
salivating....




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
The problams encountered when you swap ISP's to one that does not provide a static IP.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6069 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Links should now work until my modem is rebooted


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6069 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
quote:
utlxlsimg.fex


Thanks Waz!!! its working now... Smiler


WebFocus 8104,8204
Excel/PDF/HTML/HTMLTABLE/XML/ALPHA/GIF file/GRAPH/Active technologies
 
Posts: 182 | Location: INDIA | Registered: July 11, 2013Reply With QuoteReport This Post
Member
posted Hide Post
Waz,

I seem to be running into the same issue that Neelima ran into back in December 2014. Could you check to see if the links to your files are still working. When I tried to download them, it spun for awhile and then errored out.

Thanks,

Keith


WebFOCUS 8.104
Windows, All Outputs
 
Posts: 26 | Location: Holland, MI USA | Registered: September 30, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
Hi Keith,

I'll check it out.

My router was rebooted last night.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6069 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Should be working now


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6069 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
Hi Waz,
I was looking into this a little bit and was wondering if you could tell me where the smplogo1.gif needs to be placed initially in order to be picked up. Also, have you tested this at all with any version of 8? We are currently running version 8008. Thanks.


WebFOCUS 8008
Windows, All Outputs
 
Posts: 26 | Registered: October 11, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
Images should be on the reporting server in an application folder, e.g. somewhere in a sub-folder of Drive:\ibi\apps


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Member
posted Hide Post
Thanks Francis,
I made sure the logo was placed there but for some reason I keep getting "This image cannot currently be displayed." in excel inside of the image box. Other than that it is generating the spreadsheet without a problem. If anyone has any suggestions for troubleshooting please let me know. Smiler Thanks again.


WebFOCUS 8008
Windows, All Outputs
 
Posts: 26 | Registered: October 11, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
This only works with format EXL2K.

If this is the format you are using, then in V8, there may be changes that have caused it to not work.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6069 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
When I get a chance, I'll test it in V8.1.04


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6069 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
Excellent - Thanks Waz - i appreciate it. Smiler


WebFOCUS 8008
Windows, All Outputs
 
Posts: 26 | Registered: October 11, 2010Reply With QuoteReport This Post
Guru
posted Hide Post
Hey Waz, Could you please share these fex again?

I am getting 404 .

Thanks


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Reply With QuoteReport This Post
Master
posted Hide Post
quote:
Originally posted by Ricardo Augusto:
Hey Waz, Could you please share these fex again?

I am getting 404 .

Thanks

Maybe the text of the fex files could be posted in this thread?


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
I'll try to get it fixed tonight, I've changed servers at home from windows ( Frowner ) to Linux ( Cool ).

I'd prefer not to post the code, there are several fexes


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6069 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
OK Waz.

Turn httpd on please!


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
OK, I'll see what can do over the weekend.

Whoo hoo, fun with Apache.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6069 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Fun with apache complete, let me know if you can't access the files


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6069 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
Thanks Waz. But it didn't work here!



--------------------------------------------------------------------------------

Your request did not return any output to display.
Possible causes:
 - No data rows matched the specified selection criteria.
 - Output was directed to a destination such as a file or printer.
 - An error occurred during the parsing or running of the request. 


 (FOC227) THE FOCEXEC PROCEDURE CANNOT BE FOUND: FEX_BOUNDARY
 (FOC227) THE FOCEXEC PROCEDURE CANNOT BE FOUND: FEX_REC_CNT
 (FOC227) THE FOCEXEC PROCEDURE CANNOT BE FOUND: FEX_LOCN
 (FOC227) THE FOCEXEC PROCEDURE CANNOT BE FOUND: FEX_SIZE
 FILEDEF XLS_IMAGE DISK xls_image.xls
 DEFINE  FILE CAR
 -* Remember to set the size of the image.
 IMAGE/A160 = '<v:shape style="position:absolute;margin-left:5px;margin-top:5px;width:189px;height:58px;"><v:imagedata src="ford_logo.jpg" o:title="myimage"/></v:shape>' ;
 END
 TABLE   FILE CAR
 HEADING
 "<IMAGE"
 " "
 " "
 " "
 " "
 "Embedded Image in format EXL2K"
 PRINT
 SEATS
 DEALER_COST
 RETAIL_COST
 SALES
 BY     COUNTRY
 BY     CAR
 BY     MODEL
 BY     BODYTYPE
 ON     TABLE SET HTMLCSS ON
 ON     TABLE SET PAGE NOLEAD
 ON     TABLE HOLD AS XLS_IMAGE FORMAT EXL2K
 ON     TABLE SET COMPOUND 'BYTOC'
 ON     TABLE SET STYLE *
 GRID=OFF, SIZE=9, $
 ENDSTYLE
 END
 -RUN
 0 NUMBER OF RECORDS IN TABLE=       18  LINES=     18
 0 EXL2K FILE SAVED ...
 -SET &LOGO_SRCE = 'smplogo1.gif' ;
 -SET &XLS_FILE  = 'xls_image.xls' ;
 -SET &FILE_XLS  = 'xls_image.xls' ;
 -INCLUDE UTLXLSIMG
 -*******************************************************************************
 -*  Report Title    : Add a B64 encoded image to an excel document
 -*  Procedure Name  : UTLXLSIMG
 -*  Author          : Warren Hinchliffe
 -*  Date            : 25/10/2011
 -*******************************************************************************
 -* Input Parms - See defaults below...
 -*******************************************************************************
 -* Comments
 -*
 -* Add this to the heading to add the image to the spreadsheet. need to set the width and height and extension
 -*  <v:shape style='position:absolute;margin-left:15pt;margin-top:15pt;width:154.5pt;height:91.5pt;'>
 -*   <v:imagedata src="image001.gif" o:title="myimage"/>
 -*  </v:shape>
 -*
 -*******************************************************************************
 -* Change History
 -* DD/MM/YYYY - xx - Description (xx = Initials)
 -*******************************************************************************
 -DEFAULTS &LOGO_SRCE = ' ' ;
 -IF smplogo1.gif EQ ' ' THEN GOTO NO_FILE ;
 -SET &LOGO_NAME = GETTOK(smplogo1.gif,12,1,'.',12,'A12') ;
 -SET &LOGO_EXTN = GETTOK(smplogo1.gif,12,2,'.',12,'A12') ;
 -SET &LOGO_NAME = TRUNCATE(smplogo1    ) ;
 -SET &LOGO_EXTN = TRUNCATE(gif         ) ;
 -*******************************************************************************
 -* Set file Default File Location
 -SET &OSC_T_VAR            = IF 1 EQ 1 THEN '&XLS_FILE'     ELSE ''' ''' ;
 -SET &XLS_FILE             = xls_image.xls ;
 -IF xls_image.xls EQ ' ' THEN GOTO NO_FILE ;
 -SET &XLS_APPND = IF WINNT    EQ 'UNIX' THEN 'xls_unix' ELSE xls_image.xls ;
 -* Write out a master to read the XLS_FILE list
 EX -LINES 4 EDAPUT MASTER,XLS_FILE,CV,FILE
 FILENAME=XLS_FILE, SUFFIX=FIX,$
 SEGNAME=XLS_FILE, $
 FIELD=LINE ,ALIAS=  ,A1000 ,A1000 ,$
 -RUN
 FILEDEF XLS_FILE DISK xls_image.xls (RECFM V LRECL 1000
 -RUN
 -SET &Boundary = ' ' ;
 -* Just in case, read the header of the EXL2K file to get the MIME boundary tag
 TABLEF  FILE XLS_FILE
 PRINT
 COMPUTE Boundary/A75 = '-SET &Boundary = ''' || GETTOK(LINE,1000,-2,'"',50,'A50') || ''';' ;
 WHERE  RECORDLIMIT EQ 5
 WHERE  LINE CONTAINS 'boundary'
 AND    LINE CONTAINS 'Content-Type'
 AND    LINE CONTAINS 'multipart/related'
 ON     TABLE SET HOLDLIST PRINTONLY
 ON     TABLE SAVE AS FEX_BOUNDARY
 END
 -RUN
 ALPHANUMERIC RECORD NAMED  FEX_BOUN
 0 FIELDNAME                         ALIAS         FORMAT        LENGTH
 Boundary                                        A75            75
 TOTAL                                                          75
 1
 0 NUMBER OF RECORDS IN TABLE=        1  LINES=      1
 0
 -IF 0      GT 0 THEN GOTO RPT_FERR  ;
 -IF        1 EQ 0 THEN GOTO NO_FILE ;
 -INCLUDE FEX_BOUNDARY
 -SET &Boundary = '====Boundary====';
 -SET &xlslines = 0 ;
 TABLEF  FILE XLS_FILE
 PRINT
 LINE
 ON     TABLE HOLD AS XLS_COPY
 END
 -RUN
 1
 0 NUMBER OF RECORDS IN TABLE=     1119  LINES=   1119
 0
 TABLEF  FILE XLS_FILE
 PRINT
 COMPUTE CNTR/I9  = LAST CNTR + 1 ; NOPRINT
 COMPUTE CODE/A75 = '-SET &xlslines = ' || FTOA(CNTR,'(F9)','A9') || ';' ;
 WHERE  TOTAL LINE EQ '--====Boundary====--'
 ON     TABLE SET HOLDLIST PRINTONLY
 ON     TABLE HOLD AS FEX_REC_CNT
 END
 -RUN
 1
 0 NUMBER OF RECORDS IN TABLE=     1119  LINES=      1
 0
 -INCLUDE FEX_REC_CNT
 -SET &xlslines =     1119;
 FILEDEF XLS_FILE DISK xls_image.xls
 -RUN
 -IF 1119 EQ 0 THEN GOTO NO_FILE ;
 -SET &splitline = 1119 - 1 ;
 -*******************************************************************************
 -* Write out the main part to the XLS file
 TABLEF  FILE XLS_COPY
 PRINT
 COMPUTE ADD_VML/A1000 = STRREP (1000,LINE,13,'<HTML xmlns:o',53,'<HTML xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o', 1000,ADD_VML) ; NOPRINT
 COMPUTE CNTR/I9 = LAST CNTR + 1 ;
 COMPUTE File1/I9 = IF CNTR LT 1118
 THEN PUTDDREC('XLS_FILE',8,ADD_VML,ARGLEN(1000,ADD_VML,'I9'),File1)
 ELSE 0 ;
 ON     TABLE SET HOLDLIST PRINTONLY
 ON     TABLE HOLD AS _IMG_ADD1
 END
 -RUN
 1
 0 NUMBER OF RECORDS IN TABLE=     1119  LINES=   1119
 0
 FILEDEF XLS_FILE DISK xls_image.xls (APPEND
 -RUN
 -SET &LF = IF WINNT    EQ 'UNIX' THEN HEXBYT(10,'A1') ELSE HEXBYT(13,'A1') | HEXBYT(10,'A1') ;
 -*******************************************************************************
 -* Write out the header for the image to the XLS file
 -WRITE XLS_FILE --====Boundary====
 -WRITE XLS_FILE Content-Location: file:///C:/image001.gif
 -WRITE XLS_FILE Content-Transfer-Encoding: base64
 -WRITE XLS_FILE Content-Type: image/gif
 -RUN
 -*******************************************************************************
 -* Encode the image to Base64 format and addit to the XLS file
 -SET &TMP_SRCE = smplogo1.gif ;
 -SET &B64_DDN  = 'XLS_FILE' ;
 -* Create the base 64 encoded image (Part 3)
 -INCLUDE UTLBASE64
 -*******************************************************************************
 -*  Report Title    : Base 64 Encoder
 -*  Procedure Name  : UTLBASE64
 -*  Author          : Warren Hinchliffe
 -*  Date            : September 2009
 -*******************************************************************************
 -* Input Parms - See defaults below...
 -*******************************************************************************
 -* Comments
 -*
 -*******************************************************************************
 -* Change History
 -* DD/MM/YYYY - xx - Description (xx = Initials)
 -*******************************************************************************
 -DEFAULTS &TMP_SRCE = 'testfile' ;
 -DEFAULTS &B64_OUT  = 'testfile.b64' ;
 -DEFAULTS &B64_DDN  = 'B64_OUT' ;
 -SET &IMG_NAME = GETTOK(smplogo1.gif,12,1,'.',12,'A12') ;
 -SET &IMG_EXTN = GETTOK(smplogo1.gif,12,2,'.',12,'A12') ;
 -SET &IMG_NAME = TRUNCATE(smplogo1    ) ;
 -SET &IMG_EXTN = UPCASE(12,gif         ,'A12') ;
 -SET &IMG_EXTN = TRUNCATE(GIF         ) ;
 -* This process will use a trace file to get the results of the WHENCE command, which will give us the location of a file.
 -*******************************************************************************
 -* Set the trace file
 -SET &TEMPPATH = TEMPPATH(80,'A80');
 -SET &TSCOM    = GETTOK(C:\ibi\APPSTU~1\srv81\wfs\edatemp\ts000015\                                     ,80,-2,'/',8,'A8');
 -SET &TRACEFL  = C:\ibi\APPSTU~1\srv81\wfs\edatemp\ts000015\                                      || ('cmd_op.trc');
 -*******************************************************************************
 -* Set Commands To Set Tracing
 SET TRACEOFF  = ALL
 SET TRACEON=R1H
 SET TRACEUSER = ON
 SET TRACEUSER = C:\ibi\APPSTU~1\srv81\wfs\edatemp\ts000015\cmd_op.trc
 -RUN
 WHENCE smplogo1 GIF
 -RUN
 C:\ibi\apps\baseapp\smplogo1.gif
 -RUN
 -* The previous two -RUN's must be there for this to work.
 SET TRACEOFF  = ALL
 SET TRACEUSER = OFF
 -RUN
 -*******************************************************************************
 -* Allocate The Trace File
 FILEDEF TRACEFIL DISK C:\ibi\APPSTU~1\srv81\wfs\edatemp\ts000015\cmd_op.trc                                      (RECFM V LRECL 500
 -RUN
 -*******************************************************************************
 -* Write out a master to read the TRACEFIL list
 EX -LINES 4 EDAPUT MASTER,TRACEFIL,CV,FILE
 FILENAME=TRACEFIL, SUFFIX=FIX,$
 SEGNAME=TRACEFIL, $
 FIELD=LINE ,ALIAS=  ,A500 ,A500 ,$
 -RUN
 -*******************************************************************************
 -* Process the trace file to get the results of the command.
 -SET &IMG_Location = ' ' ;
 DEFINE  FILE TRACEFIL
 Item3/A20     = GETTOK(LINE,500,3,' ',20,'A20') ;
 MSG_Item/A493 = IF LINE CONTAINS '('
 THEN GETTOK(GETTOK(LINE,500,2,'(',493,'A493'),493,1,',',493,'A493')
 ELSE SUBSTR(500,GETTOK(LINE,500,2,'"',500,'A500'),8,500,493,'A493') ;
 CODE/A525     = '-SET &IMG_Location = ''' || MSG_Item || ''' ;' ;
 CNTR/I9       = LAST CNTR + 1 ;
 END
 TABLE   FILE TRACEFIL
 SUM
 COMPUTE LST_CNTR/I9 = MAX.CNTR ;
 PRINT
 CODE
 WHERE  LINE  CONTAINS 'smplogo1.gif'
 WHERE  TOTAL MSG_Item CONTAINS 'smplogo1.gif'
 ON     TABLE HOLD AS B64_FILES
 END
 -RUN
 1
 0 NUMBER OF RECORDS IN TABLE=       28  LINES=     25
 -IF 0      GT 0 THEN GOTO RPT_FERR ;
 TABLE   FILE B64_FILES
 SUM
 LST.CODE
 ON     TABLE SAVE AS FEX_LOCN
 END
 -RUN
 0 NUMBER OF RECORDS IN TABLE=       25  LINES=      1
 ALPHANUMERIC RECORD NAMED  FEX_LOCN
 0 FIELDNAME                         ALIAS         FORMAT        LENGTH
 CODE                              E03           A525          525
 TOTAL                                                         525
 -IF 0      GT 0 THEN GOTO RPT_FERR ;
 -IF        1     EQ 0 THEN GOTO NO_FILE ;
 -INCLUDE FEX_LOCN
 -SET &IMG_Location = '     C:\ibi\apps\baseapp\smplogo1.gif' ;
 -* Check to see if the file exists.
 STATE      C:\ibi\apps\baseapp\smplogo1.gif
 -RUN
 -IF        0 NE 0 THEN GOTO NO_FILE ;
 FILEDEF TMP_SRCE DISK      C:\ibi\apps\baseapp\smplogo1.gif (RECFM F LRECL 1
 FILEDEF B64_OUT  DISK testfile.b64
 -RUN
 -* Write out a master to read the TMP_SRCE file
 EX -LINES 4 EDAPUT MASTER,TMP_SRCE,CV,FILE
 FILENAME=TMP_SRCE, SUFFIX=FIX,$
 SEGNAME=TMP_SRCE, $
 FIELD=CHAR ,ALIAS=  ,A1 ,A1 ,$
 -RUN
 -SET &FILESIZE = '&FILESIZE' ;
 TABLE   FILE TMP_SRCE
 SUM
 COMPUTE CODE/A105 = '-SET &FILESIZE = ' || FTOA(CNT.CHAR,'(F9)','A9')  || ' ;' ;
 ON     TABLE SET HOLDLIST PRINTONLY
 ON     TABLE HOLD AS FEX_SIZE FORMAT ALPHA
 END
 -RUN
 0 NUMBER OF RECORDS IN TABLE=     2931  LINES=      1
 -IF        1 EQ 0 THEN GOTO NO_FILE ;
 -INCLUDE FEX_SIZE
 -SET &FILESIZE =     2931 ;
 -SET &Rem_Octets = IMOD(2931,3,'I1') ;
 -SET &Padding    = DECODE 0(1 '==' 2 '=' 0 ' ') ;
 DEFINE FUNCTION B64_CHAR/A1 (BIT1/I1,BIT2/I1,BIT3/I1,BIT4/I1,BIT5/I1,BIT6/I1)
 Num/I2     = (BIT6 *  1) + (BIT5 *  2) + (BIT4 *  4) +
 (BIT3 *  8) + (BIT2 * 16) + (BIT1 * 32) + 1 ;
 BASE64/A64 = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/' ;
 B64_CHAR/A1= SUBSTR(64,BASE64,Num,Num,1,'A1') ;
 END
 DEFINE  FILE TMP_SRCE
 Cntr/I9     = LAST Cntr + 1 ;
 BytBit/I9   = IF LAST BytBit EQ 3 THEN 1 ELSE LAST BytBit + 1 ;
 HEXCHAR/A2  = UFMT(CHAR, 1, HEXCHAR) ;
 Bit1/I1     = BITSON (1, CHAR, Bit1) ;
 Bit2/I1     = BITSON (2, CHAR, Bit2) ;
 Bit3/I1     = BITSON (3, CHAR, Bit3) ;
 Bit4/I1     = BITSON (4, CHAR, Bit4) ;
 Bit5/I1     = BITSON (5, CHAR, Bit5) ;
 Bit6/I1     = BITSON (6, CHAR, Bit6) ;
 Bit7/I1     = BITSON (7, CHAR, Bit7) ;
 Bit8/I1     = BITSON (8, CHAR, Bit8) ;
 LstRec/I1   = IF Cntr EQ 2931 THEN 1 ELSE 0 ;
 BIT1/I1     = IF BytBit EQ 1 THEN      Bit1 ELSE
 IF BytBit EQ 2 THEN LAST Bit7 ELSE
 IF BytBit EQ 3 THEN LAST Bit5 ELSE 0 ;
 BIT2/I1     = IF BytBit EQ 1 THEN      Bit2 ELSE
 IF BytBit EQ 2 THEN LAST Bit8 ELSE
 IF BytBit EQ 3 THEN LAST Bit6 ELSE 0 ;
 BIT3/I1     = IF BytBit EQ 1 THEN      Bit3 ELSE
 IF BytBit EQ 2 THEN      Bit1 ELSE
 IF BytBit EQ 3 THEN LAST Bit7 ELSE 0 ;
 BIT4/I1     = IF BytBit EQ 1 THEN      Bit4 ELSE
 IF BytBit EQ 2 THEN      Bit2 ELSE
 IF BytBit EQ 3 THEN LAST Bit8 ELSE 0 ;
 BIT5/I1     = IF BytBit EQ 1 THEN      Bit5 ELSE
 IF BytBit EQ 2 THEN      Bit3 ELSE
 IF BytBit EQ 3 THEN      Bit1 ELSE 0 ;
 BIT6/I1     = IF BytBit EQ 1 THEN      Bit6 ELSE
 IF BytBit EQ 2 THEN      Bit4 ELSE
 IF BytBit EQ 3 THEN      Bit2 ELSE 0 ;
 XBT1/I1     = IF BytBit EQ 1 AND LstRec EQ 1 THEN      Bit7 ELSE
 IF BytBit EQ 2 AND LstRec EQ 1 THEN      Bit5 ELSE
 IF BytBit EQ 3                 THEN      Bit3 ELSE 0 ;
 XBT2/I1     = IF BytBit EQ 1 AND LstRec EQ 1 THEN      Bit8 ELSE
 IF BytBit EQ 2 AND LstRec EQ 1 THEN      Bit6 ELSE
 IF BytBit EQ 3                 THEN      Bit4 ELSE 0 ;
 XBT3/I1     = IF BytBit EQ 2 AND LstRec EQ 1 THEN      Bit7 ELSE
 IF BytBit EQ 3                 THEN      Bit5 ELSE 0 ;
 XBT4/I1     = IF BytBit EQ 2 AND LstRec EQ 1 THEN      Bit8 ELSE
 IF BytBit EQ 3                 THEN      Bit6 ELSE 0 ;
 XBT5/I1     = IF BytBit EQ 3                 THEN      Bit7 ELSE 0 ;
 XBT6/I1     = IF BytBit EQ 3                 THEN      Bit8 ELSE 0 ;
 Char/A1     = B64_CHAR(BIT1,BIT2,BIT3,BIT4,BIT5,BIT6) ;
 XChar/A1    = IF (BytBit EQ 3) OR (Cntr EQ 2931) THEN B64_CHAR(XBT1,XBT2,XBT3,XBT4,XBT5,XBT6) ELSE ' ' ;
 NewChars/A4 = IF Cntr EQ 2931
 THEN Char || XChar || ' '
 ELSE Char || XChar ;
 Line/A76    = IF ARGLEN(76,LAST Line,'I2') EQ 76
 THEN NewChars
 ELSE SUBSTR(80,LAST Line || NewChars,1,76,76,'A76') ;
 Put_Line/A1 = IF (ARGLEN(76,Line,'I2') EQ 76) OR (Cntr EQ 2931) THEN 'Y' ELSE 'N' ;
 END
 TABLE   FILE TMP_SRCE
 PRINT
 COMPUTE
 RET_CODE/I1 = IF Put_Line EQ 'Y'
 THEN PUTDDREC('XLS_FILE',08,Line,ARGLEN(76,Line,'I2'), 'I1')
 ELSE 0 ;
 WHERE  TOTAL Put_Line EQ 'Y'
 ON     TABLE HOLD AS TMP_B64
 END
 -RUN
 1
 0 NUMBER OF RECORDS IN TABLE=     2931  LINES=     52
 0
 -GOTO RPT_OK
 -RPT_OK
 -SET &INCLUDE_RETURN = ' ' ;
 -RPT_END
 -* Pass back any errors found to the calling fex.
 -SET &INCLUDE_RETURNIF   = IF   EQ ' ' THEN 'SET &INCLUDE_DUMMY = IF &INCLUDE_RETURN' ELSE 'IF &INCLUDE_RETURN' ;
 -SET &INCLUDE_RETURNGOTO = IF   EQ ' ' THEN '0 ELSE 0' ELSE 'GOTO ' |   ;
 -* Handle any returned errors from the include.
 -SET &INCLUDE_DUMMY = IF   NE ' ' THEN 0 ELSE 0 ;
 -*******************************************************************************
 -* Add the rest of the XLS file.
 TABLEF  FILE XLS_COPY
 PRINT
 COMPUTE CNTR/I9 = LAST CNTR + 1 ;
 COMPUTE File4/I9 = IF CNTR GE 1118
 THEN PUTDDREC('XLS_FILE',8,LINE,ARGLEN(1000,LINE,'I9'),File4)
 ELSE 0 ;
 ON     TABLE SET HOLDLIST PRINTONLY
 ON     TABLE HOLD AS _IMG_ADD2
 END
 -RUN
 1
 0 NUMBER OF RECORDS IN TABLE=     1119  LINES=   1119
 0
 -* If the OS is Unix, then convert the file to Ascii format file.
 -SET &UNIX_Cmd = IF WINNT    EQ 'UNIX' THEN '' ELSE '-*' ;
 -* ! /usr/bin/unix2dos xls_unix &FILE_XLS
 -RUN
 -NO_FILE
 -* Do something with the XLS file....
 -SET &CMD_COPY = IF WINNT    EQ 'UNIX' THEN 'cp xls_image.xls /tmp' ELSE 'copy xls_image.xls c:\temp' ;
 ! copy xls_image.xls c:\temp
 1 file(s) copied.





  


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Did you check the file copied to c:\temp ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6069 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Just tried this on 8.1.04 and the XLS is "corrupt", looks like somethings changes from 7.6 to 8.1.

I'll have to check it out.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6069 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hey Waz,
Is there a way we can force the position of this image. Apparently it won't respect the POSITION when I had the image in HEADING
 TABLE FILE CAR
SUM SALES DEALER_COST SEATS
BY COUNTRY
BY CAR
BY MODEL
HEADING
""
""
""
""

ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
 INCLUDE = warm,
TYPE=HEADING,
IMAGE = IBFS:/EDA/ibisamp/smplogo1.gif,
POSITION=(4 4),
$
ENDSTYLE
END 

I know this its PITA to get the images to work in Excel but, has anyone nailed a solution to freely place the logo anywhere other than top left?


-********************
Sandbox: 8205
Dev: 8201M
Prod:8009
-********************
 
Posts: 216 | Location: Houston,TX | Registered: June 11, 2004Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Sorry for repost. Just wanted to be sure if this is possible in 8201.

Please let me know
Thanks!


-********************
Sandbox: 8205
Dev: 8201M
Prod:8009
-********************
 
Posts: 216 | Location: Houston,TX | Registered: June 11, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SHARING] Embedded Images in Excel

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.