Copy / Paste Tabular Form Report

Copy / Paste Tabular Form Report is typical requirement as User will Copy From Excel and paste in tabular form. Some typical challenges to be faced are:
1. Data from Clipboard will not be splitted properly.
2. Keeping track of column in tabular form is tedious.
Following Steps are involved in process:
I have used 3 items in this process:
P35_FROM_CURRENCY  –  Holds Whole tabular form data from                       Clipboard
P35_COL_ID            –  Holds Current Column
P35_TO_CURRENCY  –  Holds headings which are been copied          from Excel.
Step:1:  Create Static Content region and add HTML Code for splitting and      retrieval of value Process.
  <html>
  <head>
        
   <script>
   function Disable_Control_C() {
           $(“#P35_FROMCURRENCY”).val(“”);
      var keystroke = String.fromCharCode(event.keyCode).toLowerCase();
      if (event.ctrlKey && keystroke == ‘v’) {
         // alert(“Start”);
         $(“#P35_FROMCURRENCY”).css(“display”,”block”);
         $(“#P35_FROMCURRENCY”).focus();
         var a = $(“#P35_FROMCURRENCY”).val();
          setTimeout(function(){ 
                                 if (a != ” “)
                                     {
                                           var tes = $(“#P35_FROMCURRENCY”).val().split(“t”); 
                                                                                    $(“#P35_FROMCURRENCY”).val(tes); 
                                           var k=$(“#P35_FROMCURRENCY”).val();
                                           k=k.replace(/,/g, “;”);
                                           $(“#P35_FROMCURRENCY”).val(k) 
                                           var res =  $(“#P35_FROMCURRENCY”).val();
                                           res=res.split(“n”);
                                           console.log(“Karthi”+res);
                                           alert(res);
                                           $(“#P35_FROMCURRENCY”).css(“display”,”none”);
                                           var test = String(res);
                                           var str = test.indexOf(“,”);
                                           var substr = test.substring(0,str);
                                           var substr = String(substr);
                                           var cnt = substr.match(/;/g).length;
                                           f_col_names(cnt);
                                           col = $(“#P35_TOCURRENCY”).val(); 
                                           apex.submit({request:’TEST’,set:{‘P35_FROMCURRENCY’:res,’P35_TOCURRENCY’:col}});   
                                     }
                               }, 50);         
      }
    
   }
</script>
        
<script>
     function f_col_names(a)
     {
         var col_id_test;
         var col_id = $(“#P35_COL_ID”).val();
         var col_id_sub = col_id.substring(1,3);
         col_id_sub = Number(col_id_sub);
         var heading = $(“#P35_TOCURRENCY”).val();
         for (i=1;i<=a;i++)
         {   
          col_id_sub = Number(col_id_sub) + 1;
          col_id_test = pad(col_id_sub,2);
          col_value = “f”+col_id_test+”_”+col_id.substring(4,8);   
          idheader=”#”+col_value+”_LABEL”;
          var hidden_id = $(idheader).closest(“td”).attr(“headers”);   
          heading = heading + “,”+hidden_id;      
         }
         $(“#P35_TOCURRENCY”).val(heading);
     }
    
    function pad(n, width, z) {
  z = z || ‘0’;
  n = n + ”;
  return n.length >= width ? n : new Array(width – n.length + 1).join(z) + n;
}
</script>    
   
</head>
<body class=”capture-paste” onkeydown=”javascript:Disable_Control_C()”> <div id=”paste-output”></div>
    <div>
    <textarea id=”area” style=”display: none; position: absolute; left:-99em;”></textarea>
    </div>
</body>
</html>
Above Javascript Code has 3 functions:
Disable_Control_C() – It recognizes the key, If it’s Ctrl+V 
                       
                       1. It display item P35_FROM_CURRENCY and focus    been placed on it.
                       2. Automatically Contents will be placed.
                       
                       3.After we will split as row and columns.
                      
                       4. After all process, item been hided.
                      
                       5. All process happens within 2 secs.
f_col_names() –     1. It gets current focus item id and value.
                  2. It counts number of columns been selected from splitter of       columns in item P35_FROM_CURRENCY.
                  3. It gets count and column id’s and headings from tabular form        and P35_TO_CURRENCY value , also with some jquery.
pad()       –      It is used for padding 0’s to id’s.
Step:2:  Dynamic Action to be added for getting current focussed item and value in        tabular form.
         Event           :  Get Focus
         JQuery Selector   .u-TF-item      // class of item in tabular form 
         Action          :  Execute Javascript Code
         Code           :    id = event.target.id;
                             $(“#P35_COL_ID”).val(id);
                             idheader=”#”+id+”_LABEL”;
                             var hidden_id =                        $(idheader).closest(“td”).attr(“headers”);
                             var heading = $(idheader).html();
                             $(“#P35_TOCURRENCY”).val(hidden_id);   
Step:3: Create PL/SQL process for Insertion
DECLARE
   lv_col_cursor      VARCHAR2 (1000)  := :p35_tocurrency;
   lv_input           VARCHAR2 (32767) := :p35_fromcurrency;
   lv_first_row       VARCHAR2 (32767) := ”;
   ln_col_count       NUMBER           := 0;
   ln_col_start       NUMBER           := 0;
   lv_column_value    VARCHAR2 (1000)  := ”;
   lv_columns         VARCHAR2 (32767) := ”;
   ln_row_ident       NUMBER           := 0;
   ln_last_row        NUMBER           := 0;
   lv_column_values   VARCHAR2 (32767) := ”;
   i                  NUMBER           := 0;
   lv_column_head     VARCHAR2 (32767) := ”;
   lv_sql             VARCHAR2 (32767) := ”;
BEGIN
   LOOP
      i := i + 1;
      DBMS_OUTPUT.put_line (‘Loop cnt : ‘ || i);
      lv_sql :=
            ‘INSERT INTO ic_dyn_tab_form (‘ || lv_col_cursor || ‘) VALUES(”’;
      BEGIN
         SELECT REGEXP_INSTR (lv_input, ‘,’, 1, i)
           INTO ln_row_ident
           FROM DUAL;
      EXCEPTION
         WHEN OTHERS
         THEN
            ln_row_ident := 0;
      END;
      EXIT WHEN ln_row_ident = 0;
      /*DBMS_OUTPUT.put_line (   ‘Before:’
                            || lv_column_values
                            || ‘;Last Row:’
                            || ln_last_row
                            || ‘;Row Identifier:’
                            || ln_row_ident
                            || ‘,i:’
                            || i
                           );*/
      BEGIN
         SELECT TRIM (‘,’ FROM REPLACE (SUBSTR (lv_input,
                                                ln_last_row + 1,
                                                (  (ln_row_ident – ln_last_row
                                                   )
                                                 – 1
                                                )
                                               ),
                                        ‘;’,
                                        ”’,”’
                                       )
                     )
           INTO lv_column_values
           FROM DUAL;
      EXCEPTION
         WHEN OTHERS
         THEN
            lv_column_values := NULL;
      END;
      /*  DBMS_OUTPUT.put_line (   ‘After:’
                              || lv_column_values
                              || ‘;Last Row:’
                              || ln_last_row
                              || ‘;Row Identifier:’
                              || ln_row_ident
                              || ‘,i:’
                              || i
                             );
        DBMS_OUTPUT.put_line (lv_column_values);*/
      lv_sql := lv_sql || lv_column_values || ”’)’;
      –raise_application_error (-20001, lv_sql);
      EXECUTE IMMEDIATE lv_sql;
      –raise_application_error (-20001, lv_sql);
      DBMS_OUTPUT.put_line (lv_sql);
      ln_last_row := ln_row_ident;
      ln_row_ident := 0;
      lv_column_values := ”;
      lv_sql := ”;
   END LOOP;
END;
  • January 23, 2016 | 19 views