Paste from ClipBoard into Grid - PeopleCode Friday #4

A number of times in my PeopleSoft career I have been asked by users if I can provide a paste from the Windows clipboard into a grid.  Typical use cases include pasting Excel data into a grid or bulk loading text.

I've seen some PeopleCode and Javascript developer friends of mine code some really neat solutions but here I'm not going to attempt anything fancy.  I offer this code fragment for what it is - ruthlessly simple.




PIA Page with paste window and results grid

App Designer Page




Local Rowset &rs = GetRowset(Scroll.GRS_TBL);
Local array of string &aRows;
Local array of string &aFields;

&aString = CreateArray("");
&aFields = CreateArray("");

&aRows = Split(GRS_WRK.HTMLAREA, Char(10));

For &i = 1 To &aRows.Len
   
   &aFields = Split(&aRows [&i], "|");
   &rs(&i).GRS_TBL.SELNUM.Value = &aFields [1];
   &rs(&i).GRS_TBL.DESCR.Value = &aFields [2];
   &rs.InsertRow(&rs.ActiveRowCount);
End-For;



Some observations:

  • You can obviously change the separator from | (pipe) to whatever works for you. Comma is useful when pasting from Excel.
  • You will want to add some structure checking code.  If you're expecting 2 fields but a user pastes 3 then you'll want to trap that.  
  • You may want to do some datatype validation to avoid generating PeopleCode or component processor errors.
  • You could get really smart and create a button which reads data directly from the clipboard.  Most browsers don't allow you to do this for security reasons but there are ways to configure browsers to trust the clipboard.  
  • I've been using this recently to repeatedly flush and reload data into tables on a new application I've been building.  I went as far as making the table name a parameter and setting GETFIELD(n).Value rather than an explicit field name as I had numerous tables to populate.
Any other ideas or suggestions then, as usual, just post comments below.

Comments

MyBlog said…
This would work with Internet Explorer, but the carriage return/new line seems to be an issue with Google Chrome and Firefox. To overcome this limitation, I had a workaround - to look at the Browser Type, and use char(10)|char(13) to determine a new line.

Something like this:
Evaluate %Request.BrowserType
When = "IE"
RECORD.LONGVALUE.Value = Substitute(RECORD.LONGVALUE.Value, Char(10), "");
break;

When = "CHROME"
RECORD.LONGVALUE.Value = Substitute(RECORD.LONGVALUE.Value, Char(10), Char(13));
break;

End-Evaluate;
13 December 2015 at 05:58
Arpan said…
Here is the code for others reference.

Local Rowset &rs = GetLevel0()(1).GetRowset(@("Scroll." | String(&RecName)));
Local array of string &aRows;
Local array of string &aFields;
&rs.Flush();

&tab = Char(9);
&aString = CreateArray("");
&aFields = CreateArray("");

&aRows = Split(TU_EX_CPY_WRK.HTMLAREA, Char(10));
If &aRows.Len < 1 Then
Error MsgGetText(20012, 224, "No data to be processed. Please paste data from Excel first.")
Else
For &i = 1 To &aRows.Len
&aFields = Split(&aRows [&i], &tab);
If &FieldCount <> &aFields.Len Then
Error MsgGetText(20012, 223, "Copied cell count doesn't match with record count. Please validate your data.")
Else
For &j = 1 To &aFields.Len
If IsDate(&aFields [&j]) Then
&rs(&i).GetRecord(@("Record." | String(&RecName))).GetField(&j).Value = Date(&aFields [&j]);
Else
&rs(&i).GetRecord(@("Record." | String(&RecName))).GetField(&j).Value = &aFields [&j];
End-If;
End-For;
&rs.InsertRow(&rs.ActiveRowCount);
End-If;
End-For;
&rs.DeleteRow(&i);
End-If;
15 August 2016 at 10:56
Post a Comment