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] Append Excel Files

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Append Excel Files
 Login/Join
 
Virtuoso
posted
After trying several times..to APPEND 2 EXCEL FILES TOGETHER.

I discover from the IBI site the following:
An EXCEL file must be created containing data from two files. FILEDEF
or ALLOCATE the EXCEL output with a DISP=MOD, (or APPEND depending on
environment) does not contain both answer sets, but only the second.

- Problem Section

A HOLD FORMAT EXCEL issues its own FILEDEF/ALLOCATE, over-riding the
instructions to APPEND

Solution:
use the MORE option of universal
concatenation to concatenate the input.
TABLE FILE FN
WRITE FIELD1....BY SORT1...
ON TABLE HOLD FORMAT EXCEL
MORE
FILE2
END

Now maybe it is just me...but how is that putting two excel sheets together?
Anyone have any thought or clues.

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


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
<JG>
posted
Prarie,

As you are a 5.35 site, using the compound report syntax for EXL2K should deliver what you want.

However to expand on the not so helpful hottrack solution that you found.

MOD or APPEND works to create a single output file to the destination dataset,
The problem is, for EXL2K output the format is actually extended html.

WebFocus correctly generates the output to a single file however Microsoft Excel cannot read what is delivered.

The reason is that WebFocus generates a single file with 2 complete documents in the 1 file and Excel reads the first and then stops.

Using more gives you a single output from two sources with exactly the same format.

It is very definitely NOT 2 different excel reports in the same document.

