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     [CASE-OPENED] EXL07 and Excel Templates

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CASE-OPENED] EXL07 and Excel Templates
 Login/Join
 
Member
posted
I've searched Focal Point and I see a lot of posts where it says we can use Excel 2007/2010 with Excel 2003 (or maybe earlier) templates. However, I think that's misleading because it's only true if the WebFocus output is EXL2K, not EXL07, correct? Thus, I'd like someone to tell me if they think I've got this right, or wrong:

We have Excel 2010. If I write a focus report with EXL2K (.xls) as the output format, I can use a template (.mht) I previously created with Excel 2003. That works fine, but we get an annoying message about .xls not being the correct format for Excel 2010. Hitting [Yes] to that message opens the spreadsheet, but it's still annoying. When the .xls opens, the template works fine.

If we use EXL07 (.xlsx) as the WebFocus output, we don't get that annoying message, but the template doesn't work.

We'd like to use EXL07 (to avoid the annoying message) with Excel formatting macros. Does this mean our only choice is to use Vb Script (which replaces the Excel template)? If so, do you recommend any good websites for learning Vb Script?

Note: I've heard there's a registry hack to get rid of the annoying message of opening .xls with Excel 2010, but I don't think that's an option for all of our users.

This message has been edited. Last edited by: <Kathryn Henning>,


Thanks,

~Mark

WebFocus Client 7.703
WebFocus Reporting Server 7.705
Windows Server 2008 R2
Dev Studio 7.703
Apache Tomcat/6.0.20
Excel, PDF
IWAY DM Server 7.705
DataMigrator 7.705
 
Posts: 27 | Location: Maryland | Registered: March 09, 2006Report This Post
Gold member
posted Hide Post
If you are able to upgrade to WebFOCUS 7.7.04, you can make use of macro-enabled templates for EXL07.


WebFOCUS 8
 
Posts: 74 | Location: Gahanna, OH | Registered: September 22, 2009Report This Post
Member
posted Hide Post
JR,

I've never quite gotten the hang of the different component names and version #s of WebFocus.

We have WF Client 7.7.03, and WebFocus Reporting Server 7.7.05.

When you refer to WebFocus 7.7.04, are you referring to one of the two I listed, above? If so, which one.

Do you know of a good place - specific book, manual, PDF - for documentation for the macro-enabled templates for EXL07?


Thanks,

~Mark

WebFocus Client 7.703
WebFocus Reporting Server 7.705
Windows Server 2008 R2
Dev Studio 7.703
Apache Tomcat/6.0.20
Excel, PDF
IWAY DM Server 7.705
DataMigrator 7.705
 
Posts: 27 | Location: Maryland | Registered: March 09, 2006Report This Post
Master
posted Hide Post
Folkey,

There is no client version higher then 7.7.03 currently. 7.7.03 goes up to Hot Fix 7 I believe. Also you need to be on Hot Fix 5 or above if you have Reporting server 7.7.04. Since you have Reporting server 7.7.05, you are already at a version that allows macro enabled templates according to JR Lewis.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Gold member
posted Hide Post
I haven't found much documentation on Excel templates with EXL07, but I did attend a Summit presentation on the topic last year.

So, I did some testing and was able to get it to work for me. Below is a snippet of code I came up with to populate three worksheets in an Excel template, and use Excel formulas to populate cells on a fourth worksheet.

-SET &ECHO = 'ALL';

APP HOLD JOHNTEST
-RUN
-*
SET EXCELSERVURL = ''
-*
TABLE FILE CAR
SUM DEALER_COST RETAIL_COST SALES
BY COUNTRY
ON TABLE HOLD AS HOLD1 FORMAT XLSX TEMPLATE 'test_exl07_template.xltm' SHEETNUMBER 1
END
-RUN

