원본 본문으로 이동하기

자바 엑셀 데이터 읽기 : 입력한 형식대로만 읽어오기

박용서 - 서론 엑셀 리더를 작성할 일이 있어 올려봅니다. 테스트 데이터 [미디어]가리사니 1-180.png[/미디어] 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 || 결론 찜찜한 팁이지만... 조금이라도 도움이 되셨으면 합니다.!! - 자바