If you want to have multiple EXl2K Reports in the same sheet (pre EXL2K compound functionality) you must manipulate the output files to give you a single document with multiple HTML tables. (use a variation on my posting about Macro's in pre 7.1.

sample code is as follows.

 
FILEDEF EXCEL DISK EXCEL.MAS
-RUN
-*
-* write your dummy master to allow Focus to read the .XHT file as input
-*
-WRITE EXCEL FILE=EXCEL          ,SUFFIX=FIX
-WRITE EXCEL SEGNAME=EXCEL       ,SEGTYPE=S0
-WRITE EXCEL FIELDNAME   =DATALINE       ,E01         ,A256    ,A256     ,$
-RUN
TABLE FILE CAR
"Report 1"
PRINT MODEL
ON TABLE HOLD AS X FORMAT EXL2K
END
-RUN
TABLE FILE CAR
" "
"Report 2"
PRINT  MODEL
BY COUNTRY
ON TABLE HOLD AS Y FORMAT EXL2K
END
-RUN
FILEDEF Z DISK c:\ibi\apps\focalpoint\excelappend.xls (APPEND
FILEDEF EXCEL DISK x.xht
-RUN
DEFINE FILE EXCEL
WANTED/A1= IF EDIT(DATALINE,'9999999') EQ '</BODY>' THEN 'N'
            ELSE IF LAST WANTED EQ 'N' THEN 'N' ELSE 'Y';
END
TABLEF FILE EXCEL
PRINT DATALINE
WHERE WANTED EQ 'Y';
ON TABLE SAVE AS Z FORMAT ALPHA
END
-RUN
FILEDEF EXCEL DISK y.xht
-RUN
DEFINE FILE EXCEL
WANTED/A1= IF EDIT(DATALINE,'9999999') EQ '<TABLE' THEN 'Y'
            ELSE IF LAST WANTED EQ 'Y' THEN 'Y' ELSE 'N';
END
TABLEF FILE EXCEL
PRINT DATALINE
WHERE WANTED EQ 'Y';
ON TABLE SAVE AS Z FORMAT ALPHA
END
-RUN
-HTMLFORM BEGIN
<html>
<head>
<title>EXCEL2K Compound </title>
</head>
<frameset rows="*">
  <frame name="EXCEL2K" src="http://192.168.1.3:9080/approot/focalpoint/excelappend.xls" scrolling="auto" >
  <noframes>
  <body>
  <p>This page uses frames, but your browser doesn't support them.</p>
  </body>
  </noframes>
</frameset>
</html>
-HTMLFORM END


As I mentioned in the original posting this requires a permanent storage location that can be accessed by the webserver and so will need management.

JG
 
Report This Post
Virtuoso
posted Hide Post
Thanks JG...this looks good. No problem on the permanent place.


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Member
posted Hide Post
JG, I have used your example code to append around 30 excel reports. Neat trick by the way!! It seemed to work great except it also generates around 350 extra sheets in the excel file.

In your example you only append two files together and your WANTED defines are different for each report. I'm thinking there needs to be yet a different kind of define for anything after 2 reports? To get it work I ended up using the one looking for the WANTED/A1= IF EDIT(DATALINE,'9999999') EQ '' and it retrieved all the different reports, but also added a ton of extra Recovered_Sheets. How did you decide what the WANTED defined needed to look for? I would appreciate any help you can give me!!

Thanks!!

Renee


WebFOCUS 7.6.10
 
Posts: 5 | Registered: May 06, 2003Report This Post
<JG>
posted
I was very neglectful of replying to Rlmuell on this because of workload.

Fortunately he sent a private message and brought his issue to my attention so that I could try and help.

To create a compound Excel up to the Microsoft limit of 64K the following is a variation on my original code and accommodates multiple excels in the same sheet and not just two.

________________________________________________________________________

For multiple Excel documents the important thing is only selecting the table components for the reports after the first document, except for the last where you need the closing body and html tags as well.

(slightly cryptic but you will see what I mean)

I have coded up an extended example that generates a single excel containing 30 reports.
(It's just a simple loop of the same static code for the example but all you need to do is have numbered reports and -INCLUDE them)

Notice the first part of the process is outside of the loop and the define in the loop
has been extended to ensure that we only get the closing body and html tags for the last report.
  
-* File excelappend.fex
FILEDEF EXCEL DISK EXCEL.MAS
-RUN
-*
-* write your dummy master to allow Focus to read the .XHT file as input
-*
-WRITE EXCEL FILE=EXCEL          ,SUFFIX=FIX
-WRITE EXCEL SEGNAME=EXCEL       ,SEGTYPE=S0
-WRITE EXCEL FIELDNAME   =DATALINE       ,E01         ,A256    ,A256     ,$
-RUN

FILEDEF Z DISK c:\ibi\apps\focalpoint\excelappend.xls (APPEND
-RUN
TABLE FILE CAR
"Report 1"
PRINT MODEL
ON TABLE HOLD AS X FORMAT EXL2K
END
-RUN
FILEDEF EXCEL DISK x.xht
-RUN
DEFINE FILE EXCEL
WANTED/A1= IF EDIT(DATALINE,'9999999') EQ '</BODY>' THEN 'N'
            ELSE IF LAST WANTED EQ 'N' THEN 'N' ELSE 'Y';
END
TABLEF FILE EXCEL
PRINT DATALINE
WHERE WANTED EQ 'Y';
ON TABLE SAVE AS Z FORMAT ALPHA
END
-RUN
-SET &REPORT =1;
-*********************************************************************************
-* At this point you do a -INCLUDE REPORT&REPORT NOT just a loop on sataic code. The 29 is because this 
-* is what was needed for the requirement. There is no maximum Except respecting the Microsoft 64K rows limit.
-*********************************************************************************
-REPEAT ENDREPEAT 29 TIMES
-SET &REPORT =&REPORT +1;
TABLE FILE CAR
" "
"Report &REPORT"
PRINT  MODEL
BY COUNTRY
ON TABLE HOLD AS Y FORMAT EXL2K
END
-RUN

FILEDEF EXCEL DISK y.xht
-RUN
DEFINE FILE EXCEL
WANTED/A1= IF EDIT(DATALINE,'9999999') EQ '<TABLE' THEN 'Y' ELSE
            IF (&REPORT EQ 30) AND (LAST WANTED EQ 'Y') THEN 'Y'  ELSE
            IF (LAST WANTED EQ 'Y')  AND (EDIT(DATALINE,'9999999') EQ '</BODY>') THEN 'N' ELSE
             IF LAST WANTED EQ 'Y' THEN 'Y' ELSE 'N';
END
TABLEF FILE EXCEL
PRINT DATALINE
WHERE WANTED EQ 'Y';
ON TABLE SAVE AS Z FORMAT ALPHA
END
-RUN
-ENDREPEAT
-*********************************************************************************

-HTMLFORM BEGIN
<html>
<head>
<title>EXCEL2K Compound </title>
</head>
<frameset rows="*">
-***************************************************************************************************
-*     REMBER use your server address
-***************************************************************************************************
  <frame name="EXCEL2K" src="http://localhost/approot/focalpoint/excelappend.xls" scrolling="auto" >
  <noframes>
  <body>
  <p>This page uses frames, but your browser doesn't support them.</p>
  </body>
  </noframes>
</frameset>
</html>
-HTMLFORM END


Hope it helps others.

This message has been edited. Last edited by: <JG>,
 
Report This Post
Member
posted Hide Post
JG,

Your solution worked great for me. I am now only generating one worksheet on the excel.

Thanks!
Renee


WebFOCUS 7.6.10
 
Posts: 5 | Registered: May 06, 2003Report This Post
Platinum Member
posted Hide Post
Another solution, it may already be posted, but you can also use the following script at the top of each report that is in format EXL2K. This worked extremely well for me:

First report:

SET COMPOUND = 'OPEN NOBREAK'
[REPORT TEXT]
ON TABLE PCHOLD FORMAT EXL2K
END

Second report:

SET COMPOUND = NOBREAK
[REPORT TEXT]
ON TABLE PCHOLD FORMAT EXL2K
END
.
.
.
Last report:
SET COMPOUND = CLOSE
[REPORT TEXT]
ON TABLE PCHOLD FORMAT EXL2K
END

Again this works very well for multiple report, I created a report with more than 5 reports and it all fit within the limit of the Excel rows (remember, Excek only allows 65536 rows of data!)

However, I would like to know if there is a way to create a report that outputs multiple Excel "files", or at least multiple reports in one "file" but separate "sheets". Unless all reports are in exactly the same format, I really don't see a major advantage of exporting multiple files into one Excel file on the same "sheet".

Thanks!
 
Posts: 87 | Registered: August 03, 2006Report This Post
Member
posted Hide Post
This worked like a charm. Thank you.


Annette Bradley
Lead Systems Analyst/Programmer

Prod: WebFOCUS 7.1.0
Test: WebFOCUS 7.1.0
 
Posts: 26 | Location: Little Rock, AR | Registered: October 29, 2004Report This Post
Platinum Member
posted Hide Post
Horay, I like! Someone use search to find solution. It work! Big aplause SmilerSmilerSmiler

Kofi


Client Server 8.1.05: Apache; Tomcat;Windows Server 2012
Reporting Server 8.1.05; Oracle; MS SQL; Windows Server 2012
 
Posts: 106 | Registered: April 06, 2009Report 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] Append Excel Files

Copyright © 1996-2020 Information Builders