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     [SOLVED] creating .xls file -- is it XLS or XHT with a .xls extension?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] creating .xls file -- is it XLS or XHT with a .xls extension?
 Login/Join
 
Platinum Member
posted
I'm modifying some old focexecs that call Python to open the .XLS files created in WebFOCUS and save them as Excel files. This is done because they found that in doing so it reduces the file size because whether they use FILEDEF or ON TABLE SAVE FORMAT EXCL2K, the file created was not a true Excel file, but rather Excel HTML, which creates much larger files (with hundreds of these created daily, they need to minimize the file sizes).

Evidently this was the case many releases ago. Is this still the case in WebFOCUS 7.6.8? I'd like to get rid of the Python routines as part of streamlining and cleaning up the code.


Thank you,

John

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


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Report This Post
Platinum Member
posted Hide Post
According to Tech Support, this is still the case, and also is the case when creating .PDF files -- they are actually PDF-HTML. There is a new feature request to create real Excel and real PDF files.


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Report This Post
Expert
posted Hide Post
The PDF files are not HTML, but a non compressed PDF document. There is a new feature in 7.6.4, I think, with the setting SET FILECOMPRESS=ON, to compress the contents.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
Yep,
 SET FILECOMPRESS=ON 

Works fine for FORMAT PDF, does not work for FORMAT EXL2K.
Thx for the reminder Waz
Wink


--------------------------------------------------------------------------------
prod: WF/AS 8.2.05; OmniGen;
In FOCUS since 1991
 
Posts: 104 | Location: United Kingdom | Registered: February 07, 2008Report This Post
<JG>
posted
From 7.7 a new Excel format which is supposed to be Excel 2007 binary format is planned.

It will only be usable if your clients are Excel 2007.

If file size is an issue at the moment then would zipping the files be an option?
 
Report This Post
Expert
posted Hide Post
For windows, I create a VB Script file on the fly in EDATEMP and then call that. Works just fine and average file size reduction is in the order of 66%.

FILEDEF VBScript DISK ProcExcel.vbs
-RUN
-WRITE VBScript   On Error Resume Next
-WRITE VBScript   Dim ojbExcel
-WRITE VBScript   Dim objBook
-WRITE VBScript   Dim objFSO
-WRITE VBScript   set objFSO = Wscript.CreateObject("Scripting.FileSystemObject")
-WRITE VBScript   const xlNormal = 1
-WRITE VBScript   Set objExcel = CreateObject("Excel.Application")
-WRITE VBScript   if Err.Number <> 0 then
-WRITE VBScript     Wscript.Echo "Excel application not installed."
-WRITE VBScript     Wscript.Quit
-WRITE VBScript   end if
-WRITE VBScript   Set objBook = objExcel.Workbooks.Open("\\server\fullpath to file\&FILENAME.EVAL.xls")
-WRITE VBScript   If objFSO.FileExists(objBook) Then
-WRITE VBScript     objExcel.DisplayAlerts = False
-WRITE VBScript     objExcel.ActiveWorkbook.SaveAs objBook.FullName, xlNormal
-WRITE VBScript     objExcel.DisplayAlerts = True
-WRITE VBScript     objBook.Close
-WRITE VBScript   End If
-WRITE VBScript   objExcel.Quit
-WRITE VBScript   Set objBook  = Nothing
-WRITE VBScript   Set objExcel = Nothing
-WRITE VBScript   Set objFSO   = Nothing
-RUN
-* Open file in MS Eggshell on the server to regain white space !!
CMD START ProcExcel.vbs
-RUN

Of course, you have to have Excel on the reporting server.

T

This message has been edited. Last edited by: Tony A,



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
Platinum Member
posted Hide Post
Hi Tony,

Would you please show how to use your VB code to open a .XHT file and save it as a.XLS file in the same location? The way it is, it appears to open a .XLS file, yes?

I've tried your example, but nothing happened. I've tried playing with the code a little as well, but didn't get the .XLS file as a result.

Thank you,

John


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Report This Post
Expert
posted Hide Post
Filedef your output file something like FILEDEF XLSOUT DISK [fullpath]\filename.xls before using ON TABLE HOLD AS XLSOUT. Then use the VB Script code to open that file.

The VB Script code opens an xls file and then uses save as vbNormal to save it back to the same file name.

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
Platinum Member
posted Hide Post
The FILEDEF was the missing ingredient! I was trying to get the code to open the .XHT and do a Save As into a .XLS. With the FILEDEF no changes were needed to your code.

