ex_paste_from_excel

Note: this example was written before the ability to paste a range of cells from Excel into a GBC application was reintroduced with GBC 5.00.08 (https://4js.com/online_documentation/fjs-gbc-manual-html/#gbc-topics/gbc_whatsnew_50008_2.html). The coding techniques here are still interesting but you no longer need to explciitly add code to do this.

Example showing how to use reflection to take clipboard with array data and paste into a 4gl array variable

Select and Copy a range of cells from Excel

Start the test program and select Paste Special (from Excel)

Values in Excel should appear in program

Test Notes

To test a succesful paste, make sure you have a range that matches the 4gl array i.e.

To test failure conditions, have a different shaped range, should get an error message "Number of elements in clipboard does not match number of elements in array"

Also note the result of the following test. The number can go into the string field, whilst the text can't go into the number field. A future enhancement would be to test and cater for this.

The data will be appended to the end of the array. If the last row of the array is blank, it will start on that line. If the last row the array contains data, it will start on the next line.

Code Usage

Intended Usage is in an INPUT ARRAY similar to ...

    INPUT ARRAY arr ...

        ON ACTION pastefromexcel ATTRIBUTES(VALIDATE=NO, TEXT="Paste Special (from Excel)")
            CALL paste_from_excel.paste(reflect.Value.valueOf(arr)) RETURNING ok, error_text
            IF NOT ok THEN
                CALL FGL_WINMESSAGE("Error", error_text,"stop")
            END IF

If the paste was succesful, ok will be TRUE If the paste was unsuccesful, ok will be FALSE, and an error message will be in error_text

Assumes clipboard has data that has lines/rows seperated by ASCII(10) LF, and cells within the line/row seperate by ASCII(9) TAB