Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE OPENED] (FOC3289) TEMPLATE FILE: Error opening file
Go
New
Search
Notify
Tools
Reply
  
[CASE OPENED] (FOC3289) TEMPLATE FILE: Error opening file
 Login/Join
 
Platinum Member
posted
We are testing to upgrade from WF7.7 to WF8.2. One of our report programs using excel template with compound reports, and it is now get "(FOC3289) TEMPLATE FILE: Error opening file" and "(FOC3317) Error processing template file". The template file contains 10 worksheets.

I did update the template file using excel 2010 and saved the template file with extension .XLTX and made necessary changes in the focexec programs (changed from FORMAT MHT to FORMAT XLSX). After execution of the programs, the template file got corrupted. I tried using excel 2010 to recreate the template file from scratch, and still got same errors, and template file corruption (Excel cannot open the file because the file format or extention name is not valid). I can open the template file before the execution, and it is corrupted by the programs when accessing the template file. These report programs have been working in our production since WF5.3, more than 10 years now.

I did search the forum before posting, and did not find the recent posting about excel template issue. Any help is appreciated!

This message has been edited. Last edited by: FP Mod Chuck,
 
Posts: 116 | Location: Omaha, NE | Registered: June 12, 2003Reply With QuoteReport This Post
Master
posted Hide Post
My first question is does your template contain any macros? If so, try .xltm as the extenstion.

My second question is what do your ON TABLE HOLD/PCHOLD statements look like?

My third questions is on which sheet do you get the error?


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 905 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Do you have SET HTMLENCODE ON on the TABLE requests that write to your worksheets?


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: 1639 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Thanks jgelona and Wep5622 for your attention! My template does not contain macro, which I created 10 years ago. The syntax should be ON TABLE PCHOLD AS HOLD1 FORMAT XLSX TEMPLATE 'MYTEMPLATE' SHEETNUMBER 1 and subsequent worksheet should have ON TABLE PCHOLD AS HOLD2 FORMAT XLSX TEMPLATE 'HOLD1' SHEETNUMBER 2 etc. I do have FILEDEF for each worksheet except for sheet 1, eg. FILEDEF HOLD1 DISK hold1.XLSX. I just changed from FORMAT MHT TO FORMAT XLSX, and the syntax has been working in previous two versions. I checked the output from the job execution, and noticed there are non-readable characters written to worksheet1, and then causing the (FOC3289 in the next worksheet.

As for HTMLENCODE, I believe the default is ON, even if I explicitly set to ON, it does not change the errors.
 
Posts: 116 | Location: Omaha, NE | Registered: June 12, 2003Reply With QuoteReport This Post
Master
posted Hide Post
First I believe your HOLD/PCHOLD statements should look like this:
  ON TABLE HOLD AS HOLD1 FORMAT XLSX TEMPLATE 'MYTEMPLATE.XLTX' SHEETNUMBER 1
...
  ON TABLE HOLD AS HOLD2 FORMAT XLSX TEMPLATE 'HOLD1.XLTX' SHEETNUMBER 2
...
  ON TABLE HOLD AS HOLD3 FORMAT XLSX TEMPLATE 'HOLD2.XLTX' SHEETNUMBER 3
...
  ON TABLE PCHOLD FORMAT XLSX TEMPLATE 'HOLD9.XLTX' SHEETNUMBER 10


Only the last statement should have PCHOLD, the rest should be HOLD. Also you need to specify the extension like I did above. With Excel 2007 and higher, there are 2 types of template files, those that can contain a macro (extension .xltm) and those that can't (extension .xltx).

Second, you don't need the FILEDEF statements like you did for EXL2K formats. With that said, if you have them, make sure you have the correct extension. I believe your FILEDEF HOLD1 DISK hold1.XLSX should be FILEDEF FILEDEF HOLD1 DISK hold1.XLTX, you're still working with a template file. To verify, run the first report with and do a directory listing. Your signature doesn't say what kind of system WebFOCUS is running on. We are Linux so, to see the files in the agent, I would do this after the first report:
-RUN
-UNIX ls -lt
-EXIT


This will give me a list of the files in the agent. You need to do the same thing for your system. What is the extenion of hold1?

The worksheets will have non-readable character. XLSX format (Excel 2007 and higher) are really specialized .zip files.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 905 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Thanks jgelona for your response! We are in Windows Server 2012R2 environment. I changed extension name to be XLTX in the worksheet, and PCHOLD to HOLD except the last sheet, but got FOC3343 (see below). I do see the hold1 file generated in the agent with MS Template file type XLTX. When I tried to open hold1.xltx, it shows error Excel cannot open the file because the file or extension is not valid.

The following error occurs in the second worksheet:

ON TABLE HOLD AS HOLD2 FORMAT XLSX TEMPLATE 'HOLD1.XLTX' SHEETNUMBER 2
END
-RUN
0 NUMBER OF RECORDS IN TABLE= 518 LINES= 209

(FOC3343) EXL07 TEMPLATE file is not valid. File format and file extension do not match.
 
Posts: 116 | Location: Omaha, NE | Registered: June 12, 2003Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I got the first worksheet saved correctly, and the file can be opened. However, the rest of worksheets are empty, and got a lot of other errors. Hope IBI guys can provide some help.

(FOC1522) ntjprerr: CALLPGM execution error caused by:
(FOC1522) org.apache.james.mime4j.parser.MimeParseEventException: Invalid
(FOC1522) header encountered. CPJAVA: Error in processing EXECUTE command for
(FOC1522) Class ibi.jsexcel.JscomExcelZip
 
Posts: 116 | Location: Omaha, NE | Registered: June 12, 2003Reply With QuoteReport This Post
Platinum Member
posted Hide Post
From IBI documentation "Creating Reports With WebFOCUS Language Release 8.2 Version 02", there is only one syntax for template, ON TABLE PCHOLD FORMAT XLSX TEMPLATE template_name SHEETNUMBER n. Based on this syntax, I only need to change template name and sheetnumber, but that does not work either. I know in the previous versions, we defined each worksheet, the first worksheet we have ON TABLE HOLD...and the last worksheet we have ON TABLE PCHOLD. This does not work anymore. Really IBI's poor documentation.
 
Posts: 116 | Location: Omaha, NE | Registered: June 12, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Lusheng

Not sure if you have opened a case with techsupport yet, but that is my suggestion for you now.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1498 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Master
posted Hide Post
Lusheng, did you remove your FILEDEF statements?


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 905 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
jgelona, I did comment out FILEDEF statements, still got errors (FOC1522.

I opened a case with IBI, and hope they can help.
 
Posts: 116 | Location: Omaha, NE | Registered: June 12, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Apparently there are issues with templates that contain Excel comments.

References to other files, especially local ones (C: drive etc), can be problematic. That happens far too easy in Excel, it's enough to copy some cells from one Excel file to another and it writes it as a reference to the original Excel file - 9/10 times that is not what you want.

We also had an issue with custom buttons, but that requires macro's and you said you don't have those (and those files have a different extension - .xltm instead of .xltx).


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: 1639 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
Guru
posted Hide Post
Lusheng, did you ever get a solution from IBI? I'm facing the same issue.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 381 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE OPENED] (FOC3289) TEMPLATE FILE: Error opening file

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.