| ||||||||||||||
What's New* Focal Point Virtuals: 2012 | 2013 * Summit 2013: June 3 in Florida! | Full Agenda | Agenda Builder | ROI Kit| Rates | Register Now * New: Published Tips and Techniques * WebFOCUS Newsletter - May-June 2013 * Visit the ESRI Forum | Higher Education Forums Connect With Us!
RemindersUpdate your signature (now required!), do your search first, and tag your topic with keywords Forum Rules, Guidelines, best-practice tips and FAQ's for Everyone to Remember | ||||||||||||||
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Append Excel FilesGo | New | Search | Notify | Tools | Reply | |
| [SOLVED] Append Excel Files | Login/Join |
| Virtuoso |
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,
| ||||
|
| Virtuoso |
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 | |||
|
| Virtuoso |
Thanks JG...this looks good. No problem on the permanent place.
| |||||
|
| Posts: 5 | Registered: May 06, 2003 | |
| Virtuoso |
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, | |||
|
| Member |
JG, Your solution worked great for me. I am now only generating one worksheet on the excel. Thanks! Renee WebFOCUS 7.6.10 | |||
|
| Platinum Member |
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! | |||
|
| Member |
This worked like a charm. Thank you. Annette Bradley Lead Systems Analyst/Programmer Prod: WebFOCUS 7.1.0 Test: WebFOCUS 7.1.0 | |||
|
| Gold member |
Horay, I like! Someone use search to find solution. It work! Big aplause  Kofi Client Server 7.7.03: Apache; Tomcat;Windows 2003 Reporting Server 7.7.03; Oracle; MS SQL; Windows 2003 | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
|
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Append Excel Files | View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Buddies |
| Add $GS_USERNAME to my Ignore List |
| Invite $GS_USERNAME to a Private Topic |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |
