[Java] Excel 엑셀 Read/Write 읽기/쓰기 (POI) - Student 예제

■ Apache POI

우선 엑셀파일을 텍스트 파일처럼 읽고 쓸수는 없습니다. 때문에 Java에서 엑셀파일을 다루기 위해 Apache POI 라이브러리를 사용해야 합니다.

 

Apache POI 라이브러리를 다운받기 위해서는 https://poi.apache.org/download.html 요 주소로접속한 뒤에 스크롤을 내리다 보면

위와같이 Binary Distribution 항목이 있습니다. 여기에서 빨간색 부분(poi-bin-3.15-beta2-...)을 클릭!

 

그 다음 스크롤을 또 내리다 보면 HTTP 항목이 나오게 됩니다. 위 링크를 통해 라이브러리를 다운받아 주세요. 네 가지 링크 모두 같은 링크이니 아무거나 선택하셔도 됩니다.

 

다운받은 압축파일을 열어보면 위와 같이 .jar확장자로 된 라이브러리 들이 있는데요, 빨간색 네모 안에 있는 라이브러리들은 무조건 적용 해 주셔야 합니다.

 

만약 xlsx를 다루고 싶으시다면 초록색 네모로 된 ooxml-lib 폴더의 라이브러리도 적용해 주시면 됩니다.

 

 

프로젝트를 우클릭 하여 Build Path 창을 선택하고

Add External JARs..를 클릭하여 라이브러리를 추가해 주시면 됩니다.

 

 

 

■ 본격적인 엑셀 다루기

엑셀을 파싱하기 먼저 아래 사진을 통해 엑셀의 구조(용어)를 익히고 가면 좋을 것 같습니다. 저 같은 경우 엑셀의 용어를 잘 몰라 햇갈리는 부분이 많이 발생했거든요. 하지만 사진보다는 직접 엑셀을 다뤄보신다면 더할나위 없이 좋을것 같습니다.

