Focal Point
[SOLVED]over 1 million excel rows

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/1987017286

June 10, 2016, 05:07 PM
jlsnyc
[SOLVED]over 1 million excel rows
Is there a way to have a Webfocus report break the returned answer set of over a 1 million excel rows into multiple tabs.

EXL07 format limit is 1 million rows,

This message has been edited. Last edited by: <Emily McAllister>,
June 10, 2016, 10:58 PM
HarryLo
See ROWLIMIT


WebFOCUS 7.6
Windows, All Outputs
June 11, 2016, 11:30 AM
George Patton
This kind of request baffles me. Why would ANYONE want a spreadsheet with over a million rows? There has to be a better way to aggregate data before exporting to a spreadsheet than this .....

curmudgeon: a bad-tempered, difficult, cantankerous person ....


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
June 13, 2016, 08:17 AM
Tim P.
quote:
Originally posted by George Patton:
This kind of request baffles me. Why would ANYONE want a spreadsheet with over a million rows? There has to be a better way to aggregate data before exporting to a spreadsheet than this .....

curmudgeon: a bad-tempered, difficult, cantankerous person ....


A lot of times it's hard to change the hearts and minds of end users that are used to doing things a certain way. I've seen this type of thing a lot and I agree there is a better way but sometimes its a difficult road to hoe.


WebFOCUS App Studio 8.2.02
Windows 7, All Outputs
June 13, 2016, 05:10 PM
Waz
To do this you will need to create a field that has a value for each tab. to do that, just create a counter and when it goes over 1 mil, change/increment it.

The use, I think, BYTOC ??


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

June 14, 2016, 08:57 AM
Francis Mariani
Look for 'Creating Reports With WebFOCUS Language > Choosing a Display Format > Using Excel Display Formats: EXL07, EXL2K, EXL97, EXCEL > Overcoming the Excel 2007 and 2010 Row Limit Using Overflow Worksheets' in the documentation.

quote:
The maximum number of rows supported by Excel 2007 and 2010 on a Worksheet is 1,048,576 (1MB). When you create an 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 EXL07 output, the WebFOCUS page heading and page footing commands generate only Worksheet headings and Worksheet footings.

Syntax: How to Enable Overflow Worksheets
Add the ROWOVERFLOW attribute to your WebFOCUS StyleSheet

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


This doesn't work for EXL2K...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 14, 2016, 09:37 AM
Doug
quote:
This doesn't work for EXL2K...

Does it work for XLSX, in WebFOCUS 8?


   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Most recent: 8204 Gen 48 in Test and Production.
June 14, 2016, 09:46 AM
Francis Mariani
quote:
XLSX
I *think* that's equivalent to EXL07.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 20, 2016, 02:01 PM
Kevin W
In WebFocus 8.1.05 EXL07 and XLSX are exact synonyms. I also think the exact match appeared around 8.0.09, but it may be true for all of 8.


WebFOCUS 7.7.05 (Someday 8)
Windows 7, All Outputs
In Focus since 1983.
June 20, 2016, 03:02 PM
Squatch
quote:
Originally posted by George Patton:
Why would ANYONE want a spreadsheet with over a million rows?

Because Microsoft Excel is the best word processor I've ever used, and it would be nice to format more than 1 million rows of data for presentation.

With Excel, I can add in hyperlinks, clip art, charts, images and backgrounds. I can also control the page layout and add in headers, footers and page numbers.

Name me one other word processor that can do all that AND let me manipulate my 1 million rows of data as if I was working with an actual spreadsheet.


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
June 21, 2016, 12:42 PM
Kevin W
That must create a rather large printed document for one person to read (or even view on a sceen). But one of the beauties of WebFocus is that if this is what you want you can have it. In this case it takes a little extra effort. Big Grin


WebFOCUS 7.7.05 (Someday 8)
Windows 7, All Outputs
In Focus since 1983.