Utility Functions: Two UDFs to Make Spreadsheets Easier

I started diving into using ColdFusion's capabilities to work with Excel spreadsheets for the first time today. Even though its been a part of ColdFusion since CF9 dropped in 2009, I've not been called on to use the functionality, so I'm a bit behind the curve. Once I got moving though, I found some annoyances and gotcha's that drove me to come up with a couple of new UDFs to ease my pain. Find SpreadsheetExpandedInfo() and SpreadsheetGetData() after the jump.

 


Utility Functions: Geometry Functions

Today we've got a double header of functions dealing with Geometry. As I said in my last post, I've been spurred into action by a user's question, and in order to solve his problem, I'm going to need to be able to calculate some relatively simple geometry. Click "more" to see how to convert Degrees to Radians and how to calculate the position of a point based on a starting location, angle, and distance.

 

Utility Function: RoundToClosest()

Its been a while! I got an email from a reader who wanted some help with a bit of image manipulation that has roused me back into CF coding! The particular bit of functionality he wants is rather complex, and so its been forcing me to make a bunch of utility functions to piece it together. First thing I ran into: rounding a number in CF is a little tricky. If you want to round to 2 decimal places, you should multiply the number by 100, call Round() and then divide by 100 again. What a waste. Luckily a little trickery with NumberFormat() can get us what we want.

 

Utility Functions: REExtractAll() and REExtractAllNoCase()

Since it had been so long since I released utility functions, I'm doing another double header today! Not that its really that hard since these two are exactly the same... but any way, continuing where yesterday's REFindAll() and REFindAllNoCase() left off, these two new functions actually return the matching values!

 

Utility Functions: REFindAll() and REFindAllNoCase()

Its been a while since I released a utility function, so today I bring you a very closely related pair that solve a very simple problem thats missing with ColdFusion's Regular Expression functions. Given a regular expression in ColdFusion, you can find out if there is a match for that regular expression in a given string, and where it is, but you can't find out the location of ALL of the values that match the regular expression.

 

Using ColdFusion to Zip Individual Files

I ran into an interesting situation this morning that didn't have an obvious answer. I needed to create a zip with a few specific files out of a directory with lots of possible files. The problem is that <cfzip /> is designed to work against whole directories, so at first I kept thinking that I'd have to make a temporary directory, move the files to it, and then zip the directory, but a little more persistence found the right answer.

 

More Entries

Jon Hartmann, July 2011

I'm Jon Hartmann and I'm a C# .Net developer by day, a ColdFusion guru by night, and all around Javascript fanatic. Stay right here to read my technical posts as I grapple with mysterious error messages, user interface design questions, and all things baffling and irksome about programming for the web. Learn more about me.

Post a job. Find one. authenticjobs.com

Interested in becoming a sponsor? Contact me.