Search This Blog

Tuesday, October 3, 2017

Upload an Excel file into an Apex Collection Using Plugins



First download the Plugins and install to your APEX environment. Link is here  DOWNLOAD  (insert "demo" as username and password).


 1.  Create a page and a Region for uploading Excel file with a FILE BROWSE item  and a button for UPLOAD. Choose SQL insert action as Database Action. 


2.  Create a another Region for showing uploaded excel report as CLASSIC REPORT. The SQL will be like :     Select * from apex_collections ;


3.  Now create a PROCESS for the taking the PLUGIN as type Excel2Collection(Plugin). Then provide file browse item name in  BROWSE FILE give a name for COLLECTION NAME . 



4.   Now just upload a excel file then it will show in Classic Report from apex_collections table.

if you want to insert the excel data into database. Then make a database table  according to all column of the excel.

Then use query like this : 
 


BEGIN
   INSERT INTO DATABASE_TABLE_NAME  
(NAME OF ALL COLUMN  )

      (SELECT C001,

              C002,
              C003,
              C004
    ALL COLLECTION TABLE'S COLUMN NAME WHAT EVER YOU WANT TO INSERT
              
         FROM apex_collections
        WHERE COLLECTION_NAME = 'use the collection name what you provide at the time of calling Plugin' 
AND SEQ_ID > 1(more then 1 because row number 1 will be the column name));
END;

3 comments:

  1. Hello,

    is it working in 5.1 and above versions?

    if not , please suggest me.

    Thank you.

    ReplyDelete
  2. Hello Santhosh,

    will you provide the create collection sample code. Please.

    Thanks & Regards,
    Upendra.

    ReplyDelete
  3. When ever we upload a excel sheet, it takes a longer time to get the data in collection. We observe that even if there are 3 rows in excel, it takes all 64500 rows of excel and that is causing delay
    Is there any other way to avoid this

    ReplyDelete

Restrict File Upload by File Type in Oracle Apex

If you want to restrict file upload by file type/extension/format you can follow the below steps.  Goto File Browser Item --> Advanced --...