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     [WORKAROUND] XLSX Sheet titles?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[WORKAROUND] XLSX Sheet titles?
 Login/Join
 
Virtuoso
posted
We have a generic xltx template where the sheets are just titled 'Sheet1', 'Sheet2', etc.

I use this template in several reports, but usually we would prefer to base the sheet titles on the reports we print in each sheet. Basically:
TABLE FILE CAR
SUM CAR
BY COUNTRY
ON TABLE HOLD AS SHEET1 FORMAT XLSX TEMPLATE 'baseapp/generic.xltx' SHEETNUMBER 1
END

TABLE FILE CAR
SUM MODEL
BY COUNTRY
ON TABLE PCHOLD FORMAT XLSX TEMPLATE 'SHEET1.xlsx' SHEETNUMBER 2
END


I thought using styling would do the trick, but alas, the resulting compound XLSX sheet still comes up with the default 'Sheet1', etc. This doesn't work:
TYPE=REPORT, TITLETEXT='My pretty sheet title',$


I suspect I forgot about a simple setting to set the sheet title for each sheet from within the report?


A workaround seems possible, namely by initially creating an XLSX worksheet using BYTOC with a list of sheet names and using the result as the template for the remainder of the report, but that's just dirty...

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


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Expert
posted Hide Post
Well done, you have found the stylesheet option for Excel output that doesn't work with templates Frowner

I usually create a macro enabled workbook to use as a template and push data into a worksheet that is read by the macros on opening, changes the worksheet names before deleting the data worksheet.

I'd give you some sample code but I daresay you are skilled in VBA and have a plethora of code already Smiler


T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Virtuoso
posted Hide Post
Thanks for confirming that the TITLETEXT option should have worked.

The BYTOC solution worked pretty well.

One drawback is that BYTOC sorts them sheet titles alphabetically, which is only the correct order if you prefix them with a sort number, but it works:
EX MAKESEQ FILE=SHEETTITLES, LAST=3

TABLE FILE SHEETTITLES
PRINT
	COMPUTE SHEETNR/A3 = FPRINT(MIN.COUNTER, 'I1', 'A1') || '. ';	NOPRINT
	COMPUTE SHEETNAME/A12 =  DECODE COUNTER(
		1 'aaaa',
		2 'cccc',
		3 'bbbb'
	); NOPRINT
	COMPUTE SHEETTITLE/A15 =  SHEETNR | SHEETNAME;

BY TOTAL SHEETTITLE NOPRINT
BY COUNTER	NOPRINT
ON TABLE HOLD AS XLSXTEMPLATE FORMAT XLSX
ON TABLE SET COMPOUND BYTOC
END

TABLE FILE CAR
SUM CAR
BY COUNTRY
ON TABLE HOLD AS SHEET1 FORMAT XLSX TEMPLATE 'XLSXTEMPLATE.xlsx' SHEETNUMBER 1
END

TABLE FILE CAR
SUM MODEL
BY COUNTRY
ON TABLE PCHOLD FORMAT XLSX TEMPLATE 'SHEET1.xlsx' SHEETNUMBER 2
END
-RUN


In the end it's just another application of MacGyver; people familiar with it will recognize the MAKESEQ fex.

No need to mess around with macro's and Visual Basic, which is a language I try to stay as far away from as possible.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report 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     [WORKAROUND] XLSX Sheet titles?

Copyright © 1996-2020 Information Builders