poi Excel 列号和数值互转

import com.google.common.collect.ImmutableList;
import org.apache.commons.lang3.StringUtils;

import java.util.Optional;

/**
 * excel工具
 * @since 2021-11-02
 */
public class ExcelUtils {

	private static final ImmutableList<Character> xlsCellColumnList = ImmutableList.of('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N',
		'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
	/** 列字母数 */
	private static final Integer COLUMNS_LETTER_SIZE = 26;

	/**
	 * 将列好转成十进制数
	 * @param columnAddress
	 * @return Integer
	 */
	public static Integer getCellColumnNumber(String columnAddress) {
		//数据反转后才是数据正确的位子
		columnAddress = StringUtils.reverse(columnAddress);
		char[] chars = columnAddress.toCharArray();
		int result = 0;
		for (int i = 0; i < chars.length; i++) {
			if (i == 0) {
				result = result + xlsCellColumnList.indexOf(chars[i]);
			} else {
				result = result + (xlsCellColumnList.indexOf(chars[i]) + 1) * Double.valueOf(Math.pow(COLUMNS_LETTER_SIZE, i)).intValue();
			}
		}
		return result + 1;
	}

	/**
	 * 解析获得单元格地址
	 * @param cellAddress
	 * @return int[0]: 列  int[1]: 行
	 */
	public static int[] getCellAddress(String cellAddress) {
		int rowNumber = Integer.parseInt(cellAddress.replaceAll("[A-Z]", ""));
		int columnsNumber = getCellColumnNumber(cellAddress.replaceAll("\\d", ""));
		return new int[]{columnsNumber, rowNumber};
	}

	/**
	 * 将列行转地址
	 * @param columnNumber
	 * @param rowNumber
	 * @return String
	 */
	public static String getCellAddress(Integer columnNumber,Integer rowNumber){
		StringBuilder sb = new StringBuilder();
		int remainder;
		do{
			//最右侧字母，无余数说明为最高位也就是 Z
			remainder = Optional.of(columnNumber % COLUMNS_LETTER_SIZE).map(t -> t !=0?t:COLUMNS_LETTER_SIZE).get();
			//获得字母
			sb.append(xlsCellColumnList.get(remainder-1));
			columnNumber = (columnNumber - remainder) / 26;
		}while (columnNumber != 0);
		return sb.reverse().append(rowNumber).toString();
	}

	/**
	 * 将列行转地址
	 * @param intArr
	 * @return String
	 */
	public static String getCellAddress(int[] intArr){
		return	getCellAddress(intArr[0],intArr[1]);
	}
}