因项目需要,写了递归实现POI——excel英文字母列转列编号,记录。
规则1:excel英文字母列转数字编号;A→0,B→1,Z→25,AA→26,ZZ→701,AAA→702 …
public int calcLetterToNum(String letter){
// 当为A-Z列时,由ASCII码减65即可,即 A - 65
if ( letter.length() == 1 ) return letter.charAt(0) - 65 ;
// 当大于Z列时,为阶乘 (A - 64)*26^n!
return ( letter.charAt(0) - 64 ) * (int)Math.pow( 26, letter.length() - 1 ) + calcLetterToNum( letter.substring(1) );
}
规则2:excel列数字编号转英文字母;0→A,1→B,25→Z,26→AA,701→ZZ,702→AAA …
public int calcNumToLetter(int num){
if ( num < 26 ) return String.valueOf( (char)( (num % 26) + 65 ) );
return calcNumToLetter( (num / 26) -1 ) + (char)( (num % 26) + 65 );
规则3:顺序号转英文字母;1→A,2→B,26→Z,27→AA,702→ZZ,703→AAA …
public int calcIndexToLetter(int num){
if ( num < 27 ) return String.valueOf( (char)( num + 64 ) );
int firstChar = ( num % 26 ) == 0 ? 26 : ( num % 26 );
num = ( num % 26 ) == 0 ? ( num - 1 ) : num;
return calcIndexToLetter( num / 26 ) + (char)( firstChar + 64 );