퀴즈

README.md

문제

• 출처 : http://synapsoft.co.kr/jsp/recruit/13_apply.html

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

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

풀이

• J언어 : http://agile.egloos.com/5774438
• Ruby : http://legendre.tistory.com/entry/%EC%96%B4%EB%96%A4-%ED%9A%8C%EC%82%AC%EC%9D%98-%EC%B1%84%EC%9A%A9-%ED%80%B4%EC%A6%88

ExcelUtils.java

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

ExcelUtilsTest.java

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