[java/Playground] Apache POI 엑셀 파일 다루기

Apache POI 응용 라이브러리

---

Apache POI는 Java에서 엑셀을 다룰 수 있게 도와주는 라이브러리이다.

회사에서 Excel 파일을 파싱 할 일이 꽤나 있어서 자주 쓰는 기능을 라이브러리 화했다.

 

gradle로 apachepoi 라이브러리를 import 한 뒤, 아래 gitlab 코드나 full code를 복붙해서 사용하면 된다.

귀찮아서 SpringBoot 프로젝트를 만들어서 gradle로 apache poi 라이브러리를 import 했다.

implementation 'org.apache.poi:poi-ooxml:5.2.3'

참고 링크

JAVA & Spring/라이브러리 & Tools] - [Apache POI] Java 코드로 엑셀(.xlsx) 파일 생성하기 - Java로 Excel 다루기

[Apache POI] Java 코드로 엑셀(.xlsx) 파일 생성하기 - Java로 Excel 다루기

 

Gitlab link

깃랩 링크 또는 파일 내용은 수정될 수 있습니다. 안되면 댓글 남겨주세요.

https://gitlab.com/happyhsryu/java-code-repo/-/blob/3536b6e308612a2f9cffc83fe37251bb164aeabe/file-handling/ExcelFileHandling.java

file-handling/ExcelFileHandling.java · 3536b6e308612a2f9cffc83fe37251bb164aeabe · 류현식 / Java Code Repo · GitLab

full 코드

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

/**
 * gradle import APACHE_POI : implementation 'org.apache.poi:poi-ooxml:5.2.3'
 */
public class ExcelFileHandler {

    /**
     * Excel 파일 읽어서 첫번째 sheet 읽기
     * @param excelFilePath ex) "C:\\Users\\happy\\Desktop\\xxx\\test.xlsx"
     * @return
     */
    public List<List<String>> readExcelFile(String excelFilePath) {
        return this.getSheetData(this.getSheetByNumber(this.readExcelFileWorkBook(excelFilePath), 0));
    }

    /**
     * Excel 파일 읽기
     * @param excelFilePath ex) "C:\\Users\\happy\\Desktop\\xxx\\test.xlsx"
     * @return
     */
    public XSSFWorkbook readExcelFileWorkBook(String excelFilePath) {
        XSSFWorkbook workbook = null;
        try {
            FileInputStream fis = new FileInputStream(new File(excelFilePath));
            workbook = new XSSFWorkbook(fis);
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println("엑셀 읽기 성공:" + workbook);
        return workbook;
    }

    /**
     * workbook에서 sheet 찾기
     * @param workbook
     * @param sheetName sheet 이름
     * @return
     */
    public XSSFSheet getSheetByName(XSSFWorkbook workbook, String sheetName){
        return workbook.getSheet(sheetName);
    }

    /**
     * workbook에서 sheet 찾기
     * @param workbook
     * @param sheetNumber sheet 번호(0부터 시작)
     * @return
     */
    public XSSFSheet getSheetByNumber(XSSFWorkbook workbook, int sheetNumber){
        return workbook.getSheetAt(sheetNumber);
    }

    /**
     * sheet data 파싱
     * @param sheet
     * @return
     */
    public List<List<String>> getSheetData(XSSFSheet sheet) {
        List<List<String>> dataList = new ArrayList<>();
        // 모든 행을 반복합니다.
        for (Row row : sheet) {
            List<String> rowData = new ArrayList<>();
            // 각 행의 모든 셀을 반복합니다.
            for (Cell cell : row) {
                // 셀 타입에 따라 값을 가져옵니다.
                //String value = getCellValue(cell);
                String value = cell.getStringCellValue();
                if (value.matches("\\d{4}-\\d{2}-\\d{2}")) {
                    value = value.replaceAll("-", "");
                }
                rowData.add(value);
            }
            dataList.add(rowData);
        }
        System.out.println("엑셀 읽기 성공:" + dataList.size() + "건");
        return dataList;
    }

    /**
     * 엑셀 파일 생성 (byte Array)
     * @param sheetName
     * @param headers
     * @param dataList
     * @return
     */
    public byte[] createExcelFileBytes(String sheetName, List<String> headers, List<List<String>> dataList) {
        //Excel Sheet 생성
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(sheetName);

        //Setting Headers
        this.setOutputHeader(sheet, headers);
        //Setting data
        this.setOutputData(sheet, dataList);
        //create excelFileBytes
        return this.createByteFile(workbook);
    }

    /**
     * 헤더 세팅(첫째줄 컬럼)
     * @param sheet
     * @param headers
     */
    private void setOutputHeader(Sheet sheet, List<String> headers){
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.size(); i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers.get(i));
        }
    }

    /**
     * 데이터 세팅(둘째줄 부터)
     * @param sheet
     * @param dataList
     */
    private void setOutputData(Sheet sheet, List<List<String>> dataList){
        int colCount = dataList.get(0).size();
        for (int i = 0; i < dataList.size(); i++) {
            Row row = sheet.createRow(i + 1);
            List<String> data = dataList.get(i);
            for (int j = 0; j < colCount; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(data.get(j));
            }
        }
    }

    /**
     * 엑셀 파일 byte[] 생성
     * @param workbook
     * @return
     */
    private byte[] createByteFile(Workbook workbook){
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        try {
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();
        } catch (IOException e) {
            System.out.println("엑셀 파일 생성 실패");
            e.printStackTrace();
        }

        return outputStream.toByteArray();
    }

    /**
     * 엑셀 파일 저장
     * @param excelFile byte Array
     * @param fileDir ex) "C:\\Users\\happy\\Desktop\\xxx\\"
     * @param fileName ex) "test.xlsx"
     */
    public void saveFile(byte[] excelFile, String fileDir, String fileName){
        try {
            // 파일 저장
            FileOutputStream fos = new FileOutputStream(fileDir + fileName);
            fos.write(excelFile);
            fos.close();
            System.out.println("엑셀 파일 저장 성공. path=" + fileDir + fileName);
        } catch (IOException e) {
            System.out.println("엑셀 파일 저장 실패");
            e.printStackTrace();
        }
    }

    /**
     * cell의 값을 파싱
     * @param cell
     * @return
     */
    private String getCellValue(Cell cell) {
        String value = "";
        switch (cell.getCellType()) {
            case STRING:
                value = cell.getStringCellValue();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue().toString();
                    System.out.println(value);
                } else {
                    value = Double.toString(cell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                value = Boolean.toString(cell.getBooleanCellValue());
                break;
            case FORMULA:
                value = cell.getCellFormula();
                break;
            default:
                value = "";
        }
        return value;
    }
}

 

