Home » Developer & Programmer » Forms » How to export Data to excel
How to export Data to excel [message #438312] |
Fri, 08 January 2010 01:02 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
salora
Messages: 26 Registered: December 2009 Location: Thailand
|
Junior Member |
|
|
Hi All
I do the codding procedur in oracle form code to export data to Excel.
But when i run the form and call that procedure. The data is come to excel, but it's autometic create a new sheet(as "Sheet4").
The problem is.. How can specify the Sheetname or fix it as "Sheet1"?
thanks in advance.
---Followin it's my code
PROCEDURE Export2EXCEL_FILE(P_MAXCOL_NUM NUMBER) IS
cursor dtl is select * from SG_TMP_REPORT_RESULT
where SOURCE_ID = :BLK_CTRL.NB_SOURCEID
ORDER BY COLTYPE;
row_num number;
col_num number;
cell_val number;
app_acc OLE2.OBJ_TYPE;
dbs OLE2.OBJ_TYPE;
dc OLE2.OBJ_TYPE;
args_acc OLE2.OBJ_TYPE;
app OLE2.OBJ_TYPE;
args OLE2.LIST_TYPE;
args2 OLE2.LIST_TYPE;
ws OLE2.OBJ_TYPE;
wb OLE2.OBJ_TYPE;
wss OLE2.OBJ_TYPE;
wbs OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
ctr1 NUMBER := 0;
ctr2 NUMBER := 0;
ctr3 PLS_INTEGER := 0; --NUMBER := 0;
m_row number:=1;
m_len number:=0;
m_flag number:=0;
Begin
---------------- Initialise Excel
app := OLE2.CREATE_OBJ('Excel.Application');
OLE2.SET_PROPERTY(app, 'Visible','True');
wbs := OLE2.GET_OBJ_PROPERTY(app,'Workbooks');
wb := OLE2.INVOKE_OBJ(wbs,'Add');
wss := OLE2.GET_OBJ_PROPERTY(app,'Worksheets');
ws := OLE2.INVOKE_OBJ(wss,'Add');
---
For I in 1..2 loop
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, m_row); --- Row
OLE2.ADD_ARG(args, I); --- Column
cell := OLE2.GET_OBJ_PROPERTY(ws,'Cells', args);
OLE2.DESTROY_ARGLIST(args);
---- if I=1 then
---- OLE2.SET_PROPERTY(cell, 'Value','Auth. Date');
---- elsif I=2 then
---- OLE2.SET_PROPERTY(cell, 'Value','ICD Doc.No');
---- end if;
OLE2.RELEASE_OBJ(cell);
end loop;
--- Heading
-- Data
-- m_row := m_row +1; --add new row may be
For J in Dtl loop
For I in 1..P_MAXCOL_NUM loop
-- Repeat Row
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, m_row); --- Row
OLE2.ADD_ARG(args, I); --- Column
cell := OLE2.GET_OBJ_PROPERTY(ws,'Cells', args);
OLE2.DESTROY_ARGLIST(args);
if I = 1 then
OLE2.SET_PROPERTY(cell, 'Value',J.COL1);
elsif I = 2 then
OLE2.SET_PROPERTY(cell, 'Value',J.COL2);
elsif I = 3 then
OLE2.SET_PROPERTY(cell, 'Value',J.COL3);
elsif I = 4 then
OLE2.SET_PROPERTY(cell, 'Value',J.COL4);
elsif I = 5 then
|
|
|
Re: How to export Data to excel [message #438408 is a reply to message #438312] |
Fri, 08 January 2010 14:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
bad_sector007
Messages: 12 Registered: December 2009 Location: Egypt
|
Junior Member |
![bad_sector007](/forum/theme/orafaq/images/yahoo.png) ![bad_sector007](/forum/theme/orafaq/images/skype.png)
|
|
hello
I think the problem in this part
wss := OLE2.GET_OBJ_PROPERTY(app,'Worksheets');
ws := OLE2.INVOKE_OBJ(wss,'Add');
this is the code i use to open the excel sheets if you want to try some parts of it. it contains the orientation of the excel sheet - adding new sheet - color of text.
-------------
declare
-- myexcelcontent CLOB;
--l_xml_body CLOB;
arg number :=1;
lev VARCHAR2(100);--number;
old_lev VARCHAR2(100);--number;
hParagraphFormat client_ole2.OBJ_TYPE;
i_reporttitle VARCHAR2(200) := 'My excel';
COL1 VARCHAR2(2);
COL2 VARCHAR2(8);
i_orientation VARCHAR2(200) := '1';
/* definiton for commonly used variables and constants and exceptions */
ExcelID client_ole2.obj_type;
ExcelWorkBooksID client_ole2.obj_type;
ExcelWorkBookID client_ole2.obj_type;
ExcelWorkSheetsID client_ole2.obj_type;
ExcelWorkSheetID client_ole2.obj_type;
ExcelCellID client_ole2.obj_type;
SHEET client_ole2.obj_type;
ExcelFontID client_ole2.obj_type;
ExcelPageSetupID client_ole2.obj_type;
ExcelArgs client_ole2.list_type;
args1 client_ole2.LIST_TYPE;
range client_ole2.OBJ_TYPE;
range_col client_ole2.OBJ_TYPE;
-- Define th ecolors if they are to be used
colour client_ole2.obj_type;
myBlue CONSTANT number(8 ) := 16711680; --FF0000
myGreen CONSTANT number(8 ) := 65280; --00FF00
myRed CONSTANT number(8 ) := 255; --0000FF
myDkGreen CONSTANT number(8 ) := 32768; --008000
myBlack CONSTANT number(8 ) := 0; --000000
cursor is ........
begin
BEGIN
ExcelID := client_ole2.create_obj('Excel.Application');
--message(3333333333);message(333333333333);
ExcelWorkBooksID := client_ole2.get_obj_property(ExcelID, 'Workbooks');
ExcelWorkBookID := client_ole2.invoke_obj(ExcelWorkBooksID, 'Add');
ExcelWorkSheetsID := client_ole2.get_obj_property(ExcelWorkBookID, 'Worksheets');
--ExcelWorkSheetsID := client_ole2.get_obj_property(ExcelWorkBookID, 'vvvv');
--++**+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+
--start in the first sheet
ExcelArgs:=client_ole2.CREATE_ARGLIST;
client_ole2.ADD_ARG(ExcelArgs, 1);
ExcelWorkSheetID :=client_ole2.GET_OBJ_PROPERTY(ExcelWorkSheetsID,'Item',ExcelArgs);
client_ole2.DESTROY_ARGLIST(ExcelArgs);
--++**+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+
-----------------------------------------------------------------------------------
--message(22222222222);message(2222222222222);
--Add new sheet
--ExcelWorkSheetID := client_ole2.invoke_obj(ExcelWorkSheetsID, 'Add');
--ExcelPageSetupID := client_ole2.get_obj_property(ExcelWorkSheetID, 'PageSetup');
--client_ole2.set_property(ExcelPageSetupID, 'Orientation',i_orientation);
--client_ole2.release_obj(ExcelPageSetupID);
--*********************************Titles**************************************************
-- arg := 1;
ExcelArgs := client_ole2.create_arglist;
client_ole2.add_arg(ExcelArgs,2); --row postion |
client_ole2.add_arg(ExcelArgs,10); --Column postion -
ExcelCellId := client_ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);
client_ole2.destroy_arglist(ExcelArgs);
client_ole2.set_property(ExcelCellId, 'Value', 'fixed name for cell'); --value
client_ole2.SET_PROPERTY ( ExcelCellId, 'Merge', 'center' );
ExcelFontId := client_ole2.get_obj_property(ExcelCellId, 'Font');
client_ole2.set_property(ExcelFontId, 'Bold', 'True');
client_ole2.set_property(ExcelFontId, 'Color', 'Red');
client_ole2.set_property(ExcelFontId, 'Size', '14');
client_ole2.release_obj(ExcelFontId);
client_ole2.release_obj(ExcelCellId);
|
|
|
|
|
|
Re: How to export Data to excel [message #438551 is a reply to message #438408] |
Sun, 10 January 2010 21:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
salora
Messages: 26 Registered: December 2009 Location: Thailand
|
Junior Member |
|
|
Hello
when I do the complie
is has the error in this line
.
.
.
hParagraphFormat client_ole2.OBJ_TYPE;
.
.
seem like i have to attach some object,
can you plese sugges me
thanks
|
|
|
|
Re: How to export Data to excel [message #438898 is a reply to message #438312] |
Tue, 12 January 2010 22:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
tamzidulamin
Messages: 132 Registered: October 2009 Location: Dhaka
|
Senior Member |
|
|
Dear Salora,
I think your probelm will be solve by the following code:
PROCEDURE fpr_forms_to_excel(p_block_name in varchar2 default NAME_IN('system.current_block'),
p_path in varchar2 default 'C:\',
p_file_name in varchar2 default 'Temp') IS
-- Declare the OLE objects
application OLE2.OBJ_TYPE;
workbooks OLE2.OBJ_TYPE;
workbook OLE2.OBJ_TYPE;
worksheets OLE2.OBJ_TYPE;
worksheet OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
range OLE2.OBJ_TYPE;
range_col OLE2.OBJ_TYPE;
-- Declare handles to OLE argument lists
args OLE2.LIST_TYPE;
arglist OLE2.LIST_TYPE;
-- Declare form and block items
form_name VARCHAR2(100);
f_block VARCHAR2(100);
l_block VARCHAR2(100);
f_item VARCHAR2(100);
l_item VARCHAR2(100);
cur_block VARCHAR2(100):= NAME_IN('system.current_block');
cur_item VARCHAR2(100);
cur_record VARCHAR2(100);
item_name VARCHAR2(100);
baslik VARCHAR2(100);
row_n NUMBER;
col_n NUMBER;
filename VARCHAR2(1000):= p_path||p_file_name;
ExcelFontId OLE2.list_type;
BEGIN
-- Start Excel
application:=OLE2.CREATE_OBJ('Excel.Application');
OLE2.SET_PROPERTY(application, 'Visible', 'False');
-- Return object handle to the Workbooks collection
workbooks:=OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');
-- Add a new Workbook object to the Workbooks collection
workbook:=OLE2.GET_OBJ_PROPERTY(workbooks,'Add');
-- Return object handle to the Worksheets collection for the Workbook
worksheets:=OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');
-- Get the first Worksheet in the Worksheets collection
-- worksheet:=OLE2.GET_OBJ_PROPERTY(worksheets,'Add');
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 1);
worksheet:=OLE2.GET_OBJ_PROPERTY(worksheets,'Item',args);
OLE2.DESTROY_ARGLIST(args);
-- Return object handle to cell A1 on the new Worksheet
go_block(p_block_name);
baslik := get_block_property(p_block_name,FIRST_ITEM); --commented to consider the second item as the first item
f_item := p_block_name||'.'||get_block_property(p_block_name,FIRST_ITEM); --in order to skip the old filename in the excel file
l_item := p_block_name||'.'||get_block_property(p_block_name,LAST_ITEM);
first_record;
LOOP
item_name := f_item;
row_n := NAME_IN('SYSTEM.CURSOR_RECORD');
col_n := 1;
LOOP
IF get_item_property(item_name,ITEM_TYPE)<>'BUTTON' AND get_item_property(item_name,VISIBLE)='TRUE' THEN
-- Set first row with the item names
IF row_n=1 THEN
args := OLE2.create_arglist;
OLE2.add_arg(args, 1);
OLE2.add_arg(args, col_n);
cell := OLE2.get_obj_property(worksheet, 'Cells', args);
OLE2.destroy_arglist(args);
--cell_value := OLE2.get_char_property(cell, 'Value');
ExcelFontId := OLE2.get_obj_property(Cell, 'Font');
OLE2.set_property(ExcelFontId, 'Bold', 'True');
--------------------------------------------
baslik:=NVL(get_item_property(item_name,PROMPT_TEXT),baslik);
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, row_n);
OLE2.ADD_ARG(args, col_n);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, 'Value', baslik);
OLE2.RELEASE_OBJ(cell);
END IF;
-- Set other rows with the item values
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, row_n+1);
OLE2.ADD_ARG(args, col_n);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
IF get_item_property(item_name,DATATYPE)<>'NUMBER' THEN
OLE2.SET_PROPERTY(cell, 'NumberFormat', '@');
END IF;
OLE2.SET_PROPERTY(cell, 'Value', name_in(item_name));
OLE2.RELEASE_OBJ(cell);
END IF;
IF item_name = l_item THEN
exit;
END IF;
baslik := get_item_property(item_name,NEXTITEM);
item_name := p_block_name||'.'||get_item_property(item_name,NEXTITEM);
col_n := col_n + 1;
END LOOP;
EXIT WHEN NAME_IN('system.last_record') = 'TRUE';
NEXT_RECORD;
END LOOP;
-- Autofit columns
range := OLE2.GET_OBJ_PROPERTY( worksheet,'UsedRange');
range_col := OLE2.GET_OBJ_PROPERTY( range,'Columns');
OLE2.INVOKE( range_col,'AutoFit' );
OLE2.RELEASE_OBJ( range );
OLE2.RELEASE_OBJ( range_col );
-- Save as worksheet with a Specified file path & name.
IF NVL(filename,'0')<>'0' THEN
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args,filename );
OLE2.INVOKE(worksheet,'SaveAs',args );
OLE2.DESTROY_ARGLIST( args );
END IF;
-- Release the OLE objects
OLE2.RELEASE_OBJ(worksheet);
OLE2.RELEASE_OBJ(worksheets);
OLE2.RELEASE_OBJ(workbook);
OLE2.RELEASE_OBJ(workbooks);
OLE2.INVOKE (application,'Quit');
OLE2.RELEASE_OBJ(application);
-- Focus to the original location
exception
when others then null;
raise form_trigger_failure;
END;
Also Find the attachment....
Tamzidul Amin
Dhaka.
-
Attachment: XLFILE.fmb
(Size: 76.00KB, Downloaded 3243 times)
|
|
|
|
|
|
Re: How to export Data to excel [message #585488 is a reply to message #585058] |
Sun, 26 May 2013 06:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/9641157e752f88c4bb17b0455980d2c9?s=64&d=mm&r=g) |
spadefk
Messages: 5 Registered: May 2013
|
Junior Member |
|
|
Dear Littlefoot,
The message to save changes is from Excel.
The datablock is a view based one.
There are no changes done here.
Once the Excel file gets filled with data, the question is asked to Save changes.
If you notice, there is an Invoke command to exit Excel, in the code.
Since the file is not saved in the default location, C Drive, and the Exit is invoked, then it asks whether to save.
Hope my understanding is right.
I commented the Exit Invoke for Excel.
Then the file gets saved into the My documents of the user's PC.
Wanted to know how to change this default behaviour and forcibly save the file to a client folder.
This is very crucial, for users working through Remote Desktop, when the file gets saved into the My Documents folder of the user, on the Server.
Your favorable help is required.
Thanks a lot again.
|
|
|
|
Re: How to export Data to excel [message #599894 is a reply to message #585494] |
Wed, 30 October 2013 01:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/3e3845a2395673f59d64f481e8a89723?s=64&d=mm&r=g) |
nhvduy
Messages: 4 Registered: October 2013 Location: HCM City
|
Junior Member |
|
|
Dear TamZidulamin
I congigured Webutil to upload and download files sucessful
I reuse your code in XLFILE.fmb attached,because i compile on AppServer so i replaced OLE2 with CLIENT_OLE2. but when i pressed button, it does not show any message. seem when it go into procedure, it do not go out. Becase i have "error := show_alert('FINISH')" after call procedure but error does not work. It also means that :control.MESSAGE:='See Your FIle into C:\emp.xls' does not work.
When i shutdown PC, i receive message to Save book1.xlsx ? . any idea? thank you in advance!
Hello Tamzidulamin
I test per row of your code then i realize this row "client_ole2.SET_PROPERTY(cell, 'Value', name_in(item_name));" does not work. I comment it by add "--" before it then procedure finish but when i open the excel file, there is no items value but item names.
[Updated on: Wed, 30 October 2013 03:28] Report message to a moderator
|
|
|
|
Re: How to export Data to excel [message #627247 is a reply to message #627229] |
Mon, 10 November 2014 01:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read
Some advice on forum usage:
You have replied to a topic from four years ago. Better practice would be to create new topic, and refer to the older one. That will give you better visibility and therefore better chance of answers.
You need to show the work you have done already. The code, and what attempts you have made to identify and fix the performance problem.
Lastly, you are asking other people to do work for free, which you you will then be paid for. Well, OK - but perhaps your attitude does not come across well.
Better to start again, with a new topic.
Incidentally, does your client really want to load 200000 rows into a spreadsheet? Surely the whole design is silly. That many rows should be processed in the database.
[Updated on: Mon, 10 November 2014 01:54] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Mon Sep 09 08:38:58 CDT 2024
|