기타(framework)
2018.06.29 / 21:53

Jakarta POI - 읽기

aichatbot
추천 수 21

http://poi.apache.org/index.html
http://blog.naver.com/levin01.do?Redirect=Log&logNo=100011049989


I. POI 란?

일반적으로 POI가 엑셀파일을 쓰는 컴퍼넌트로 알려져 있으나 POI는 프로젝트 이름입니다.
즉 POI는 Microsoft Format File을 액세스 할 수 있는 API를 제공합니다. (한마디로 자바에서 MS파일을 읽고 쓸수있도록 지원합니다.)

POI안에는 여러 컴퍼넌트들이 있습니다.

① POIFS 
Microsoft의 OLE2 포맷 형식의 문서를 자바로 읽고 쓸수 있는 컴퍼넌트입니다
기본적으로 POI의 모든 컴퍼넌트들이 POIFS를 사용합니다.
② HSSF
Microsoft의 엑셀파일을 읽고 쓸수 있도록 지원하는 컴퍼넌트입니다.
③ HWPF
Microsoft의 워드파일을 읽고 쓸수 있도록 지원하는 컴퍼넌트입니다.
이 컴퍼넌트는 디자인 초기단계입니다.
④ HPSF
Microsoft의 OLE2 포맷 형식의 문서 속성을 어플리케이션에서 사용 할수 있도록 지원하는 컴퍼넌트입니다.
현재 읽기 기능만 제공합니다

워드파일을 핸들링 하는 HWPF는 초기단계라 사용을 못하지만 기대는 되는군요 ^^

ps. 영어사전을 찾아보니 poi는 하와이의 토란 요리를 뜻하더군요.
우리나라말로 하니 자카르타 토란 프로젝트 쯤 될라나? ㅎㅎ

II. 다운로드 및 설치
다운로드 받으러 갑시다~!
http://jakarta.apache.org/site/downloads/downloads_poi.cgi

현재 2.5.1버젼입니다.
다운받은 파일을 압축을 풀면 *.jar 파일들이 있을겁니다 이 파일들을 자신의 어플리케이션 /lib/에 복사합시다

POI API http://jakarta.apache.org/poi/apidocs/index.html

Quick Guide http://jakarta.apache.org/poi/hssf/quick-guide.html

III. Formula(수식) 지원에 관해..

엑셀을 읽고 쓸때 수식을 지원합니다. 
org.apache.poi.hssf.usermodel.HSSFCell의 setCellFormula("formulaString") 메쏘드는 스프레드시트에 수식을 추가하는데 사용되며 getCellFormula() 메쏘드는 수식을 대표하는 문자열을 해석하는데 사용됩니다. 하지만 엑셀에서 사용하는 수식을 모두 사용 할 수는 없습니다.

 지원되는 부분
-. 셀 참조, 시트참조, 지역참조
-. 상대적 혹은 절대적 참조 
-. 수연산 및 논리연산
-. 시트 혹은 매크로 함수
-. 수식 결과값 반환

 부분적 지원
문자열을 포함하는 수식을 해석할 수는 있지만 문자열값을 반환하는 수식은 아직 지원하지 않습니다.

 지원되지 않는 부분
-. 배열 수식 
-. 1진법 수식
-. 3D 참조
-. 에러 값 (cells containing #REF's or #VALUE's)

IV. 기본객체

가장 기본이되는 객체가 다음 4가지 입니다. 이름에서 무엇을 뜻하는지 대강 짐작 할 수 있겠죵?

 HSSFWorkbook - 엑셀 워크북을 말합니다.
 HSSFSheet - 엑셀 쉬트를 나타냅니다.
 HSSFRow - 엑셀에서 특정 행입니다.
 HSSFCell - 엑셀에서 특정 행에대한 특정 셀입니다

위 4가지 객체는 앞으로 계속 나올겁니다. 눈여겨 미리 봐 둡시다. @.@

V. 엑셀 읽기 예제

① POSFS을 이용하여 엑셀 워크북을 생성합니다.

POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("excelfile.xls"));
HSSFWorkbook workbook = new HSSFWorkbook(fs);

 생성된 워크북을 이용하여 시트 수만큼 돌면서 엑셀 시트 하나씩을 생성합니다.

