14.1 Looking at Excel for ideas

In the time that I’ve been using personal computers I have had the opportunity to use a variety of different spreadsheet applications whose names in some cases have vanished into the annals of computing history: Visicalc, Supercalc, Lotus 1-2-3, Quattro Pro. Microsoft Excel seems to have swept them all into oblivion. I did for a short time (when first exposed to a spreadsheet) think that it might displace programming with a high-level language for many routine tasks of tabulating and listing, and indeed it has. However, there are computing tasks where it simply cannot compete because it is so much slower.

I think that it is worth taking time to look deeply at how the tabular input in Excel is actually managed. I’m not sure that duplicating Excel is a useful way of spending one’s time, but looking at it will not only reveal how clever and sophisticated it is but also to identify some of the things that make it more intuitive to use particularly when simply entering a table of data.

When Excel loads, it presents a grid, and along the top of the grid taking up the first row are a set of letters and down the left-hand side taking up the first column there are a set of numbers. These enable you to select an entire column or an entire row as well as referencing every single cell by means of the column letter and the row number. You can click on any cell and enter numbers or text or indeed formulae. What you see above the top row of the grid are some boxes. In one of them you see the address is: letter and row number of the cell you have highlighted. The next control along is basically a toolbar to accept or reject anything you edit. Then you have a long data entry box where you can put in your numbers, text or formulae. Icons in the toolbox allow you to accept or reject what you have done.

Early spreadsheets only permitted this out-of-grid editing mode, and it stays there even in Excel partly because users are used to it and also because the length of the data input box allows formulae much longer than the size of the cell, which only really needs to contain the result.

If you click on a column letter you can insert a column at that position or delete it. Similarly, you can do those operations on rows. The grid in Excel can be scrolled and the mechanism for scrolling is via scroll bars. It’s pretty clever, and many tables simply don’t need that degree of flexibility – in Excel, the tables are flexible because by and large, it is about tables, whereas tables in many other applications don’t need to be so flexible because the tables are about something other than simply being a table!

If you use Excel on a regular basis then the editing actions are not only straightforward but become second nature. Users of your program, including yourself, will probably expect to edit any table using the same sort of interface as one finds in Excel. However, a grid input for a table of data will probably have a fixed number of columns. The column widths will probably be fixed and so will the row heights. There won’t be any doubt as to what goes in any cell: an integer, a real, or a text string. You may not need to be able to select a column, but you will almost certainly need to select a row because it may be necessary to delete it, to edit it or to insert a new blank row at that position, pushing all the later rows down one position in the table. There may be comparatively few columns, but lots of rows.

The first simple strategy is not to allow editing directly in the table grid but outside it in a set of data input boxes corresponding to each column. You will still need the accept and reject buttons and you will need a mechanism to pick a row for editing.

If you permit in-grid editing, then you have a choice as to whether or not to provide the out of grid editing facility as an alternative or not to provide it at all.

14.2 Out of grid (or off-grid) editing

If you only have out of grid editing, then the job in hand is basically to provide an appropriate %rd, %rf or %rs data input box for each cell in a particular row and, apart from the ability to select a row, the table that is displayed only has to be updated with numbers when the contents of those input boxes are accepted. To make matters simpler it may even be possible not to need a response from the table itself to a click selecting a row but instead to have the row selected in its own data input box which could have a spin wheel control.

14.3 Data interchange between programs

Data interchange between programs originated by the same person or organisation is probably best done using unformatted files, but between different programs then a formatted and hence person readable file is probably better and almost certainly if drawn by a third party program, will be in a sort of comma-separated format.

It is possible to launch another application such as Excel using START_PROCESS@ or START_PPROCESS@, which can be done with a filename on the ‘command line’ that can later be picked up easily by the originating program. Files up to a certain length may even still be in a disk cache and therefore be readable at much greater speed than reading from the physical device. On return, a file saved in that launched application may be read by your program, but with all the caveats concerning its acceptability when it comes to reading it.

14.4 Listview (%lv) – setting up the grid

14.5 Listview – setting the initial values in the rows

Now suppose that each row that follows comprises a capital letter and 3 REAL values, quoted to 3 decimal places, then:

       SUBROUTINE SET_ROWS (I)
C      ==============
       CHARACTER*(80) ROWS(11)
       COMMON /INPUT_GRID/ ROWS
       COMMON /COORDINATES/ X(10), Y(10), Z(10)
       WRITE(ROWS(I-1),900) CHAR(I+63), X(I-1), Y(I-1), Z(I-1)
   900 FORMAT ('|', A, '|', F11.3, '|', F11.3, '|', F8.3)
       END

I used F12.3 because in the UK, using National grid coordinates, the x and y values could (just) get into thousands of kilometres but no more, and are never negative, thus requiring at most, 7 places before the decimal point, the decimal point and 3 decimal places afterwards, making 11 in total. For many locations the string will contain blanks before the numeric values, but they don’t matter. Elevations range from 0 to slightly over 1000 metres, and may include some negatives but of much smaller magnitude, so with 4 characters before the point, the decimal point itself, and 3 characters after, I got to F8.3. SET_ROWS is fine if I have start values x, y and z, but if I do not, then I will need ‘||||’ as my string.

A completely blank row needs to have:

       ROWS(11) = '||||'

as an absolutely blank string won’t show at all.

14.6 Scrolling the contents of a listview control

