Generating Excel Column Names
When using the Apache POI to generate Excel formulas, you need to gain access to the corosponding column name associated with the column id, such as "A" for column 0.
I ran accross a simple solution, however this will only generate up to "Z".
visualColIDAsc = Asc("A");
visualColIDAsc = visualColIDAsc + currentColumnCount ;
visualColID = Chr(visualColIDAsc);
Unfortunatly I have more than 26 columns, so I had to come up with another solution. Here is a UDF. Just pass it the current column (remember to start with 0) and it will return the corosponding column name. This took me a good while to think this through, though it's a fairly simple concept.
<cffunction name="generateExcelColumnName" access="public" output="no" returntype="string" hint="Returns the Excel column character equivalant to the column number.">
<cfargument name="colCount" required="yes" type="numeric">
<cfscript>
var c = "";
var i = "";
var currCharAsc = "";
var incrementNextChar = true;
var lastCurrCharAsc = "";
var lastChar = "A";
var newChar = "A";
var newCharAsc = "";
var newCurrCharAsc = "";
if (arguments.colCount gt 0) {
for (c=0; c lte arguments.colCount - 1; c=c+1) {
newChar = '';
incrementNextChar = true;
for (i=1; i lte len(lastChar); i=i+1) {
// set last used character ASCII code if defined
currCharAsc = Asc(Right(lastChar, i));
// incrememnt character ASCII code if flagged to do so
if (incrementNextChar)
newCurrCharAsc = currCharAsc + 1;
else
newCurrCharAsc = currCharAsc;
// handle out of character range
if (newCurrCharAsc gt Asc("Z")) {
newCurrCharAsc = Asc("A");
incrementNextChar = true;
}
else
incrementNextChar = false;
// append character to final result
newChar = Chr(newCurrCharAsc) & newChar;
}
if (incrementNextChar)
newChar = "A" & newChar; // append character to final result
lastChar = newChar;
}
}
return newChar;
</cfscript>
</cffunction>
There are no comments for this entry.
[Add Comment]