Conditional Formatting Excel File Using ColdFusion

Good question off Stack Overflow. CF provides functions to create Excel spreadsheets, change the formatting of a cell, set formulas, etc. But what about Conditional Formatting, where you want Excel to dynamically highlight a cell iff the value is outside a given range? Here, you need to dip into the underlying Apache POI library that CF hooks into.

The following will only work with CF11; earlier versions do not include a new enough version of POI. This example will color cells A1:A6 in red if the value in them is greater than 100. Assume downloadDoc is the object returned by SpreadsheetNew(), after you've filled in the data.

<cfset var poiSheet = downloadDoc.getWorkBook().getSheet("Sheet1")>
<cfset poiSheet.setFitToPage(true)>

<cfset comparison = CreateObject("java", "org.apache.poi.ss.usermodel.ComparisonOperator")>

<cfset rule = poiSheet.getSheetConditionalFormatting().createConditionalFormattingRule( comparison.GE, "100.0", javacast("null", ""))>
<cfset patternFmt = rule.createPatternFormatting()>
<cfset color = CreateObject("java", "org.apache.poi.ss.usermodel.IndexedColors")>

<cfset patternFmt.setFillBackgroundColor(javacast("short", color.RED.index))>

<cfset cellRangeAddress = CreateObject("java", "org.apache.poi.ss.util.CellRangeAddress")>
<cfset regions = [ cellRangeAddress.valueOf("A1:A6") ]>
<cfset poiSheet.getSheetConditionalFormatting().addConditionalFormatting(regions, rule)>

For your own purposes,You will probably want to modify the lines I have placed in italics. the assignment to rule defines the condition under which a cell gets highlighted. The call to patternFmt.setFillBackgroundColor sets the color that is displayed (available colors are given here). Finally, the assignment to regions tells the sheet which cells should be conditionally formatted.

incorrect response header at com.jnbridge.jnbcore.c.b.a.a

There have been many times I've found myself writing some glue code in C# to provide integration into a system that ColdFusion otherwise couldn't do. Usually, this works smoothly; I actually prefer the C# integration better than the Java integration.

But then, every so often, it crashes for no clear reason. The error message is just "incorrect response header" and the stack trace isn't much more helpful:
com.jnbridge.jnbcore.c.b: incorrect response header at com.jnbridge.jnbcore.c.b.a.a(Unknown Source) at com.jnbridge.jnbcore.DotNetSideProxy.int(Unknown Source) at com.jnbridge.jnbcore.DotNetSideProxy.objectVirtualCall(Unknown Source) at com.bitfauna.StringLonginator.StringLonginator.Embiggen() at sun.reflect.GeneratedMethodAccessor57.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:601) at coldfusion.runtime.java.JavaProxy.invoke(JavaProxy.java:126) at coldfusion.runtime.dotnet.DotNetProxy.invoke(DotNetProxy.java:38) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:2432) at
and on and on.

I'm here to help you begin the healing process. You are not alone!

Sadly, I don't have a fix. What I do have is an explanation, in the hopes that you can work around this. This error pops up (very consistently) when you are trying to return a large string back from .NET. I've written a small test program to activate the bug.

According to my tests, returning a string of length 49994 or greater will cause the crash, while anything smaller is safe. Hopefully, you can keep your string below the threshold of doom. If not, you might want to go complain to Adobe on this old thread.


Here's the CFML code:
And this is the component it calls:

Debugging queries the dirty way

What if you have some hideously complex query generation code, and you're trying to figure out exactly what that query ends up looking like? Also, you can't just dump the query object to peek at the SQL property. (Perhaps it performs an update or some other non-select query; these will result in the query variable being undefined.)

There is, it turns out, a quick and only slightly dirty answer. Use an inner cfsavecontent. To take a vastly simplified example, suppose this query that is giving you trouble:

(Complicated query code has been omitted for demonstration purposes. Just imagine a twisting nether of ifs and function calls and who knows what else.)

You could pull the body out and stick it inside a cfsavecontent. This will work, but you'll also need manually remove every cfqueryparam, as these will crash when used outside a query. But, it turns out that you can safely use them inside a cfsavecontent as long as this is done within the cfquery.

