Best of CF9: spreadEdit

This post is more than 2 years old.

Welcome to the first (of many) "Best of ColdFusion 9" entry reviews. The first entry is spreadEdit by Sam Farmer. This entry was reviewed by Paul Hastings. (I've added a few notes as well.)

At a basic level, spreadEdit is an Excel spreadsheet editor. However it goes beyond just simple editing. It makes use of ORM and the VFS in a way that I didn't think was possible. Each spreadsheet is parsed into an entity CFC stored in the VFS. These virtual CFCs then become database tables. I'm not sure what's going to happen after my server restarts, but it's a unique approach and it's cool to see it in action. (I literally went back and forth looking at the code and shaking my head in disbelief.)

Paul listed out what he liked about the application:

  • ease of setup making use of the gifts God gave cf9 ;-) it would have setup the derby DB automagically (if it had actually worked on our ubuntu server, see below)
  • nice wizardy approach
  • excel file upload page is nice & clean, uses new cf9 "web 2.0"-ish bits
  • data entry grids also used new cf9 "web 2.0"-ish bits
  • liked the display of spreadhseet metadata for uploaded files (nit pick that maybe shouldn't strip timezone info from date formats, but of course i would nit pick that), pretty thoughtful
  • liked that it uses the VFS for temp model/view/etc storage, nice touch & illustrates one use case (that i hadn't thought about before)
  • liked the bits to keep track of the VFS usage, though the graph was kind of crappy looking (the more flex/AIR we do, the more i notice this sort of thing)
  • use of ORM
  • use of built-in derby db
  • threw unicode spreadsheets at it & more or less worked (one rather large & complex one brought it crashing down though, not sure if it's a bug in cf9 or the app's not accounting for embedded quotes, "weird" char stuff
  • kudos for still using cf tags (i actually don't like to use them even for output anymore but gotta give him credit for going old school ;-) [Ray's Note: Paul is - I assume, talking about Sam's use of custom tags for layout. I've got a nit on that below.]
  • use of new style CFC syntax

So Paul definitely like the setup wizard, and I agree, it's pretty dang cool.

My only complaint here is that my CF Admin doesn't have a password and the application demanded I enter one. (I put something in bogus and it worked fine.)

Paul also complimented the "web 2" stuff, and they do look pretty snazzy here. Here is an example of the multi-file uploader:

and another example of the grid editor:

Paul liked the VFS lister, but not the chart, which I thought was fine.

As to what Paul didn't like...

  • linux - adminapi NOT adminAPI [Ray: Case is a bitch, isn't it? ;) You will never convince case sensitivity for files is a good thing!]
  • linux -
    • security is usually "fiercer" (or you know, better) than windows
    • the app needs more than simply copying to the web server to actually work, gotta have correct permissions on the right dir, blah, blah, blah
    • because of that, the step 2 bits never actually get uploaded & the write spreadsheet bits failed
  • "any more files" popup constantly hidden behind file datagrid, kind of a big UI no-no (another of the many reasons why we usually prefer flex front ends these days, also if flex UI maybe a nice sparkline for the VFS tracking instead of that crappy looking pie chart ;-)
  • no clean way back after viewing file on VFS, just "save" instead of a "cancel" button as well
  • not enough error trapping for things like blank metadata (eg no "last edited" bits for brand new spreadsheet)
  • didn't use the new CFC syntax everywhere

Some of my own comments in response to Paul - first off - the file permissions is kind of a big deal, especially for folks who write code for multiple operating systems. You really do have to be careful when working with the file system. Hopefully the VFS will help with that... but let me point out something I think Sam missed. He makes use of a VFS folder called "/se". What if some other application uses the same folder? One thing I really wish VFS would do differently is work at the Application RAM level, not the Server level. If I ever decided to make use of VFS for BlogCFC for example, I'd probably use a root directory based on a UUID, just to ensure I was in a unique server space.

Also - I'll nit that I don't like the fact that Sam uses custom tags for layout but has two separate files:

<cf_header title="Credits"> ... stuff here ... <cf_footer>

It may be a minor thing, but I'm much more a fan of wrapper tags:

<cf_layout title="Credits"> ... stuff here ... </cf_layout>

Ok folks - you can download the bits below. It's been a while since my last contest, but I want to remind folks of something. The point here is to play and share. Please be polite. I know Sam wants feedback, but be constructive. Thanks Sam!

Download attached file.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA

Archived Comments

Comment 1 by Kumar posted on 12/8/2009 at 7:57 AM

Very nice. I was just thinking about how something like could be done over the weekend. Use of ORM and VFS is very interesting, time to pour over the code.

Comment 2 by Mike Chandler posted on 12/8/2009 at 9:08 AM

This is a really outstanding presentation. The UI looks pretty slick based on the screenshots, and in my past web dev experience I know there are a lot of potential users for this app. I'm downloading the code now. Cheers Sam!

Comment 3 by Russ S. posted on 12/8/2009 at 1:02 PM

I like the wizardy stuff, I'll have to keep that in mind for my projects.

Comment 4 by Marko Simic posted on 12/8/2009 at 4:17 PM

Nice. Several interesting ideas introduced: creating CFCs on the fly and convert XL files to ORM objects + wizard. You can learn a lot about VFS browsing the code.

Comment 5 by Marko Simic posted on 12/8/2009 at 4:29 PM

Sam, if you are interested in bugs (and solutions) I stumbled upon while testing your app, mail me (tweet me or whatever) and I'll send you a report.

Comment 6 by Sam Farmer posted on 12/8/2009 at 7:37 PM

Thanks for all the feedback. Please do send along any errors you find to sam at samfarmer dot com. I will say I did this for fun and didn't spend too much time getting it production ready!

I plan on blogging some of the features, including some not mentioned, like using onMissingTemplate to include files that are in RAM but not on disk. A technique that could give a nice speed increase. In fact my rough testing found that putting model ORM files in RAM was 30% faster than on disk.

Comment 7 by Raymond Camden posted on 12/8/2009 at 8:12 PM

One thing that confuses me Sam - post CF restart - does your app support ORM for the tables created before?

Comment 8 by Sam Farmer posted on 12/8/2009 at 8:23 PM

Nope. Because the ORM files wouldn't exist. It was certainly something I never thought of -- I was setting it up more as a way to edit a Spreadsheet and get it back as an Excel file once done editing.

It could be done by during creation of the CFC's writing the files to disk and copying to RAM in onApplicationStart (or before actually since I believe ORM runs before onApplicationStart? )

My emphasis for this was to try out as many CF 9 features as I could rather than create something production worthy.

Comment 9 by Raymond Camden posted on 12/8/2009 at 9:52 PM

Makes sense Sam. You know... I wonder if it would drop all the tables if you used the 'dropcreate' ormsetting? If so, that would actually kind of work. It would reset the app on restart.

Comment 10 by Sam Farmer posted on 12/8/2009 at 10:04 PM

Ah yeah good point. I ran into some issue with dropcreate not removing all tables, since I believe it will only drop tables if there is a persistent cfc, and hence the code that starts on line 45 of step3.cfm.

Comment 11 by Ben Nadel posted on 12/9/2009 at 6:03 PM

Sam, the UI looks badass :) And, from what the description is, I can't wait to download the code to see how you played around with the VFS. Great looking stuff.

Comment 12 by Sam Farmer posted on 12/11/2009 at 10:58 PM

I wanted to respond to the criticism to explain what I was thinking or not as was often the case! Overall I love code reviews and a big thanks to Paul, Ray and others for providing feedback.

CFAdmin needing password:

Good point.


Paul -- Thanks for fixing it to work on Linux. I tested on Windows and Mac but don't have access to a Linux box unfortunately. With retrospect I could have put all the file writing stuff into RAM and maybe got around those issues. I should have also mentioned in the install doc that file writing was a prerequisite.

Any More Files Pop-up hidden:

This works in some browsers (Safari) and not others (Firefox, Chrome). I didn't realize until after I submitted and am bummed it didn't work as I thought it was quite slick. Probably needs some messing with a z-index. And as I mentioned about I didn't really get this production worthy just fun ready! :)

VFS File Editor:

Good point. A cancel button would be nice.

didn't use the new CFC syntax everywhere:

Not entirely sure what this means but is it the mix of script and tag based cfcs? I tried to use script everywhere unless I would need a tag thats not available in script. Though I just checked the docs and looks like I missed a few spreadsheet functions. D'oh.

Application safe folder name:

I used the folder se (for spreadedit) but yes it could easily be used by someone else. Probably the safest and best practice would be to either a) onApplicationStart use a folder named after UUID or b) have the base folder be the same as the application name? Others thoughts on this? In shared hosting this is a big issue, if not shared hosting then using a base folder the same as application name should do it.

Layout in two custom tags vs one

Advantages of two:
- can use tags like cfflush
- if the footer takes a lot of arguments somewhat nicer to provide there.

Advantages of one:
- better encapsulation of the UI chrome.

I've used both over the years and actually prefer one tag (like Ray) but used two here for some reason that escapes me now.