Ask Reuben

Copy And Paste From Excel

How can I copy and paste a range of cells from Excel into a Genero application?

Accountants and Analysts love Excel.  One of the tasks they may have is to use Excel to create a bunch of numbers that they then want to import into your Genero application.    Examples might include budget and forecast figures.  Another usage of Excel is as an intermediary in a cheap method to do a one time transfer of data from one application to another (export to csv or Excel, import the csv or Excel into your application).

Rather than writing dedicated load programs that might use LOAD, or base.Channel to read files, or a dedicated Web Service API to load data, the concept is to use an existing screen where the user can manually enter many rows of data via an INPUT ARRAY.  The question is then can I copy a range of cells from Excel and paste it into this INPUT ARRAY?

With the Genero Desktop Client (GDC) and Native interfaces, if the user attempted this then the GDC keyboard buffer would read this range of cells and would tab between fields when it read a field or row delimiter in the clipboard data.  This would work but there had to be a range of conditions for this copy and paste to be as successful.  These factors include

  • making sure the field with focus was the first column
  • making sure that the number of columns in the array matched the number of columns in the range copied
  • making sure that all data was of the right datatype, and did not trigger any validation defined in the .per i.e the “A character to numeric conversion failed” error
  • making sure that the AFTER FIELD, AFTER ROW etc did not have any logic that could change the field with focus.  That could be in the form of a NEXT FIELD or a new dialog.

To do this successfully meant having what I would call a “naked INPUT ARRAY”, that is it had nothing in/on it.  Whilst the key was to not have a NEXT FIELD or a new dialog in any of the triggers that could be executed, in practise it meant no BEFORE ROW, BEFORE FIELD, AFTER FIELD, AFTER ROW triggers.  The only triggers were BEFORE INPUT, AFTER INPUT, and ON ACTION’s.

With the movement to GBC and Universal Rendering, this ability to read the clipboard and treat it as part of the keyboard buffer was lost.

You could write a function for each array that parses the clipboard but that required doing so for each array.  The introduction of reflection gives a way to write this functionality generically, that is pass the name of an array variable and have that populated with the clipboard data.

I have  created a Github repository that illustrates such a function, the name of the repository is ex_paste_from_excel.

Some screenshots below that illustrate the usage.  Start with a spreadsheet and select and copy a range of cells in this spreadsheet that has the same number of columns as the intended target array (in this case 3 columns).  Note that the array on the right is empty at this stage …



… and then with the Genero application, with the INPUT ARRAY being the active dialog, click “Paste Special (from Excel)”, this will call code that copies the clipboard data into the array.  Note that the array on the right is now populated with the values from the spreadsheet on the left …



The code in the ON ACTION and the usage of the function is …

CALL paste_from_excel.paste(reflect.Value.valueOf(arr)) RETURNING ok, error_text

… the key thing to note is that the input parameter is a reflection of an array variable, this is the array variable that you want to populate.  The function will attempt to append to this array the contents of the clipboard.  The function will return TRUE or FALSE in the first return parameter, and if FALSE will place some error text in the second return parameter.

Inside the function, interesting lines of code are …

CALL ui.Interface.frontCall("standard","cbget",[], str)

… this reads the clipboard and places it in a string variable str.  Note this front-call uses the Clipboard API, (https://developer.mozilla.org/en-US/docs/Web/API/Clipboard_API), note the browser compatibility and other comments.   A user may have to enable this feature within their browser.

There are two usages of base.StringTokenizer.  This class is used to take the string received and split it, first into lines or rows, and then again to split the line or row into cells.  The delimiter is what is inside the clipboard separating the lines and rows within the range copied.  First the clipboard data is split into lines based on ASCII(10) or LF, the line separator …

LET tok_line = base.StringTokenizer.createExt(str,ASCII(10), "\\", TRUE)

… then the line is split into cells based on ASCII(9) or TAB, the column separator …

LET tok_cell = base.StringTokenizer.createExt(line,ASCII(9), "\\", TRUE)

There is a variable first.  This is some logic that is executed when parsing the first line to make sure that the number of columns in the clipboard data matches the number of record members in the array.   If the range copied has 3 columns, then the array it is pasted into should have 3 columns.  If these numbers differ then no attempt will be made to paste the data.

You will note some logic involving a variable auto_append_row.  This function will append the clipboard data to the end of the existing array.  If the array has no rows, or the user has tabbed past the last row, the Genero front-end will create a new row automatically.  See the paragraph Automatic temporary row here.  The auto_append_row variable is an attempt to detect when that has occurred and start the paste in this row rather than appending rows past this temporary empty row.

Otherwise the key code involving reflection is to …

Create a reflection of the array row

LET reflect_row = reflect_array.getArrayElement( reflect_array.getLength())

Create a reflection of an array cell

LET reflect_cell = reflect_row.getField(idx)

Parse through the clipboard data, parse through the array variable at same rate, so that we can then place the value from a cell in the clipboard into an array cell using the set method and copyOf methods.

CALL reflect_cell.set(reflect.Value.copyOf(cell))

Something to note is that your INPUT ARRAY does not have to be “naked”.  It can have the normal range of triggers that include NEXT FIELD statements and jumping to other dialogs.  You do need to be conscious that the validation in the AFTER INPUT has to validate every field and every row.

If you think this is some functionality you would like, take the function from the repository and modify to meet your needs.  It is also a good example of reflection being able to solve a problem generically.