Oracle PL/SQL Reports: Multi-sheet Excel Output Generation Utility

Introduction

Oracle provides various reporting tools and RDF, XML reports, etc. are most commonly used tools. These generate output in various formats like Text, PDF, HTML, MS-Word, Excel, etc. However, these tools have few limitations when business requests for output in excel format. Using XML reports, we can generate excel outputs, but it has the limitation that in each excel tab, content format and grouping must be same. There was such requirement where users requested for excel output with multiple sheets, but there is no supporting solution provided by Oracle with these tools.

There are various critical reports required by Accounts Department, which are to be reported to tax authorities of the respective countries, revenue generating reports, etc. These reports contain data pulled from Oracle in either PDF or Text format and then imported to excel manually. This creates problems while manipulating the data because of formatting issues. Moreover, this is overhead to users resulting in more business time.

This overhead could be reduced by providing excel reports which have outputs in excel and in multiple sheets. This data can be easily manipulated using built-in excel functionalities like formulae, macros, etc. This saves time and increases accuracy as there will be very less user intervention for getting data in excel.

Let us see how this works:

MS-Excel has built-in support for XML spreadsheets. An excel file can be converted to XML spreadsheet by saving it as “XML Spreadsheet 2003 (*. xml)” type. Similarly, these spreadsheets can be opened in excel also. Reopening the saved XML spreadsheets shows the same content of excel which was there in excel file.

For example, below is an excel file with some data and it has many sheets having different contents.

When this file is saved as XML spreadsheet and open in text viewers like notepad or edit-plus it shows XML tags as:

This means when excel file is saved as XML spreadsheet; it stores it in the form of XML tags.

This excel reporting utility uses the same principle. The data which need to feed in excel sheet is written as to get output in XML tags which are used in spreadsheets. There is a tag “Worksheet” which holds the contents of the single worksheet.

For multiple excel sheet, this tag can be manipulated and written such that this creates excel format with the number of sheets. When we open output file after completion of the report generation program, it displays the report in a single excel file with multiple sheets.

Setup Required for Excel Output

For viewing output file directly in excel, viewer options need to be set and the same file format needs to be used while defining concurrent program definition. The viewer options can be set as follows:

1.Navigate: System Administrator -> Install -> Viewer Options

A form will appear as shown below:

2.Click on File menu ->New to add new record.

3.Create new record as:

Check the checkbox under “Allow Native Client Encoding” and save the record.

4.Now after setting this viewer option change output format as “FO”.

How to open excel output?

1.After completing a concurrent request for the report, click on “View Output” button. This will show a dialog box for viewer options as:

2.Select Viewer as “FO in Microsoft Excel” and click “Ok”. This will open a window asking to open or save the file. Click on “Save” button to save the file with extension “.xls” to the local machine and open it after the download completes.

3.After opening the file, it will show an information message as

Click on “Yes”, it will show the desired output in excel.