Skip to content

Instantly share code, notes, and snippets.

@benelog
Last active December 29, 2015 22:09
Show Gist options
  • Save benelog/7734532 to your computer and use it in GitHub Desktop.
Save benelog/7734532 to your computer and use it in GitHub Desktop.
퀴즈

문제

셀과 같은 스프레드시트에서, 컬럼 이름은 첫번째가 A, 두번째가 B... 같은 식인데, 26번째인 Z 다음부터는 AA, AB,.. 처럼 됩니다. 숫자를 입력받으면 그 순서에 대응되는 엑셀 컬럼을 출력하는 프로그램을 작성하세요.

프로그래밍 언어의 제약은 없으며, 외부 라이브러리를 쓰시는 경우 용도를 같이 명시해 주세요.

풀이

package net.benelog.quiz;
import java.util.Arrays;
import java.util.List;
public class ExcelUtils {
private static int RADIX = 'Z' - 'A' + 1;
private static List<Integer> levelBase = Arrays.asList(1, 27, 703, 18279, 475255, 12356631);
public static String getColumnName(int index) {
int level = getLevelOfColumn(index);
StringBuilder colName = new StringBuilder();
getColumnName(index, colName, level);
return colName.toString();
}
static int getLevelOfColumn(int index) {
int level = 0;
int firstIndexOfLevel = 0;
do {
level ++ ;
firstIndexOfLevel = levelBase.get(level);
} while (index >= firstIndexOfLevel);
return level;
}
private static void getColumnName(int index, StringBuilder colName, int level) {
int base = (int) (Math.pow(RADIX, level -1));
int digit = 0;
if (level == 0 ) {
return;
} else if (level == 1){
digit = index;
} else {
digit = (index - levelBase.get(level -2))/ base;
}
index -= base * digit;
append(colName, digit);
getColumnName(index , colName, level -1);
}
private static void append(StringBuilder colName, int digit) {
colName.append((char)('A' + (char)(digit) -1));
}
public static int getIndex(String colName) {
int index = 0;
char[] colNameElems = colName.toCharArray();
for(int i=0,n=colNameElems.length ; i<n;i++) {
int alphaOrder = colNameElems[i] - 'A' + 1;
index += alphaOrder * Math.pow(RADIX, (colNameElems.length -i -1));
}
return index;
}
}
package net.benelog.quiz;
import static org.fest.assertions.Assertions.*;
import org.junit.Test;
public class ExcelUtilsTest {
@Test
public void shouldBeGetIndexFromColName() {
assertIndex("A", 1);
assertIndex("Z", 26);
assertIndex("AA", 27);
assertIndex("AB", 28);
assertIndex("AY", 51);
assertIndex("AZ", 52);
assertIndex("BA", 53);
assertIndex("CA", 79);
assertIndex("ZZ", 702);
assertIndex("AAA", 703);
assertIndex("AAAA", 18279);
assertIndex("AAAAA", 475255);
}
@Test
public void shouldNextNextIndex() {
assertNext("ZZ", "AAA");
assertNext("AZZ", "BAA");
assertNext("ZBZ", "ZCA");
assertNext("ZZZ", "AAAA");
}
@Test
public void shouldBeGetColumnNameFromIndex() {
assertColName(1, "A");
assertColName(26, "Z");
assertColName(27, "AA");
assertColName(28, "AB");
assertColName(51, "AY");
assertColName(52, "AZ");
assertColName(702, "ZZ");
assertColName(703, "AAA");
}
@Test
public void shouldGetLevel() {
assertLevel("A", 1);
assertLevel("Z", 1);
assertLevel("AA", 2);
assertLevel("ZZ", 2);
assertLevel("AAA", 3);
assertLevel("ZZZ", 3);
assertLevel("AAAA", 4);
assertLevel("AAAAA", 5);
}
private void assertNext(String colName1, String colName2) {
int diff = ExcelUtils.getIndex(colName2) - ExcelUtils.getIndex(colName1);
assertThat(diff).isEqualTo(1);
}
private void assertLevel(String colName, int level) {
int index = ExcelUtils.getIndex(colName);
assertThat(ExcelUtils.getLevelOfColumn(index)).isEqualTo(level);
}
private void assertIndex(String colName, int index) {
assertThat(ExcelUtils.getIndex(colName)).isEqualTo(index);
}
private void assertColName(int index, String colName) {
assertThat(ExcelUtils.getColumnName(index)).isEqualTo(colName);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment