Two utility ColdFusion UDF

 

While working for the ExcelProcessor utility  I required a function to convert the Excel cell reference to the number. ie. for a cell reference A17, I had to refer data in 17 row, 1 column. similarly AC6 would be 6 row, 29 column. To accomplish this I wrote a UDF that took the alphabets representing Excel column and returned number.

Here is the method code, in case you bump across the need to do something similar

<cfscript>
  function convertExcelCRtoNumber(cr) {
  var intVal =0;
  var charVal ="";
  var j=0;
  var t=0;
  if(cr.length() > 2) {  return -1; }
  cr= ucase(cr);
  for(i=cr.length()-1; i >=0;i--)  { 
    charVal = cr.charAt(i);
    t = iif(j >0,  (charVal-64)* (j*26), charVal-64);
    intVal += t;
    j++;
  }
  return val(intVal);
}
</cfscript>

So now <cfoutput>#convertExcelCRtoNumber(‘AZ’)#</cfoutput> would give 52 and <cfoutput>#convertExcelCRtoNumber('IV')#</cfoutput> would give 256. Since Excel has the maximum limit of 256 columns i.e till IV I am limiting the string to 2 chars in length. To be able to return correct number in the series for more than 2 char string like AAB, ZZZ was something I was not able to achieve easily and since the above function met my immediate requirement I didn’t spent much of my grey matter figuring that out. Though that is something I  keep it as a exercise for readers ;-)

Now out of curiosity, I tried to do the reverse of this, i.e a function that will return Excel style column reference given a number and this is what I could manage

<cfscript>
  function convertNumbertoExcelCR(colnum){
    var colname="";  
    var div = colnum \ 26;
    var offset =  colnum mod 26;
   if(colnum > 256){
     return "invalid";
   }  
   if(offset == 0) { offset = 26; --div;}
   colname =chr(64+offset);
   if(div){
    colname=chr(64+div) & colname;
   }
  return colname;
  }
</cfscript>

<cfoutput>#convertNumbertoExcelCR(40)#</cfoutput> would give AN and <cfoutput>#convertNumbertoExcelCR(79)#</cfoutput> would give CA. Again this UDF will limit the number to 256. This method again will not give the expected cell reference if the number goes big enough to correspond to 3 char string ie AAA et al.

I trying posting these 2 UDFs on cflib.org but was unsuccessful due to some error on the site. Hopefully will be able to upload it sometime.