Format Excel Using PLSQL [message #437476] |
Mon, 04 January 2010 11:26  |
syed.nayeem316
Messages: 21 Registered: December 2009 Location: US
|
Junior Member |

|
|
I can upload data into excel sheet using UTL_FILE. But can anyone explain how do I change column width in excel report.
Thanks
Nayeem
|
|
|
|
Re: Format Excel Using PLSQL [message #437479 is a reply to message #437478] |
Mon, 04 January 2010 11:57   |
syed.nayeem316
Messages: 21 Registered: December 2009 Location: US
|
Junior Member |

|
|
This is an Oracle Question. I am using UTL_FILE to upload data on to the excel report.
Please see the below script. By default excel column has certail width. I need the change the width according to the requirement.
Let me know if you have further concerns.
Thanks
Nayeem
CREATE OR REPLACE procedure print_reports is
cursor c_mgr is
select
t1.ename,
t1.empno
from
emp t1
where exists
(select
'x'
from
emp t2
where
t1.empno=t2.mgr);
cursor c_direct_reports (cv_mgr number) is
select
empno,
ename,
job,
hiredate,
sal
from
emp
where
mgr=cv_mgr;
wfile_handle utl_file.file_type;
v_wstring varchar2 (100);
v_header varchar2(100);
v_file varchar2(100);
v_date varchar2(20);
begin
select
to_char(sysdate,'dd_mon_yyyy')
into
v_date
from
dual;
v_header :='empno'||chr(9)||'ename'||chr(9)||'job'||chr(9)||'hiredate'||chr(9)||'sal';
for r_mgr in c_mgr loop
v_file := r_mgr.ename||'_direct_reports_'||v_date||'.xls';
wfile_handle := utl_file.fopen ('REPORTS',v_file, 'W');
utl_file.put_line(wfile_handle,v_header);
for r in c_direct_reports(r_mgr.empno) loop
v_wstring := r.empno||chr(9)||r.ename||chr(9)||r.job||chr(9)||to_char(r.hiredate,'dd/mm/yyyy')
||chr(9)||r.sal;
utl_file.put_line(wfile_handle,v_wstring);
end loop;
utl_file.fclose (wfile_handle);
end loop;
end print_reports;
|
|
|
|
|
|
Re: Format Excel Using PLSQL [message #437828 is a reply to message #437483] |
Wed, 06 January 2010 03:08   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Or you can write the output in the MS Office XML format - someone here had some code that would do that, but I couldn't just find it when I looked for it.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Format Excel Using PLSQL [message #438286 is a reply to message #438284] |
Thu, 07 January 2010 16:46   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
There is no error in your post.
Also, when you need help for every compilation error that comes up then this might take months to resolve.
|
|
|
|
|
|
|
|