To Generate Oracle Report Output in Excel
Oracle Reports in csv and tsv . For the output to be displayed in Excel format.
I had to do a report to display the output with Japan Characters.
declare
demoDocument xoxo_ExcelDocumentType;
documentArray xoxo_ExcelDocumentLine := xoxo_ExcelDocumentLine();
clobDocument CLOB;
v_file UTL_FILE.FILE_TYPE;
lc_desc table.column%TYPE;
lc_subject xoxo_email.subject%TYPE;
ln_batch_id xoxo_email.batch_id%TYPE;
lc_email_address xoxo_email.to_address%TYPE;
lc_message xoxo_email.text_body%TYPE;
lc_appl_name applsys.fnd_application.application_short_name%TYPE := 'xoxo';
lc_prog_name applsys.fnd_concurrent_programs.concurrent_program_name%TYPE := 'xoxo_BULK_EMAILER_CP';
ln_request_id NUMBER;
ln_loop_counter NUMBER := 1;
lc_instance VARCHAR2(10);
BEGIN
FND_GLOBAL.APPS_INITIALIZE(USER_ID=>1872,RESP_ID=>51097,RESP_APPL_ID=>401);
BEGIN
SELECT substr(table.column,1,50)
INTO lc_desc
FROM table m
WHERE m.column = ...... ;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('My Error');
END;
demoDocument := xoxo_ExcelDocumentType();
-- Open the document
demoDocument.documentOpen;
-- Define Styles
demoDocument.stylesOpen;
-- Include Default Style
demoDocument.defaultStyle;
-- Add Custom Styles
/* Style for Column Header Row */
demoDocument.createStyle(p_style_id =>'ColumnHeader',
p_font =>'Times New Roman',
p_ffamily =>'Roman',
p_fsize =>'10',
p_bold =>'Y',
p_underline =>'Single',
p_align_horizontal=>'Center',
p_align_vertical=>'Bottom');
/* Styles for alternating row colors. */
demoDocument.createStyle(p_style_id=>'NumberStyleBlueCell',
p_cell_color=>'Cyan',
p_cell_pattern =>'Solid',
p_number_format => '###,###,###.00',
p_align_horizontal => 'Right');
demoDocument.createStyle(p_style_id=>'TextStyleBlueCell',
p_cell_color=>'Cyan',
p_cell_pattern =>'Solid');
/* Style for numbers */
demoDocument.createStyle(p_style_id => 'NumberStyle',
p_number_format => '###,###,###.00',
p_align_horizontal => 'Right');
/* Style for Column Sum */
demoDocument.createStyle(p_style_id => 'ColumnSum',
p_number_format => '###,###,###.00',
p_align_horizontal => 'Right',
p_text_color => 'Blue');
/* Style for Column Sum */
demoDocument.createStyle(p_style_id => 'RowSum',
p_number_format => '###,###,###.00',
p_align_horizontal => 'Right',
p_text_color => 'Red');
-- Close Styles
demoDocument.stylesClose;
-- Open Worksheet
demoDocument.worksheetOpen('Weekly Earnings');
-- Define Columns
demoDocument.defineColumn(p_index=>'1',p_width=>100); -- Emp Name
demoDocument.defineColumn(p_index=>'2',p_width=>16); -- Daily Dollar
demoDocument.defineColumn(p_index=>'3',p_width=>16);
demoDocument.defineColumn(p_index=>'4',p_width=>16);
demoDocument.defineColumn(p_index=>'5',p_width=>16);
demoDocument.defineColumn(p_index=>'6',p_width=>16);
demoDocument.defineColumn(p_index=>'7',p_width=>16); -- Sum column
-- Define Header Row
demoDocument.rowOpen;
--Define Header Row Data Cells
demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Employee Name');
demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Monday');
demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Tuesday');
demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Wednesday');
demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Thursday');
demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Friday');
demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Totals');
demoDocument.rowClose;
/*------------------------------------*/
/* Sheet Data would normally be */
/* data driven via cursor loops */
/* or other means. */
/* The purpose here is to demonstrate */
/* the features of the utility. */
/*------------------------------------*/
-- Row 1
demoDocument.rowOpen;
demoDocument.addCell(p_data=>'Jason Bennett');
demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'50000');
demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'25000');
demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'25000');
demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'14000');
demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'200');
demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
demoDocument.rowClose;
-- Row 2
demoDocument.rowOpen;
demoDocument.addCell(p_style=>'TextStyleBlueCell', p_data=>/*'Joe Smith'*/lc_desc);
demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'500');
demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'8000');
demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'35');
demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'1000');
demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'15');
demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
demoDocument.rowClose;
-- Row 3
demoDocument.rowOpen;
demoDocument.addCell(p_data=>'Wilma Jones');
demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'300');
demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'9000');
demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'350');
demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'2000');
demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'159');
demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
demoDocument.rowClose;
-- Row 4
demoDocument.rowOpen;
demoDocument.addCell(p_style=>'TextStyleBlueCell', p_data=>'Chris P.');
demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'45000');
demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'67000');
demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'200');
demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'650');
demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'21000');
demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
demoDocument.rowClose;
-- Summary Row 5
demoDocument.rowOpen;
demoDocument.addCell(p_col_index=>'2',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
demoDocument.addCell(p_col_index=>'3',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
demoDocument.addCell(p_col_index=>'4',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
demoDocument.addCell(p_col_index=>'5',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
demoDocument.addCell(p_col_index=>'6',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
demoDocument.addCell(p_col_index=>'7',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
demoDocument.rowClose;
-- Close the Worksheet
demoDocument.worksheetClose;
-- Open New Worksheet
demoDocument.worksheetOpen('Weekly Earnings 2');
-- Define Columns
demoDocument.defineColumn(p_index=>'1',p_width=>30); -- Emp Name
demoDocument.defineColumn(p_index=>'2',p_width=>16); -- Daily Dollar
demoDocument.defineColumn(p_index=>'3',p_width=>16);
demoDocument.defineColumn(p_index=>'4',p_width=>16);
demoDocument.defineColumn(p_index=>'5',p_width=>16);
demoDocument.defineColumn(p_index=>'6',p_width=>16);
demoDocument.defineColumn(p_index=>'7',p_width=>16); -- Sum column
-- Define Header Row
demoDocument.rowOpen;
--Define Header Row Data Cells
demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Employee Name');
demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Monday');
demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Tuesday');
demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Wednesday');
demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Thursday');
demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Friday');
demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Totals');
demoDocument.rowClose;
/*------------------------------------*/
/* Sheet Data would normally be */
/* data driven via cursor loops */
/* or other means. */
/* The purpose here is to demonstrate */
/* the features of the utility. */
/*------------------------------------*/
-- Row 1
demoDocument.rowOpen;
demoDocument.addCell(p_data=>'Jason Bennett');
demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'80000');
demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'75000');
demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'25000');
demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'94000');
demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'200');
demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
demoDocument.rowClose;
-- Row 2
demoDocument.rowOpen;
demoDocument.addCell(p_style=>'TextStyleBlueCell', p_data=>'Joe Smith');
demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'500');
demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'8000');
demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'35');
demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'1000');
demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'15');
demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
demoDocument.rowClose;
-- Row 3
demoDocument.rowOpen;
demoDocument.addCell(p_data=>'Wilma Smith');
demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'500');
demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'77000');
demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'850');
demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'9000');
demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'359');
demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
demoDocument.rowClose;
-- Row 4
demoDocument.rowOpen;
demoDocument.addCell(p_style=>'TextStyleBlueCell', p_data=>'Jeff F.');
demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'99000');
demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'67000');
demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'500');
demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'650');
demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'21000');
demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
demoDocument.rowClose;
-- Summary Row 5
demoDocument.rowOpen;
demoDocument.addCell(p_col_index=>'2',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
demoDocument.addCell(p_col_index=>'3',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
demoDocument.addCell(p_col_index=>'4',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
demoDocument.addCell(p_col_index=>'5',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
demoDocument.addCell(p_col_index=>'6',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
demoDocument.addCell(p_col_index=>'7',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
demoDocument.rowClose;
-- Close the Worksheet
demoDocument.worksheetClose;
-- Close the document.
demoDocument.documentClose;
-- Get CLOB Version
clobDocument := demoDocument.getDocument;
-- Display the document to browser.
demoDocument.displayDocument;
-- Write document to a file
-- Assuming UTL file setting are setup in your DB Instance.
--
documentArray := demoDocument.getDocumentData;
-- Use command CREATE DIRECTORY FOO as '
-- to create a directory for the file.
v_file := UTL_FILE.fopen('/usr/tmp','ExcelObjectTest.xml','W',4000);
FOR x IN 1 .. documentArray.COUNT LOOP
UTL_FILE.put_line(v_file,documentArray(x));
END LOOP;
UTL_FILE.fclose(v_file);
-- get the next batch id
SELECT xoxo.xoxo_email_batch_id_s.NEXTVAL
INTO ln_batch_id
FROM dual;
dbms_output.put_line('Insert into xoxo_email table');
lc_subject := 'Test';
lc_message := 'Message';
lc_email_address := 'email@address.com';
INSERT INTO xoxo_email
(
email_id
,batch_id
,creation_date
,created_by
,from_address
,reply_to_address
,to_address
,cc_address
,bcc_address
,subject
,html_body
,text_body
,attachments
,processed
)
VALUES
(
xoxo.xoxo_email_email_id_s.NEXTVAL -- email id
,ln_batch_id -- batch id
,SYSDATE -- creation date
,fnd_global.USER_ID -- user_id
,'from@address.com' -- from address
,NULL -- reply to address
,lc_email_address -- to address
,NULL -- 'oracle_archive@kkkk.com' -- cc address
,NULL -- bcc_address
,lc_subject -- subject
,NULL -- html body
,lc_message -- text body
,'/usr/tmp/ExcelObjectTest.xml' -- attchments
,'N' -- processed
);
COMMIT;
dbms_output.put_line('Submit Request to send email with batch id: ' || ln_batch_id);
-- submit the request
ln_request_id := fnd_request.submit_request(application => lc_appl_name
,program => lc_prog_name
,argument1 => ln_batch_id);
COMMIT;
dbms_output.put_line('Submit Request id: ' || ln_request_id);
EXCEPTION
WHEN OTHERS THEN
/* For displaying web based error.*/
htp.p(sqlerrm);
/* For displaying command line error */
dbms_output.put_line(sqlerrm);
END;
