Drag and drop an excel file to an APEX collection.

On apex-plugin.com I found an excellent process type plugin Excel2Collection for uploading an excel file
into a collection. A nice piece of work from Anton Scheffer.

However, in this age of HTML5 our customer wanted to drag and drop his CSV-file instead of uploading it with a button.
So I took the plugin from Anton and tweaked it such that with older browser you get the original upload button, but with
the more current browsers you will be able to drag and drop your file.

First of all you have to follow the instructions that come with the plugin (create a filebrowser item, create a process etc.)
But then….

1. You need a container to drop your file.
I created an HTML Text Region with the source

<div id="dropbox">
<span id="droplabel">Drop file here...</span>
</div>

2. You need some styling. I used

#dropbox {
  width: 425px;
  height: 300px;
  border: 2px solid #DDD;
  -moz-border-radius: 8px;
  -webkit-border-radius: 8px;
  background-color: #FEFFEC;
  text-align: center;
  color: #BBB;
  font-size: 2em;
  font-family: Arial, sans-serif;
}

#droplabel {
  position: relative;
  top: 40%;
}

3. This step depends on what you want to do with your collection. here, we are just showing the results.
To show the collection you can create a classic report, based on the collection you will be filling:
eg. to show the contents of a CSV file with three columns:

  select c001
  ,      c002
  ,      c003
  from   apex_collections
  where  collection_name = 'EDWINS_COLLECTION'

The name of the collection is of course the same name you entered in the attribute of the plugin.

4. The magic I used in the footer of the page

if (window.File && window.FileList && window.FileReader) {
  // Our functionality is supported by the browser
  var dropbox = document.getElementById("dropbox");
  $("#P1_FILE").hide(); //The Id of the filebrowser item
  $('#P1_SUBMIT_BUTTON').hide();
  //
  // If the browser doesn't support draganddrop we have to
  // show the filebrowser and a submit button
  // when supported, don't show them
  //
  dropbox.addEventListener("dragenter", dragEnter, false);
  dropbox.addEventListener("dragexit", dragExit, false);
  dropbox.addEventListener("dragover", dragOver, false);
  dropbox.addEventListener("drop", drop, false);
}
else {
  $("#dropbox").hide(); //No drag and drop supported. Hide the container
}

function dragEnter(evt) {
  evt.stopPropagation();
  evt.preventDefault();
}

function dragExit(evt) {
  evt.stopPropagation();
  evt.preventDefault();
}

function dragOver(evt) {
  evt.stopPropagation();
  evt.preventDefault();
}

function drop(evt) {
  var files, count;
  evt.stopPropagation();
  evt.preventDefault();

  files = evt.dataTransfer.files;
  count = files.length;
  if (count > 0)
    handleFiles(files);
}

function handleFiles(files) {
  var file, reader;
  file = files[0];
  $("#droplabel").html("Processing " + file.name);
  reader = new FileReader();
  reader.readAsDataURL(file);
  reader.onloadend = handleReaderLoadEnd;
}

function handleReaderLoadEnd(evt) {
  // This callback will insert the base64 results of the filereader
  // in the CLOB001 column of the CLOB_CONTENT collection
  var clobObj = new apex.ajax.clob( function(p){
    if (p.readyState == 4){
      var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=dummy',0);
      gReturn = get.get();
      apex.submit('TEST');
     //Fire the submit to get the proces plugin working
    }
  });
  clobObj._set(evt.target.result);
}

So when the file is dropped in the container area, the CLOB001 column is filled with the base64 string of the file.
Now alter the code of the plugin from:

 begin
    select aaf.id
         , aaf.blob_content
         , aaf.filename
    into t_file_id
       , t_document
       , t_filename
    from apex_application_files aaf
    where aaf.name = t_filename;
    --
    delete from apex_application_files aaf
    where aaf.id = t_file_id;
    --
    log( 'retrieved!'  );
  exception
    when no_data_found
    then
      raise e_no_doc;
  end;

to

 begin
    select aaf.id
         , aaf.blob_content
         , aaf.filename
    into t_file_id
       , t_document
       , t_filename
    from apex_application_files aaf
    where aaf.name = t_filename;
    --
    delete from apex_application_files aaf
    where aaf.id = t_file_id;
    --
    log( 'retrieved!'  );
  exception
    when no_data_found
    then
     log( 'looking in CLOB_CONTENT'  );
     begin
       /* 
         We didn't use the filebrowser, but the container
       */
       select apex_web_service.clobbase642blob
                (dbms_lob.substr( col.clob001
                                ,  dbms_lob.getlength(col.clob001)
                                , dbms_lob.instr(col.clob001,',',1,1) + 1 
                                )
                )
       into   t_document
       from   apex_collections col
       where  col.collection_name = 'CLOB_CONTENT'
       and    col.clob001 is not null ;
       --
       log ('Found');
     exception
       when no_data_found
       then
         log('Not found');
         raise e_no_doc;
     end ;
  end;

and you are done.

Advertisement