POI Library를 이용한 Excel Read

Apache POI

Grid 형태의 데이터를 핸들링 하는 화면에는 보통 '엑셀다운로드', '엑셀업로드' 버튼이 존재한다. 이를 구현하기 위해서 Apache에서 제공하는 POI 라이브러리를 이용하는 방법을 알아보도록 하자. Java로 작성된 Client 어플리케이션의 경우 엑셀업로드는 Client 어플리케이션이 실행되는 로컬에서 파일에 접근하여 내용을 read하여 서버로 전송할 데이터로 변경하면 되기 때문에 본 포스트는 엑셀 파일에 접근하고 내용을 read하는 방법을 다루기로 한다.

[위키백과] 

아파치 POI(Apache POI)는 아파치 소프트웨어 재단에서 만든 라이브러리로서 마이크로소프트 오피스 파일 포맷을 순수 자바 언어로서 읽고 쓰는 기능을 제공한다. 주로 워드, 엑셀, 파워포인트와 파일을 지원하며 최근의 오피스 포맷인 Office Open XML File Formats (OOXML, 즉 xml 기반의 *.docx, *.xlsx, *.pptx 등) 이나 아웃룩, 비지오, 퍼블리셔 등으로 지원 파일 포맷을 늘려가고 있다.

라이브러리 다운로드

• http://poi.apache.org/download.html
• 2016.01.17 기준 Window OS 기준 last stable 버젼인 poi-bin-3.13-20150929.zip 다운로드 했다.

<참고사항> 

Window OS 인 경우 : poi-bin-*.zip

Linux / Unix OS인 경우 : poi-bin-*.tar.gz

Example

엑셀파일

아래와 같은 내용의 엑셀 파일을 xls, xlsx 형태로 각각 저장

POI를 이용하여 엑셀 파일을 파싱하는 경우 아래와 같이 각 xls, xlsx에 따라 다른 클래스를 이용하여 처리하기 때문에 각각 파일을 따로 준비한다.

소스코딩

[CustomerVo Class]

엑셀 파일의 칼럼 항목과 동일하게 구성된 Vo

package com.javaking.vo;
 
public class CustomerVo {
 
    private String  custId;        //고객ID
    private String  custName;    //고객명
    private String    custAge;    //고객나이
    private String    custEmail;    //고객이메일
    
    public String getCustId() {
        return custId;
    }
    public void setCustId(String custId) {
        this.custId = custId;
    }
    public String getCustName() {
        return custName;
    }
    public void setCustName(String custName) {
        this.custName = custName;
    }
    public String getCustAge() {
        return custAge;
    }
    public void setCustAge(String custAge) {
        this.custAge = custAge;
    }
    public String getCustEmail() {
        return custEmail;
    }
    public void setCustEmail(String custEmail) {
        this.custEmail = custEmail;
    }
    
    @Override
    public String toString() {
        StringBuffer sb = new StringBuffer();
        
        sb.append("ID : " + custId);
        sb.append(" ,NAME : " + custName);
        sb.append(" ,AGE : " + custAge);
        sb.append(" ,EMAIL : " + custEmail);
        return sb.toString();
    }
}

 

[CustomerExcelReader Class]

파일의 경로를 입력받아 List<CustomerVo>의 형태로 리턴해주는 메소드를 구현

실제로 xsl이나, xslx나 로직의 차이는 없고, 메소드역시 동일하다. 클래스 명만 변경하여 주면 된다.

주요요점은 Workbook, Sheet, Row, Cell의 순서로 엑셀 파일의 내용을 탐색하는데 있다.

• xsl : HSSFWorkbook -> HSSFSheet -> HSSFRow -> HSSFCell -> 값 획득
• xslx : XSSFWorkbook -> XSSFSheet -> XSSFRow -> XSSFCell -> 값 획득

package com.javaking.excel;
 
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
 
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
import com.javaking.vo.CustomerVo;
 
public class CustomerExcelReader {
    
