Microsoft Whiteboard Excel xls column number number to letter

Source: Internet
Author: User

Excel xls column number number to letter 53432817?utm_source=itdadao&utm_medium=referral

Recently encountered in the exported XLS column is generated dynamically, and the cells need to use a formula, and the XLS formula is not a numeric column number instead of a column letter, you need to convert the number column number to the corresponding letter of the column. Since it is exported one months up to 31 days per month, the first approach is to define an array that contains 1 to 31 columns of letters. It's not always a way to think about it. In case the number of columns is more and uncertain. Then we studied how to turn the XLS number column number into the corresponding letter.

First look at the law of letters in XLSX. In XLS, 1 to 26 columns are a~z, starting with the 27th column is a combination of more than 2 letters AA AB ... AZ then to BA BB ... BZ until ZA ZB ... ZZ at this point the two-letter combination is finished next to the 3-letter AAA AAB ... Aaz then to ABA ABB ... ABZ until Azz and then Baa BAB ... Bzz Last to Zaa ... ZZZ, followed by a 4-letter combination so loop ...?

Then found the law: Aa~az is in front of A~z added A,ba~bz is in front of the A~z plus B, ... Za~zz is in front of the a~z added Z, this time two letters combination is completed, to 3 letters Aaa~aaz is in front of Aa~az added A,aba~abz is in front of Ba~bz added a, ... Aza~azz is in front of za~zz add a, then aa~zz traversed once (here Aa~zz is the first two letters in the combination appeared); next to the next round of traversal in front plus B,baa~baz is in front of the Aa~az added B, ... Bza~bzz is in front of the Za~zz added B, then aa~zz again traversed once, and then the next round of traversal until all the two-letter combination before all add a~z, then the combination of 3 letters are all finished. The next to the 4-letter combination is similar to the previous 2, 3-letter combinations, each preceded by a a~z on the previous combination. Such as

The letter combination starts with the 27th column and each time the combination is preceded by a a~z on the previous combination. For example, calculate the combination of n=3 letters AAA~ZZZ (Red vertical Arrow part): The 2-letter combination is in the range of Aa~zz (the Red "Currentlen" section), the position of the current array (such as AAB) I and Currentlen to take the remainder operation (i% Currentlen) The result is a string concatenation of which combination of 2 letter combinations (the value is also added to the "Lastlen" (red Lastlen) to locate the corresponding letter), such as the current is the first round of traversal is a and ab stitching into AAB, This is the letter (AAB) corresponding to the current array position for the example above. Let's say we use the variable LETTERIDX to represent the first few rounds, and each last combination iterates through the LETTERIDX to +1, indicating the next letter to be stitched in front. Because stitching in the front of the letter is a~z so every time to take the wheel letter with 26 to take the remainder (letteridx%26), when Letteridx%26=0 explained A~z are combined with the last combination of the current n-letter combination of all the cases have been combined, then the next round n+ 1 (4) Letters of the combination (black vertical arrow part), then currentlen into the last combination of length (black Currentlen), and Lastlen became "the last combination of Currentlen+lastlen" (Black Lastlen part), the next loop traversal is the same as the previous combination.

