Excel Sheet Column Title in Java
In this tutorial, we are going to learn about Excel Sheet Column Title in Java. Considering an integer that represents a column number in the Excel Sheet. The objective is to get the title of the relevant column in an Excel sheet, where the digits 1 to 26 are represented by the letters A to Z, as well as AA to AZ, BA to BZ, and so on.
Let’s see a few examples to understand it.
Example 1:
Input: colNumb = 1
Output: A
Explanation: In the Excel sheet, column 1 corresponds to column title "A".
Example 2:
Input: colNumb = 29
Output: AC
Explanation: Columns in the Excel sheet numbering scheme are labeled with letters ranging from A to Z, with combinations such as AA, AB, AC, and so on used for columns greater than 26. Thus, the word "AC" is the equivalent column title for column number 29.
Example 3:
Input: colNumb = 700
Output: ZX
Explanation: Columns in the Excel sheet numbering scheme are labeled with letters ranging from A to Z, with combinations such as AA, AB, AC, and so on used for columns greater than 26. Thus, the word "ZX" is the equivalent column title for column number 700.
Approach: Using String
To begin with, alter the input column number so that it begins at zero in accordance with the indexing convention. Next, calculate each character of the column title iteratively by dividing the substituted column number by 26 to find the position and add the corresponding offset character to 'A'. Use a StringBuilder and enter these characters in reverse order to create the column title from right to left. Keep going till there is no more column number. Finally, return the created column title in string form.
FileName: ColunmTilteOfExcelSheet.java
public class ColunmTilteOfExcelSheet {
public String convertToTitle(int colNumb) {
char[] map = {'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();
int remdr = 0;
while(colNumb > 26){
remdr = colNumb % 26;
colNumb = colNumb/26;
if(remdr==0) {
remdr = 26;
colNumb--;
}
sb.append(map[remdr-1]);
}
sb.append(map[colNumb-1]);
return sb.reverse().toString();
}
// main method
public static void main(String[] args) {
ColunmTilteOfExcelSheet cte = new ColunmTilteOfExcelSheet();
// provide input values
int[] colNumbs = {1, 29, 700};
for (int colNumb : colNumbs) {
String colmTitle = cte.convertToTitle(colNumb);
System.out.println("Column number " + colNumb + " is represented as " + colmTitle);
}
}
}
Output:
Column number 1 is represented as A
Column number 29 is represented as AC
Column number 700 is represented as ZX
Complexity analysis: The program takes O(log(n)) time complexity because the program divides column numbers by 26. then reverses the StringBuilder object, converting it to a string, which takes linear time. The overall time complexity is O(log (n)), where n is column number. The space complexity is O(log (n)) because the program uses a StringBuilder object and a char array for column titles.
Approach: Using Recursion
The approach is straightforward. First of all, check if the number of columns is equal to or fewer than 26. If so, give back the matching character for column titles directly from the string named alphabet. When dividing a column number by 26, if the column number is larger than 26, determine the quotient (qnt) and the remainder (remdr). Use qnt - 1 to execute the function recursively if the remainder is 0, meaning that the column number is a multiple of 26. Append the title of the 26th column to the result. Concatenate the results, return the column title, and call the procedure recursively with qnt and remdr if the remainder is not 0.
FileName: ColunmTilteOfExcelSheet.java
public class ColunmTilteOfExcelSheet {
public String alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
String colTitle = "";
// method for converting the number to title
public String convertToTitle(int colmNumb) {
if (colmNumb <= 26) {
return Character.toString(alphabet.charAt(colmNumb - 1));
} else {
int remdr = colmNumb % 26;
int qnt = colmNumb / 26;
if (remdr == 0) {
colTitle = convertToTitle(qnt - 1).concat(convertToTitle(26));
} else {
colTitle = convertToTitle(qnt).concat(convertToTitle(remdr));
}
}
return colTitle;
}
// main method
public static void main(String[] args) {
ColunmTilteOfExcelSheet cte = new ColunmTilteOfExcelSheet();
// provide input values
int[] colmNumbs = {1, 29, 700};
for (int colmNumb : colmNumbs) {
String colmTitle = cte.convertToTitle(colmNumb);
System.out.println("Column number " + colmNumb + " is represented as " + colmTitle);
}
}
}
Output:
Column number 1 is represented as A
Column number 29 is represented as AC
Column number 700 is represented as ZX
Complexity analysis: The program uses O(log (n)) time complexity because the recursive function divides the input columnNumb by 26 until it is less than or equal to 26. So each step reduces the value by a factor of 26. Therefore, the overall time complexity is O(log (n)), where n is the column number. The space complexity is also O(log (n)) because the program uses a recursion stack, which gradually increases when input coluNumb increases. ueries.