rasti.hil@hilandco.com +41 79 367-9677

Search This Blog

Oracle BI Publisher Enterprise and Multi-sheet Excel Output

1) Create new report in Oracle BI Publisher Enterprise
Home -> My Folders -> Click on "Create a new folder" link on left side -> add a name for folder (for example: first_sample) -> Click on the "Create" button -> click on new folder which was created by you
-> Click on "Create a new report" on left side -> add a name for report (for example: multisheet_excel) -> click on "Create" button
-> click on "Edit" link

-> click on "Data Model" -> New ->
Name: first_query
Type: Sql Query
Data Source: choose you data schema
SQL Query:

select EMP.JOB as JOB,
EMP.SAL as SAL,
EMP.HIREDATE as HIREDATE,
EMP.EMPNO as EMPNO,
EMP.ENAME as ENAME,
EMP.COMM as COMM,
EMP.MGR as MGR,
EMP.DEPTNO as DEPTNO
from ZSUZSI.EMP EMP

-> Click on "Save" on left side and top of page

-> Click on "View" on right side and top of page
-> Click on "View" button
If you see the XML data then you can start multi-sheet Excel template.
2) Create multi-sheet Excel template
->Start MS Excel -> create new excel file like the following pictures:



3) Convert Excel file to XSL file
-> Save As your excel file and change your file type to XML Spreadsheet type -> use the XLS Processor Engine for Oracle program for convert the xml file to xsl file
4)Upload excel template
In Oracle BI Publisher Enterprise edit your report ->Click on "Layout" -> Manage Template Files ->Upload Template: browse your xsl file and click on Upload button -> click on "New" on top of side bar (create a new layout)->
Name: first_layout
Template: your uploaded XSL file
Template Type: XSL Stylesheet(XML)
Output Format: All Formats

-> Click on "Save"
-> Click on "View" link on right side and top of page
-> select "first_layout" and "XML" for template and click on View button, after that click on Export button and save the XML file. -> change this file's extension (XML) to XLS and open it.