On the code: (Comments in the code, as noted above, the "combination of situations" mentioned in the note refers to a combination of this combination, such as BK, AH, Xi are 2 letter combinations of a case. Because I really don't know which word to use to express it.

Public final class Columns {

Private Columns () {}private static string[] sources = new string[]{"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"};/** * (*.xls, 16384 for *.xlsx) * @param columnnum column At least 1 * @throws illegalargumentexception if columnnum exceeds the range [1,16384] * @return return [1,columnnum] A total of columnnum corresponding to an array of XLS column letters */public static string[] getcolumnlabels (int columnnum) {if (columnnum<1| |    columnnum>16384) throw new IllegalArgumentException ();    string[] columns = new String[columnnum];        if (columnnum<27) {//less than 27 columns without combination system.arraycopy (sources, 0, columns, 0, columnnum);    return columns;   } system.arraycopy (sources, 0, columns, 0, 26);    The first 26 columns do not need to be combined//because the array is based on 0, each to a new round of letteridx will increment, so the first round before incrementing is -1 int letteridx = 1;    int Currentlen = 26;//The first-round combination (2-letter combination) is the concatenation of a-Z, so it is an int remainder;    int lastlen = 0;  Used to locate the above mentioned I%currentlen actual position in the array int totallen = 26; Totallen=currentlen+lastlen int currentloopidx = 0; The number of all cases used to record the current combination for (int i=26;i<columnnum;i++) {//27th column (corresponding to the 26th position of the array)//currentlen is the number of all cases in the last combination.        And it takes the remainder to find the case to be combined with the last combination remainder = Currentloopidx%currentlen;        if (remainder==0) {letteridx++;//completion of a previous combined traversal, go to the next letter to stitch int j = letteridx%26; A-Z 26 sub-mothers are all stitched together with the previous combination, requiring the next combination of stitching if (j==0&&letteridx!=0) {lastlen = Totallen;// The next combination of Lastlen is the last combination of Totallen/** * The next combination of Currentlen is the number of all combination cases of the last combination * (equals the last combination of currentlen*26                ), 26 is the number of a-Z stitching in front */Currentlen = 26*currentlen; Totallen = Currentlen+lastlen; Prepare for the start of the next round currentloopidx = 0; To the next round so need to reset}}/** * sources[letteridx%26] is the round to be stitched in front of the letter * Columns[remainder+la        Stlen] is the case of the last combination of splicing/columns[i] = Sources[letteridx%26]+columns[remainder+lastlen]; Currentloopidx++; } return columns;}

}
Test:
public static void Main (string[] args) {
string[] Columns = getcolumnlabels (37);
System.out.println ("1 to 37 columns:" +arrays.tostring (columns));
System.out.println ();
Long start = System.nanotime ();
columns = Getcolumnlabels (256);
System.out.println ("Create +columns.length+" column time (nanoseconds): "
+ (System.nanotime ()-start));
System.out.println ("xls" +columns.length+ "column:"
+columns[columns.length-1]);
System.out.println ();
Start = System.nanotime ();
columns = Getcolumnlabels (16384);
System.out.println ("Create +columns.length+" column time (nanoseconds): "
+ (System.nanotime ()-start));
System.out.println ("xlsx" +columns.length+ "column:"
+columns[columns.length-1]);
}
Print:
1 to 37 columns: [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, AA, AB, AC, AD, AE, AF, AG, AH, AI, AJ, AK]

Create 256-column time (nanoseconds): 192833
XLS column NO. 256: IV

Create 16384-column time (nanoseconds): 9574147
xlsx 16,384th Column: XFD
------------------------------------------------------Divider Line-----------------------------------------------------------------

Later it was thought that if you do not need (or the condition is not allowed) to create (such a large) array in advance, then you need to get the column number directly corresponding to the letter. So how to convert ...
First look at the number from the letter, such as BGQCV into a number (although the last list of xlsx is XfD, here only to discuss the number and the letter of the mutual transfer): The column is labeled with 5 letters, indicating that there are 4 letters, 3, 2, 1 letters of the full combination of the first to 5 letters of the combination, so w1=26^4+26 The first letter of ^3+26^2+26^1,BGQCV is B, which indicates that there is a axxxx in front of the whole combination, so there is the number of the combination t1=1(26^4). Then the second letter is G, which indicates that there is a baxxx~bfxxx in front of the whole combination, so there is the combination number t2=6(26^3). The third letter is Q, which shows that there are bgaxx~bgpxx in front of the whole combination, so there is the number of the combination t3=16(26^2). The fourth letter is C, which indicates that the BGQAX~BGQBX is already in full combination, the number of t4=2(26^1). The last letter is V, indicating that the prefix is a combination of BGQC BGQCA~BGQCV a total of t5=22.
All right, BGQCV. Number of columns w=w1+t1+t2+t3+t4+t5= (26^4+26^3+26^2+26^1) +1(26^4) +6(26^3) +16(26^2) +2(26^1) +22 = 1048576.
string[] sources = new string[]{"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 "};
The array sources is the source of the letters we use to solve the problem (you can also do this without defining an array, but using ASCII code).
Now that we know how to find out the corresponding column by alphabet, we generalize the question: we can find the column corresponding to the columns in column W. The corresponding letter in column W is N, according to the above analysis, w1=26^ (n-1) +26^ (n-2) +26^ (n-3) +...+26^2+26^1. We stipulate that a corresponds to 1,b 2 ... Z corresponds to 26. Set the 1th letter corresponding to the number is num_1, the second letter corresponds to the number is num_2, ..., the nth letter corresponds to a number num_n. According to the above analysis there is t1= (num_1-1)26^ (n-1), t2= (num_2-1)26^ (n-2), ..., tn= (num_n-1)26^0.
Integration, and because 26^0=1 then have w=w1+t1+t2+...+tn= (26^ (n-1) +26^ (n-2) +26^ (n-3) +...+26^2+26^1) + (num_1-1)
26^ (n-1) + (num_2-1)26^ (n-2) +...+ (Num_ (n-1)-1)26^1+ (num_n-1).
Except for the number num_n-1 the rightmost letter (that is, the nth letter), the number of positions corresponding to the other letters is a multiple of 26. So, the first time W to 26 w%26 The result of the corresponding letter (remember we use 1 for a,2 to represent B, ..., 26 for z) is the rightmost letter. Well now the right letter has been found, and left n-1 an unknown letter, the same way can be found on the right side of the letter: W (num_n-1) and then divided by 26, resulting in the result minus 1 (because there is 26^1 in W1, the result is 1 after dividing by 26, In order to ensure that in addition to the second right letter corresponding to the position of the number of the other letters corresponding to the position of the number is a multiple of 26, the result is assigned to the W, then the letter to the right of the second is similar to the letter to the right:
W (num_n-1) = (26^ (n-1) +26^ (n-2) +26^ (n-3) +...+26^2+26^1) + (num_1-1)26^ (n-1) + (num_2-1)26^ (n-2) +...+ (Num_ (n-1)-1)26^1
Then divide the sides by the sum: (w (num_n-1))/26= (26^ (n-2) +26^ (n-3) +26^ (n-4) +...+26^1+1) + (num_1-1)
26^ (n-2) + (num_2-1)26^ (n-3) +...+ (Num_ (n-1)-1)
Then subtract 1 on both sides: (W (num_n-1))/26-1= (26^ (n-2) +26^ (n-3) +26^ (n-4) +...+26^1) + (num_1-1)
26^ (n-2) + (num_2-1) *26^ (n-3) +...+ (Num_ (n-1)-1)
Then the left (W (num_n-1))/26-1 as the overall W, is not the first to find the right letter is very similar?
This way the string from right to left is exactly the opposite of what you want, and you need to reverse it.
On the code:
/**

  • Returns the letter corresponding to the column number
  • @param columnno (XLS) column (starting from 1)
    */
    public static String Getcorrespondinglabel (int columnno) {
    if (columnno<1/| | columnno>16384/)
    throw new IllegalArgumentException ();
    string[] sources = new string[]{"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"};
    StringBuilder sb = new StringBuilder (5);
    int remainder = columnno%26; Find the right letter.
    if (remainder==0) {//description (Num_n-1) = 26, 26th letter is Z
    Sb.append ("Z");
    remainder = 26; Because the next W (num_n-1) is columnno-remainder, you need to assign the remainder back to 26.
    }
    else{//If the rightmost letter is not Z, go to sources the corresponding position of the array to take the letter, remainder do not change
    Sb.append (Sources[remainder-1]);
    }
    ColumnNo = (columnno-remainder)/26-1; To determine if there are any other letters next.

    When the current loop is seeking the last letter (from right to left), (Columnno-remainder)/26 will be 0, minus 1 is 1.
    So by judging (Columnno-remainder)/26-1 is greater than-the end of judgment
    while (columnno>-1) {
    remainder = columnno%26;
    Sb.append (Sources[remainder]);
    ColumnNo = (columnno-remainder)/26-1;
    }

    Return Sb.reverse (). toString (); Because it's parsed from right to left, it needs to be reversed.
    }

Test:
public static void Main (string[] args) {
String label = Getcorrespondinglabel (37);
System.out.println ("37th column:" +label);
System.out.println ();
Long start = System.nanotime ();
Label = Getcorrespondinglabel (256);
System.out.println ("Find the No. 256 column corresponding to the letter time (nanoseconds):"
+ (System.nanotime ()-start));
System.out.println ("XLS column NO. 256:" +label);
System.out.println ();

label = getCorrespondingLabel(16384);System.out.println("xlsx第16384列:"+label);

}
Print:
37th column: AK

Find No. 256 column corresponding letter time (nanosecond): 7776
XLS column NO. 256: IV

xlsx 16,384th Column: XFD
At this time and thought, to find the N column column standard Method 1 better or use Method 2 loop n times Good ...
Method 1 for the 26+26*26=702 column of the array, you can extract the combined parts to reduce unnecessary multiple%26, because at this time the multiple will not exceed 26. Method 2 can also be changed to start from 0 after all, the number of POI columns is starting from 0. An ASCII code can also be used to convert a letter array without pre-defining it.
-----------------------------------------------Divider Line-----------------------------------------------------
Final code:

/**

  • Excel column number to letter tool class
  • */
    Public final class Columns {

    Private Columns () {
    }

    private static string[] sources = new string[] {"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"};

    /**
    • ( in. xls, 16384 for . xlsx)
    • @param columnnum
    •        列的个数,从1开始
    • @throws illegalargumentexception
    •         如果 columnNum 超出该范围 [1,16384]
    • @return returns an array of [1,columnnum] columnnum corresponding XLS column letters
      /
      public static string[] Getcolumnlabels (int columnnum) {
      if (Columnnum < 1 | | columnnum > 16384)
      Throw New IllegalArgumentException ();
      string[] columns = new String[columnnum];
      if (Columnnum <) {
      System.arraycopy (sources, 0, columns, 0, columnnum);
      return columns;
      }
      Int multiple =-1;
      int remainder;
      System.arraycopy (sources, 0, columns, 0, 26);
      int currentloopidx = 0;
      if (Columnnum < 703) {
      for (int i = n; i < Columnnum; i++) {
      remainder = currentloopidx%;
      if (remainder = = 0) {
      multiple++;
      }
      Columns[i] = Sources[multiple] + Columns[remainder];
      currentloopidx++;
      }
      } else {
      int currentlen =;
      int totallen = 26;
      int lastlen = 0;
      for (int i = n; i < Columnnum; i++) {
      remainder = currentloopidx% Currentlen;
      if (remainder = = 0) {
      multiple++;
      Int J = multiple% 26;
      if (j = = 0 && Multiple! = 0) {
      Lastlen = Totallen;
      Currentlen =
      Currentlen;
      Totallen = Currentlen + Lastlen;
      Currentloopidx = 0;
      }
      }
      Columns[i] = sources[multiple% 26]
      + Columns[remainder + Lastlen];
      currentloopidx++;
      }
      }

      return columns;
      }

    /**
      • Returns the letter corresponding to the column number
      • @param ColumnNo
      •        (xls的)第几列(从1开始)

        */
        private static String Getcorrespondinglabel (int columnno) {
        if (ColumnNo < 1/ | | columnno>16384 /
        )
        throw new IllegalArgumentException ();

        StringBuilder sb = new StringBuilder (5);
        int remainder = ColumnNo% 26;
        if (remainder = = 0) {
        Sb.append ("Z");
        remainder = 26;
        } else {
        Sb.append (Sources[remainder-1]);
        }

        while ((ColumnNo = (columnno-remainder)/26-1) >-1) {
        remainder = ColumnNo% 26;
        Sb.append (Sources[remainder]);
        }

        Return Sb.reverse (). toString ();
        }

    /**
      • Column number to letter
      • @param columnindex
      •        poi里xls的列号(从0开始)
      • @throws illegalargumentexception
      •         if columnIndex less than 0
      • @return the letter that corresponds to the column
        */
        public static String Getindexlabel (int columnindex) {
        Return Getcorrespondinglabel (columnindex + 1);
        }
}

Microsoft Whiteboard

Microsoft Whiteboard Excel xls column number number to letter

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.