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;
No comments:
Post a Comment