The Excel column number and letter Conversion Function in Java, javaexcel

The Excel column number and letter Conversion Function in Java, javaexcel

This example describes how to convert the Excel column numbers and letters in Java. We will share this with you for your reference. The details are as follows:

When importing and exporting Excel files, we often need to provide users with accurate information and prompt the specific Excel cells, here we need to convert the numbers and letters in the Excel column number. Today we are using this requirement, so we wrote a demo to summarize:

Java implementation:

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;  }}

Test results:

‘AA' column index of 2726 column in excel of Z‘AAAA' column index of 18279466948 column in excel of ZNSN