Posted At : January 11, 2007 12:00 PM | Posted By : todd sharp
Related Categories:
ColdFusion,
Excel Hacks
I blogged a while back about using the XML Spreadsheet schema to create spreadsheets in ColdFusion. I believe Ben Nadel has posted a few posts about this recently too. A coworker was using this technique today and found an issue when trying to output dates. Here's the issue:
He was using the ss:Type of 'DateTime' and was noticing that Excel didn't like the following value:
<Data ss:Type="DateTime"><cfoutput>#dateFormat(now(), "mm/dd/yy")#</cfoutput></Data>
I suspected that Excel was being picky about the format of the date and did not recognize mm/dd/yy as such. I confirmed by changing the Type to 'string' - but that didn't quite solve the problem to my liking. I decided to do a quick test by creating a blank spreadsheet, entering a date, applying a mask in Excel and saving as XML. Viewing the generated XML told me that excel was expecting the following format:
yyyy-mm-ddThh:mm:ss.l
With that in mind I whipped up this super easy UDF to handle the excel date formatting:
<cffunction name="excelDateFormat" output="false" hint="i take a cf date and format it as excel friendly" access="public" returntype="string">
<cfargument name="theDate" type="date" required="true" hint="the date to make excel friendly">
<cfreturn dateFormat(arguments.theDate, "yyyy-mm-dd") & "T" & timeFormat(arguments.theDate, "hh:mm:ss.l") />
</cffunction>
Posted At : December 1, 2006 3:52 PM | Posted By : todd sharp
Related Categories:
Excel Hacks
Every once in a while I get an Excel file that I need to pull data from to use as an in list for a query. I've blogged before about a cool technique that I use to do this (the quick and dirty method). Well, someone just recently requested my help in creating a quick in list out of several hundred elements for use in running a Business Objects report (On a side note - I hate Business Objects). I would have normally used my method, but the only problem with that method is that you have to explicitly tell Excel which cells to concatenate like so:
=concatenate(a1,b1,c1...z1)
Also, there is a limit as to how many cells you can concatenate by default in Excel (30).
So, feeling especially lazy today I decided to Google a solution and found this pretty cool answer. It's a custom Excel function that allows you to specify a range of cells to concatenate. Just paste this macro into your project (instructions on where to stick it are here) and call it like so (the second arguments is optional, but will insert a delimiter between each item):
=multicat(a1:z1, ",")
That's it for me for the week. I'm off to the wife's Christmas party.
