[Java] jxl 라이브러리 팁

[Java] jxl 라이브러리 팁

  오후 4:56    홍다슬

---

Laptop

운영체제	Windows 10 Pro 64bit
개발환경	JDK1.7.0_80

현 시점에서는 상당히 오래전에 쓰던 엑셀 라이브러리지만,

유지보수 프로젝트 참여 중 기능을 추가해보면서 알게 된 내용을 정리해본다.

일단 기본적인 개념으로, jxl은 읽기 객체와 쓰기 객체가 따로 존재한다.

예를 들어 아래와 같은 소스가 있을 때,

Workbook originExcel = Workbook.getWorkbook(path);
WritableWorkbook newExcel = Workbook.createWorkbook(file, originExcel);

Workbook 클래스로 생성된 originExcel는 읽기 전용 객체가 되고,

.getSheet, .getCell 메소드로 얻는 Sheet, Cell 객체들 또한 읽기 전용 객체가 되며, 즉 원본 파일(path)에 대해 엑셀 객체를 생성하여 내용을 읽을 수는 있지만 직접적으로 파일에 영향을 줄 수는 없다는 뜻이다.

그러나 WritableWorkbook 클래스로 생성된 newExcel은 쓰기 가능 객체로,

.getSheet, .getWritableCell 메소드를 통해 WritableSheet, WritableCell 라는 셀의 추가나 수정 등을 할 수 있는, 즉 파일에 영향을 줄 수 있는 객체를 얻을 수 있게 된다.

정리하면 위 상태는 원본 파일(String path)로부터 엑셀 객체를 생성한 뒤,

그것의 복사 파일(File file)을 만들어 수정 가능 상태로 만들어놓은 설정이라고 할 수 있다.

그럼 이제 원본 파일의 경우 아무 내용도 없는 템플릿으로 만들어두고,

복사 파일에서는 프로그래밍적으로 값을 입력하면 될텐데, 여기서 치명적인 문제가 하나 있다.

셀의 스타일 등을 유지한 채 값만 바꾸는, 즉 비교적 최신 엑셀 라이브러리인 poi 라이브러리의 setCellValue에 해당하는 기능이 jxl에는 없다.

WritableCell 객체에도 setCellFormat과 같이 속성을 바꾸는 메소드는 있지만 값을 바꾸는 메소드가 없다.

WritableCellFormat를 생성해서 일일히 설정하여 셀을 생성하는 것도 방법이지만,

그렇게 되면 굳이 앞에서 복사본을 생성한 이유가 없어진다.

그래서 셀을 생성하되, 원래 셀의 속성을 가져와 사용하는 방식을 사용하면 된다.

WritableSheet sheet = newExcel.getSheet(0);
for (int r = 0; r < sheet.getRows(); r++) {
	for (int c = 0; c < sheet.getColumns(); c++) {
		CellFormat format = sheet.getCell(c, r).getCellFormat();
		Label cell = new Label(c, r, "값", format);
		sheet.addCell(cell);
	}
}

이렇게 만들어진 셀에서는 cell.setString("값2"); 를 통해 값 수정도 가능하다.

예시로 Label을 썼는데, 이것은 문자열 값을 받는 셀 객체를 의미하기 때문에

숫자에 쉼표를 넣는 등의 설정이 필요할 경우 java에서 구현해서 넣어야 하는 비효율적인 문제가 발생한다.

좀 더 괜찮은 방법으로는 원본 엑셀에 해당하는 부분은 셀 서식에서 숫자를 설정해준 뒤,  

위 소스에서 Label 부분을

Number cell = new Number(c, r, 100, format);

이렇게 전환하면 숫자 설정을 유지할 수 있게 된다.

또한 수식을 사용하고 싶으면 Formula 객체를 이용하면 되는데,

주의사항은 jxl에서 사용하는 index의 개념은 0부터 시작하지만,

실제 엑셀 파일에서 사용되는 값은 행은 1부터 시작되는 순번, 열은 알파벳으로 인식되기 때문에

이 부분에 주의해서 수식을 입력해야 한다.

아래는 getColNm 메소드를 통해 index를 알파벳으로 치환하여,

c가 0이라는 전제에서 =SUM(A1:A10) 수식 셀을 생성하는 예시이다.

int start = 0;
int end = 9;
String nm = getColNm(c);
Formula cell = new Formula(c, r, "SUM(" + nm + (start + 1) + ":" + nm + (end + 1) + ")", format);

...

private String getColNm(int columnNumber) {
	StringBuilder columnName = new StringBuilder();
	columnNumber++;
	while (columnNumber > 0) {
		int rem = columnNumber % 26;
		if (rem == 0) {
			columnName.insert(0, 'Z');
			columnNumber = (columnNumber / 26) - 1;
		} else {
			columnName.insert(0, (char) ((rem - 1) + 'A'));
			columnNumber = columnNumber / 26;
		}
	}
	return columnName.toString();
}

추가로 jxl-2.6.12 버전에서 SUM와 같은 엑셀 함수 사용 시

Can't find bundle for base name functions ...

와 같은 오류를 발생시키는 경우가 있는데,

이는 특정 시기에 배포 오류가 있었던건지 jxl 파일 내에 관련 파일이 없는 경우가 있다.

기존에 구축된 프로젝트라 버전이나 라이브러리를 바꾸는데 부담이 있다면, 

새로운 버전을 jar로 임시로 받아 압축 프로그램을 사용하여 functions.properties 파일만 추출해서 기존 파일에 넣으면 정상적으로 동작한다.

(이름이 비슷한 다른 파일들은 언어별 파일인것 같은데, 맞는 언어가 없어도 자동적으로 기본 파일만 참조하는 것 같다.)

Formula로 생성한 셀의 수식은 cell.getContents()로 조회 가능하나

수식 결과로 나온 값은 java에서는 조회가 불가능하며,

읽기 전용인 원본 파일에서 수식이 입력되어있던 셀은 수식, 결과 모두 조회 불가능하다.

마지막으로 아무 값도 없이 속성만 복사한 셀을 생성할 때는

Blank cell = new Blank(c, r, format);

이렇게 하면 된다.

(원본 파일에 있던 셀인 경우 겉보기에는 굳이 java에서 건드리는 의미는 없으나, 엑셀 함수 사용 시 범위에 포함된 셀을 Blank로 생성했을 경우 0으로 인식되지만 그대로 둔 셀은 값 오류가 발생하는 경우가 있었다.)

참고로 Label, Number, Formula, Blank 모두 WritableCell 인터페이스의 구현 클래스들이므로

위의 예시들에서 WritableCell cell = ... 로 선언해도 상관없으나,

setString(Number의 경우 setValue)과 같이 나중에 값을 수정할 수 없다는 단점은 있으나

셀 타입마다 일일히 다른 cell 객체를 생성하지 않아도 된다는 장점이 있다.