최신 게시글(JAVA)
HOME > JAVA > 최신 게시글(JAVA)
2018.07.30 / 10:29

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

summerman
추천 수 244 
서론
엑셀 리더를 작성할 일이 있어 올려봅니다.


테스트 데이터

C:/abc.xlsx
C:/abc.xls
각각 저장


아파치 poi-ooxml 를 디팬던시에 등록합니다.
  1. <dependency>
  2. 	<groupId>org.apache.poi</groupId>
  3. 	<artifactId>poi-ooxml</artifactId>
  4. 	<version>3.15</version>
  5. </dependency>


일반적인 사용
  1. import java.io.FileInputStream;
  2. import java.io.FileNotFoundException;
  3. import java.io.IOException;
  4. import java.util.Arrays;
  5. import java.util.List;
  6.  
  7. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  8. import org.apache.poi.ss.usermodel.Row;
  9. import org.apache.poi.ss.usermodel.Sheet;
  10. import org.apache.poi.ss.usermodel.Workbook;
  11. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  12. import org.junit.Test;
  13.  
  14. /**
  15.  * 엑셀 jexcelapi test
  16.  */
  17. public class ExcelReadTest
  18. {
  19. 	@Test
  20. 	public void excelRead() throws FileNotFoundException, IOException
  21. 	{
  22. 		List<String> paths = Arrays.asList("C:/abc.xlsx", "C:/abc.xls");
  23. 		
  24. 		for (String path : paths) 
  25. 		{
  26. 			Workbook tempWorkbook;
  27. 			
  28. 			if (path.endsWith(".xls"))
  29. 			{
  30. 				tempWorkbook = new HSSFWorkbook(new FileInputStream(path));
  31. 			}
  32. 			else if (path.endsWith(".xlsx"))
  33. 			{
  34. 				tempWorkbook = new XSSFWorkbook(new FileInputStream(path));
  35. 			}
  36. 			else
  37. 			{
  38. 				throw new IllegalAccessError("xls / xlsx 확장자만 읽을 수 있습니다.");
  39. 			}
  40. 			
  41. 			try (Workbook workbook = tempWorkbook)
  42. 			{
  43. 				System.out.println("경로 : " + path);
  44. 				Sheet sheet = workbook.getSheetAt(0);
  45. 				System.out.println("첫번째 sheet 읽음");
  46. 				
  47. 				// 행을 가져옵니다.
  48. 				for (Row row : sheet)
  49. 				{
  50. 					// cell (행의 각 열) 을 가져옵니다.
  51. 					row.forEach(cell -> 
  52. 					{
  53. 						System.out.print(cell.toString());
  54. 						System.out.print(" || ");
  55. 					});
  56. 					System.out.println();
  57. 				}
  58. 			}
  59. 		}
  60. 	}
  61. }
결과 : 
경로 : 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 를 써보도록 하겠습니다.
  1. import java.io.FileInputStream;
  2. import java.io.FileNotFoundException;
  3. import java.io.IOException;
  4. import java.util.Arrays;
  5. import java.util.List;
  6.  
  7. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  8. import org.apache.poi.ss.usermodel.DataFormatter;
  9. import org.apache.poi.ss.usermodel.Row;
  10. import org.apache.poi.ss.usermodel.Sheet;
  11. import org.apache.poi.ss.usermodel.Workbook;
  12. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  13. import org.junit.Test;
  14.  
  15. /**
  16.  * 엑셀 jexcelapi test
  17.  */
  18. public class ExcelReadTest
  19. {
  20. 	@Test
  21. 	public void excelRead() throws FileNotFoundException, IOException
  22. 	{
  23. 		List<String> paths = Arrays.asList("C:/abc.xlsx", "C:/abc.xls");
  24. 		// 데이터 포멧터
  25. 		DataFormatter formatter = new DataFormatter();
  26. 		
  27. 		for (String path : paths) 
  28. 		{
  29. 			Workbook tempWorkbook;
  30. 			
  31. 			if (path.endsWith(".xls"))
  32. 			{
  33. 				tempWorkbook = new HSSFWorkbook(new FileInputStream(path));
  34. 			}
  35. 			else if (path.endsWith(".xlsx"))
  36. 			{
  37. 				tempWorkbook = new XSSFWorkbook(new FileInputStream(path));
  38. 			}
  39. 			else
  40. 			{
  41. 				throw new IllegalAccessError("xls / xlsx 확장자만 읽을 수 있습니다.");
  42. 			}
  43. 			
  44. 			try (Workbook workbook = tempWorkbook)
  45. 			{
  46. 				System.out.println("경로 : " + path);
  47. 				Sheet sheet = workbook.getSheetAt(0);
  48. 				System.out.println("첫번째 sheet 읽음");
  49. 				
  50. 				// 행을 가져옵니다.
  51. 				for (Row row : sheet)
  52. 				{
  53. 					// cell (행의 각 열) 을 가져옵니다.
  54. 					row.forEach(cell -> 
  55. 					{
  56. 						// 데이터 포멧터 사용.
  57. 						System.out.print(formatter.formatCellValue(cell));
  58. 						System.out.print(" || ");
  59. 					});
  60. 					System.out.println();
  61. 				}
  62. 			}
  63. 		}
  64. 	}
  65. }
