이 문서는 가리사니 개발자 포럼에 올렸던 글의 백업 파일입니다. 오래된 문서가 많아 현재 상황과 맞지 않을 수 있습니다.
서론
엑셀 리더를 작성할 일이 있어 올려봅니다.
테스트 데이터
C:/abc.xlsx
C:/abc.xls
각각 저장
아파치 poi-ooxml 를 디팬던시에 등록합니다.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
일반적인 사용
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
/**
* 엑셀 jexcelapi test
*/
public class ExcelReadTest {
@Test
public void excelRead() throws FileNotFoundException, IOException {
List<String> paths = Arrays.asList("C:/abc.xlsx", "C:/abc.xls");
for (String path : paths) {
Workbook tempWorkbook;
if (path.endsWith(".xls")) {
tempWorkbook = new HSSFWorkbook(new FileInputStream(path));
}
else if (path.endsWith(".xlsx")) {
tempWorkbook = new XSSFWorkbook(new FileInputStream(path));
}
else {
throw new IllegalAccessError("xls / xlsx 확장자만 읽을 수 있습니다.");
}
try (Workbook workbook = tempWorkbook) {
System.out.println("경로 : " + path);
Sheet sheet = workbook.getSheetAt(0);
System.out.println("첫번째 sheet 읽음");
// 행을 가져옵니다.
for (Row row : sheet) {
// cell (행의 각 열) 을 가져옵니다.
row.forEach(cell -> {
System.out.print(cell.toString());
System.out.print(" || ");
});
System.out.println();
}
}
}
}
}
결과 : 경로 : C:/abc.xlsx 첫번째 sheet 읽음 그냥숫자 || 그냥문자 || 아무거나 || 날짜 || 332492.0 || 안녕하세요 || 3234.23 || 30-10월-2011 || 23422.0 || 가리사니 || 3234.23 || 31-10월-2011 || 234234.0 || 개발자 || 3234.23 || 01-11월-2011 || 234234.0 || 공간 || 3234.23 || 02-11월-2011 || 234234.0 || 입니다. || 3234.23 || 03-11월-2011 || 234111.0 || 테스트 || 3234.23 || 04-11월-2011 || 3679.0 || 문서 || 3234.23 || 05-11월-2011 || 2134234.0 || 입니다. || 3234.23 || 06-11월-2011 || 경로 : C:/abc.xls 첫번째 sheet 읽음 그냥숫자 || 그냥문자 || 아무거나 || 날짜 || 332492.0 || 안녕하세요 || 3234.23 || 30-10월-2011 || 23422.0 || 가리사니 || 3234.23 || 31-10월-2011 || 234234.0 || 개발자 || 3234.23 || 01-11월-2011 || 234234.0 || 공간 || 3234.23 || 02-11월-2011 || 234234.0 || 입니다. || 3234.23 || 03-11월-2011 || 234111.0 || 테스트 || 3234.23 || 04-11월-2011 || 3679.0 || 문서 || 3234.23 || 05-11월-2011 || 2134234.0 || 입니다. || 3234.23 || 06-11월-2011 || 숫자와 날짜가.. 심각합니다....
일반적인 사용
- 위 방법은 포멧을 마음대로 바꿔버리는 단점때문에 DataFormatter 를 써보도록 하겠습니다.
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
/**
* 엑셀 jexcelapi test
*/
public class ExcelReadTest {
@Test
public void excelRead() throws FileNotFoundException, IOException {
List<String> paths = Arrays.asList("C:/abc.xlsx", "C:/abc.xls");
// 데이터 포멧터
DataFormatter formatter = new DataFormatter();
for (String path : paths) {
Workbook tempWorkbook;
if (path.endsWith(".xls")) {
tempWorkbook = new HSSFWorkbook(new FileInputStream(path));
}
else if (path.endsWith(".xlsx")) {
tempWorkbook = new XSSFWorkbook(new FileInputStream(path));
}
else {
throw new IllegalAccessError("xls / xlsx 확장자만 읽을 수 있습니다.");
}
try (Workbook workbook = tempWorkbook) {
System.out.println("경로 : " + path);
Sheet sheet = workbook.getSheetAt(0);
System.out.println("첫번째 sheet 읽음");
// 행을 가져옵니다.
for (Row row : sheet) {
// cell (행의 각 열) 을 가져옵니다.
row.forEach(cell -> {
// 데이터 포멧터 사용.
System.out.print(formatter.formatCellValue(cell));
System.out.print(" || ");
});
System.out.println();
}
}
}
}
}
결과 : 경로 : C:/abc.xlsx 첫번째 sheet 읽음 그냥숫자 || 그냥문자 || 아무거나 || 날짜 || 332492 || 안녕하세요 || 3234.23 || 10/30/11 || 23422 || 가리사니 || 3234.23 || 10/31/11 || 234234 || 개발자 || 3234.23 || 11/1/11 || 234234 || 공간 || 3234.23 || 11/2/11 || 234234 || 입니다. || 3234.23 || 11/3/11 || 234111 || 테스트 || 3234.23 || 11/4/11 || 3679 || 문서 || 3234.23 || 11/5/11 || 2134234 || 입니다. || 3234.23 || 11/6/11 || 경로 : C:/abc.xls 첫번째 sheet 읽음 그냥숫자 || 그냥문자 || 아무거나 || 날짜 || 332492 || 안녕하세요 || 3234.23 || 10/30/11 || 23422 || 가리사니 || 3234.23 || 10/31/11 || 234234 || 개발자 || 3234.23 || 11/1/11 || 234234 || 공간 || 3234.23 || 11/2/11 || 234234 || 입니다. || 3234.23 || 11/3/11 || 234111 || 테스트 || 3234.23 || 11/4/11 || 3679 || 문서 || 3234.23 || 11/5/11 || 2134234 || 입니다. || 3234.23 || 11/6/11 || 숫자는 제대로 나오지만 불행이도 날짜가... 아주 불행하게도 예외 처리 없이 처리할 방법이 없는 것 같습니다. xsl 지원을 포기하고 XSSFCell 로 가더라도 getRawValue 가 날짜형에선 입력한 대로 나오지 않습니다. 그럼 어떤 포멧을 예외처리해줘야할까 아래 사이트에 들어가봤습니다. 참고 : http://stackoverflow.com/questions/34918775/xlsx-issue-cell-getcellstyle-getdataformat-changing-value-after-adding-or-r
int dataFormat = cell.getCellStyle().getDataFormat();
if (dataFormat == 14) {
dateFmt = "dd/mm/yyyy";
} else if(dataFormat == 165) {
dateFmt = "m/d/yy";
} else if(dataFormat == 166) {
dateFmt = "d-mmm-yy";
} else if(dataFormat == 167) {
dateFmt = "mmmm d yyyy ";
} else if(dataFormat == 168) {
dateFmt = "m/d/yyyy";
} else if(dataFormat == 169) {
dateFmt = "d-mmm-yyyy";
}
매우 다행이도 위처럼 처리할 필요없이 poi 의 HSSFDateUtil 에 아래와 같이 작성된 부분이 있습니다.
/**
* Given a format ID this will check whether the format represents
* an internal excel date format or not.
* @see #isADateFormat(int, java.lang.String)
*/
public static boolean isInternalDateFormat(int format) {
switch(format) {
// Internal Date Formats as described on page 427 in
// Microsoft Excel Dev's Kit...
case 0x0e:
case 0x0f:
case 0x10:
case 0x11:
case 0x12:
case 0x13:
case 0x14:
case 0x15:
case 0x16:
case 0x2d:
case 0x2e:
case 0x2f:
return true;
}
return false;
}
그럼 코드를 고쳐보겠습니다.
날짜 예외 처리
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
/**
* 엑셀 jexcelapi test
*/
public class ExcelReadTest {
@Test
public void excelRead() throws FileNotFoundException, IOException {
List<String> paths = Arrays.asList("C:/abc.xlsx", "C:/abc.xls");
// 데이터 포멧터
DataFormatter formatter = new DataFormatter();
// 데이트 포맷
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
for (String path : paths) {
Workbook tempWorkbook;
if (path.endsWith(".xls")) {
tempWorkbook = new HSSFWorkbook(new FileInputStream(path));
}
else if (path.endsWith(".xlsx")) {
tempWorkbook = new XSSFWorkbook(new FileInputStream(path));
}
else {
throw new IllegalAccessError("xls / xlsx 확장자만 읽을 수 있습니다.");
}
try (Workbook workbook = tempWorkbook) {
System.out.println("경로 : " + path);
Sheet sheet = workbook.getSheetAt(0);
System.out.println("첫번째 sheet 읽음");
// 행을 가져옵니다.
for (Row row : sheet) {
// cell (행의 각 열) 을 가져옵니다.
row.forEach(cell -> {
String value;
// 날짜형 예외
if (HSSFDateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat())) {
value = sdf.format(cell.getDateCellValue());
}
// 기타
else {
value = formatter.formatCellValue(cell);
}
System.out.print(value);
System.out.print(" || ");
});
System.out.println();
}
}
}
}
}
출력 : 경로 : C:/abc.xlsx 첫번째 sheet 읽음 그냥숫자 || 그냥문자 || 아무거나 || 날짜 || 332492 || 안녕하세요 || 3234.23 || 2011-10-30 || 23422 || 가리사니 || 3234.23 || 2011-10-31 || 234234 || 개발자 || 3234.23 || 2011-11-01 || 234234 || 공간 || 3234.23 || 2011-11-02 || 234234 || 입니다. || 3234.23 || 2011-11-03 || 234111 || 테스트 || 3234.23 || 2011-11-04 || 3679 || 문서 || 3234.23 || 2011-11-05 || 2134234 || 입니다. || 3234.23 || 2011-11-06 || 경로 : C:/abc.xls 첫번째 sheet 읽음 그냥숫자 || 그냥문자 || 아무거나 || 날짜 || 332492 || 안녕하세요 || 3234.23 || 2011-10-30 || 23422 || 가리사니 || 3234.23 || 2011-10-31 || 234234 || 개발자 || 3234.23 || 2011-11-01 || 234234 || 공간 || 3234.23 || 2011-11-02 || 234234 || 입니다. || 3234.23 || 2011-11-03 || 234111 || 테스트 || 3234.23 || 2011-11-04 || 3679 || 문서 || 3234.23 || 2011-11-05 || 2134234 || 입니다. || 3234.23 || 2011-11-06 ||
결론
찜찜한 팁이지만... 조금이라도 도움이 되셨으면 합니다.!!