meidu2021/daju/target/classes/cn/com/lzt/budget/data/util/BudgetExcelUtil.java

package cn.com.lzt.budget.data.util;

import org.apache.commons.lang.StringUtils;
import org.codehaus.groovy.runtime.dgmimpl.arrays.IntegerArrayGetAtMetaMethod;
import org.springframework.data.util.Pair;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class BudgetExcelUtil {


//如输入A12获得是第12行的。
    public static int row(String cellName) {
        Pattern p = Pattern.compile("\\d+");
        Matcher m = p.matcher(cellName);
        Integer row = null;
        while (m.find()){
            row =  Integer.valueOf(m.group());
            row--;
        }
        if(row==null){
            System.out.println(cellName);
        }
        return row;
    }

    //通过单元格名获得列号。如输入AB12，对应的列号为28列
    public static int column(String cellName) {
        int column=0;
        char[] c=cellName.toUpperCase().toCharArray();
        int index=0;
        while(index<c.length)
        {
            if(c[index]<'A'||c[index]>'Z')
                break;

            column=column*26+(c[index]-'A'+1);
            index++;
        }
        return column-1;
    }

    //通过给定一个行号，列号，得到一个单元格名称如给定12行，28列；得到的是AB12
    public static String cellName(int row,int col){
        row++;col++;
        ArrayList<Character> list=new ArrayList();
        while(col>0)
        {
            list.add((char) ((col-1)%26+'A'));
            col=(col-1)/26;
        }
        StringBuffer buffer=new StringBuffer();
        for(int i=list.size()-1;i>=0;i--)
            buffer.append(list.get(i));
        buffer.append(""+row);
        return buffer.toString();
    }

    /**
     * 比如，通过A1:B3返回的是A1,B1,A2,B2,A3,B3
     * @return
     */
    public static List<String> cellNames(String cellStr)
    {
        if(cellStr==null)
            return null;
        cellStr = StringUtils.remove(cellStr,"$" );
        String[] splits=cellStr.split(":");
        if(splits.length>=3) {
//            throw new IllegalArgumentException("输入格式有误!  多个:"+cellStr);
            return Collections.emptyList();
        }else if (splits.length==2) {
            int startrow = row(splits[0]);
            int startcolumn = column(splits[0]);
            int endrow = row(splits[1]);
            int endcolumn = column(splits[1]);
            if (startrow > endrow || startcolumn > endcolumn)
                throw new IllegalArgumentException("输入格式有误!   请保证左上->右下");
            ArrayList<String> list = new ArrayList<>();
            for (int i = startrow; i <= endrow; i++)//保证先行后列
                for (int j = startcolumn; j <= endcolumn; j++) {
                    list.add(cellName(i,j));
                }
            return list;
        }else {// ,分割的多列 或单列
            ArrayList<String> list = new ArrayList<>();
            splits = cellStr.split(",");
            for(int i=0;i<splits.length;i++){
                Integer row = row(splits[i]);
                Integer col = column(splits[i]);
                list.add(cellName(row,col));
            }
            return list;
        }
    }
    public static void main(String[] args) {
    }
}