결과 : 
경로 : 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
  1. int dataFormat = cell.getCellStyle().getDataFormat();
  2. if (dataFormat  == 14) { 
  3. 	dateFmt = "dd/mm/yyyy"; 
  4. } else if(dataFormat  == 165) { 
  5. 	dateFmt = "m/d/yy";
  6. } else if(dataFormat  == 166) {
  7. 	dateFmt = "d-mmm-yy"; 
  8. } else if(dataFormat  == 167) { 
  9. 	dateFmt = "mmmm d yyyy ";
  10. } else if(dataFormat  == 168) { 
  11. 	dateFmt = "m/d/yyyy";
  12. } else if(dataFormat  == 169) { 
  13. 	dateFmt = "d-mmm-yyyy";
  14. }
매우 다행이도 위처럼 처리할 필요없이 poi 의 HSSFDateUtil 에 아래와 같이 작성된 부분이 있습니다.
  1. /**
  2.  * Given a format ID this will check whether the format represents
  3.  *  an internal excel date format or not.
  4.  * @see #isADateFormat(int, java.lang.String)
  5.  */
  6. public static boolean isInternalDateFormat(int format) {
  7.         switch(format) {
  8.             // Internal Date Formats as described on page 427 in
  9.             // Microsoft Excel Dev's Kit...
  10.             case 0x0e:
  11.             case 0x0f:
  12.             case 0x10:
  13.             case 0x11:
  14.             case 0x12:
  15.             case 0x13:
  16.             case 0x14:
  17.             case 0x15:
  18.             case 0x16:
  19.             case 0x2d:
  20.             case 0x2e:
  21.             case 0x2f:
  22.                 return true;
  23.         }
  24.    return false;
  25. }
그럼 코드를 고쳐보겠습니다.


날짜 예외 처리
  1. import java.io.FileInputStream;
  2. import java.io.FileNotFoundException;
  3. import java.io.IOException;
  4. import java.text.SimpleDateFormat;
  5. import java.util.Arrays;
  6. import java.util.List;
  7.  
  8. import org.apache.poi.hssf.usermodel.HSSFDateUtil;
  9. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  10. import org.apache.poi.ss.usermodel.DataFormatter;
  11. import org.apache.poi.ss.usermodel.Row;
  12. import org.apache.poi.ss.usermodel.Sheet;
  13. import org.apache.poi.ss.usermodel.Workbook;
  14. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  15. import org.junit.Test;
  16.  
  17. /**
  18.  * 엑셀 jexcelapi test
  19.  */
  20. public class ExcelReadTest
  21. {
  22. 	@Test
  23. 	public void excelRead() throws FileNotFoundException, IOException
  24. 	{
  25. 		List<String> paths = Arrays.asList("C:/abc.xlsx", "C:/abc.xls");
  26. 		// 데이터 포멧터
  27. 		DataFormatter formatter = new DataFormatter();
  28. 		// 데이트 포맷
  29. 		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
  30. 		
  31. 		for (String path : paths) 
  32. 		{
  33. 			Workbook tempWorkbook;
  34. 			
  35. 			if (path.endsWith(".xls"))
  36. 			{
  37. 				tempWorkbook = new HSSFWorkbook(new FileInputStream(path));
  38. 			}
  39. 			else if (path.endsWith(".xlsx"))
  40. 			{
  41. 				tempWorkbook = new XSSFWorkbook(new FileInputStream(path));
  42. 			}
  43. 			else
  44. 			{
  45. 				throw new IllegalAccessError("xls / xlsx 확장자만 읽을 수 있습니다.");
  46. 			}
  47. 			
  48. 			try (Workbook workbook = tempWorkbook)
  49. 			{
  50. 				System.out.println("경로 : " + path);
  51. 				Sheet sheet = workbook.getSheetAt(0);
  52. 				System.out.println("첫번째 sheet 읽음");
  53. 				
  54. 				// 행을 가져옵니다.
  55. 				for (Row row : sheet)
  56. 				{
  57. 					// cell (행의 각 열) 을 가져옵니다.
  58. 					row.forEach(cell -> 
  59. 					{
  60. 						String value;
  61. 						// 날짜형 예외
  62. 						if (HSSFDateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat()))
  63. 						{
  64. 							value = sdf.format(cell.getDateCellValue());
  65. 						}
  66. 						// 기타
  67. 						else
  68. 						{
  69. 							value = formatter.formatCellValue(cell);
  70. 						}
  71. 						System.out.print(value);
  72. 						System.out.print(" || ");
  73. 					});
  74. 					System.out.println();
  75. 				}
  76. 			}
  77. 		}
  78. 	}
  79. }
출력 : 
경로 : 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 || 


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