If we supposed that there was an array of character strings in the background, each one formatted as per the elements of ROWS, then scrolling up would be effectively to replace ROWS(2) to ROWS(11) with the character strings from the background, and then CALL WINDOW_UPDATE@(ROWS). The simplicity of this must be one of the strengths of listview.

14.7 Editing cells in a %lv

The cells displayed in a list view are individually selectable, and indeed may be edited. The problem is that the changes are not persistent and once that cell loses the focus then it returns to its original contents. What must happen is that the particular row character string must be updated and once that is done it must be re-shown in the list view with CALL WINDOW_UPDATE@ (ROWS).

The first step in the callback function is to find what cell we are in. Paradoxically, the numbering begins at the second row, but, the row and column numbers are found by two calls to the CLEARWIN_INFO@ function:

       IROW = CLEARWIN_INFO@ ('ROW_NUMBER') 
       ICOL = CLEARWIN_INFO@ ('COLUMN_NUMBER')

Armed with that positional information, then if the first column is not editable, it is a matter of setting the return value to 2 and returning.

For any other cell, the next step is to examine the reason for the callback being invoked. This is done using the function CLEARWIN_STRING@ putting the result into an appropriately long CHARACTER variable CBR:

       CBR = CLEARWIN_STRING@ ('CALLBACK_REASON')

Now, you can test CBR. If it contains ‘BEGIN_EDIT’ then the callback function can return with value 2. If it contains ‘EDIT_KEY_DOWN’, then you can find the ASCII code for that key NK with:

       NK = CLEARWIN_INFO@('KEYBOARD_KEY')

If the cell is to contain an unsigned INTEGER, then the only valid keystrokes are the numerals 0 ... 9 inclusive, so if it is anything else, return from the callback, but this time with return value 4, which will discard that keypress. The ASCII numbers for the digits are 48 to 57 inclusive.

Similarly, if the cell is to contain an unsigned REAL, then once again, only the numerals 0 ... 9 are immediately acceptable with an immediate return with value 2.

However, in the case of a REAL number, a decimal point is acceptable, but only if there is a single decimal point in the cell. The same goes for + and -, which not only should only occur once, but should be at the beginning of the cell contents, regardless of whether the cell contains an INTEGER or REAL.

If you allow + and -, then they have ASCII numbers 43 and 45, and the decimal point is ASCII 46 (comma is ASCII 44 for Europeans) but you first need to obtain the currently edited text from the function:

       CBE = CLEARWIN_STRING@ ('EDITED_TEXT')

where CBE is a CHARACTER variable of an appropriate length. You can check for duplicated decimal points and correct, and similarly for the occurrence and position of positive and negative signs.

       L = LEN_TRIM (CBE)
       K = 0
       DO 10 M=1,L
       IF (CBE(M:M) .EQ. '.') K = 1   ! decimal point exists
    10 CONTINUE
       IF (NK .GE. 48 .AND. NK .LE. 57) RETURN
       IF (NK .EQ. 46 .AND. K .EQ. 0) RETURN
       KB_FOR_LV_GRID = 4      ! reject this key RETURN

If the callback reason is given as ‘END_EDIT’, then the edited text has to be (a) converted into a value, and (b) the character string for the appropriate row has to be edited appropriately and then the listview box updated using:

       CALL WINDOW_UPDATE@ (ROWS)

The numeric value of the character string in CBE can be obtained by

       READ (CBE,*) variable name

And the text to be spliced into the CHARACTER string of the appropriate row is probably not CBE, but rather the variable written into a different character string using an appropriate FORMAT. Such a procedure may not be necessary for an INTEGER, where CBE will already be the right format, but writing a REAL variable will give, for example, the correct number of decimal places. You cannot do the READ if the string is ‘’, so that would create an error if you entered a cell and skipped out without putting anything in, so:

       ELSE IF (CBR .EQ. 'END_EDIT') THEN 
       L = LEN_TRIM (ROWS(IROW+1))
       INDEX = L      ! initialise whole INDEX array
       BAND = ROWS (IROW + 1)
       KHAR = BAND(1:1)  ! find the separator
       INDEX(1) = 1     ! INDEX is an integer array long enough K = 2
       DO 20 M=2,L
       IF (BAND(M:M) .EQ. KHAR) THEN
       INDEX(K) = M
       K = K + 1 
       ENDIF
    20 CONTINUE
       IF (CBE .EQ. '') THEN
       TEXT = ' '
       GO TO 30
       ENDIF
       READ (CBE,*) VALUE
       WRITE (TEXT,'(F12.3)') VALUE
    30 CONTINUE

Finally, you need to update ROWS:

       ROWS(IROW + 1) = BAND(1:INDEX(ICOL))//TEXT//BAND(INDEX(ICOL+1):L)
       CALL WINDOW_UPDATE@ (ROWS)
       KB_FOR_LV_GRID = 2 RETURN

14.8 Fancy effects with %lv

The first column in a listview control can be enhanced with an icon or a tick box, and the appearance changed to any one of the 4 view modes. As well as INTEGER and REAL cell contents, you can enter text or even formulae, but parsing formulae is well beyond the intentions of this book. However, those enhancements are dealt with in the online help files and if you understood the foregoing, then adding those enhancements should be plain sailing.

14.9 Grids of %rs, %rd and %rf

FORTRAN and the ART of Windows Programming, Copyright © Eddie Bromhead, 2023.