Home » SQL & PL/SQL » SQL & PL/SQL » Format Excel Using PLSQL
Format Excel Using PLSQL [message #437476] Mon, 04 January 2010 11:26 Go to next message
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 #437478 is a reply to message #437476] Mon, 04 January 2010 11:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How is this an Oracle question?
Maybe you should post your question in an Excel forum.

Regards
Michel
Re: Format Excel Using PLSQL [message #437479 is a reply to message #437478] Mon, 04 January 2010 11:57 Go to previous messageGo to next message
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 #437481 is a reply to message #437479] Mon, 04 January 2010 12:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

You don't generate an Excel file but a CSV file (which can be read by Excel). There is no way to give a width in a csv file (and it is meaningless in such a file).

An advice, you can do it and faster with a single SQL statement and a spool in SQL*Plus.

Regards
Michel
Re: Format Excel Using PLSQL [message #437482 is a reply to message #437481] Mon, 04 January 2010 12:44 Go to previous messageGo to next message
syed.nayeem316
Messages: 21
Registered: December 2009
Location: US
Junior Member

Thanks For your reply,

But I have a program scheduled in Appworxp which executes a procedure in Oracle and genrates the Excel report and send the report as an attachment.

I am able to send the report but only thing I need is adjust the column width in excel report.

Thanks
Nayeem
Re: Format Excel Using PLSQL [message #437483 is a reply to message #437482] Mon, 04 January 2010 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case do not generate a csv file but a sylk (Excel basic format) one.
See AskTom topic "Oracle datas into Excell Sheet using pl/sql procedure" and its associated owa_sylk package.

Regards
Michel

[Updated on: Wed, 06 January 2010 03:12]

Report message to a moderator

Re: Format Excel Using PLSQL [message #437828 is a reply to message #437483] Wed, 06 January 2010 03:08 Go to previous messageGo to next message
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 #437976 is a reply to message #437828] Wed, 06 January 2010 11:53 Go to previous messageGo to next message
syed.nayeem316
Messages: 21
Registered: December 2009
Location: US
Junior Member

If you can send me the code I will appreciate it.

Thanks
Nayeem
Re: Format Excel Using PLSQL [message #437980 is a reply to message #437483] Wed, 06 January 2010 11:59 Go to previous messageGo to next message
syed.nayeem316
Messages: 21
Registered: December 2009
Location: US
Junior Member

Hey,

I have managed to export the data to execl report. I am struggling with formatting.

I need the column width or size to autofit according to data.

If you can help me I will appreciate it.

Thanks
Nayeem
Re: Format Excel Using PLSQL [message #437984 is a reply to message #437980] Wed, 06 January 2010 12:09 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If you use the sylk format, then set p_widths accordingly.

If you still use csv then it's still not possible.
Re: Format Excel Using PLSQL [message #437985 is a reply to message #437980] Wed, 06 January 2010 12:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try what we told you instead of just stupidely repeat the same thing?

Regards
Michel
Re: Format Excel Using PLSQL [message #437997 is a reply to message #437980] Wed, 06 January 2010 12:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This might be what I was thinking of.
Re: Format Excel Using PLSQL [message #437999 is a reply to message #437985] Wed, 06 January 2010 12:49 Go to previous messageGo to next message
syed.nayeem316
Messages: 21
Registered: December 2009
Location: US
Junior Member

Man

I could see you have an attitude problem. I suggest you to see a Doctor.

Do you think I have fun doing this. I am trying my best to achieve this.

Nayeem
Re: Format Excel Using PLSQL [message #438000 is a reply to message #437999] Wed, 06 January 2010 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And did you try the package I pointed you to?

Regards
Michel
Re: Format Excel Using PLSQL [message #438001 is a reply to message #438000] Wed, 06 January 2010 13:06 Go to previous messageGo to next message
syed.nayeem316
Messages: 21
Registered: December 2009
Location: US
Junior Member

Yes I create the package succesfully.

Not how do I execute it to create a execl report.

Thanks
Nayeem
Re: Format Excel Using PLSQL [message #438003 is a reply to message #438001] Wed, 06 January 2010 13:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you search for owa_sylk in the same page you'll see some examples.
If you click on "Home" in the same page and put "owa_sylk" in the search field, you will get more examples.

Regards
Michel
Re: Format Excel Using PLSQL [message #438005 is a reply to message #438003] Wed, 06 January 2010 13:37 Go to previous messageGo to next message
syed.nayeem316
Messages: 21
Registered: December 2009
Location: US
Junior Member

I created a proceure to execute the package.( pls find the attached document).

I gives me error
PLS-00103: Encountered the symbol "OUTPUT"


Please help.

Thanks
  • Attachment: OWA_EMP.txt
    (Size: 1.13KB, Downloaded 219 times)
Re: Format Excel Using PLSQL [message #438006 is a reply to message #438005] Wed, 06 January 2010 13:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before "begin" there should be a "declare" section where you declare the variable "output" as in the example in the same page at:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:728625409049#2577375071651

Regards
Michel
Re: Format Excel Using PLSQL [message #438014 is a reply to message #438006] Wed, 06 January 2010 14:06 Go to previous messageGo to next message
syed.nayeem316
Messages: 21
Registered: December 2009
Location: US
Junior Member

I am still getting error.

Please find the atteched document.

  • Attachment: OWA_EMP.txt
    (Size: 1.03KB, Downloaded 234 times)
Re: Format Excel Using PLSQL [message #438017 is a reply to message #438014] Wed, 06 January 2010 14:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems there is an error in the package: the loop in " build_cursor" procedure has no end.
Either try to fix it or search for one of the other packages that exist in the same site: goto Home and put "owa_sylk" in search field.

Regards
Michel
Re: Format Excel Using PLSQL [message #438284 is a reply to message #438017] Thu, 07 January 2010 16:30 Go to previous messageGo to next message
syed.nayeem316
Messages: 21
Registered: December 2009
Location: US
Junior Member

Please find the Attach document and I will aprreciate if you can resolve this error for me.

Thanks
Nayeem
  • Attachment: help.txt
    (Size: 11.18KB, Downloaded 198 times)
Re: Format Excel Using PLSQL [message #438286 is a reply to message #438284] Thu, 07 January 2010 16:46 Go to previous messageGo to next message
ThomasG
Messages: 3189
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.

Re: Format Excel Using PLSQL [message #438287 is a reply to message #438286] Thu, 07 January 2010 16:50 Go to previous messageGo to next message
syed.nayeem316
Messages: 21
Registered: December 2009
Location: US
Junior Member

I am sorry but I have to say that you Think I don't know what I am doing. I am a OCA and have been working on plsql for more that 3 years.

I need your help only on this one.

Code is with procedure "na". Please find the attachement


Error is:
ORA-06533: Subscript beyond count
ORA-06512: at "ORSC_RMS.OWA_SYLK", line 30
ORA-06512: at "ORSC_RMS.OWA_SYLK", line 267
ORA-06512: at "ORSC_RMS.NA", line 7
ORA-06512: at line 1


Thanks
Nayeem
  • Attachment: help.txt
    (Size: 11.18KB, Downloaded 210 times)
Re: Format Excel Using PLSQL [message #438288 is a reply to message #438287] Thu, 07 January 2010 16:57 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, if you know what you are doing then I'm confident you will find the solution.
Re: Format Excel Using PLSQL [message #438327 is a reply to message #438287] Fri, 08 January 2010 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 06 January 2010 21:38
It seems there is an error in the package: the loop in " build_cursor" procedure has no end.
Either try to fix it or search for one of the other packages that exist in the same site: goto Home and put "owa_sylk" in search field.

Regards
Michel


You did nothing about this, so you get the same error.

Regards
Michel
Re: Format Excel Using PLSQL [message #438383 is a reply to message #438327] Fri, 08 January 2010 09:08 Go to previous messageGo to next message
syed.nayeem316
Messages: 21
Registered: December 2009
Location: US
Junior Member

Thanks Michel Cadot

Re: Format Excel Using PLSQL [message #438384 is a reply to message #438288] Fri, 08 January 2010 09:10 Go to previous message
syed.nayeem316
Messages: 21
Registered: December 2009
Location: US
Junior Member

Who are You "ThomasG"?

Mad
Previous Topic: how to show sinlge line text data in seperated column wise
Next Topic: Calculate One Query against another
Goto Forum:
  


Current Time: Fri Dec 09 09:29:09 CST 2016

Total time taken to generate the page: 0.15841 seconds