ExcelFileHandler

---

라이브러리를 사용하는 예시

    public static void test(){
        String beforeFileName = "test.xlsx";
        List<List<String>> dataList = excelFileHandler.readExcelFile(TEST_FOLDER_PATH + beforeFileName);
        dataList.remove(0);//설명로우 제거
        dataList.remove(0);//헤더 제거
        dataHandler.sortList(dataList, 1, 5); //조건에 따라 정렬
        List<List<String>> result = dataHandler.removeDuplicates(dataList, 4, 5); //조건에 따라 중복 제거
        
        List<List<String>> newFileData = dataHandler.colMapping(dataList, ExcelConstants.업로드MAP());

        String fileName = "output" + beforeFileName;
        byte[] excelFileBytes = excelFileHandler.createExcelFileBytes("기관표준단어", ExcelConstants.기관표준단어업로드헤더(),newFileData);
        excelFileHandler.saveFile(excelFileBytes, TEST_FOLDER , fileName);
    }

 

엑셀 파일 읽기(기본, 첫 번째 sheet)

기본적으로 엑셀 파일의 첫번째 시트를 읽어오는 메서드

아래 3개의 메서드를 기본값으로 호출한다.

/**
 * Excel 파일 읽어서 첫번째 sheet 읽기
 * @param excelFilePath ex) "C:\\Users\\happy\\Desktop\\xxx\\test.xlsx"
 * @return
 */
public List<List<String>> readExcelFile(String excelFilePath) {
    return this.getSheetData(this.getSheetByNumber(this.readExcelFileWorkBook(excelFilePath), 0));
}

---

엑셀 파일을 읽어서 Workbook 객체 반환

workbook은 여러 sheet를 포함한 엑셀 파일 자체라고 이해하면 좋다.

workbook 하나에 여러 개의 sheet가 있어서 getSheet() 등의 메서드로 sheet를 선택할 수 있다.

/**
 * Excel 파일 읽기
 * @param excelFilePath ex) "C:\\Users\\happy\\Desktop\\xxx\\test.xlsx"
 * @return
 */
public XSSFWorkbook readExcelFileWorkBook(String excelFilePath) {
    XSSFWorkbook workbook = null;
    try {
        FileInputStream fis = new FileInputStream(new File(excelFilePath));
        workbook = new XSSFWorkbook(fis);
    } catch (IOException e) {
        e.printStackTrace();
    }
    System.out.println("엑셀 읽기 성공:" + workbook);
    return workbook;
}