◆ 엑셀 파일의 셀 값 출력하기

        try {
            FileInputStream fis = new FileInputStream("불러올 파일 경로/이름.xlsx");
            HSSFWorkbook workbook = new HSSFWorkbook(fis);
            HSSFSheet sheet = workbook.getSheetAt(0); // 해당 엑셀파일의 시트(Sheet) 수
            int rows = sheet.getPhysicalNumberOfRows(); // 해당 시트의 행의 개수
            for (int rowIndex = 1; rowIndex < rows; rowIndex++) {
                HSSFRow row = sheet.getRow(rowIndex); // 각 행을 읽어온다
                if (row != null) {
                    int cells = row.getPhysicalNumberOfCells();
                    for (int columnIndex = 0; columnIndex <= cells; columnIndex++) {
                        HSSFCell cell = row.getCell(columnIndex); // 셀에 담겨있는 값을 읽는다.
                        String value = "";
                        switch (cell.getCellType()) { // 각 셀에 담겨있는 데이터의 타입을 체크하고 해당 타입에 맞게 가져온다.
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            value = cell.getNumericCellValue() + "";
                            break;
                        case HSSFCell.CELL_TYPE_STRING:
                            value = cell.getStringCellValue() + "";
                            break;
                        case HSSFCell.CELL_TYPE_BLANK:
                            value = cell.getBooleanCellValue() + "";
                            break;
                        case HSSFCell.CELL_TYPE_ERROR:
                            value = cell.getErrorCellValue() + "";
                            break;
                        }
                        System.out.println(value);
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
 

◆ 엑셀 파일을 생성하여 셀에 값 삽입하기

        HSSFWorkbook workbook = new HSSFWorkbook(); // 새 엑셀 생성
        HSSFSheet sheet = workbook.createSheet("시트명"); // 새 시트(Sheet) 생성
        HSSFRow row = sheet.createRow(0); // 엑셀의 행은 0번부터 시작
        HSSFCell cell = row.createCell(0); // 행의 셀은 0번부터 시작
        cell.setCellValue("테스트 데이터"); //생성한 셀에 데이터 삽입
        try {
            FileOutputStream fileoutputstream = new FileOutputStream("저장할 경로/이름.xlsx");
            workbook.write(fileoutputstream);
            fileoutputstream.close();
            System.out.println("엑셀파일생성성공");
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("엑셀파일생성실패");
        }

 

위 두 가지를 잘 응용하시면 기존에 있던 엑셀파일을 수정하시거나 새로운 엑셀파일을 생성할 수 있습니다.

아마 2차원 배열을 많이 다뤄보셨다면 엑셀을 다루시는데 큰 어려움은 없으실 것 같습니다.

 

■ 구현

◆ 엑셀 파일 형식

번호	이름	생년월일
1	홍길동	2001-01-31
2	홍길순	2010-02-28
3	홍길자	2019-05-05

 

 

위와 같이 학생 목록의 엑셀을 읽어서

학생 List를 만들것이므로 우선 학생 DTO를 만든다.

 

◆ DTO 생성

 

StudentDTO.java

package kr.co.swk.poi;
 
import java.util.Date;
 
public class StudentDTO {
    Integer id;    // 번호
    String name; // 이름
    Date birthDate;    // 생년월일
    
    public Integer getId() {
        return id;
    }
    
    public void setId(Integer id) {
        this.id = id;
    }
    
    public String getName() {
        return name;
    }
    
    public void setName(String name) {
        this.name = name;
    }
    
    public Date getBirthDate() {
        return birthDate;
    }
    
    public void setBirthDate(Date birthDate) {
        this.birthDate = birthDate;
    }
}
 

◆ pom.xml 에 dependency 추가

메이븐 프로젝트인 경우 디펜던시를 추가한다.

다른 형식의 프로젝트인 경우 해당 프로젝트에 맞추어 라이브러리를 임포트한다.

	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi</artifactId>
	    <version>4.0.1</version>
	</dependency>
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi-ooxml</artifactId>
	    <version>4.0.1</version>
	</dependency>

◆ 엑셀 파일 읽기

 

ExcelManager.java

    public static List<StudentDTO> getStudentList() throws EncryptedDocumentException, IOException {
        List<StudentDTO> studentList = new ArrayList<StudentDTO>();
 
        String filePath = "student.xlsx";
        // String filePath = "C:\\student.xls";
        InputStream inputStream = new FileInputStream(filePath);
 
        // 엑셀 로드
        Workbook workbook = WorkbookFactory.create(inputStream);
        // 시트 로드 0, 첫번째 시트 로드
        Sheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowItr = sheet.iterator();
        // 행만큼 반복
        while (rowItr.hasNext()) {
            StudentDTO student = new StudentDTO();
            Row row = rowItr.next();
            // 첫 번째 행이 헤더인 경우 스킵, 2번째 행부터 data 로드
            if (row.getRowNum() == 0) {
                continue;
            }
            Iterator<Cell> cellItr = row.cellIterator();
            // 한 행이 한열 씩 읽기 (셀 읽기)
            while (cellItr.hasNext()) {
                Cell cell = cellItr.next();
                int index = cell.getColumnIndex();
                switch (index) {
                case 0: // 번호
                    // 셀이 숫자형인 경우 Double형으로 변환 후 int형으로 변환
                    student.setId(((Double) getValueFromCell(cell)).intValue());
                    break;
                case 1: // 성명
                    student.setName((String) getValueFromCell(cell));
                    break;
                case 2: // 생년월일
                    student.setBirthDate((Date) getValueFromCell(cell));
                    break;
                }
            }
            studentList.add(student);
        }
        return studentList;
    }
 
    private static Object getValueFromCell(Cell cell) {
        switch (cell.getCellType()) {
        case STRING:
            return cell.getStringCellValue();
        case BOOLEAN:
            return cell.getBooleanCellValue();
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue();
            }
            return cell.getNumericCellValue();
        case FORMULA:
            return cell.getCellFormula();
        case BLANK:
            return "";
        default:
            return "";
        }
    }

기존에 xls, xlsx 파일을 구분하지 않고 읽는다.

웹시스템에서는 주로 Multipart 형식으로 파일을 업로드 하여 엑셀을 읽으므로

서버에 저장없이 메모리에서 처리가 가능하다.

InputStream을 바로 생성하여 엑셀 파일을 읽으면 된다.

 

◆ 실행결과

 

 

◆ 엑셀 파일 쓰기

 

ExcelManager.java

    public static void writeExcelFile(List<StudentDTO> list) throws EncryptedDocumentException, IOException {
        String filePath = "student_transfer.xlsx";    // 저장할 파일 경로
        
        FileOutputStream fos = new FileOutputStream(filePath);
        
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("studentList");    // sheet 생성
        
        XSSFRow curRow;
        
        int row = list.size();    // list 크기
        for (int i = 0; i < row; i++) {
            curRow = sheet.createRow(i);    // row 생성
            curRow.createCell(0).setCellValue(list.get(i).getId());    // row에 각 cell 저장
            curRow.createCell(1).setCellValue(list.get(i).getName());
            curRow.createCell(2).setCellValue(list.get(i).getBirthDate());
        }
        
        workbook.write(fos);
        fos.close();
    }

◆ 오름차순 기능이 추가된 메인함수

 

ExcelManager.java

    public static void main(String[] args) throws EncryptedDocumentException, IOException {
        List<StudentDTO> studentList = getStudentList();
        
        // id 기준 오름차순 정렬
        Collections.sort(studentList, new Comparator<StudentDTO>() {
            @Override
            public int compare(StudentDTO o1, StudentDTO o2) {
                // TODO Auto-generated method stub
                return o1.getId().compareTo(o2.getId());
            }
        });
        
        for (StudentDTO studentDTO : studentList) {
            System.out.println(studentDTO.getId() + ", " + studentDTO.getName() + ", " + studentDTO.getBirthDate());
        }
        
        writeExcelFile(studentList);
    }

◆ 실행결과

 

■ 소스코드

package kr.co.swk.poi;
 
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
 
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
import kr.co.swk.agency.AgencyDTO;
 
public class ExcelManager {
    public static void main(String[] args) throws EncryptedDocumentException, IOException {
        List<StudentDTO> studentList = getStudentList();
        
        // id 기준 오름차순 정렬
        Collections.sort(studentList, new Comparator<StudentDTO>() {
            @Override
            public int compare(StudentDTO o1, StudentDTO o2) {
                // TODO Auto-generated method stub
                return o1.getId().compareTo(o2.getId());
            }
        });
        
        for (StudentDTO studentDTO : studentList) {
            System.out.println(studentDTO.getId() + ", " + studentDTO.getName() + ", " + studentDTO.getBirthDate());
        }
        
        writeExcelFile(studentList);
    }
 
    public static void writeExcelFile(List<StudentDTO> list) throws EncryptedDocumentException, IOException {
        String filePath = "student_transfer.xlsx";    // 저장할 파일 경로
        
        FileOutputStream fos = new FileOutputStream(filePath);
        
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("studentList");    // sheet 생성
        
        XSSFRow curRow;
        
        int row = list.size();    // list 크기
        for (int i = 0; i < row; i++) {
            curRow = sheet.createRow(i);    // row 생성
            curRow.createCell(0).setCellValue(list.get(i).getId());    // row에 각 cell 저장
            curRow.createCell(1).setCellValue(list.get(i).getName());
            curRow.createCell(2).setCellValue(list.get(i).getBirthDate());
        }
        
        workbook.write(fos);
        fos.close();
    }
    
    public static List<StudentDTO> getStudentList() throws EncryptedDocumentException, IOException {
        List<StudentDTO> studentList = new ArrayList<StudentDTO>();
 
        String filePath = "student.xlsx";
        // String filePath = "C:\\student.xls";
        InputStream inputStream = new FileInputStream(filePath);
 
        // 엑셀 로드
        Workbook workbook = WorkbookFactory.create(inputStream);
        // 시트 로드 0, 첫번째 시트 로드
        Sheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowItr = sheet.iterator();
        // 행만큼 반복
        while (rowItr.hasNext()) {
            StudentDTO student = new StudentDTO();
            Row row = rowItr.next();
            // 첫 번째 행이 헤더인 경우 스킵, 2번째 행부터 data 로드
            if (row.getRowNum() == 0) {
                continue;
            }
            Iterator<Cell> cellItr = row.cellIterator();
            // 한 행이 한열 씩 읽기 (셀 읽기)
            while (cellItr.hasNext()) {
                Cell cell = cellItr.next();
                int index = cell.getColumnIndex();
                switch (index) {
                case 0: // 번호
                    // 셀이 숫자형인 경우 Double형으로 변환 후 int형으로 변환
                    student.setId(((Double) getValueFromCell(cell)).intValue());
                    break;
                case 1: // 성명
                    student.setName((String) getValueFromCell(cell));
                    break;
                case 2: // 생년월일
                    student.setBirthDate((Date) getValueFromCell(cell));
                    break;
                }
            }
            studentList.add(student);
        }
        return studentList;
    }
 
    private static Object getValueFromCell(Cell cell) {
        switch (cell.getCellType()) {
        case STRING:
            return cell.getStringCellValue();
        case BOOLEAN:
            return cell.getBooleanCellValue();
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue();
            }
            return cell.getNumericCellValue();
        case FORMULA:
            return cell.getCellFormula();
        case BLANK:
            return "";
        default:
            return "";
        }
    }
}