int sheetNum = workbook.getNumberOfSheets();

for (int k = 0; k < sheetNum; k++) {
   System.out.println("Sheet Number : "+k);

   System.out.println(Sheet Name : " + workbook.getSheetName(k));
   HSSFSheet sheet = workbook.getSheetAt(k);

}

 생성된 시트를 이용하여 그 행의 수만큼 돌면서 행을 하나씩 생성합니다.

int rows = sheet.getPhysicalNumberOfRows();

for (int r = 0; r < rows; r++) {
   HSSFRow row   = sheet.getRow(r);

   System.out.println("Row : "+row.getRowNum());

}

 역시나 생성된 행을 이용하여 그 셀의 수만큼 돌면서 셀을 하나씩 생성합니다.

int cells = row.getPhysicalNumberOfCells();

for (short c = 0; c < cells; c++) {              <--!! short 형입니다. 255개가 max!
    HSSFCell cell  = row.getCell(c);

    int celltype = cell.getCellType();

    ...

}

셀을 생성하여 셀 타입에 따라 처리를 해주면 끝~

 주의사항

만약 엑셀에서 A열에 아무런 값이 없으면 그 행은 읽지 못합니다.
행을 읽지 못하니 셀또한 처리 할 수 없습니다

VI. 엑셀읽기 샘플소스

샘플 데이터

사용자 삽입 이미지

A열은 B열에 대한 셀 타입을 나타내며 C열은 D열에대한 셀 타입을 나타냅니다.
즉 B:1 의 123456의 셀 타입은 A:1 일반 이라는 것이며 마찬가지로
D:1의 2005-02-09의 셀타입은 C:1 사용자정의로 세팅하였다는 겁니다

이 엑셀의 데이터를 다음 소스로 읽어 보겠습니다.

<%@ page
language="java"
contentType="text/html;charset=euc-kr" 
import="java.io.*,
 org.apache.poi.poifs.filesystem.POIFSFileSystem,
 org.apache.poi.hssf.record.*,
 org.apache.poi.hssf.model.*,
 org.apache.poi.hssf.usermodel.*,
 org.apache.poi.hssf.util.*" %>


<html>
<head><title>Read example</title></head>
<body>

<%

  String excelfile = "C:\\Tomcat 5.0\\webapps\\ROOT\\example.xls";

  try {
       POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelfile));


       //워크북을 생성!               

       HSSFWorkbook workbook = new HSSFWorkbook(fs);

       int sheetNum = workbook.getNumberOfSheets();


       for (int k = 0; k < sheetNum; k++) {


            //시트 이름과 시트번호를 추출
%>

            <br><br>
            Sheet Number <%= k %> <br>
            Sheet Name <%= workbook.getSheetName(k) %><br>
<%
            HSSFSheet sheet = workbook.getSheetAt(k);
            int rows = sheet.getPhysicalNumberOfRows();


            for (int r = 0; r < rows; r++) {


                // 시트에 대한 행을 하나씩 추출
                HSSFRow row   = sheet.getRow(r);
                if (row != null) { 
                     int cells = row.getPhysicalNumberOfCells();
%>
                     ROW  <%= row.getRowNum() %> <%=cells%></b><br>
<%

                     for (short c = 0; c < cells; c++) {


                         // 행에대한 셀을 하나씩 추출하여 셀 타입에 따라 처리
                         HSSFCell cell  = row.getCell(c);
                         if (cell != null) { 
                              String value = null;

                              switch (cell.getCellType()) {

                                   case HSSFCell.CELL_TYPE_FORMULA :
                                       value = "FORMULA value=" + cell.getCellFormula();
                                        break;
                                   case HSSFCell.CELL_TYPE_NUMERIC :
                                       value = "NUMERIC value=" + cell.getNumericCellValue(); //double
                                       break;
                                  case HSSFCell.CELL_TYPE_STRING :
                                       value = "STRING value=" + cell.getStringCellValue(); //String
                                       break;
                                  case HSSFCell.CELL_TYPE_BLANK :
                                      value = null;
                                     break;
                                 case HSSFCell.CELL_TYPE_BOOLEAN :
                                     value = "BOOLEAN value=" + cell.getBooleanCellValue(); //boolean
                                    break;
                                case HSSFCell.CELL_TYPE_ERROR :
                                     value = "ERROR value=" + cell.getErrorCellValue(); // byte
                                     break;
                                default :
                             }
%>         
                          <%= "CELL col=" + cell.getCellNum() + " VALUE=" + value %> <br>
<%
                        } 
                    }
                }
            }
       }
   } catch (Exception e) {
%>
       Error occurred:  <%= e.getMessage() %>
<%   
       e.printStackTrace();
    }

