Home » Developer & Programmer » Forms » Saving to an Excel file using forms
Saving to an Excel file using forms [message #299770] Wed, 13 February 2008 02:49 Go to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Hi everybody

In the procedure given below I want to save the empcode,empname
into an excel file through a form. In the procedure below I am getting
the empcode and the empname in one cell. Can anyone tell be how to put
them in seperate cells. Empcode is of size 8 and empname is of size
25.

Thank you in advance.


BEGIN
DECLARE
xlfile text_io.file_type;
BEGIN
xlfile := text_io.fopen ('e:\afempmst.xls', 'w');

FOR c_table_data IN (SELECT empcode, empname
FROM afpfmstr)

LOOP
text_io.put (xlfile, NVL(c_table_data.empcode,'NULL'));
text_io.put (xlfile, ' ');
text_io.put (xlfile, NVL(c_table_data.empname,'NULL'));
text_io.put_line (xlfile, ' ');

END LOOP;
-- text_io.put_line (xlfile,'/');
text_io.fclose (xlfile);
END;
HOST ('e:\msoffice\office\EXCEL.exe ' || 'e:\afempmst.xls' );
end;
Re: Saving to an Excel file using forms [message #299801 is a reply to message #299770] Wed, 13 February 2008 04:23 Go to previous messageGo to next message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
hi,

try the following code:

BEGIN
   DECLARE
      xlfile   text_io.file_type;
   BEGIN
      xlfile := text_io.fopen ('e:\afempmst.csv', 'w');

      FOR c_table_data IN (SELECT empcode, empname
                             FROM afpfmstr)
      LOOP
         text_io.put (xlfile, NVL (c_table_data.empcode, 'NULL'));
         text_io.put (xlfile, ',');
         text_io.put (xlfile, NVL (c_table_data.empname, 'NULL'));
         text_io.put_line (xlfile, ',');
      END LOOP;

      text_io.fclose (xlfile);
   END;

   HOST ('e:\msoffice\office\EXCEL.exe ' || 'e:\afempmst.csv');
END;

Minto
Re: Saving to an Excel file using forms [message #299968 is a reply to message #299770] Wed, 13 February 2008 21:41 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Thank you Mr.Minto. I tried your code and it works well, but the empcode is a character with leading zeros which is not being displayed. Can you please tell me how to include the leading zeros.

Thanks once again.

Alister.
Re: Saving to an Excel file using forms [message #299973 is a reply to message #299968] Wed, 13 February 2008 22:11 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
No, you mean that WE think the 'empcode' is character with leading zeroes, but it is really a set of numbers that we think of as being characters.

Put a single quote infront of the 'empcode' so that Excel THINKS that it is composed of characters, not numbers.

Also, to facilitate development open the 'cvs' file using a flat file editor and not the spreadsheet processor to see EXACTLY what is being produced.

David

Upd: Change 'double quote' to 'single quote'.

[Updated on: Wed, 13 February 2008 22:13]

Report message to a moderator

Re: Saving to an Excel file using forms [message #299987 is a reply to message #299968] Wed, 13 February 2008 23:11 Go to previous messageGo to next message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
hi,

try this code:

BEGIN
   DECLARE
      xlfile   text_io.file_type;
   BEGIN
      xlfile := text_io.fopen ('e:\afempmst.csv', 'w');

      FOR c_table_data IN (SELECT empcode, empname
                             FROM afpfmstr)
      LOOP
         text_io.put (xlfile, '''' || NVL (c_table_data.empcode, 'NULL'));
         text_io.put (xlfile, ',');
         text_io.put (xlfile, NVL (c_table_data.empname, 'NULL'));
         text_io.put_line (xlfile, ',');
      END LOOP;

      text_io.fclose (xlfile);
   END;

   HOST ('e:\msoffice\office\EXCEL.exe ' || 'e:\afempmst.csv');
END;

Minto
Re: Saving to an Excel file using forms [message #300039 is a reply to message #299770] Thu, 14 February 2008 01:30 Go to previous message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Hi

An example of empcode is '02506348', so when converting to excel the result was '2506348'. But now by adding the quotes before empcode as given by Mr.Minto the problem is solved. Thanks a lot the both of you.


Alister
Previous Topic: How to sustain a parameter between two instances in formss
Next Topic: Date Function that returns First Day of the Current month
Goto Forum:
  


Current Time: Fri Dec 02 22:34:34 CST 2016

Total time taken to generate the page: 0.10216 seconds