Tuesday, May 26, 2015
BI Publisher API Example – Creating EXCEL Report from XML/XSL-FO/RTF in Oracle Apps?
Oracle has shared us a set of BI Publisher Java Classes for performing various BI Publisher function like converting RTF to XSL-FO, generating PDF output from XSL-FO and XML etc.
In this post, we will generate an EXCEL report output from XML file and RTF Template using the standard Oracle BI Publisher API’s (Java based).
STEP1: Prepare and Convert RTF template to XSL-FO file
Oracle has provided a Java Class to generate PDF output based on XSL-FO file and XML input. Hence, first step is to generate the XSL-FO file based on RTF template. Details are available in another article,
Now, we should be having a XSL-FO file now.
STEP2: Prepare the Java wrapper Class for calling Standard class FOProcessor
import java.io.InputStream;import java.io.OutputStream;import oracle.apps.xdo.template.FOProcessor;import oracle.apps.xdo.template.fo.util.FOUtility;import java.io.*;import oracle.apps.xdo.common.pdf.util.PDFDocMerger;import oracle.apps.xdo.batch.DocumentProcessor;public class convertxmltoexcel {public static void main(String[] args){try{FOProcessor processor = new FOProcessor();processor.setData(args[0]); //input XMLprocessor.setTemplate(args[1]); //input XSL templateprocessor.setOutput(args[2]); // output Excel File// Set output format (for EXCEL generation)processor.setOutputFormat(FOProcessor.FORMAT_EXCEL);// Start processingprocessor.generate();}catch (Exception e){e.printStackTrace();System.exit(1);}System.exit(0);}}
STEP3: Compile the Java wrapper
javac -classpath $CLASSPATH convertxmltoexcel.java
Step 4: Copy the Wrapper Class to JAVA_TOP
cp convertxmltoexcel.class $JAVA_TOP
STEP5: Verify the Sample RTF template, XSL-FO file and XML File
Input XML:
![clip_image001[4]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzifpJDNAPyG6Y_vyvCYPrJiWT-I9h_XAMzLxzbb2KqmPFLopvb1TrvGH7HxqbSO24EF-Y76QoL5erjYsj2rBtOKDMq0f94owDINICZ8QvREtvEJdwKeRtIG92kNWkF6yG3RIlEbvi8MUV/s1600-h/clip_image001%25255B4%25255D%25255B2%25255D.png "clip_image001[4]")
Actual RTF:
![clip_image003[4]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpPz_r0EgjKNc4NQAl_9l4WA2w7ENPOfhHGBnChcucrYGrHBv_xUJ62W1WSszNDfi0rYs03GsivVT9KI9KG6rGTvILs1uXgNcYm82sOzr-Nz0voaE7dqNZ8gGBGE0DlTWi7LjwO51nMizD/s1600-h/clip_image003%25255B4%25255D%25255B2%25255D.jpg "clip_image003[4]")
Generated XSL-FO based on Step 1:
![clip_image005[4]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmVkkRUsskdH2qeYZq0ceKajarkgXBGhgCS5rEGWGH0GwHacV7nlqJpbByou7N2Hnp4OGkQoZD5WeL73J63J0hWSaLJxFzt-ZZysp9_ipI5meqIQ55kHmMGjnwIZZ_esF9YEiHzzmK2ayc/s1600-h/clip_image005%25255B4%25255D%25255B2%25255D.jpg "clip_image005[4]")
Step6: Place the XSL-FO and Input XML file in Desired Directory and Call the Wrapper Java Class
java -classpath $CLASSPATH convertxmltoexcel ./INPUTDATAXML.xml ./RTFTEMPLATEEX.xsl ./RTFTEMPLATEEX.xls
![clip_image007[4]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-3eLIuLRh9Wo_gsz__OVotbR-wjH_zS2xDqqRIhTSvimUx6f1xCLXlNAjMUhCXmuR2ekDCnI6cGu57Y9QmdNqR4XLENSdD67hnEYV2upUIz84yA9nbSDetFbVKvnwyOyhyphenhyphenjnMZAoBldXX/s1600-h/clip_image007%25255B4%25255D%25255B2%25255D.jpg "clip_image007[4]")
Output Excel Report:
![clip_image008[4]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhGxSyIidR_hHccvdMt31guwdXTQErzxjMfK5QmUQFHjeJjEe2SugiBts-Vu68A8KNFGzc6dpXV5oO89f6OQQ6ZBpZrz8EvGgFWAYCTBvDldSIzt5Len5LSE0lYiLdN7uyFRLCJ9oMdNStV/s1600-h/clip_image008%25255B4%25255D%25255B2%25255D.png "clip_image008[4]")
Now the Command given in the step6 can be called from HOST based concurrent program in Oracle Apps.
Happy Shairng !! Enjoy Learning!!
Happy Shairng !! Enjoy Learning!!
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
1 Responses to “BI Publisher API Example – Creating EXCEL Report from XML/XSL-FO/RTF in Oracle Apps?”
November 15, 2019 at 11:17 AM
even if I pass format as FOProcessor.FORMAT_EXCEL, file contains data in HTML format. Any suggestions.
Post a Comment