[WORKAROUND] XLSX Sheet titles?

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, 2010

Tony A

Expert

posted

Hide Post

Well done, you have found the stylesheet option for Excel output that doesn't work with templates

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

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, 2004

Wep5622

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, 2010

Ignored post by Wep5622 posted

Show Post

Please Wait. Your request is being processed...