TABLE FILE CAR
SUM DEALER_COST RETAIL_COST SALES
BY CAR
ON TABLE HOLD AS HOLD2 FORMAT XLSX TEMPLATE 'HOLD1.xlsm' SHEETNUMBER 2
END
-RUN

TABLE FILE CAR
SUM DEALER_COST RETAIL_COST SALES
BY BODYTYPE
ON TABLE PCHOLD FORMAT XLSX TEMPLATE 'HOLD2.xlsm' SHEETNUMBER 3
END
-RUN


My copy of 'test_exl07_template.xltm' has 4 worksheets. Sheets 1-3 have only the value of 'empty' in cell A1 of each sheet. Sheet 4 includes some references to cells in other worksheets just to make sure I could get that to work.

There is some server-side configuration that needs to be done as well. Java Services needs to be established, and a JAVA_HOME environment variable needs set up. I am not so familiar with these items - IBI can provide some assistance with these steps if you want to proceed.


WebFOCUS 8
 
Posts: 74 | Location: Gahanna, OH | Registered: September 22, 2009Report This Post
Member
posted Hide Post
Thanks for the info. I know we have a hotfix applied to our WF Client, but I don't remember the number. It says "branch7703_hotfix" on the WF Admin Console page under WF Info.

After today, I may not be able to work on this for another week - but I'll get back to you then. Keep the suggestions coming.


Thanks,

~Mark

WebFocus Client 7.703
WebFocus Reporting Server 7.705
Windows Server 2008 R2
Dev Studio 7.703
Apache Tomcat/6.0.20
Excel, PDF
IWAY DM Server 7.705
DataMigrator 7.705
 
Posts: 27 | Location: Maryland | Registered: March 09, 2006Report This Post
Platinum Member
posted Hide Post
I am not able to get data on any of my reports since the upgrade for format EXL07, however EXL2K opens fine. Is there really a hotfix for this? We upgraded to 7.7.3


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 2006Report This Post
Member
posted Hide Post
From what I understand, Excel 2010 has 3 types of template files, ending in .xlsm, .xltm and .xltx. Using the following lines in a focexec, either nothing happens (when format is EXL07) or I get a FOC3289 error when using format EXL2K:

ON TABLE PCHOLD FORMAT EXL07 TEMPLATE mark/LandscapeBoldHeadingTemplate.xlsm SHEETNUMBER 2 - nothing happens
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE mark/LandscapeBoldHeadingTemplate.xlsm SHEETNUMBER 2 - error foc3289

ON TABLE PCHOLD FORMAT EXL07 TEMPLATE mark/LandscapeBoldHeadingTemplate.xltm SHEETNUMBER 2 - nothing happens
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE mark/LandscapeBoldHeadingTemplate.xltm SHEETNUMBER 2 - error foc3289

ON TABLE PCHOLD FORMAT EXL2K TEMPLATE mark/LandscapeBoldHeadingTemplate.xltx SHEETNUMBER 2 - error foc3289

However, when I use:

ON TABLE PCHOLD FORMAT EXL07 TEMPLATE mark/LandscapeBoldHeadingTemplate.xltx SHEETNUMBER 2

I get my output in worksheet # 2, but the formatting I originally had in the template (big fonts and colors) are no longer in sheet # 2. In the template, I also put Excel formulas in sheet # 1, and the non-formatted data in sheet # 2 was copied, and formatted in sheet # 1. However, I can't create a universal template (applicable to all of my reports) which will copy all the data in sheet # 2 to sheet # 1 (& then delete sheet # 2). I don't remember, but either VBA (to copy and delete sheet # 2) isn't allowed in .xltx, or if it is allowed, WebFocus will ignore the VBA.

I haven't looked into the JAVA_HOME environment variable yet. All of my Excel templates have at least one non-empty cell.

As stated in my first post, we can use EXL2K with an Excel 2003 template, but that results in an annoying message about the wrong format. Not sure if that's what's meant when Tracie says EXL2K opens fine.

