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,

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?

Do you have SET HTMLENCODE ON on the TABLE requests that write to your worksheets?

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.

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.

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.

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

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.

Lusheng

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

Lusheng, did you remove your FILEDEF statements?

jgelona, I did comment out FILEDEF statements, still got errors (FOC1522.

I opened a case with IBI, and hope they can help.

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).

Lusheng, did you ever get a solution from IBI? I'm facing the same issue.