4

I wish to have a java method which gives me, index given, a corresponding letter set excel like, so:

258 => IZ (last index)

30 => AD

120 => DR

56 => BD

First method gives correct output, but it's very dumb and I don't like that. I tried to build a second method that involves a bit of thinking. I already saw methods using String Builder or something else like this one but I tried to build a method myself aka betterGetColumnName.

better 258 => IHGFEDCBAX (not ok)

better 30 => AD (OK, 2nd alphabet round it's ok)

better 120 => DCBAP (not ok)

better 56 => BAD (seems like 3rd alphabet round breaks my logic)

public String getColumnName(int index){
    String[] letters = {
        "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","AL","AM","AN","AO","AP","AQ","AR","AS","AT","AU","AV",
        "AW","AX","AY","AZ","BA","BB","BC","BD","BE","BF","BG","BH","BI","BJ",
        "BK","BL","BM","BN","BO","BP","BQ","BR","BS","BT","BU","BV","BW","BX",
        "BY","BZ","CA","CB","CC","CD","CE","CG","CH","CI","CJ","CK","CL","CM",
        "CN","CO","CP","CQ","CR","CS","CT","CU","CV","CW","CX","CY","CZ","DA",
        "DB","DC","DD","DF","DG","DH","DI","DJ","DK","DL","DM","DN","DO","DP",
        "DQ","DR","DS","DT","DU","DV","DW","DX","DY","DZ","EA","EB","EC","ED",
        "EE","EF","EG","EH","EI","EJ","EK","EL","EM","EN","EO","EP","EQ","ER",
        "ES","ET","EU","EV","EW","EX","EY","EZ","FA","FB","FC","FD","FE","FF",
        "FG","FH","FI","FJ","FK","FL","FM","FN","FO","FP","FQ","FR","FS","FT",
        "FU","FV","FW","FX","FY","FZ","GA","GB","GC","GD","GE","GF","GG","GH",
        "GI","GJ","GK","GL","GM","GN","GO","GP","GQ","GR","GS","GT","GU","GV",
        "GW","GX","GY","GZ","HA","HB","HC","HD","HE","HF","HG","HH","HI","HJ",
        "HK","HL","HM","HN","HO","HP","HQ","HR","HS","HT","HU","HV","HW","HX",
        "HY","HZ","IA","IB","IC","ID","IE","IF","IG","IH","II","IJ","IK","IL",
        "IM","IN","IO","IP","IQ","IR","IS","IT","IU","IV","IW","IX","IY","IZ"
    };

    if (index<=letters.length){
        return letters[index-1];
    }else{
        return null;
    }

}

I think I should save how many times I made a full alphabet round, I wouldn't use StringBuilder or else, just char, String and integers because at school we can't upgrade java version (1.5.x) also I think it might be useful for me to understand why is my logic so wrong. 

public String betterGetColumnName(int index){
    int res=0;
    String s = "";
    char h='0';

    while(index>26){ 
        res=index/26;
        h=(char)(res+64); 
        s+=h;
        index -=26;
    }
    h=(char)(index+64);
    s+=h;
    return s;
}
Arun Sudhakaran
  • 2,167
  • 4
  • 27
  • 52
Phyxil
  • 105
  • 1
  • 8
  • 1
    It seems some sort of `static data` to your application, I my self prefer to create an static hash map with the key as numbers and the value as string. This makes you application much faster. I think there is no use to create a function for this. At least your `getColumnName` seems ok to me :) – Alireza Fattahi Jan 24 '18 at 11:27
  • @AlirezaFattahi the problem is that there _might_ be a lot more of those columns (e.g. Excel 2013 allows columns up to XFD, i.e. max index 16358). Adding all those as a static map manually is a lot of work and error prone so you'd need some code to calculate them. And when you have that code already it's not hard to adapt it so you won't need the map at all - and it might even be faster too (map lookups aren't free). – Thomas Jan 24 '18 at 11:30
  • If betterGetColumnName is restricted to input values <= 258, replace `while` by `if` and `index -=26;` by `index %= 26;`. – laune Jan 24 '18 at 11:31
  • No I just stopped at 258 because I was tired of copy-pasting alphabet letters more and more. – Phyxil Jan 24 '18 at 11:33

1 Answers1

8

You are definitely on the right track, though your logic is a bit off. What you are effectively trying to do is to convert a base 10 integer into a base 26 character. But instead of digits, the converted "number" actually consists of the 26 letters of the alphabet.

The algorithm you want here is to determine each letter of the output by taking the remainder of the input number divided by 26. Then, divide the input by 26 and again inspect the "tens" position to see what letter it is. In the code snippet below, I assume that 1 corresponds to A, 26 corresponds to Z, and 27 to AA. You may shift the indices however you feel is best.

int input = 53;
String output = "";
while (input > 0) {
    int num = (input - 1) % 26;
    char letter = (char)(num+65);
    output = letter + output;
    input = (input-1) / 26;
}
System.out.println(output);

BA

Demo

Note: A helpful edit was suggested which uses StringBuilder instead of String to do the concatenations. While this might be more optimal than the above code, it might make it harder to see the algorithm.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360