It's interesting that JR uses a format of XLSX instead of EXL07. I didn't notice that in the manual. I get the same results whether I use XLSX or EXL07.


Thanks,

~Mark

WebFocus Client 7.703
WebFocus Reporting Server 7.705
Windows Server 2008 R2
Dev Studio 7.703
Apache Tomcat/6.0.20
Excel, PDF
IWAY DM Server 7.705
DataMigrator 7.705
 
Posts: 27 | Location: Maryland | Registered: March 09, 2006Report This Post
Guru
posted Hide Post
Here is the technical memo for 7.7.03. This is what I used to setup my environments. I used the JSCOM3 approach on the reporting server on 7.7.03 and 8002M


EXL07

Building the .xlsx Workbook File
Microsoft changed the format and structure of the Excel workbook in Excel 2007. The new
.xlsx file is a binary compilation of a group of xml files. Generating this new file format using

WebFOCUS is a two-step process that consists of generating the xml files containing the
report output and zipping the xml documents into the binary .xlsx format. The Reporting
Server performs the xml generation process. The zipping process can be completed either
by the client (WebFOCUS Servlet) or the server (JSCOM3):

WebFOCUS Servlet. -- The WebFOCUS Client within the application server performs the
zipping process. This can be done within the local client or through a remotely accessed
client. The servlet method is the default approach defined for each WebFOCUS client,
with the client by default pointing to itself.

JSCOM3. -- The Java layer of the Reporting Server performs the zipping operation. This
option should be used when the WebFOCUS Servlet is configured on a secured Web or
application server. This is because JSCOM3 does not require URL access to a remote
WebFOCUS Servlet.


In order to get the Java Services started on the WFRS



I needed to setup the JAVA_HOME variable on my server





In each procedure that I am going to put out as EXL07 I add this

SET EXCELSERVURL = ''


Now between 7.7.03 and 8002M, I have noticed some differences in how templates behave so my assumption is there are differences between 7.7.03 and .04. I think some of the big differences is the ability for macros in templates in the later versions.

You may want to ask your admin, how your environment is setup. Maybe this option will work for you.

Hope this helps.


WebFOCUS 8.1.05
 
Posts: 496 | Registered: January 04, 2008Report This Post
Gold member
posted Hide Post
I believe the XLSX and EXL07 are interchangeable. I have used both successfully.

If you want formatting and formulas to be retained, you will need the .xltm format (Excel template with macros).

The same applies to using VBA in your template.

IBI's tech support site has information that may be helpful in resolving the issue.


WebFOCUS 8
 
Posts: 74 | Location: Gahanna, OH | Registered: September 22, 2009Report This Post
Master
posted Hide Post
quote:
I get my output in worksheet # 2, but the formatting I originally had in the template (big fonts and colors) are no longer in sheet # 2. In the template, I also put Excel formulas in sheet # 1, and the non-formatted data in sheet # 2 was copied, and formatted in sheet # 1. However, I can't create a universal template (applicable to all of my reports) which will copy all the data in sheet # 2 to sheet # 1 (& then delete sheet # 2). I don't remember, but either VBA (to copy and delete sheet # 2) isn't allowed in .xltx, or if it is allowed, WebFocus will ignore the VBA.


Let me preface this with I'm still on 7.6.11. Which means I have to build my excel templates with Excel 2003 because Excel 2007 & 2010 are not supported in 7.6.11. The user community uses Excel 2010.

With that said, I have never been able to get excel template file (.mht files in 2003) to save any formats. The reason for this is the report overwrites everything on the worksheet. I do as much formating in the .fex as I can and use an auto open macro in the templace to do the rest. I've never had any trouble doing any excel specific formatting (i.e. inserting forumulas, copying data from 1 sheet to another, deleting a sheet, inserting a new sheet, etc.) with method.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 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     [CASE-OPENED] EXL07 and Excel Templates

Copyright © 1996-2020 Information Builders