Java实现的Excel列号数字与字母互相转换功能

时间:2021-12-03 07:21:51

本文实例讲述了Java实现的Excel列号数字与字母互相转换功能。分享给大家供大家参考,具体如下:

我们在实现对Excel的导入导出的时候,往往需要准确的给用户提示信息,提示到具体的Excel的单元格,这里就需要对Excel的列号进行数字和字母的转换,今天正好用到这个需求,所以就写了一个demo,总结一下:

Java实现:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
package test;
/**
 * Deal with Excel column indexToStr and strToIndex
 * @author Stephen.Huang
 * @version 2015-7-8
 */
public class ExcelColumn {
  public static void main(String[] args) {
    String colstr = "AA";
    int colIndex = excelColStrToNum(colstr, colstr.length());
    System.out.println("'" + colstr + "' column index of " + colIndex);
    colIndex = 26;
    colstr = excelColIndexToStr(colIndex);
    System.out.println(colIndex + " column in excel of " + colstr);
    colstr = "AAAA";
    colIndex = excelColStrToNum(colstr, colstr.length());
    System.out.println("'" + colstr + "' column index of " + colIndex);
    colIndex = 466948;
    colstr = excelColIndexToStr(colIndex);
    System.out.println(colIndex + " column in excel of " + colstr);
  }
  /**
   * Excel column index begin 1
   * @param colStr
   * @param length
   * @return
   */
  public static int excelColStrToNum(String colStr, int length) {
    int num = 0;
    int result = 0;
    for(int i = 0; i < length; i++) {
      char ch = colStr.charAt(length - i - 1);
      num = (int)(ch - 'A' + 1) ;
      num *= Math.pow(26, i);
      result += num;
    }
    return result;
  }
  /**
   * Excel column index begin 1
   * @param columnIndex
   * @return
   */
  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;
  }
}

测试结果:

?
1
2
3
4
‘AA' column index of 27
26 column in excel of Z
‘AAAA' column index of 18279
466948 column in excel of ZNSN

希望本文所述对大家java程序设计有所帮助。

原文链接:http://blog.csdn.net/u010571844/article/details/46806265