Inserting Images Into EXL07 Workbook Headers and Footers

How to:

Reference:

WebFOCUS supports the insertion of images into Excel headers and footers and the definition of key page settings within the Excel Worksheet Page Setup to support the placement of these images in relationship to the overall worksheet and Excel generated page breaks. This new access to the Excel page functionality is designed to enhance overall usability of the worksheets for users who will be printing these reports. Page settings including orientation, page size, and page margins will directly affect the layout of each Excel page based on values defined within the FOCEXEC. Images can be included on headers and footers on every printed page, on the first page of the report only, or only on all subsequent pages. The WebFOCUS headings and footings continue to display within the worksheet. With this new feature, WebFOCUS can insert logos to be printed once at the top of a report and watermark images that need to be displayed on every printed page.
Top of page
x
Syntax: How to Define Excel Page Settings
[TYPE=REPORT,] XLSXPAGESETS={ON|OFF} [,PAGESIZE={pagesize|LETTER}] [,ORIENTATION={PORTRAIT|LANDSCAPE}] [,TOPMARGIN=n]
 [,BOTTOMMARGIN=m],$

where:

XLSPAGESETS={ON|OFF}

ON causes the page settings defined in the WebFOCUS request to be applied to the Excel Worksheet page settings. OFF retains the default page settings defined in the standard Excel workbook. OFF is the default value.

n

Defines the top margin for the Worksheet in the units identified by the UNITS parameter (inches, by default). The default value is .25.

m

Defines the bottom margin for the Worksheet in the units identified by the UNITS parameter (inches, by default). The default value is .25.

pagesize

Is one of the PAGESIZE values supported in a WebFOCUS StyleSheet, as described in Selecting Page Size, Orientation, and Color in Chapter 21, Laying Out the Report Page in the Creating Reports With WebFOCUS Language manual. LETTER is the default page size.

PORTRAIT|LANDSCAPE

PORTRAIT displays the report across the narrower dimension of a vertical page, producing a page that is longer than it is wide. PORTRAIT is the default value.

LANDSCAPE displays the report across the wider dimension of a horizontal page, producing a page that is wider than it is long.
Top of page
x
Syntax: How to Insert Images Into Excel Headers and Footers
TYPE={PAGEHEADER|PAGEFOOTER},OBJECT=IMAGE,
 IMAGE=imagename, JUSTIFY={LEFT|CENTER|RIGHT} 
 [,DISPLAYON={FIRST|NOT-FIRST}] [,SIZE=(w h)],$

where:

PAGEHEADER

Places the image in the Worksheet header.

PAGEFOOTER

Places the image in the Worksheet footer.

imagename

Is the name of a valid image file to be placed in the header or footer. The image must be located on the defined application path. The image types supported are GIF and JPEG.

JUSTIFY={LEFT|CENTER|RIGHT}

Identifies the area in the header or footer to contain the image and the justification or placement within that defined area.

DISPLAYON

Defines whether the image should be placed on the first page only or on all pages except the first. Omit this attribute to place the image on all pages.

Valid values are:

FIRST places the image only on the first page.

NOT-FIRST places the image on every page except the first page.

SIZE=(w h)

Is the size of the image. By default, an image is added at its original size.

w is the width of the image, expressed in the unit of measurement specified by the UNITS parameter.

h is the height of the image, expressed in the unit of measurement specified by the UNITS parameter.
Example: Inserting Images in Excel Headers and Footers and Defining Page Settings

The following request against the GGSALES data source places the image ibi_logo.gif on the left header area of the first page and the right header area of every other page of the resulting worksheet. It places the image webfocus1.gif in the center area of the footer on every page:

TABLE FILE GGSALES
SUM DOLLARS UNITS BUDDOLLARS BUDUNITS 
BY REGION
BY ST
BY CATEGORY
BY PRODUCT
ON TABLE SET BYDISPLAY ON
ON TABLE PCHOLD FORMAT EXL07 
ON TABLE SET STYLE *
FONT=ARIAL,SIZE=12,
XLSXPAGESETS=ON,TOPMARGIN=1,BOTTOMMARGIN=1,ORIENTATION=LANDSCAPE,PAGESIZE=LETTER,$
TYPE=TITLE, COLOR=WHITE, BACKCOLOR=GREY,$
TYPE=PAGEHEADER, OBJECT=IMAGE, JUSTIFY=LEFT, IMAGE=IBI_LOGO.GIF, DISPLAYON=FIRST,$
TYPE=PAGEHEADER, OBJECT=IMAGE, JUSTIFY=RIGHT, IMAGE=IBI_LOGO.GIF,  DISPLAYON=NOT-FIRST,$
TYPE=PAGEFOOTER, OBJECT=IMAGE, JUSTIFY=CENTER, IMAGE=webfocus1.gif, $
END

The first page of output has the image ibilogo.gif in the left area of the header and the image webfocus1.gif in the center area of the footer.

The second page of output has the image ibilogo.gif in the right area of the header and the image webfocus1.gif in the center area of the footer.
Top of page
x
Reference: Usage Notes for Inserting Images Into EXL07 Worksheet Headers and Footers
Top of page
x
Reference: Displaying Watermarks on EXL07 Output

Watermark images can be placed into the Excel headers to display on every printed page of the generated worksheet.

Excel places images on the page starting in the header from left to right and then the footer from left to right. Large images placed in the header may overlap images before them in the presentation order and will overlay the previous images. For page layouts with a logo in the left area and watermark centered on the page, watermark image background should be transparent so it does not overlay the logo image.

In Excel, images are placed first on the page. All other contents of the worksheet are then placed on top of the images. Text in cells and styling such as background color and drawing objects are placed on top of the images. Excel supports transparency in drawing objects and images, but not in cell background color. BACKCOLOR will cover over images placed on the page.
Example: Placing a Watermark in an EXL07 Header

The following request against the GGSALES data source uses the image internaluseonly.gif as a watermark to display in the background of every page of the worksheet. Although the image is placed in the center area of the header, it is large enough to span the entire worksheet page. It has a transparent background, so it does not cover the logo images placed at the left in the header and the center in the footer:

TABLE FILE GGSALES
SUM DOLLARS UNITS BUDDOLLARS BUDUNITS 
BY REGION
BY ST
BY CATEGORY
BY PRODUCT
ON TABLE SET BYDISPLAY ON
ON TABLE PCHOLD FORMAT EXL07 
ON TABLE SET STYLE *
XLSXPAGESETS=ON,
TOPMARGIN=1,BOTTOMMARGIN=1,LEFTMARGIN=1, RIGHTMARGIN=1, ORIENTATION=LANDSCAPE,PAGESIZE=LETTER,$
TYPE=PAGEHEADER, OBJECT=IMAGE, JUSTIFY=LEFT, IMAGE=IBI_LOGO.GIF, DISPLAYON=FIRST, $
TYPE=PAGEHEADER, OBJECT=IMAGE, JUSTIFY=CENTER, IMAGE=INTERNALUSEONLY.GIF, $
TYPE=PAGEFOOTER, OBJECT=IMAGE, JUSTIFY=RIGHT, IMAGE=WEBFOCUS1.GIF, $
END

The first page of the generated worksheet shows the watermark image beneath the data. This image is displayed on every page of the worksheet.

WebFOCUS