Home » Fusion Middleware & Colab Suite » Weblogic & Application Server » Export Excel
Export Excel [message #152172] Wed, 21 December 2005 08:24 Go to next message
rajasekharam
Messages: 12
Registered: July 2005
Junior Member
Hai,

Can anybody help me in achieving my task.

I have created web page with pl/sql cartridges and in that 46 fields are there. Now the problem is this 46 fields I have to export into excel format. Now I am able to export into excel by using the
owa_util.mime_header( 'application/vnd.ms-excel', TRUE );
for field seperation "fldsep". But I need ur help to get some information regarding

1. When I export this data the file name and sheet name was creating as "package name.procedure name?parameters"

but I want to give particular name as constant

2. When I export this data all the fields are setting as same width

but I need to set it as auto fit to the particular data width

3. I have three fields like Approver ID, Approver First Name, Approver Last Name like this some have two & four so

can I show like ID, First Name, Last Name as three fields under Approver as heading


Thanks
Rajasekharam
Re: Export Excel [message #152245 is a reply to message #152172] Wed, 21 December 2005 19:05 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
For the "filename" try setting the page title in the HTML to the filename you want.

For richer formatting info in the created file, search for "owa_sylk" on asktom.oracle.com
Re: Export Excel [message #152494 is a reply to message #152245] Fri, 23 December 2005 08:28 Go to previous message
rajasekharam
Messages: 12
Registered: July 2005
Junior Member
Hai,

Thanks for ur reply but I didn't get u. Here I am sending one samll procedure which I am using for export into excel like this I have another five procedures are there with more calculations fields wirh more conditions. please go through that and suggest me the better way.

Here I am attching the procedure and screenshots of the outputs of excel.

PROCEDURE expoExcel
            ( p_comp_prop_id IN   NUMBER    DEFAULT NULL
             ,p_outType      IN   VARCHAR2  DEFAULT 'EXCEL'
             )
IS
CURSOR c_company( v_comp_prop_id   IN   VARCHAR2)
IS
  SELECT company_property_id
        ,company
        ,year
        ,action_status
        ,approver_wwid
        ,due_date
        ,comments
        ,email_address
        ,legel_entity_no
        ,legel_entity_name
        ,prepared_by_fname
        ,prepared_by_lname
        ,netting_Participant_no
        ,currency_code
        ,premium_bill_fname
        ,premium_bill_lname
  FROM  brvo_company_property
  WHERE company_property_id = v_comp_prop_id
  ORDER BY company;

r_company  c_company%rowtype;

CURSOR c_com_desc(v_comp   IN   VARCHAR2)
IS
  SELECT fv.description description
  FROM   fnd_flex_values_vl  fv
        ,fnd_flex_value_sets fs
  WHERE fs.flex_value_set_id   = fv.flex_value_set_id
  AND   fs.flex_value_set_name = 'BRVO_COMPANY'
  AND   fv.enabled_flag        ='Y'
  AND   fv.summary_flag        ='N'
  AND   fv.flex_value          = v_comp
  ORDER BY fv.flex_value;

r_com_desc  c_com_desc%rowtype;

CURSOR c_approver_list(v_wwid   IN   VARCHAR2)
IS
  SELECT approver_id
        ,approver_wwid
        ,approver_name
        ,approver_email
  FROM  brvo_ins_approver_list
  WHERE approver_wwid = v_wwid
  ORDER by approver_wwid,approver_name;

r_approver_list  c_approver_list%rowtype;

i               NUMBER := 0;
fldSep          CHAR(1);
v_comp_prop_id  NUMBER := 0;
l_baseUrl       VARCHAR2( 50 ) := bws.getBaseUrl;

BEGIN

   IF p_comp_prop_id is not null and p_comp_prop_id <> 0 THEN
      OPEN  c_company(p_comp_prop_id);
      FETCH c_company into r_company;
      CLOSE c_company;
      v_comp_prop_id := p_comp_prop_id;
   ELSE
      v_comp_prop_id := 0;
   END IF;

   OPEN  c_com_desc(r_company.company);
   FETCH c_com_desc into r_com_desc;
   CLOSE c_com_desc;

   OPEN c_approver_list(r_company.approver_wwid);
   FETCH c_approver_list into r_approver_list;
   CLOSE c_approver_list;

   IF ( p_outType = 'EXCEL' ) THEN
      fldSep := CHR(9);
      owa_util.mime_header( 'application/vnd.ms-excel', TRUE );
      htp.print(            'Year End'
               || fldSep || 'MRC'
               || fldSep || 'Description'
               || fldSep || 'Approver WWID'
               || fldSep || 'Approver First Name'
               || fldSep || 'Approver Last Name'
               || fldSep || 'Email Address'
               || fldSep || 'Legal No'
               || fldSep || 'Legal Name'
               || fldSep || 'Prepared By First Name'
               || fldSep || 'Prepared By Last Name'
               || fldSep || 'Premium Billing Contact First Name'
               || fldSep || 'Premium Billing Contact Last Name'
               || fldSep || 'Premium Billing Contact Currency'
               || fldSep || 'Netting Participant No'
               );

      htp.print(            r_company.year
               || fldSep || tic||r_company.company
               || fldSep || r_com_desc.description
               || fldSep || r_company.approver_wwid
               || fldSep || (substr(r_approver_list.approver_name,1,instr(r_approver_list.approver_name,' ')))
               || fldSep || (substr(r_approver_list.approver_name,instr(r_approver_list.approver_name,' '),length(r_approver_list.approver_name)))
               || fldSep || r_approver_list.approver_email
               || fldSep || r_company.legel_entity_no
               || fldSep || r_company.legel_entity_name
               || fldSep || r_company.prepared_by_fname
               || fldSep || r_company.prepared_by_lname
               || fldSep || r_company.premium_bill_fname
               || fldSep || r_company.premium_bill_lname
               || fldSep || r_company.currency_code
               || fldSep || r_company.netting_Participant_no
               );
   END IF;
END;




The output format is like this:

Year End    MRC	   Description	Approver WWID	Approver First Name	Approver Last Name	Email Address	Legal No	Legal Name	Prepared By First Name	Prepared By Last Name	Premium Billing Contact First Name	Premium Billing Contact Last Name	Premium Billing Contact Currency	Netting Participant No
2003	  '001220  Ethicon USA	UMA						                                                                UMA				                                                                                                USD	

The required output format is like this:

Year End    MRC	   Description	       Approver	                Email Address	   Legal                Prepared By             Premium Billing Contact   Premium Billing Contact Currency	Netting Participant No
                                    WWID   First Name   Last Name	                     No	   Name	   First Name     Last Name   First Name	Last Name	                                                                                    
2003	  '001220  Ethicon USA	UMA						                UMA				                          USD	



Thanks
Rajasekharam
Previous Topic: problem in installation
Next Topic: installation oracle 10gAS is failed
Goto Forum:
  


Current Time: Thu Mar 28 03:40:22 CDT 2024