| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: sql o/p to Excel
Hi
I heard, that there is a OLE2 package in Forms 6, which has capability to
create Excel file from blocks on forms.
Below is an example that one guy sent me about this issue... Hope it helps - check the package in forms.
CREATE or REPLACE FORCE PROCEDURE export_transaction_to_excel IS
application ole2.obj_type;
workbooks ole2.obj_type;
workbook ole2.obj_type;
worksheets ole2.obj_type;
worksheet ole2.obj_type;
cell ole2.obj_type;
rowcounter number:=1;
local_cursor_record number:=:system.cursor_record;
old_cursor_style varchar2(100);
errors_occured boolean:=false;
ole_error exception;
pragma exception_init(ole_error, -305500);
my_alert_id alert;
alert_respnse number;
procedure place_value_in_cell(rownum_in in number,colnum_in in
number,value_in in varchar2) is
args ole2.list_type;
begin
args:=ole2.create_arglist;
ole2.add_arg(args,rownum_in);
ole2.add_arg(args,colnum_in);
--cell:=ole2.invoke_obj(worksheet,'Cells',args);
cell:=ole2.get_obj_property(worksheet,'Cells',args);
ole2.destroy_arglist(args);
ole2.set_property(cell,'Value',value_in);
ole2.release_obj(cell);
procedure savespreadsheet is
args ole2.list_type;
vDatestamp varchar2(20);
begin
vDatestamp:=to_char(sysdate,'mmddyyyyy')| |'-'| |to_char(sysdate,'hh24miss');
args:=ole2.create_arglist;
ole2.add_arg(args,'C:\SSLO\TESTFORM.XLS');
ole2.invoke(worksheet,'SaveAs',args);
ole2.destroy_arglist(args);
ole2.invoke(application,'Quit');
end savespreadsheet;
procedure open_excel_workbook is
begin
application:=ole2.create_obj('Excel.Application');
workbooks:=ole2.get_obj_property(application,'Workbooks');
workbook:=ole2.invoke_obj(workbooks,'Add');
worksheets:=ole2.get_obj_property(application,'Worksheets');
worksheet:=ole2.invoke_obj(worksheets,'Add');
end open_excel_workbook;
procedure write_column_header is
begin
place_value_in_cell(rowcounter,1,'Depart No');
place_value_in_cell(rowcounter,2,'Depart Name');
place_value_in_cell(rowcounter,3,'Loc');
rowcounter:=rowcounter+1;
procedure export_the_data is
original_receipt number;
original_date date;
original_transmital_number number;
begin
go_block('dept');
first_record;
loop
place_value_in_cell(rowcounter,1,to_char(:deptno));
place_value_in_cell(rowcounter,2,:dname);
place_value_in_cell(rowcounter,3,:loc);
exit when :system.last_record='TRUE';
next_record;
rowcounter:=rowcounter+1;
end loop;
BEGIN begin
old_cursor_style:=get_application_property(cursor_style);
set_application_property(cursor_style,'BUSY');
open_excel_workbook;
write_column_header;
export_the_data;
exception
when form_trigger_failure then
raise;
when ole_error then
message('error sending data to excel');
message(' ');
errors_occured:=true;
savespreadsheet;
ole2.release_obj(worksheet);
ole2.release_obj(worksheets);
ole2.release_obj(workbook);
ole2.release_obj(workbooks);
ole2.release_obj(application);
if not errors_occured then
go_record(local_cursor_record);
end if;
set_application_property(cursor_style,old_cursor_style);
END export_transaction_to_excel;
Rok
>>>-----Original Message-----
>>>From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of
>>>pierre.olaru_at_citicorp.com
>>>Sent: 22. junij 2001 10:46
>>>To: Multiple recipients of list ORACLE-L
>>>Subject: RE: sql o/p to Excel
>>>Importance: Low
>>>
>>>
>>>Hi Ravindra,
>>>
>>>Bein in the same situation like you a few months ago I manage to
>>>figure out 2
>>>solutions at your problem:
>>>
>>>1. There is a nice Excel add-in called SecondWind which is able
>>>to perform
>>>export operations from Oracle to Excel in a very nice manner and
>>>formatted in
>>>the way which you described below.
>>>
>>>2. You can open a data source using Oracle ODBC driver. From
>>>Excel, you can
>>>start Microsoft Query and you are able to place the results in a
>>>spreadsheet.
>>>
>>>Regards,
>>>Pierre
>>>
>>>-----Original Message-----
>>>From: ravindra [SMTP:ravindra_at_sentica.com]
>>>Sent: Friday, June 22, 2001 4:10 AM
>>>To: ORACLE-L
>>>Cc: ravindra
>>>Subject: sql o/p to Excel
>>>
>>>I have a sql script to generate a dialy report like this.
>>>
>>>01-JUN-200107 8 4
>>> 17 18 11
>>>*********** -------------
>>>MAXIMUM No. 11
>>>TOTAL 15
>>>
>>>04-JUN-200108 9 2
>>> 10 11 1
>>> 11 12 2
>>> 12 13 3
>>> 13 14 16
>>> 14 15 4
>>> 17 18 2
>>> 18 19 2
>>>*********** -------------
>>>MAXIMUM No. 16
>>>TOTAL 32
>>>
>>>I want to present this output in excel so that I can draw graphs.I am not
>>>able to open the file
>>>in excel in alligned format ie each column must appear in
>>>different columns
>>>in excel.How can I do that.
>>>Is that possible?
>>>
>>>Thanks
>>>Ravindra
>>>
>>>
>>>--
>>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>>--
>>>Author: Ravindra Basavaraja
>>> INET: ravindra_at_sentica.com
>>>
>>>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>>>San Diego, California -- Public Internet access / Mailing Lists
>>>--------------------------------------------------------------------
>>>To REMOVE yourself from this mailing list, send an E-Mail message
>>>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>>>the message BODY, include a line containing: UNSUB ORACLE-L
>>>(or the name of mailing list you want to be removed from). You may
>>>also send the HELP command for other information (like subscribing).
>>>
>>>
>>>--
>>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>>--
>>>Author:
>>> INET: pierre.olaru_at_citicorp.com
>>>
>>>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>>>San Diego, California -- Public Internet access / Mailing Lists
>>>--------------------------------------------------------------------
>>>To REMOVE yourself from this mailing list, send an E-Mail message
>>>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>>>the message BODY, include a line containing: UNSUB ORACLE-L
>>>(or the name of mailing list you want to be removed from). You may
>>>also send the HELP command for other information (like subscribing).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rok Kodrun INET: rok.kodrun_at_uni-lj.si Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Jun 22 2001 - 05:50:43 CDT
 |
 |