Export data into Excel Template [message #634039] |
Tue, 03 March 2015 06:13 |
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 #634051 is a reply to message #634045] |
Tue, 03 March 2015 09:50 |
Lalit Kumar B
Messages: 3174 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 #634075 is a reply to message #634053] |
Tue, 03 March 2015 23:34 |
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 2078 times)
[Updated on: Tue, 03 March 2015 23:54] by Moderator Report message to a moderator
|
|
|
|
|
|
|
Re: Export data into Excel Template [message #634596 is a reply to message #634388] |
Wed, 11 March 2015 13:17 |
|
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 |
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 |
|
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...
|
|
|