Home » Developer & Programmer » Forms » Export data into Excel Template
Export data into Excel Template [message #634039] Tue, 03 March 2015 06:13 Go to next message
Sudha S Teki
Messages: 11
Registered: April 2007
Junior Member
Hi Everyone,

I want to download from oracle 10g table data into excel which I have a template with headings and logo. how to do that? Please help.


Regards,

Sudha.
Re: Export data into Excel Template [message #634045 is a reply to message #634039] Tue, 03 March 2015 09:16 Go to previous messageGo to next message
Michel Cadot
Messages: 67306
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you post some test case (create table and insert statements) and an example of the template (is it in a file? in a table?) and the result we should get.

Re: Export data into Excel Template [message #634051 is a reply to message #634045] Tue, 03 March 2015 09:50 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3171
Registered: May 2013
Location: World Wide on the Web
Senior Member
@OP,

Did you even try to search for an answer before asking this FAQ? How difficult is it with SQL Developer tool? If not, did you search/try with SPOOL in SQL*Plus?

Is it a homework question? Anyway, irrespective of that, you need to show your attempt.
Re: Export data into Excel Template [message #634053 is a reply to message #634051] Tue, 03 March 2015 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 67306
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Hmm, are you sure SQL Developer can generate an "Excel" file using a custom template and logo?

Re: Export data into Excel Template [message #634075 is a reply to message #634053] Tue, 03 March 2015 23:34 Go to previous messageGo to next message
Sudha S Teki
Messages: 11
Registered: April 2007
Junior Member
I searched a lot before posting in the forum. I have a query screen which I am showing the result based on the user selected options. This data I want to download in EXCEL template. This I use to do in forms 6i without any error. Because of three tier now I am facing problem. I kept my excel file in the Application server which user can access and I have given read and write access to all users.

I am given message in between download and its executing the download, but excel file is not opening.

DECLARE
	R NUMBER;
	V_WO_FOR 		VARCHAR2(50);
	V_WO_STATUS VARCHAR2(50);
	V_CUST_CODE VARCHAR2(10);
	V_CUST_NM		VARCHAR2(75);
	V_PATH 			VARCHAR2(50);
	WORKBOOKS   OLE2.OBJ_TYPE;
	WORKBOOK    OLE2.OBJ_TYPE;
	WORKSHEETS  OLE2.OBJ_TYPE;
	WORKSHEET   OLE2.OBJ_TYPE;
	APPLICATION OLE2.OBJ_TYPE;
	
BEGIN

  MESSAGE('DOWNLOADING....', NO_ACKNOWLEDGE);
---  MESSAGE('TESTING',ACKNOWLEDGE);
---  MESSAGE('TESTING',ACKNOWLEDGE);
	ES.OPEN_SHEET(ES.FILE_NAME,'WI_REG','True');  
  SET_APPLICATION_PROPERTY(CURSOR_STYLE,'BUSY');
  OLE2.SET_PROPERTY(ES.APPLICATION,'WINDOWSTATE',-4140);
  R:=9;
  
  GO_BLOCK('PROJ_PO');
  FIRST_RECORD;

	FOR I IN 1..:PROJ_PO.PROJ_PO_CNT LOOP
		ES.PUT_CHAR(R,'A',:PROJ_PO.WO_NO);
		ES.PUT_CHAR(R,'B',:PROJ_PO.WO_DT);
		ES.PUT_CHAR(R,'C',:PROJ_PO.WO_TYPE);
		ES.PUT_CHAR(R,'D',:PROJ_PO.WO_SUB_TYP);

---		  MESSAGE('TESTING WRITING',ACKNOWLEDGE);
---  		MESSAGE('TESTING WRITING',ACKNOWLEDGE);
		
		NEXT_RECORD;
		R := R + 1;
	END LOOP;
  OLE2.SET_PROPERTY(ES.APPLICATION,'WINDOWSTATE',-4143);
  SET_APPLICATION_PROPERTY(CURSOR_STYLE,'DEFAULT');
  MESSAGE('DOWNLOADED.....',NO_ACKNOWLEDGE);
  ES.CLOSE_APPS;  

 	WEB.SHOW_DOCUMENT('[url]http://ipaddress/folder/WI_QUERY_REG.XLS[/url]', '_BLANK');
--- 	WEB.SHOW_DOCUMENT(:KEY_BLK.CTL_PATH||'WI_QUERY_REG.XLS','_blank');
END;


I am attaching here my Procedure to open EXCEL file.


Thanks in advance

Regards,

Sudha.


Lalit : Added code tags
  • Attachment: ES Proc.txt
    (Size: 6.15KB, Downloaded 793 times)

[Updated on: Tue, 03 March 2015 23:54] by Moderator

Report message to a moderator

Re: Export data into Excel Template [message #634161 is a reply to message #634075] Thu, 05 March 2015 01:15 Go to previous messageGo to next message
Sudha S Teki
Messages: 11
Registered: April 2007
Junior Member
Hellooooo,

Please help.............

Regards,

Sudha.
Re: Export data into Excel Template [message #634196 is a reply to message #634161] Thu, 05 March 2015 09:35 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
Sounds like you are trying to do this from the Oracle Forms product and based on your code sample, it looks like code you would write in Forms. Is this correct? If so, you have posted your question in the wrong forum. Smile

All versions (9i and Higher) of Oracle are Web Deployed only. For any OLE type of operations you performed in Client/Server Forms you will have to convert to using WebUtil. The WebUtil library lets you perform operations on the Client like OLE. Take a look at the following Oracle Forms help topics:
1. Introduction to WebUtil
2. Using Webutil in your applications
3. WebUtil User's Guide
4. Configuring WebUtil
5. Runtime Setup Checklist.

If you are not using Oracle Forms, then you'll need provide a better explanation...

Craig...
Re: Export data into Excel Template [message #634339 is a reply to message #634196] Sun, 08 March 2015 03:12 Go to previous messageGo to next message
Sudha S Teki
Messages: 11
Registered: April 2007
Junior Member
Yes Craig,

I'm using Oracle Forms 10g. We configured WebUtil. With this can we open excel template from application server and download the data?

Regards,

Sudha.
Re: Export data into Excel Template [message #634388 is a reply to message #634339] Mon, 09 March 2015 06:45 Go to previous messageGo to next message
Sudha S Teki
Messages: 11
Registered: April 2007
Junior Member
Hello all,

Can I download the data in a excel template or not from oracle forms 10g?

Regards,

Sudha.
Re: Export data into Excel Template [message #634596 is a reply to message #634388] Wed, 11 March 2015 13:17 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
Quote:
With this can we open excel template from application server and download the data?

Yes, but you would have to transfer the Excel Template file to the client computer first and then open it with WebUtil. One of the features of WebUtil allows you to perform OLE operations on the Client computer. You can still use the standard OLE built-in with Oracle Forms, but all OLE activities will occur on the Application Server layer.
Quote:
Can I download the data in a excel template or not from oracle forms 10g?

Yes, you can take data displayed in a Form and output it to an Excel file on the client computer using WebUtil. How many rows of data are you expecting to output? If it is a couple of hundred rows - WebUtil will be OK. If it is a couple of Thousand or greater - then you are better off exporting the data to a file on the Application Server or on the Database Server and then transfer the file to the client using WebUtil.

There are numerous examples of how to use WebUtil in this forum, the OTN Forum, as well as on the Internet.

Craig...

[Updated on: Wed, 11 March 2015 13:18]

Report message to a moderator

Re: Export data into Excel Template [message #634608 is a reply to message #634596] Thu, 12 March 2015 01:07 Go to previous messageGo to next message
Sudha S Teki
Messages: 11
Registered: April 2007
Junior Member
Hi Craig,

Thank you for the reply, Excel file I want to open from Application Server, because multiple users will be using this. I tried searching in forum as well as with google too. But I couldn't find any example with pre-template, can you post the code here?

Thanks in advance,

Sudha.
Re: Export data into Excel Template [message #634643 is a reply to message #634608] Thu, 12 March 2015 08:57 Go to previous message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
Sudha,
I've not done this with an Excel Template file, however, you should be able to take "opening an Excel sheet" and "how to write to Excel" examples from the Internet and modify the code to suit your needs. Once you have the Excel template open you would do a "Save As" instead of a "Save". If you have special formatting in your Excel template, you're just going to have to play around with the export of data to put the data in the cells you want the data in.

Craig...
Previous Topic: Execution after process
Next Topic: Primary Key Item Behavior
Goto Forum:
  


Current Time: Sat Aug 15 10:02:25 CDT 2020