가리사니의 조각들...
[백업][가리사니] 자바 엑셀 데이터 읽기 ： 입력한 형식대로만 읽어오기
java
박용서 • 2016-10-30

이 문서는 가리사니 개발자 포럼에 올렸던 글의 백업 파일입니다. 오래된 문서가 많아 현재 상황과 맞지 않을 수 있습니다.

서론

엑셀 리더를 작성할 일이 있어 올려봅니다.

테스트 데이터

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 를 써보도록 하겠습니다. ``` java 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 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 ``` java 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 에 아래와 같이 작성된 부분이 있습니다. ``` java  /**
  • 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 ||

결론

찜찜한 팁이지만… 조금이라도 도움이 되셨으면 합니다.!!