CFLib.org – excelColumnNumtoColumnName

excelColumnNumtoColumnName(columnName)

Last updated May 3, 2010

author

Version: 1 | Requires: ColdFusion 5 | Library: UtilityLib

Rated 1 time(s). Average Rating: 5.0

Description:
Converts a numeric column position (i.e. 127) to its Excel Column Name (DW). Assumes column numbers are 1-based (1=A,2=B,...)

Return Values:
Returns a string.

Example:

view plain print about

column 1 is #ExcelColumnNumtoColumnName(1)#<br/>
column 18 is #ExcelColumnNumtoColumnName(18)#<br/>
column 100 is #ExcelColumnNumtoColumnName(100)#<br/>
column 653 is #ExcelColumnNumtoColumnName(653)#

column 1 is #ExcelColumnNumtoColumnName(1)#<br/>
column 18 is #ExcelColumnNumtoColumnName(18)#<br/>
column 100 is #ExcelColumnNumtoColumnName(100)#<br/>
column 653 is #ExcelColumnNumtoColumnName(653)#

Parameters:

Name	Description	Required
columnName	Numeric column number.	Yes

Full UDF Source:

	
	
			
			
			
				
				view plain
				
				
				copy to clipboard
				
				
				
				
				print
				
				
				about
				ColdFISH is developed by Jason Delmore.  Source code and license information available at coldfish.riaforge.org.  Version 3.1.1
				
			<cfscript>
/**
 * Converts a numeric column position to its Excel Column Name
 * 
 * @param columnName 	 Numeric column number. (Required)
 * @return Returns a string. 
 * @author Adam Tuttle (&#97;&#100;&#97;&#109;&#64;&#102;&#117;&#115;&#105;&#111;&#110;&#103;&#114;&#111;&#107;&#107;&#101;&#114;&#46;&#99;&#111;&#109;) 
 * @version 1, May 3, 2010 
 */
function excelColumnNumtoColumnName(columnNumber){
	var dividend = fix(arguments.columnNumber); //make sure input is an integer
	var columnName = '';
	var modulo = 0;
	//if dividend <= 0, an empty string will be returned
	while (dividend gt 0){
		modulo = (dividend - 1) mod 26;
		columnName = "#chr(65 + modulo)##columnName#";
		dividend = fix((dividend - modulo) / 26);
	}
	return columnName;
}
</cfscript>
		<cfscript>
/**
 * Converts a numeric column position to its Excel Column Name
 * 
 * @param columnName      Numeric column number. (Required)
 * @return Returns a string. 
 * @author Adam Tuttle (adam@fusiongrokker.com) 
 * @version 1, May 3, 2010 
 */
function excelColumnNumtoColumnName(columnNumber){
    var dividend = fix(arguments.columnNumber); //make sure input is an integer
    var columnName = '';
    var modulo = 0;
    //if dividend <= 0, an empty string will be returned
    while (dividend gt 0){
        modulo = (dividend - 1) mod 26;
        columnName = "#chr(65 + modulo)##columnName#";
        dividend = fix((dividend - modulo) / 26);
    }
    return columnName;
}
</cfscript>