package com.rjxx.util;
public class ExcelColumn {
/**
* 根据原位置和偏移量计算目标位置
* @param src
* @param row
* @param col
* @return
*/
public static String getLocation(String src,int row,int col) {
String rows = src.replaceAll("[^a-z^A-Z]", "");
int rs = excelColStrToNum(rows)+row;
String cols = src.replaceAll("[^0-9]", "");
int rs2 = Integer.valueOf(cols)+col;
return excelColIndexToStr(rs)+rs2;
}
/**
* 根据2个单元格位置,计算偏移量
* @param src
* @param target
* @return
*/
public static int[] getOffset(String src,String target) {
String row1 = src.replaceAll("[^a-z^A-Z]", "");
String row2 = target.replaceAll("[^a-z^A-Z]", "");
int a = excelColStrToNum(row2)-excelColStrToNum(row1);
String col1 = src.replaceAll("[^0-9]", "");
String col2 = target.replaceAll("[^0-9]", "");
int b= Integer.valueOf(col2)-Integer.valueOf(col1);
int[] ceshi = {a,b};
return ceshi;
}
/**
* 该方法用来将Excel中的ABCD列转换成具体的数据
* @param column:ABCD列名称
* @return integer:将字母列名称转换成数字
* **/
public static int excelColStrToNum(String column) {
int num = 0;
int result = 0;
int length =column.length();
for(int i = 0; i < length; i++) {
char ch = column.charAt(length - i - 1);
num = (int)(ch - 'A' + 1) ;
num *= Math.pow(26, i);
result += num;
}
return result;
}
/**
* 该方法用来将具体的数据转换成Excel中的ABCD列
* @param int:需要转换成字母的数字
* @return column:ABCD列名称
* **/
public static String excelColIndexToStr(int columnIndex) {
if (columnIndex <= 0) {
return null;
}
String columnStr = "";
columnIndex--;
do {
if (columnStr.length() > 0) {
columnIndex--;
}
columnStr = ((char) (columnIndex % 26 + (int) 'A')) + columnStr;
columnIndex = (int) ((columnIndex - columnIndex % 26) / 26);
} while (columnIndex > 0);
return columnStr;
}
public static void main(String[] args) {
String ZB1 ="AR2";
String ZB2 ="FE1";
int[] ceshi = getOffset(ZB1,ZB2);
System.out.println(ceshi[0]+"---"+ceshi[1]);
System.out.println(getLocation(ZB1,ceshi[0],ceshi[1]));
}
}