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
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 messageGo to next message
bad_sector007
Messages: 12
Registered: December 2009
Location: Egypt
Junior Member

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 #438547 is a reply to message #438408] Sun, 10 January 2010 20:38 Go to previous messageGo to next message
salora
Messages: 26
Registered: December 2009
Location: Thailand
Junior Member
Thank you so much for your suggestion Smile

Also I have one more problem
When do export data , the values of varchar2 column (of oracle) it automatic change

Exp.
the value in oracle (varchar2(100) is --> 605013113925
when do export to excel it's change to ---> 6.05013E+11

How can I solve this problem?.

Thank you in addvance. Embarassed


Re: How to export Data to excel [message #438548 is a reply to message #438547] Sun, 10 January 2010 20:46 Go to previous messageGo to next message
bad_sector007
Messages: 12
Registered: December 2009
Location: Egypt
Junior Member

You can use this code to autofit the column

--------- auto fit coulumns
range := client_ole2.GET_OBJ_PROPERTY( ExcelWorkSheetId,'UsedRange');
range_col := client_ole2.GET_OBJ_PROPERTY( range,'Columns');
client_ole2.INVOKE( range_col,'AutoFit' );
client_ole2.RELEASE_OBJ( range );
client_ole2.RELEASE_OBJ( range_col );
Re: How to export Data to excel [message #438550 is a reply to message #438548] Sun, 10 January 2010 20:56 Go to previous messageGo to next message
salora
Messages: 26
Registered: December 2009
Location: Thailand
Junior Member
Thank you verry much. bad_sector007 Cool
Re: How to export Data to excel [message #438551 is a reply to message #438408] Sun, 10 January 2010 21:06 Go to previous messageGo to next message
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 #438572 is a reply to message #438551] Mon, 11 January 2010 00:46 Go to previous messageGo to next message
bad_sector007
Messages: 12
Registered: December 2009
Location: Egypt
Junior Member

Hello
I think I found the problem. In your coding method you are using ole2 but the method I use is client_ole2.
that means you didn't install the webutil yet and that will not enable you to compile the form.
if you want to test locally on your machine try to replace every client_ole2 with ole2. that will work with you locally. but if you will run this form at the application server it will not work with the clients. only it will open files on the application server.

Finally you will need to install the webutil in your PC and the application server if you are going to use it.

Respectfully
Khaled Farouk
Re: How to export Data to excel [message #438898 is a reply to message #438312] Tue, 12 January 2010 22:27 Go to previous messageGo to next message
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 1011 times)
Re: How to export Data to excel [message #584996 is a reply to message #438898] Tue, 21 May 2013 03:19 Go to previous messageGo to next message
spadefk
Messages: 5
Registered: May 2013
Junior Member
Thanks a lot, this works 100%.
I have been searching for a solution for long.

Spadef
Re: How to export Data to excel [message #585024 is a reply to message #438898] Tue, 21 May 2013 07:51 Go to previous messageGo to next message
spadefk
Messages: 5
Registered: May 2013
Junior Member
Dear tamzidulamin,

I tried with your code, which is working fine 100%.
I have a small question.
When I run this as a part of Client Server Application, the user is asked with a question of Do you want to save changes? Yes/No.
This might be due to the insufficient rights to write to C: drive on the server.
Can u pls help me to get a way, to save the Excel file to a folder in the Client's PC.
Urgent responses are well appreciated.

Thanks a a lot again.
Re: How to export Data to excel [message #585058 is a reply to message #585024] Tue, 21 May 2013 11:07 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
Do you want to save changes? Yes/No.
This might be due to the insufficient rights to write to C: drive on the server.

I don't think so. It is raised when user made changes in a data block, wants to leave it (in any way) so Oracle asks him whether he wants to save these changes or not (if not, they will be lost). Therefore, you need to find out what has been changed and fix it.
Re: How to export Data to excel [message #585488 is a reply to message #585058] Sun, 26 May 2013 06:52 Go to previous messageGo to next message
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 #585494 is a reply to message #585488] Sun, 26 May 2013 09:36 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, I see! I thought that it was Forms that raised the message, not Excel. My bad, sorry.

Unfortunately, I have never done anything like that (importing/exporting Excel data using Forms) so I can't assist. Hopefully, someone more experienced will be able to help.
Re: How to export Data to excel [message #599894 is a reply to message #585494] Wed, 30 October 2013 01:30 Go to previous messageGo to next message
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 #627229 is a reply to message #599894] Sun, 09 November 2014 19:15 Go to previous messageGo to next message
praveenjin@gmail.com
Messages: 10
Registered: November 2014
Location: India
Junior Member
Hi All,
I used the code given by you for extracting the record from the form. This code is working fine for lesser number of records. I've around 200000 records, when I extract the records from this way, it takes around 2 hours to complete the download into excel file. This is not acceptable by client.

Please provide me any alternate solution for the same.

Thanks.
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
John Watson
Messages: 6563
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

Previous Topic: Hierarchy tree structure (merged)
Next Topic: numeric error return date
Goto Forum:
  


Current Time: Fri Dec 02 14:33:00 CST 2016

Total time taken to generate the page: 0.33138 seconds