To sum this up, your routine serves 2 purposes:
1. It compresses the Excel file
2. It converts the .XHT into .XLS

Thank you,

John


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Report This Post
Platinum Member
posted Hide Post
After the focexec runs the .vbs routine, later on it issues a DOS COPY command to copy the .XLS file to a different server. The .XLS file size after the .vbs routine finished was 884kb. After the copy it was 3,166kb.

I opened each version and did a SAVE AS to see what file type Excel thought each was. The original, 884 kb version was a true Excel file, but the copied 3,166kb version was the Excel HTML web archive version.

Why it revert back to the EXCEL HTML version? Any ideas on how to prevent this? I suppose I could see if the .vbs routine would work on the file after it's been copied to the other server.

Thank you,

John


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Report This Post
Platinum Member
posted Hide Post
What I think it's doing is not waiting for the .vbs routine to finish and is copying the original Excel HTML version. Is there a way to make the focexce wait until the .vbs routine is finished before it continues and executes the copy command?

Thank you,

John


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Report This Post
Platinum Member
posted Hide Post
The SYSTEM function is the solution.

The SYSTEM function forces WebFOCUS to pause processing until the DOS command finishes executing.


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Report This Post
<JG>
posted
John, wish there were more members like you.

Found your own answer and solution before you got a reply.

Much more important though, you posted the solution.
 
Report This Post
Gold member
posted Hide Post
quote:
Originally posted by JohnB:
The SYSTEM function is the solution.

The SYSTEM function forces WebFOCUS to pause processing until the DOS command finishes executing.


Hi John,

What you mean by the SYSTEM function?

Shirley


Shirley Huang
WF 7.7.04M MRE BID DevStudio
WIN2003, SQL Server 2000/DB2
HTML/PDF/EXL2k/AHTML
iSM 6.16
 
Posts: 55 | Registered: May 05, 2006Report This Post
Expert
posted Hide Post
There is a UWS called SYSTEM, have a look at the functions manual.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Gold member
posted Hide Post
Hi Waz,

Please excuse my lack of knowledge on SYSTEM function. I looked at the help on SYSTEM function, but I'm still not sure how that applies to the VBScript coding above. I'm not familiar with VBScript.

Thanks for the response.


Shirley Huang
WF 7.7.04M MRE BID DevStudio
WIN2003, SQL Server 2000/DB2
HTML/PDF/EXL2k/AHTML
iSM 6.16
 
Posts: 55 | Registered: May 05, 2006Report This Post
Master
posted Hide Post
I have been bugging IBI to create true Excel files for a long time now and found out in the Expert Room at Summit that they are finally there. We use OpenOffice and WebFOCUS output looks ugly and/or crashes OpenOffice when opening the "HTML Excel" currently produced by most versions of WebFOCUS.

IBI has been working on getting output to display properly on mobile devices and found out that it was necessary to get Excel to display properly in OpenOffice first.

I've had a preview of this, and it isn't perfect, but a big improvement. The folks I spoke to said it would be part of a maintenance release for 7.7.04 and perhaps 7.7.03.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Member
posted Hide Post
I wanted to add my two cents even though this is almost a year old.

I had to use the VBScript that Tony showed to be able to allow users getting the report by email on the iPads, iPhones and Macs. The mht Excel file sent by WebFocus will not open on their devices.

I had difficulty setting it up as the ReportCaster service couldn't call the function as the Windows LocalSystem account. Here are the steps I went through to get it to work.

1. Setup ReportCaster schedule to run from an Active Directory account whose password does not change.
2. Run DCOMCNFG -32 (Since the excel version we were using was 32 bit. You can run DCOMCNFG to get to the 64 bit settings)
3. Go to Component Services\Computers\My Computer\DCOM Config\Microsoft Excel Application and right click to go to the Properties.
4. Go to the Identity tab, and change to "This user" and put in the credentials for the new Active Directory user you are using in the ReportCaster schedule
5. Go to the Security tab and for each of the settings, make sure this same user has the capability to access and launch Excel.

Once I did this, the ReportCaster schedule could run the report and the VBScript to open Excel and re-save it as a "real" Excel file. I could then send this new file to our users and they could open it on any OS or device that could open normal Excel files.

Thought this might help.


WebFocus Server 7.7.04
Client 7.7.03 HF 5
Windows Server 2008 R2 x64
HTML,Active HTML, Excel
 
Posts: 6 | Registered: May 02, 2012Report 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     [SOLVED] creating .xls file -- is it XLS or XHT with a .xls extension?

Copyright © 1996-2020 Information Builders