Oracle Reports 10g Release 2 (10.1.2) introduces a spreadsheet output format, which enables you to generate output with rich formatting from paper layout reports to Microsoft Excel-compatible HTML format that can be directly opened with Microsoft Excel 2000 or higher.
Using the new DESFORMAT
=
SPREADSHEET
,
you can:
Generate report output to spreadsheet format from existing paper layout
reports saved in any format (.rdf
, .xml
, .jsp
),
using rwrun
or Reports Server clients (rwcgi
,
rwclient
, rwservlet
). Example.
Preserve the rich layout formatting such as colors, fonts, conditional formatting, graphs, and images.
Note: DESFORMAT=SPREADSHEET
behavior is the same
as the functionality of the rw:include
JSP tag. Spreadsheet output is not paginated; no page setup information
is written in the output, and it is formatted as a single worksheet. Spreadsheet
output is driven by the layout; the report output displays objects that are
in the body area of each section (Header, Main, and
Trailer) of the paper layout, and does not format any content in the margin,
header, or footer areas.
To change the name of the output worksheet, set the report's Title property.
Only bitmapped reports can be generated to spreadsheet output; you can not generate a character-mode report to spreadsheet output.
The following paper layout objects are supported in spreadsheet output:
text (boilerplate and field)
images (any format), including images that are imported, linked, or stored in the database
imported drawings, as well as CGM and OWF drawings
graphic lines
The following paper layout objects are not supported in spreadsheet output: graphic arc, polygon, rectangle, rounded rectangle, stretchable line, underlined text, and OLE external object (for reports developed prior to Oracle9i Reports (9.0.2) ). Space for these drawn objects is reserved, but there is no visible representation in the output. This limitation does not apply to horizontal lines.
The following font attributes are supported in spreadsheet output: size, face, color, style (italic, oblique, underline, overstrike, outline, shadow), weight (bold, normal, extra bold, extra bold, ultra bold).
The following Web report properties are supported in spreadsheet output:
Additional Attributes (HTML) property (for Parameter Form fields)
Table Attributes (HTML) property (for frames and repeating frames)
Alternative Text property (for images)
Contains HTML Tags property (for boilerplate text or field objects that include HTML tags)
Document taxonomy (classification) for spreadsheet output is provided by the report properties Title, Author, Subject, and Keywords, which assist in cataloging and searching a report document.
Graphs embedded in spreadsheet output are static image files, and are not interactive. Thus, the Graph Hyperlink property is not supported in spreadsheet output.
For reports that support globalization, the metadata for direction
and charset
must be provided in a before
report escape, because this information is not written to the generated
output by default. For example, you can define
a before report escape as follows:
<html>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=&encoding">
<body dir=&Direction bgcolor="#ffffff">
The frames and repeating frames defined in the paper layout are translated
to tables in the output. You can specify table attributes such as cellspacing
,
cellpadding
, and width
using the Table
Attributes (HTML) property for frames and repeating frames.
For reliable formatting of spreadsheet output, the whole layout area should be enclosed in a frame. This prevents the possibility of parallel objects displaying in different vertical positions, one below the other.
The order of the report sections (Header, Main, and Trailer) is preserved in spreadsheet output; that is, the Header section output appears first, followed by the Main section, followed by the Trailer section.
If boilerplate objects or fields are not contiguous to each other; that is, if there is a gap between their boundaries, this might result in empty cells in the spreadsheet output.
If your report defines conditions that may change cell positions when the report is formatted, these changes are not reflected in spreadsheet output format. For example, if a report defines a condition that causes column 2 to display at the position of column 1 when column 1 is empty, spreadsheet output will always display both columns, even when the condition is met. Oracle Reports generates the HTML table that forms the spreadsheet immediately after the report is compiled, not during formatting. During formatting of spreadsheet output, the table cells are filled with values.
The Oracle Reports color palette is used while designing the report. When you generate your report spreadsheet output, Microsoft Excel will show the closest matching color from its color palette.
If you rotate a boilerplate object in the paper layout, the object will appear horizontal in the spreadsheet output.
The following cell properties are not supported in spreadsheet output: background pattern, border color, and border pattern.
Images included in the paper layout of the report will appear in the spreadsheet output only if the Reports Server is running in non-secure mode. In the case of a secure Reports Server, images will not appear in the spreadsheet output. This is because generating images in the output involves multiple calls to the Reports Server (one call per image). Once the user is authenticated, Oracle Reports passes the user's identity between the browser and the secure Reports Server using cookies. However, Excel does not support cookies. As a result, the call to the secure Reports Server seems like a call from an non-authenticated user. Thus, the Reports Server refuses to pass on the images. As a workaround, you can generate the spreadsheet output from a secure Reports Server to a URL using WebDAV.
Summary and formula columns will be shown as values, not as dynamic Excel formulas. Also, the page-level summary columns are reduced to report-level summary columns, since there is no page concept in Excel.
If you set the Vertical Elasticity property of a frame to Fixed, the output in Excel will show only as many records as could appear on the first page of the paper output. Since Excel does not have a page concept, it is not able to "overflow" the remaining rows to the next "page".
Since spreadsheet output is not paginated, only the before report escape and after report escape are supported.
In an after report escape,
adding an HTML command outside the body does not reflect when the report
is generated to spreadsheet output, even though the HTML command is written
in output properly. For example:
</body><table><tr><td><B> the
text to be bolded </B></td><tr></table> </html>
Adding the HTML command before the closing body tag reflects correctly
when the report is generated to spreadsheet output. For example:
<table><tr><td><B> the text to be bolded </B></td><tr></table></body></html>
Spreadsheet format is supported in rwservlet
or rwcgi
commands such as SHOWJOBS
.
It is also supported in Enterprise Manager's job queue, and in OracleAS
Portal's access object definition.
It is not possible to generate spreadsheet output directly from Reports
Builder. Instead, on the command line, you can run the report using rwrun
or Reports Server clients (rwservlet
, rwclient
,
rwcgi
), with DESFORMAT
=SPREADSHEET
.
You cannot store DESFORMAT
=SPREADSHEET
as a system parameter value in the report definition (.rdf
file).
To generate the paper layout of your report to Microsoft Excel output through
a URL (using rwservlet
), you can type a URL that includes the following
in your browser:
http://hostname:port/reports/rwservlet?report=report_name
+server=server_name+userid=db_connect_string+destype=file
+desformat=spreadsheet+desname=C:\temp\myexcel_output.htm
Note: Microsoft Office 2000/2002/2003 supports Hypertext Markup Language (HTML) as a native file format. Spreadsheet output relies on producing HTML output, which can be understood by Excel as a file with valid Excel format.
In addition to the new spreadsheet output functionality, you can also generate spreadsheet output as implemented in releases prior to Oracle Reports 10g Release 2 (10.1.2), in the following ways:
Generate a report to delimited output, as described in Generating delimited output.
Advantage: Delimited output can be generated from existing
paper layout reports saved in any format (.rdf
, .xml
,
.jsp
), either directly from Reports Builder, or by running
the report from the command line.
Disadvantage: Only data (as defined by the report Data Model), no layout information, displays in the output.
Deploy a report with the Reports Server under OC4J to display it in Microsoft Excel inside your Web browser, using either of the following methods:
When you do not have an existing paper layout: open an Excel
HTML template file in Reports Builder, add the data, save the report
as a JSP file, then deploy the report. This method enables you to use
JSP coding to include dynamic Excel formulas (not just static values
of Reports summary and formula columns), dynamic graphs (not just static
Reports graph images). For the steps to implement this method, see Getting
Started with Oracle Reports, available on the Oracle
Technology Network Oracle Reports 10g page
(http://www.oracle.com/technology/products/reports/index.html
).
Click Getting Started with Oracle Reports. In the list of topic
sections on the left, click Index. In the Collateral Type
list, choose Demonstrations, and click Search. In the
list that displays, click Generating Excel Output with Oracle Reports.
When you do have an existing paper layout: starting with a paper
layout report, edit the Web Source view to add a rw:include
tag to include the group frame of your paper layout in the Web source
view as JSP code, save the report as a JSP file, then deploy the report.
For the steps to implement this method, see the chapter "Deploying
a Web Layout Report to Microsoft Excel Output" in Oracle Reports
Building Reports, available on the Oracle
Technology Network Oracle Reports Documentation page (http://www.oracle.com/technology/documentation/reports.html
).
Advantage: You can use rich formatting such as colors and fonts so that you generate report layout, not just data, in Microsoft Excel output inside a Web browser.
Disadvantages: You must add custom JSP coding to get the
output in Excel. The report can only be run through the JSP engine and not
through rwservlet
, thus this method can only display the Microsoft
Excel output inside a Web browser.
For choosing the appropriate delimited output solution for given requirements, see "Displaying Report Output in Microsoft Excel" in Appendix D, "Troubleshooting OracleAS Reports Services" in the Oracle Application Services Publishing Reports to the Web manual.
Copyright © 1984, 2005, Oracle. All rights reserved.