%>


</body>
</html>

위 소스의 결과입니다.

Sheet Number 0 
Sheet Name 한글
ROW 0 4
CELL col=0 VALUE=STRING value=일반 
CELL col=1 VALUE=NUMERIC value=123456.0 
CELL col=2 VALUE=STRING value=사용자정의 
CELL col=3 VALUE=NUMERIC value=38392.0 
ROW 1 4
CELL col=0 VALUE=STRING value=숫자 
CELL col=1 VALUE=NUMERIC value=123456.0 
CELL col=2 VALUE=STRING value=날짜 (yy-m-d h:mm) 
CELL col=3 VALUE=NUMERIC value=38393.0 
ROW 2 4
CELL col=0 VALUE=STRING value=통화 
CELL col=1 VALUE=NUMERIC value=123456.0 
CELL col=2 VALUE=STRING value=날짜 (yy年 mm月 dd日) 
CELL col=3 VALUE=NUMERIC value=38394.0 
ROW 3 4
CELL col=0 VALUE=STRING value=텍스트 
CELL col=1 VALUE=NUMERIC value=123456.0 
CELL col=2 VALUE=STRING value=날짜 (yyyy년 mm월 dd일) 
CELL col=3 VALUE=NUMERIC value=38395.0


결과를 보니 사용자가 지정한 셀 타입에 관계없이 숫자관련 셀은 POI에서 모두 숫자 타입으로 인식해 버렸습니다.날짜 역시 지정한 셀 타입에 관계없이 모두 숫자 타입으로 인식해 버리는군요!
그럼 어떻게 날짜를 제대로 표현할까요?
날짜 타입을 제대로 나타내기 위해서는 날짜 Cell에는 getDateCellValue()를 사용하면 정상적으로 처리 할 수 있습니다.

SimpleDateformat sdf = new SimpleDateformat("yyyy-MM-dd hh:mm");
String date = sdf.format(cell.getDateCellValue());

등을 이용하면 나타내고자 하는 알짜를 표현 하기 더 쉽겠지요 나머지 수식을 가져 올때도 마찬가지입니다. 이런 사항을 도표로 나타내보았습니다.

org.apache.poi.hssf.usermodel.HSSFCell 에는 모두 6가지의 Cell Type이 있는데, cell.getCellType()을 하면 그 셀의 반환값을 알 수 있으며 그에 상응하는 static 필드타입은 다음과 같습니다.

셀타입필드타입

함수

함수반환값
0CELL_TYPE_NUMERIC

getNumericCellValue() 
-> 숫자 타입일때
getDateCellValue()
-> 날짜 타입일때

double

Date

1CELL_TYPE_STRING

getStringCellValue()

String
2CELL_TYPE_FORMULA

getCellFormula()
-> 수식자체를 가져올때
getNumericCellValue()
-> 수식 반환값이 숫자일때
getStringCellValue()
-> 수식 반환값이 문자일때

String

double

String

3CELL_TYPE_BLANK

4CELL_TYPE_BOOLEAN

getBooleanCellValue()

boolean
5CELL_TYPE_ERROR

getErrorCellvalue()

byte