Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] getting control of Excel output Page Setup options

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] getting control of Excel output Page Setup options
 Login/Join
 
Member
posted
Hi,
I have gone through a lot of posts about Excel output, macros and templates but have not been able to zoom in on a solution to this particular issue.

Overview
I am running a simple focexec from Report Caster to produce an excel output which is bursted and emailed to recipients outside of my company.
Running Webfocus 7.1.7 on Windows 7 Using Microsoft Office 2010

Problem: Business Customer requires the Excel output to have several Page Setup options overridden, specifically:

Page: Orientation to Landscape
Margins: customized values in margins and Center on Page horizontally
Header/Footer: add a Customized Footer
Sheet:: Print Titles: Rows to repeat at top $4:$4 and Print: Gridlines

Looking for an easy way to get control of the Excel Page Setup options without using macros.

Details:
We have used macros/templates/.MHT files successfully to accomplish this, however, many recipients of the Excel output report problems due to the embedded macros AND with Office 2010 we can no longer save the macros in the .MHT format as well.

I have attempted using templates without the macros with very limited success. On whatever sheet (SHEET1) I tell Webfocus to place the data into, ALL the Page Setup options get overridden, and on the other 2 sheets (SHEET2, SHEET3) only the margin and footer options remain intact, dropping the Landscape , the gridlines, the repeating title rows. I have placed some minimal data into each of the 3 sheets as well as I understand that must be done when using templates.

Some additional notes:
Focexec and data are MVS mainframe accessed through EDASERVE (We are not using Developers Studio to build the focexecs)
report output set to: ON TABLE PCHOLD FORMAT EXL2K
OR when using templates: ON TABLE PCHOLD FORMAT EXL2K FORMULA TEMPLATE 'MYTEMPL1' SHEETNUMBER 1
no problems accessing the templates, just keeping the Page Setup options is the issue

Of course I can get the Page Setup options the way I want them if I manually edit the Report Caster Excel output and in the "@page" section and the xml section, add the following into the output:
mso-page-orientation: landscape
mso-footer-data: "My highly customized footer &R Page &P of &N";
margin: .85in .75in .85in .75in;
mso-header-margin: .5in;
mso-footer-margin: .5in;
add lines to the xml section , , and =Sheet1!$4:$4

Any suggestions short of editing the final output are greatly appreciated.
Thanks,

This message has been edited. Last edited by: Kerry,
 
Posts: 2 | Registered: May 10, 2012Report This Post
Expert
posted Hide Post
FYI

If you add this code to your output, you can turn on the page numbers.

DEFINE FILE...
Page_Number/A70 = '<style><!--@page {mso-footer-data:"&|CPage &|P of &|N";}--></style>' ;
END
TABLE FILE ...
HEADING or ON TABLE SUBHEAD
"<Page_Number.....


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Expert
posted Hide Post
You can also set the print area with this technique.

I guess you could do some of the other things you want with a little investigation on how Excel stores it.

SET COMPOUND = OPEN NOBREAK

DEFINE FILE CAR
COL0/A25= 'Clearance/Type';
COL1/A25= 'Count';
COL2/A25= 'Average Cycle Time (Days)';

Page_Number/A70 = '<style><!--@page {mso-footer-data:"&|CPage &|P of &|N";}--></style>' ;
END
TABLE FILE CAR
HEADING
"<+0 <COL0<+0 <COL1<+0 <COL2"
PRINT SEATS
BY COUNTRY
BY CAR NOPRINT
ON COUNTRY RECAP
FLD0/A250= COUNTRY | '/ ' | CAR;
CLRTYP/A500='<td align=left  x:num x:fmla=''=A1''>0</td>';
ON COUNTRY SUBFOOT
"<FLD0<CLRTYP"
ON TABLE SUBHEAD
"<Page_Number SUMMARY - BY CLEARANCE - TYPE"
ON TABLE SUBFOOT
""
""
ON TABLE SET STYLE *
TYPE=TITLE, COLOR=NAVY, STYLE=BOLD+UNDERLINE, $
TYPE=REPORT, TITLETEXT='Testing', $
TYPE=TABHEADING,STYLE=BOLD,$
TYPE=SUBHEAD,STYLE=BOLD,$
TYPE=HEADING, LINE=1, STYLE=BOLD+UNDERLINE, COLOR=NAVY,$
TYPE=HEADING,HEADALIGN=BODY,$
TYPE=HEADING,OBJECT=TEXT,LINE=1,ITEM=1, STYLE=BOLD+UNDERLINE,
COLOR=NAVY,$
TYPE=HEADING,OBJECT=TEXT,LINE=1,ITEM=2, STYLE=BOLD+UNDERLINE,
COLOR=NAVY,$
TYPE=HEADING,OBJECT=TEXT,LINE=1,ITEM=3, STYLE=BOLD+UNDERLINE,
COLOR=NAVY,$
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K
END

SET COMPOUND=CLOSE

-*******************************************************************************
-* Add an extra line with the Page Breaks

TABLE   FILE SYSTABLE
 PRINT  
        COMPUTE PgrBreaks/A400 = '<head><!--[if gte mso 9]><xml> <x:WorksheetOptions><x:Print><x:ValidPrinterInfo/>'
                               | '<x:Scale>63</x:Scale></x:Print></x:WorksheetOptions>'
                               | '<x:PageBreaks>'
                               | '<x:RowBreaks>'
                               | '<x:RowBreak><x:Row>5</x:Row><x:ColEnd>6</x:ColEnd></x:RowBreak>'
                               | '<x:RowBreak><x:Row>10</x:Row><x:ColEnd>6</x:ColEnd></x:RowBreak>'
                               | '</x:RowBreaks></x:PageBreaks></xml><![endif]--></head>' ; AS ''

 BY     NAME NOPRINT

 WHERE  RECORDLIMIT EQ 1

 ON     TABLE SET LINES 99999
ON TABLE PCHOLD FORMAT EXL2K
 ON     TABLE SET STYLE *
 TYPE=REPORT, FONT=ARIAL, SIZE=12, $
END

-RUN


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
@WAZ ---

Unravelling the guts:
<head>
<!--[if gte mso 9]>
  <xml>
    <x:WorksheetOptions>
      <x:Print>
        <x:ValidPrinterInfo/>
        <x:Scale>63</x:Scale>
      </x:Print>
    </x:WorksheetOptions>
    <x:PageBreaks>
      <x:RowBreaks>
        <x:RowBreak>
          <x:Row>5</x:Row>
          <x:ColEnd>6</x:ColEnd>
        </x:RowBreak>
        <x:RowBreak>
          <x:Row>10</x:Row>
          <x:ColEnd>6</x:ColEnd>
        </x:RowBreak>
      </x:RowBreaks>
    </x:PageBreaks>
  </xml>
<![endif]-->
</head>


CoolCoolCool!

I gather from your remark about "experimentation" that this is thoroughly undocumented.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Expert
posted Hide Post
It takes advantage of the way Excel reads the MHT file.

Big Grin


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Member
posted Hide Post
@Waz and j.gross....Thanks very much for the suggestions, I did try the first method using the Page_Number/A70 but the heading with the mso info just gets placed in the first row of the excel output and is basically treated as heading data and therefore does not treated as Excel Page Setup information, I will give that another try to make sure i am doing it correctly and will try the other technique as well...Best Regards
 
Posts: 2 | Registered: May 10, 2012Report This Post
Expert
posted Hide Post
If you would like me to look at your code, send me a PM.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] getting control of Excel output Page Setup options

Copyright © 1996-2020 Information Builders