Replicating a spreadsheet application using CFForm.

While I'm waiting for System Mechanic to find all the bits of Fiddler and uninstall it, so that I can reinstall it, I though I would post an note on some CFForms stuff I've been doing recently.

The client wanted to allow users to be able to click on a cell in a grid, the value in that grid to be displayed in an edit box, in much the same way as it does in the likes of Excel, and the user be able to edit that cell either in the cell or in the edit box.

Sounds simple enough. The problem comes when you try to work out which column is currently selected. CFGrid will give you the currently selectedIndex of the row in the grid and all the columns in that row, but not the column of the cell that has been selected.

In order to find out what is in the currently selected cell I had to add an event listener to the grid when the form is loaded and wait for a cell to be selected. This is the cellFocusIn event.

I have a function called onFormLoad() {...} which I call via cfform's onload attribute. In this function I have 2 global variables, _global.currentCol and _global.currentRow. I default these to '' and 0 respectively.

I then create a DataGrid in my onLoadForum that point to my cfgrid :

view plain print about
1var datasheet:mx.controls.DataGrid = sheet; // sheet is the name of my cfgrid in the form.

I then create a listener object :

view plain print about
1var listener:Object = {};

And create a function for when I get a "cellFocusIn" event.

view plain print about
1listener.cellFocusIn = function(evt):Void {...}

This event has a number of attributes and functions, but I'm really only interested in the column related attributes and functions, specifically columnIndex and getColumnAt().

In my cellFocusIn function I get the row and column details and put them into my global variables and then use that information to file my "edit box".

view plain print about
1//current grid row +1 for display purposes
2var thisRow = evt.itemIndex+1;
3// shorthand for the whole cfform
4var thisForm = _root.chartsheet;
5// evt.ColumnIndex contains a number which when passed to the method getColumnAt() returns the text name of a column as one of its attributes.
6var thisCol = datasheet.getColumnAt(evt.columnIndex);
7// set the global variables to the selected row and the column name
8_global.currentRow = evt.itemIndex;
9_global.currentCol = thisCol.columnName;
10// change a text formitem to display the current row and column name
11thisForm.thisCellNo = thisCol.columnName+' '+thisRow;
12// set my edit box to be the contents of the currently selected cell.
13if (datasheet.selectedItem[thisCol.columnName] != null)
14 thisForm.thisCell = datasheet.selectedItem[thisCol.columnName];

The last thing to do is to attach this listener to the grid. This is done using the method addEventListener()

view plain print about

So now when a user selects a cell, the column name and row number are stored in the global variables, a display of row and column is done and an edit box is filled with the content of the selected cell.

Here is the complete onload function:

view plain print about
1function onFormLoad() {
2 _global.currentCol = '';
3 _global.currentRow = 0;
5 var datasheet:mx.controls.DataGrid = sheet;
6 datasheet.hScrollPolicy = 'on';
8 // listener for the grid, so that I can tell which cell has been selected and I can populate the edit box.
9 var listener:Object = {};
11 listener.cellFocusIn = function(evt):Void {
12 var thisRow = evt.itemIndex+1;
13 var thisForm = _root.chartsheet;
14 var thisCol = datasheet.getColumnAt(evt.columnIndex);
15 _global.currentRow = evt.itemIndex;
16 _global.currentCol = thisCol.columnName;
17 thisForm.thisCellNo = thisCol.columnName+' '+thisRow;
18 if (datasheet.selectedItem[thisCol.columnName] != null)
19 thisForm.thisCell = datasheet.selectedItem[thisCol.columnName];
20 }
21 datasheet.addEventListener('cellFocusIn',listener);
22 }

and the form it belongs to :

Here's the dataUpdate function that is attached to the fieldeditor form item.

view plain print about
1function dataUpdate() {
2 // updates the grid when the user types into the edit box
3 var datasheet:mx.controls.DataGrid = sheet;
4 var newdata = _root.chartsheet.fieldeditor;
5 if (_global.currentCol != '') {
6 datasheet.editField(_global.currentRow,_global.currentCol,newdata);
7 }
8 }

I have to be honest, I have chopped some of the form content out in order to protect the innocent ;) and I haven't tested that the form displays correctly, but the onFormLoad() function is unchanged. I'll sort out a working example and a download of the source soon.

I am trying to do the same thing as this example shows. It would be great if you could pass on an example. I see the function, but how are you calling it? are you using cfsavecontent, cfformitem type = script or cffunction?

I can call it using cfform onload, but where do you store the actual action script code? I can't seem to get it to work the way i want it to work.

thanks for any help
# Posted By Dan Fredericks | 3/13/08 9:39 PM

Sorry for the slow response - unfortunately you posted your comment the day after I went on holiday and I'm not actually back in the UK yet.

All the actionscript goes in as a cfformitem - its just so much neater and can squeeze so much more into your flash form if you use cfformitem rather than the cfsavecontent method.

What is it thats not working the way you expect?

When I get back to the UK I'll dig out the example I did and get it posted.
# Posted By StephenM | 3/13/08 9:39 PM
I know this is an old post, but can you show an example of how "All the actionscript goes in as a cfformitem". I've tried to add a listener

var listener:Object = {};
listener.cellFocusIn = function(evt):Void {...}

several different ways but keep getting the error:

"This statement is not permitted in a class definition."
# Posted By jM | 3/13/08 9:39 PM
After all this time I've finally added a working example. You can download it by right clicking on the download button and "Save As" (I forgot to zip it up when I uploaded it), you can also se it in action here :

I'm not really sure why you're getting that error jM, but hopefully the example will help
# Posted By Stephen Moretti | 3/13/08 9:39 PM
Thanks for the example. The syntax error went away so my syntax must have been off.

I tried the online example, but it doesn't seem to allow or detect cell clicks. Like I tried to click on cell B4 but nothing happens.
# Posted By jM | 3/13/08 9:39 PM
The error with Internet Explorer is:

Line: 30
Char: 1
Error: lc_id is undefined
Code: 0

With Firefox:

lc_id is not defined
top.getSearch is not a function
updateHiddenValue is not defined
# Posted By jM | 3/13/08 9:39 PM
That's embarrassing!
On my server I keep a separate folder for CFIDE/Scripts, so that I don't have to map to the main administrator folder in CF. I hadn't actually set up the mapping on my blog! The example should be working now.
# Posted By Stephen Moretti | 3/13/08 9:39 PM
No big deal. It works perfectly now :) Thanks for posting it.
# Posted By jM | 3/13/08 9:39 PM