ROWOVERFLOW Enhancements for EXL07 and EXL2K

The following enhancements have been added to the ROWOVERFLOW feature that overcomes the Excel row limit for a Worksheet:

Overcoming the Excel 2003, 2007, and 2010 Row Limit Using Overflow Worksheets

How to:

Enable Overflow Worksheets

Reference:

Usage Notes for EXL2K and EXL07 Overflow Worksheets

The maximum number of rows supported by Excel 2003 on a Worksheet is 65,536 (65K). The maximum number of rows supported by Excel 2007 and 2010 on a Worksheet is 1,048,576 (1MB). When you create an EXL2K or EXL07 output file from a WebFOCUS report, the number of rows generated can be greater than this maximum.

To avoid creating an incomplete 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: By default, when generating EXL2K or EXL07 output, the WebFOCUS page heading and page footing commands generate only Worksheet headings and Worksheet footings.

Top of page

Syntax: How to Enable Overflow Worksheets

Add the ROWOVERFLOW attribute to your WebFOCUS StyleSheet

TYPE=REPORT, ROWOVERFLOW={ON|OFF|PBON}, [ROWLIMIT={n|MAX}...

where:

ON: Enables overflow Worksheets.
OFF: Disables overflow Worksheets. OFF is the default value.
PBON: Inserts WebFOCUS page breaks that display the page heading, footing, and column titles at the appropriate places within the Worksheet rows. This option does not cause a new Worksheet to start when a WebFOCUS page break occurs.
ROWLIMIT=n: Sets a target value for the number of rows to be included on a Worksheet to n rows. The default value is the LINES value (by default, 57).
ROWLIMIT=MAX: Sets a target value for the number of rows to be included on a Worksheet to 65,000 rows for EXL2K output or 1,048,000 rows for EXL07 output.

This attribute will work only with EXL2K or EXL07 output. For all other output types, the ROWOVERFLOW StyleSheet attribute is ignored, and data flow is not affected.

Top of page

Reference: Usage Notes for EXL2K and EXL07 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.
Unless the PBON setting is used, 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.
If ROWOVERFLOW=PBON, the page headings and footings and column titles display within the Worksheet when a WebFOCUS command causes a page break.
For EXL2K output, if the ROWOVERFLOW attribute is specified in the StyleSheet and ROWLIMIT is greater than 65K, the following message is presented and no output file is generated:
```
(FOC3313) The row limit for EXL2K worksheets is 65536.
```
For EXL07 output, if the ROWOVERFLOW attribute is specified in the StyleSheet and ROWLIMIT is greater than 1MB, the following message is presented and no output file is generated:
```
(FOC3338) The row limit for EXCEL XLSX worksheets is 1048576.
```
Output types that contain formula references (EXL2K PIVOT and EXL2K FORMULA) are not supported, as 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 or the Excel 2007/2010 limit or 16,384 columns.
ROWOVERFLOW is supported for compound layout reports in EXL2K and BYTOC reports for both EXL2K and EXL07.
As named ranges in Excel cannot run across multiple Worksheets, the IN-RANGES phrase that defines named ranges in the resulting workbook is not supported with the ROWOVERFLOW feature. When they exist together in the same request, ROWOVERFLOW takes precedence and the IN-RANGES phrase is ignored.

Top of page

Example: Creating Overflow Worksheets With EXL2K Report Output

The following request creates EXL2K report output with overflow Worksheets. 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 REGION SUBFOOT
" "
" End of Region <REGION"
" "
-* ****Report Footing****
ON TABLE SUBFOOT
" "
"END OF REPORT"
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=EXLOVER, ROWOVERFLOW=ON, ROWLIMIT=2000,$
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.

Top of page

Example: Creating Overflow Worksheets With WebFOCUS Page Breaks

The following request creates EXL07 report output with overflow Worksheets. The ROWOVERFLOW=PBON attribute in the StyleSheet activates the overflow feature, and the ROWLIMIT=250 sets the maximum number of rows in each Worksheet to approximately 250. Without this attribute, one Worksheet would have been generated. The PRODUCT sort phrase specifies a page break.

TABLE FILE GGSALES
-* ****Report Heading****
ON TABLE SUBHEAD
"SALES BY REGION, CATEGORY, AND PRODUCT"
" "
PRINT DOLLARS UNITS BUDDOLLARS BUDUNITS
BY REGION 
BY HIGHEST CATEGORY 
BY PRODUCT PAGE-BREAK
BY DATE
WHERE DATE GE '19971001'
-* ****Page Heading****
HEADING
" Product: <PRODUCT in Category: <CATEGORY for Region: <REGION"
-* ****Page Footing****
FOOTING
" "
-* ****Report Footing****
ON TABLE SUBFOOT
" "
"END OF REPORT"
ON TABLE SET BYDISPLAY ON
ON TABLE PCHOLD FORMAT EXL07
ON TABLE SET STYLE *
INCLUDE=endeflt,TITLETEXT=EXLOVER, ROWOVERFLOW=PBON, ROWLIMIT=250,
$
ENDSTYLE
END

The report heading displays on the first Worksheet only. The page heading, footing, and column titles display on each Worksheet and at each WebFOCUS page break (each time the product changes), and the subhead and subfoot display whenever the associated sort field changes value. The following image shows the top of the first Worksheet.