So, wrap the body of the query in the cfsavecontent. Right after that, but still within the query, output the variable you saved the content to. Otherwise, the cfquery will crash because it has nothing to send. Once you're outside the cfquery, output that sql content again, wrapped in <pre> tags for easier reading, like so:

In this case, the output was:

 update D_customer
 set customername = ?
 where pkid = ?

Notice that the cfqueryparam tags were automatically converted into question marks, and the query runs just like it did before.

Notice also that I didn't adjust the indentation of my query when I wrapped cfsavecontent around it. The expectation is that you will remove this as soon as you figure out the problem. Do not leave this stuff in your code! Get in, solve the problem, and then get it out.

cfusion_ecrypt in ruby

First off, you should not use cfusion_encrypt. It does not provide actual encryption. Even if it did, you're probably thinking of using it for storing passwords, and passwords should be hashed, not encrypted. It is not at all secure. It has even been dropped from newer versions of Coldfusion.

That said, we don't always have total control over all the systems we must interact with. Maybe you as supporting or tying into some legacy app that used cfusion_encrypt, and you need a way to decrypt. Maybe you're doing so from some other language, like Ruby. I have a present for you. Here is a ruby implementation of cfusion_decrypt. Just promise to use these powers only for good.

 def cfusion_decrypt(encrypted, key)  
   padded_key = ""  
   encrypted.length.times do |i|  
    padded_key += key[i % key.length]  
   end  
   acc = ""  
   0.step(encrypted.length-2, 2) do |i|  
    e_hex = encrypted[i, 2].hex  
    key_ascii = padded_key[i/2].ord  
    acc += (e_hex ^ key_ascii).chr  
   end  
   acc  
  end  

What about uploadAll?

Raymond Camden raised a good point: why not just use cffile uploadAll?

The reason is that uploadAll provides no facility for determining what field the uploaded file came from. It just copies every file that was submitted into the folder.

But sometimes, it matters very much where a file came from. In my case, users were primarily uploading a file (or sometimes a group of files) to the system which the system would then read through and import. At the same time, they also needed to upload an arbitrary number of documents which the system would associate with the import, but which were only there for human consumption. The way I needed to treat these two groups of files was very different, and uploadAll gave me no way to distinguish between them. UploadMultipleFiles() does.

Uploading Multiple Files Using Coldfusion, HTML5-style

You know how to upload a file with coldfusion. Add an file input tag to form, make sure the form encytype is "multipart/form-data", and then use cffile. Easy.

Ok, now what if you want to upload an arbitrary number of files? You could just show them more inputs, but HTML5 added the very nice multiple attribute to the file input tag, allowing a user to select more than one file at a time.

Sadly, ColdFusion never really supported it. Until now, that is.

Yes, dear reader, I have cracked the code. This is why I was delving into the inner workings of the form object before. I knew the information I needed was had to be accessible somewhere, it was just a matter of finding out where. I've packaged up the functionality and published it at GitHub, ready for you to use yourself.

Usage is easy. Make sure that you have the multiple attribute on your form's file upload field.

<input name="fieldname" type="file" multiple="multiple" />

Then, on the page that receives the form submission, make the following function call:

uploadedFiles = UploadMultipleFiles("fieldname", directoryToUploadTo)

The function will return an array of the paths to the newly uploaded files.

Scoped variable performance: form

In light of the speed penalty for scoping your query variables, and my recent foray into the form object, I started to wonder: Does explicitly scoping other variables have the same performance penalty?

tldr: Explicitly specifying the form scope is faster than leaving it implicit. This is the reverse of the query scope's behavior.

Here's the test code:

 <cfset form.item1=1>  
 <cfset form.item2=2>  
 <cfsilent>  
 <cftimer label="unscoped">  
     <cfloop index="i" from=1 to=1000000>  
         #item1# #item2#  
     </cfloop>  
 </cftimer>  
 <cftimer label="scoped">  
     <cfloop index="i" from=1 to=1000000>  
         #form.item1# #form.item2#  
     </cfloop>  
 </cftimer>  
 </cfsilent>  

Result: unscoped took 900ms to scoped's 560ms. The gap varied, but accessing form directly was always faster.

Now, don't go rushing to change all your old code. It took me 2 million accesses to make a difference of half a second. This is not going to be a drain on your application unless you're inside a very troublesome loop.

For what it's worth, I also ran the same test against url instead of form, with the same result. The query scope appears to be unique in its sloth.