Using Overflow Worksheets for EXL2K Report Output

The maximum number of rows supported by Excel 2003 on a worksheet is 65,536 (65K). When you create an EXL2K output file from a FOCUS report, the number of rows generated can be greater than this maximum.

To avoid creating an unusable output file, you can have extra rows flow onto a new worksheet, called an overflow worksheet. The name of each overflow worksheet will be the name of the original worksheet appended with an increment number.

In addition, when the overflow worksheet feature is enabled, you can set a target value for the maximum number of rows to be included on a worksheet. By default, the row limit will be set to the default value for the LINES parameter (57).

Note: When generating EXL2K output, the FOCUS page heading and page footing commands generate worksheet headings and worksheet footings.

Syntax: How to Enable Overflow Worksheets

Add the ROWOVERFLOW=ON attribute to your FOCUS StyleSheet.

TYPE=REPORT, ROWOVERFLOW=ON, ...

This attribute will work only with EXL2K output, which generates Web archive files (.XHT). For all other output types, the ROWOVERFLOW StyleSheet attribute is ignored, and data flow is not affected.

Syntax: How to Set a Target Number of Rows for an EXL2K Worksheet

The ROWOVERFLOW=ON attribute is required in the FOCUS StyleSheet in order to set a target number of rows for the worksheets in the EXL2K output file.

In a FOCEXEC or profile, use the following syntax

SET LINES = nnnnn

In a request, use the following syntax

ON TABLE SET LINES nnnnn

where:

nnnnn: Is a target value for the maximum number of lines to be included on any worksheet in the EXL2K output file when the overflow worksheet feature is enabled. The value must be less than 65K. Each worksheet will end within a range of the designated limit. The actual row count for each sheet is calculated individually based on user defined heading/footing lines and other system generated lines added to each sheet by the specific report request. If necessary, you can modify how your report flows across worksheets by adding or removing space in subheadings or subfootings.

Reference: Usage Notes for Excel 2000 Overflow Worksheets

The report heading is placed once at the start of the first sheet. The report footing is placed once at the bottom of the last overflow sheet.
Worksheet headings and column titles are repeated at the top of the original sheet and each subsequent overflow sheet. Worksheet footings are placed at the bottom of the original sheet and each subsequent overflow sheet. The data values are displayed on the top data row of each overflow sheet as they would be on a standard new page.
Report total lines are displayed at the bottom of the last overflow sheet directly above the final page and table footings.
Subheadings, subfootings, and subtotal lines display within the data flow as normal. No special consideration is made to retain groupings within a given sheet.
For EXL2K output, if the ROWOVERFLOW attribute is specified in the StyleSheet and SET LINES is greater than 65K, the following message is presented and no output file is generated:
```
(FOC3313) The line number limit per EXCEL 2003 worksheet is 65536.
```
Output types that contain formula references (EXL2K PIVOT and EXL2K FORMULA) are not supported. Formula references are not automatically updated to reflect placement on new overflow worksheets.
The overflow worksheet feature applies to rows only, not columns. A new worksheet will not automatically be created if a report generates more than the Excel 2003 limit of 256 columns.

Top of page

Example: Creating Overflow Worksheets With EXL2K Report Output

The following request creates EXL2K report output with overflow worksheets. The SET LINES command sets the maximum number of rows in each worksheet to approximately 2000, and the ROWOVERFLOW=ON attribute in the StyleSheet activates the overflow feature. Without this attribute, one worksheet would have been generated instead of three.

TABLE FILE GGSALES
-* ****Report Heading****
ON TABLE SUBHEAD
"SALES BY REGION, CATEGORY, AND PRODUCT"
" "
-* ****Worksheet Heading****
HEADING
"SALES REPORT WORKSHEET <TABPAGENO"
" "
-* ****Worksheet Footing****
FOOTING
" "
"END OF WORKSHEET <TABPAGENO"
PRINT DOLLARS UNITS BUDDOLLARS BUDUNITS
BY REGION
BY CATEGORY
BY PRODUCT
BY DATE
-* ****Subfoot****
ON REGION SUBFOOT
" "
" End of Region <REGION"
" "
-* ****Subhead****
ON CATEGORY SUBHEAD
" "
" Category <CATEGORY for Region <REGION"
" "
-* ****Report Footing****
ON TABLE SUBFOOT
" "
"END OF REPORT"
ON TABLE SET LINES 2000
ON TABLE HOLD FORMAT EXL2K
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=EXLOVER, ROWOVERFLOW=ON,$
ENDSTYLE
END

The report heading displays on the first worksheet only, the page heading and column titles display on each worksheet, and the subhead and subfoot display whenever the associated sort field changes value. The following image shows the top of the first worksheet, displaying the report heading, page heading, column titles, and first subhead.

Note that the TITLETEXT attribute in the StyleSheet specified the name EXLOVER, so the three worksheets were generated with the names EXLOVER1, EXLOVER2, and EXLOVER3. If there had been no TITLETEXT attribute, the sheets would have been named SHEET1, SHEET2, and SHEET3.

The worksheet footing displays at the bottom of each worksheet and the report footing displays at the bottom of the last worksheet. The following image shows the bottom of the last worksheet, displaying the last subfoot, the page footing, and the report footing.