    /**
     * XLS 파일을 분석하여 List<CustomerVo> 객체로 반환
     * @param filePath
     * @return
     */
    @SuppressWarnings("resource")
    public List<CustomerVo> xlsToCustomerVoList(String filePath) {
        
        // 반환할 객체를 생성
        List<CustomerVo> list = new ArrayList<CustomerVo>();
        
        FileInputStream fis = null;
        HSSFWorkbook workbook = null;
        
        try {
            
            fis= new FileInputStream(filePath);
            // HSSFWorkbook은 엑셀파일 전체 내용을 담고 있는 객체
            workbook = new HSSFWorkbook(fis);
            
            // 탐색에 사용할 Sheet, Row, Cell 객체
            HSSFSheet curSheet;
            HSSFRow   curRow;
            HSSFCell  curCell;
            CustomerVo vo;
            
            // Sheet 탐색 for문
            for(int sheetIndex = 0 ; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                // 현재 Sheet 반환
                curSheet = workbook.getSheetAt(sheetIndex);
                // row 탐색 for문
                for(int rowIndex=0; rowIndex < curSheet.getPhysicalNumberOfRows(); rowIndex++) {
                    // row 0은 헤더정보이기 때문에 무시
                    if(rowIndex != 0) {
                        // 현재 row 반환
                        curRow = curSheet.getRow(rowIndex);
                        vo = new CustomerVo();
                        String value;
                        
                        // row의 첫번째 cell값이 비어있지 않은 경우 만 cell탐색
                        if(!"".equals(curRow.getCell(0).getStringCellValue())) {
                            
                            // cell 탐색 for 문
                            for(int cellIndex=0;cellIndex<curRow.getPhysicalNumberOfCells(); cellIndex++) {
                                curCell = curRow.getCell(cellIndex);
                                
                                if(true) {
                                    value = "";
                                    // cell 스타일이 다르더라도 String으로 반환 받음
                                    switch (curCell.getCellType()){
                                    case HSSFCell.CELL_TYPE_FORMULA:
                                        value = curCell.getCellFormula();
                                        break;
                                    case HSSFCell.CELL_TYPE_NUMERIC:
                                        value = curCell.getNumericCellValue()+"";
                                        break;
                                    case HSSFCell.CELL_TYPE_STRING:
                                        value = curCell.getStringCellValue()+"";
                                        break;
                                    case HSSFCell.CELL_TYPE_BLANK:
                                        value = curCell.getBooleanCellValue()+"";
                                        break;
                                    case HSSFCell.CELL_TYPE_ERROR:
                                        value = curCell.getErrorCellValue()+"";
                                        break;
                                    default:
                                        value = new String();
                                        break;
                                    }
                                    
                                    // 현재 column index에 따라서 vo에 입력
                                    switch (cellIndex) {
                                    case 0: // 아이디
                                        vo.setCustId(value);;
                                        break;
                                        
                                    case 1: // 이름
                                        vo.setCustName(value);;
                                        break;
                                        
                                    case 2: // 나이
                                        vo.setCustAge(value);
                                        break;
                                        
                                    case 3: // 이메일
                                        vo.setCustEmail(value);
                                        break;
        
                                    default:
                                        break;
                                    }
                                }
                            }
                            // cell 탐색 이후 vo 추가
                            list.add(vo);
                        }
                    }
                }
            }
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            
        } finally {
            try {
                // 사용한 자원은 finally에서 해제
                if( workbook!= null) workbook.close();
                if( fis!= null) fis.close();
                
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return list;
    }
    
    /**
     * XLSX 파일을 분석하여 List<CustomerVo> 객체로 반환
     * @param filePath
     * @return
     */
    public List<CustomerVo> xlsxToCustomerVoList(String filePath) {
        // 반환할 객체를 생성
        List<CustomerVo> list = new ArrayList<CustomerVo>();
        
        FileInputStream fis = null;
        XSSFWorkbook workbook = null;
        
        try {
            
            fis= new FileInputStream(filePath);
            // HSSFWorkbook은 엑셀파일 전체 내용을 담고 있는 객체
            workbook = new XSSFWorkbook(fis);
            
            // 탐색에 사용할 Sheet, Row, Cell 객체
            XSSFSheet curSheet;
            XSSFRow   curRow;
            XSSFCell  curCell;
            CustomerVo vo;
            
            // Sheet 탐색 for문
            for(int sheetIndex = 0 ; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                // 현재 Sheet 반환
                curSheet = workbook.getSheetAt(sheetIndex);
                // row 탐색 for문
                for(int rowIndex=0; rowIndex < curSheet.getPhysicalNumberOfRows(); rowIndex++) {
                    // row 0은 헤더정보이기 때문에 무시
                    if(rowIndex != 0) {
                        // 현재 row 반환
                        curRow = curSheet.getRow(rowIndex);
                        vo = new CustomerVo();
                        String value;
                        
                        // row의 첫번째 cell값이 비어있지 않은 경우 만 cell탐색
                        if(!"".equals(curRow.getCell(0).getStringCellValue())) {
                            
                            // cell 탐색 for 문
                            for(int cellIndex=0;cellIndex<curRow.getPhysicalNumberOfCells(); cellIndex++) {
                                curCell = curRow.getCell(cellIndex);
                                
                                if(true) {
                                    value = "";
                                    // cell 스타일이 다르더라도 String으로 반환 받음
                                    switch (curCell.getCellType()){
                                    case HSSFCell.CELL_TYPE_FORMULA:
                                        value = curCell.getCellFormula();
                                        break;
                                    case HSSFCell.CELL_TYPE_NUMERIC:
                                        value = curCell.getNumericCellValue()+"";
                                        break;
                                    case HSSFCell.CELL_TYPE_STRING:
                                        value = curCell.getStringCellValue()+"";
                                        break;
                                    case HSSFCell.CELL_TYPE_BLANK:
                                        value = curCell.getBooleanCellValue()+"";
                                        break;
                                    case HSSFCell.CELL_TYPE_ERROR:
                                        value = curCell.getErrorCellValue()+"";
                                        break;
                                    default:
                                        value = new String();
                                        break;
                                    }
                                    
                                    // 현재 column index에 따라서 vo에 입력
                                    switch (cellIndex) {
                                    case 0: // 아이디
                                        vo.setCustId(value);;
                                        break;
                                        
                                    case 1: // 이름
                                        vo.setCustName(value);;
                                        break;
                                        
                                    case 2: // 나이
                                        vo.setCustAge(value);
                                        break;
                                        
                                    case 3: // 이메일
                                        vo.setCustEmail(value);
                                        break;
        
                                    default:
                                        break;
                                    }
                                }
                            }
                            // cell 탐색 이후 vo 추가
                            list.add(vo);
                        }
                    }
                }
            }
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            
        } finally {
            try {
                // 사용한 자원은 finally에서 해제
                if( workbook!= null) workbook.close();
                if( fis!= null) fis.close();
                
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return list;
    }
}
 

[MainApplication Class]

실제 엑셀 파일 read를 테스트 하여 출력한다.

 
import java.util.List;
 
import com.javaking.excel.CustomerExcelReader;
import com.javaking.vo.CustomerVo;
 
 
public class MainApplication {
    
    public static void main(String[] args) {
        
        CustomerExcelReader excelReader = new CustomerExcelReader();
        
        System.out.println("*****xls*****");
        List<CustomerVo> xlsList = excelReader.xlsToCustomerVoList("C:\\excel\\test.xls");
        printList(xlsList );
        
        System.out.println("*****xlsx*****");
        List<CustomerVo> xlsxList = excelReader.xlsxToCustomerVoList("C:\\excel\\test.xlsx");
        printList(xlsxList );
    }
    
    public static void printList(List<CustomerVo> list) {
        CustomerVo vo;
        
        for (int i = 0; i < list.size(); i++) {
            vo = list.get(i);
            System.out.println(vo.toString());
        }
    }
}
 

수행결과

 *****xls*****

ID : javaking ,NAME : 이지수 ,AGE : 31.0 ,EMAIL : asdf9090@nate.com

ID : hong ,NAME : 홍길동 ,AGE : 50.0 ,EMAIL : hong@gmail.com

ID : kim ,NAME : 김철수 ,AGE : 20.0 ,EMAIL : kcs@daum.net

*****xlsx*****

ID : javaking ,NAME : 이지수 ,AGE : 31.0 ,EMAIL : asdf9090@nate.com

ID : hong ,NAME : 홍길동 ,AGE : 50.0 ,EMAIL : hong@gmail.com

ID : kim ,NAME : 김철수 ,AGE : 20.0 ,EMAIL : kcs@daum.net

참고사항

xls형식

• Microsoft Excel 97-2003 버젼 사용되는 형식
• 최대 256컬럼, 65,536행 제한 
• POI를 이용하여 데이터 접근 시 org.apache.poi.hssf 패키지 이용
• org.apache.poi.hssf.usermodel.HSSFWorkbook
• org.apache.poi.hssf.usermodel.HSSFSheet
• org.apache.poi.hssf.usermodel.HSSFRow
• org.apache.poi.hssf.usermodel.HSSFCell

xlsx형식

• Microsoft Excel 2007 이 후 버젼 사용되는 형식
• 최대 16,384컬럼, 1,048,567행 제한
• POI를 이용하여 데이터 접근 시 org.apache.poi.xssf패키지 이용
• org.apache.poi.xssf.usermodel.XSSFWorkbook
• org.apache.poi.xssf.usermodel.XSSFSheet
• org.apache.poi.xssf.usermodel.XSSFRow
• org.apache.poi.xssf.usermodel.XSSFCell

다운로드

(용량제한으로 2개 파일로 나누어 예제를 올림)

POIExcelSample.vol1.egg

POIExcelSample.vol2.egg

test.xls

test.xlsx

같이보기

POI Library를 이용한 Excel Write (http://javaslave.tistory.com/79)