OraFAQ Forum: Forms » forms to excel data transfer

Home » Developer & Programmer » Forms » forms to excel data transfer (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

forms to excel data transfer [message #450725]

Thu, 08 April 2010 23:43

shaz
Messages: 182
Registered: June 2009

Senior Member

Hello Everyone,

I found another way to transfer the data from forms to excel apart from doing it through ole2 or client. So thought of sharing it with all...

Here it goes:-

declare
ifile client_text_io.file_type;
temp varchar2(1000); 
   Cursor c1 is select ename, job from emp;
   
BEGIN
 ifile := client_text_io.fopen(ltrim(rtrim('C:\test.xls')),'w');
 
 -- Header creation
 client_text_io.put_line(ifile,'Employee Num'||chr(9)||'Job');

 for i in c1 loop
 	temp:=i.ename||chr(9)|| i.job;
  client_text_io.put_line(ifile,temp);
 end loop;
   client_text_io.FCLOSE(IFILE);
End;

Attach webutil to form and just paste the code on a button and log on to scott. The excel file will be created in c: drive with name test.
Smile

Report message to a moderator

Re: forms to excel data transfer [message #450729 is a reply to message #450725]

Fri, 09 April 2010 00:08

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

The excel file will be created in c: drive with name test.

No, it will not.

File name will be TEST.XLS, but extension is all that it shares with Excel. The result within the file will be TAB separated values. True, Excel is capable of opening it, but this is not an Excel file.

[Updated on: Fri, 09 April 2010 00:08]

Report message to a moderator

Re: forms to excel data transfer [message #450733 is a reply to message #450729]

Fri, 09 April 2010 00:25

shaz
Messages: 182
Registered: June 2009

Senior Member

Hi Littlefoot,
Glad to hear from you.

Quote:

this is not an Excel file.

How can you say that? If its not excel file then which file is it? Please justify.

I had to create a file in excel, which is used by other team in there program created not in oracle. They discard the file provided in txt or csv file format.

Earlier I had created the xls using client_ole and it was working fine. A week back, the requirement got changed and i had to do some changes. I changed the logic and created the file using the procedure stated above. It is accepted by them and is running fine.

[Updated on: Fri, 09 April 2010 00:34]

Report message to a moderator

Re: forms to excel data transfer [message #450735 is a reply to message #450733]

Fri, 09 April 2010 00:45

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I didn't say that it won't work.

I said that TAB delimited file is not an Excel file.

Here is an example:
- orafaq.xls is a genuine Excel file
- orafaq_tab.xls is a TAB delimited file (result of your code)

This is how they look like in Excel 2003 (the same, as expected):

/forum/fa/7679/0/

This is how they "really" look like (different, as expected):

/forum/fa/7678/0/

Excel file is/was a binary file. Wikipedia:

Wikipedia

Microsoft Excel up until 2007 version used a proprietary binary file format called Binary Interchange File Format (BIFF) as its primary format. Excel 2007 uses Office Open XML as its primary file format, an XML-based format that followed after a previous XML-based format called "XML Spreadsheet" ("XMLSS"), first introduced in Excel 2002.

Once again: your TAB delimited file is not a genuine Excel file.

Attachment: excel_tab_2.PNG
(Size: 16.58KB, Downloaded 1968 times)
Attachment: excel_tab_1.PNG
(Size: 15.13KB, Downloaded 1900 times)

Report message to a moderator

Re: forms to excel data transfer [message #450745 is a reply to message #450735]

Fri, 09 April 2010 01:27

shaz
Messages: 182
Registered: June 2009

Senior Member

OK. Thanks for the justification.

But I feel that this method is very simple and easy to apply. I don't think it really matters to the client that it is a genuine excel file or not. They just want to see the data and perform some calculations. Right???
Smile

Report message to a moderator

Re: forms to excel data transfer [message #450748 is a reply to message #450745]

Fri, 09 April 2010 01:39

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Right.

Report message to a moderator

Previous Topic:	Hierarchical tree
Next Topic:	Close a window in query mode

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 10:33:04 CDT 2024