---

sheet 선택(이름)

/**
 * workbook에서 sheet 찾기
 * @param workbook
 * @param sheetName sheet 이름
 * @return
 */
public XSSFSheet getSheetByName(XSSFWorkbook workbook, String sheetName){
    return workbook.getSheet(sheetName);
}

---

sheet 선택(index)

/**
 * workbook에서 sheet 찾기
 * @param workbook
 * @param sheetNumber sheet 번호(0부터 시작)
 * @return
 */
public XSSFSheet getSheetByNumber(XSSFWorkbook workbook, int sheetNumber){
    return workbook.getSheetAt(sheetNumber);
}

---

 

전체 데이터 파싱

엑셀 파일은 row, column으로 이루어진 표 형태의 데이터를 담고 있다.

따라서 List<List<String>>에 데이터를 담을 수 있다.

데이터를 전부 파싱 해서 해당 자료구조에 담는 메서드이다.

여기서, 실제 데이터 이외에 헤더 등도 모두 담기니 필요 없는 부분은 직접 제거하고 사용해야 한다.

/**
 * sheet data 파싱
 * @param sheet
 * @return
 */
public List<List<String>> getSheetData(XSSFSheet sheet) {
    List<List<String>> dataList = new ArrayList<>();
    // 모든 행을 반복합니다.
    for (Row row : sheet) {
        List<String> rowData = new ArrayList<>();
        // 각 행의 모든 셀을 반복합니다.
        for (Cell cell : row) {
            // 셀 타입에 따라 값을 가져옵니다.
            //String value = getCellValue(cell);
            String value = cell.getStringCellValue();
            if (value.matches("\\d{4}-\\d{2}-\\d{2}")) {
                value = value.replaceAll("-", "");
            }
            rowData.add(value);
        }
        dataList.add(rowData);
    }
    System.out.println("엑셀 읽기 성공:" + dataList.size() + "건");
    return dataList;
}

---

엑셀 파일(byte[]) 생성

엑셀 파일은 Java에서 byte[]로 표현된다.

List <String> header는 엑셀 맨 위 row의 칼럼명에 해당된다.

그다음부터 row별로 dataList 값이 들어가게 된다.

/**
 * 엑셀 파일 생성 (byte Array)
 * @param sheetName
 * @param headers
 * @param dataList
 * @return
 */
public byte[] createExcelFileBytes(String sheetName, List<String> headers, List<List<String>> dataList) {
    //Excel Sheet 생성
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet(sheetName);

    //Setting Headers
    this.setOutputHeader(sheet, headers);
    //Setting data
    this.setOutputData(sheet, dataList);
    //create excelFileBytes
    return this.createByteFile(workbook);
}

private void setOutputHeader(Sheet sheet, List<String> headers){
    Row headerRow = sheet.createRow(0);
    for (int i = 0; i < headers.size(); i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellValue(headers.get(i));
    }
}

private void setOutputData(Sheet sheet, List<List<String>> dataList){
    int colCount = dataList.get(0).size();
    for (int i = 0; i < dataList.size(); i++) {
        Row row = sheet.createRow(i + 1);
        List<String> data = dataList.get(i);
        for (int j = 0; j < colCount; j++) {
            Cell cell = row.createCell(j);
            cell.setCellValue(data.get(j));
        }
    }
}

private byte[] createByteFile(Workbook workbook){
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    try {
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
    } catch (IOException e) {
        System.out.println("엑셀 파일 생성 실패");
        e.printStackTrace();
    }

    return outputStream.toByteArray();
}

---

엑셀 파일 저장

만든 byte[] 엑셀 파일을 실제 컴퓨터에 저장하는 과정이다.

/**
 * 엑셀 파일 저장
 * @param excelFile byte Array
 * @param fileDir ex) "C:\\Users\\happy\\Desktop\\xxx\\"
 * @param fileName ex) "test.xlsx"
 */
public void saveFile(byte[] excelFile, String fileDir, String fileName){
    try {
        // 파일 저장
        FileOutputStream fos = new FileOutputStream(fileDir + fileName);
        fos.write(excelFile);
        fos.close();
        System.out.println("엑셀 파일 저장 성공. path=" + fileDir + fileName);
    } catch (IOException e) {
        System.out.println("엑셀 파일 저장 실패");
        e.printStackTrace();
    }
}