Java

Aparche POI Excel을 이용한 엑셀 파일 읽기

까오기 2019. 1. 10. 20:11

요즘 프로젝트 리팩토링을 하고 있습니다. 

엑셀 관련 된 부분이 엉망인데 그 엉망인 코드를 복사해서 여기 저기 쓰고 있었습니다. 

깔끔한 소스, 중복제거, 재사용 가능할 것을 목표로 만들었습니다. 

1. 요구사항 

프로젝트의 "/src/main/resources/static/format" 디렉토리 아래 "testExcel.xlsm"를 읽는다. 
해당 엑셀의 3번째 시트의 데이터를 가져온다.  
해당 엑셀의 시트명("Sheet3")으로 데이터를 가져온다.
데이터는 List에 자료구조(Map)으로 반환한다. 
데이터는 List에 객체로 반환한다. 
헤더를 정의해서 가져온다.  
엑셀의 첫번째 Row를 헤더로 사용하는 경우도 있다. 
원하는 데이터를 스킵할 수 있어야 한다. 

2. Gradle Dependencies

    compile ('org.apache.poi:poi:3.7')

    compile ('org.apache.poi:poi-ooxml:3.7')

3. 개발

ExcelSettings.java

공통으로 만들어 놓고 보니 어떤 설정을 해야 할지 설명해야 하는 이슈가 생겼습니다. 
그래서 설정 관련된 클래스를 별도로 만들고 거기 값 채워서 넘기세요라고 하면 편할거 같아서 만들었습니다.  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
package eblo.refactoring.excel;
 
import java.util.List;
import java.util.Map;
 
import eblo.common.domain.BaseObject;
import lombok.Getter;
import lombok.Setter;
 
@Getter
@Setter
public class ExcelSettings extends BaseObject{
 
    private static final long serialVersionUID = 1L;
 
    /* 엑셀 파일 읽는 경우 옵션 */
    // [필수]읽는 파일 경로 
    private String excelFilePath;
    // [필수]읽는 파일명
    private String excelFileName;
    
    // [선택필수(sheetIdx/sheetName)]엑셀 시트번호
    private Integer sheetIdx;
    // [선택필수(sheetIdx/sheetName)]엑셀 시트명
    private String sheetName;
    // [조건필수(isFirstRowHeader값이 false인 경우)]헤더 정보  
    private String[] header;
    // 첫번째 row를 헤더로 사용하는 경우 true로 설정, 기본은 false 
    private boolean isFirstRowHeader;
    // 첫번째 값이 헤더인 경우는 skipRowCount = 1로 변함. 사용자가 스킵할 row 지정하는 옵션  
    private int skipRowIdx;
 
    /* 엑셀 파일 쓰기하는 경우 옵션 */
    // [선택필수(data/dataObject)]엑셀 쓰기할 때 필수
    private List<Map<String, Object>> data;
 
    private int headerSize = 0;
 
    // 기본 설정
    public ExcelSettings() {
        this.sheetIdx = 0;
        this.skipRowIdx = -1;
        this.isFirstRowHeader = false;
    }
    
    public void setHeader(String[] header) {
        if(header == null) {
            return;
        }
        this.header = header;
        this.headerSize = header.length;
    }
 
    public void setFirstRowHeader(boolean allow) {
        this.isFirstRowHeader = allow;
        if(allow && this.skipRowIdx == -1) {
            this.skipRowIdx = 0;
        }
    }
    
    public void setSkipRowCount(int skipRowCount) {
        this.skipRowIdx = skipRowCount-1;  // skipRowCount는 갯수, skipRowIdx는 0부터 시작함.
    }
}
 
cs

ExcelCommon.java

read와 write에서 사용하는 공통 클래스입니다 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
package eblo.refactoring.excel;
 
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
 
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.Assert;
 
import eblo.common.exception.EbloNotFoundException;
import eblo.common.exception.EbloSystemException;
import eblo.refactoring.message.MessageUtil;
 
public class ExcelCommon {
    
    protected XSSFWorkbook wb;
    protected ExcelSettings settings;
    
    public ExcelCommon() {
        super();
    }
    
    protected String getHeaderKey(int colId) {
        Assert.isTrue(settings.getHeaderSize() >= (colId+1), MessageUtil.getMessage("excel.header.notfound")); 
        return settings.getHeader()[colId];
    }
 
    protected String[] extractHeader(Row row) {
        List<String> headers = new ArrayList<>();
        for (Cell cell : row) {
            headers.add(cell.getStringCellValue());
        }
        return headers.toArray(new String[0]);
    }
    
    protected void initCommon(ExcelSettings settings){
        this.settings = settings;
        Assert.notNull(settings, MessageUtil.getMessage("excel.settings.requried")); 
 
        Assert.notNull(settings.getExcelFileName(), MessageUtil.getMessage("excel.filename.requried"));
        Assert.notNull(settings.getExcelFilePath(), MessageUtil.getMessage("excel.filepath.requried")); 
 
        File exFile = new File(settings.getExcelFilePath()+settings.getExcelFileName());
        Assert.isTrue(exFile.exists(), MessageUtil.getMessage("excel.file.notfound"));
        try(FileInputStream excelFile = new FileInputStream(exFile); ){
            this.wb = new XSSFWorkbook(excelFile);
        } catch (FileNotFoundException e) {
            throw new EbloNotFoundException(MessageUtil.getMessage("excel.file.notfound"), e);
        } catch (IOException ie) {
            throw new EbloSystemException(MessageUtil.getMessage("excel.file.readfail"), ie);
        }
        
        Assert.isTrue(settings.getSheetName() != null || settings.getSheetIdx() != null, MessageUtil.getMessage("excel.sheetnameorsheetidx.requried")); 
        // 시트명 설정 
        if(settings.getSheetName() != null) {
            settings.setSheetIdx(wb.getSheetIndex(settings.getSheetName()));
            // 시트명 유효 확인 
            Assert.isTrue(settings.getSheetIdx() > -1, MessageUtil.getMessage("excel.error.sheet.notexist"));
        }
        // 유효 시트인지 확인 
        Assert.isTrue(settings.getSheetIdx() <= (wb.getNumberOfSheets()-1), MessageUtil.getMessage("excel.sheetidx.notvalid"));
    }
 
    protected boolean checkIfRowIsEmpty(Row row) {
        if (row == null) {
            return true;
        }
        if (row.getLastCellNum() <= 0) {
            return true;
        }
        for (int cellNum = row.getFirstCellNum(); cellNum < row.getLastCellNum(); cellNum++) {
            Cell cell = row.getCell(cellNum);
            if (cell != null && cell.getCellType() != HSSFCell.CELL_TYPE_BLANK && StringUtils.isNotBlank(cell.toString())) {
                return false;
            }
        }
        return true;
    }
    
    protected String getExcelValue(Cell cell) {
        switch (cell.getCellType()) { // 각 셀에 담겨있는 데이터의 타입을 체크하고, 해당 타입에 맞게 가져온다.
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    return dateFormat.format(cell.getDateCellValue());
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_BLANK:
                return "";
            case Cell.CELL_TYPE_ERROR:
                return String.valueOf(cell.getErrorCellValue());
            default : 
                return cell.getStringCellValue();
        }
    }
 
}
cs

ExcelReader

ExcelSettings에 설정값을 채워서 생성하고 read()하고 getResult()를 반환합니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
package eblo.refactoring.excel;
 
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.springframework.util.Assert;
 
import com.fasterxml.jackson.databind.DeserializationFeature;
import com.fasterxml.jackson.databind.JavaType;
import com.fasterxml.jackson.databind.ObjectMapper;
 
import eblo.refactoring.message.MessageUtil;
 
public class ExcelReader extends ExcelCommon {
    
    private List<Map<String, Object>> data;
    
    private void addItem(Map<String, Object> item) {
        data.add(item);
    }
 
    private void readProcess() {        
        XSSFSheet sheet = wb.getSheetAt(settings.getSheetIdx());
 
        // header 확인 및 설정
        if(settings.isFirstRowHeader()) {
            settings.setHeader(extractHeader(sheet.getRow(0)));
        }else {
            Assert.notNull(settings.getHeader(), MessageUtil.getMessage("excel.error.header.notexist"));
        }
        data = new ArrayList<>();
        for (Row row : sheet) {
            if(row.getRowNum() <= settings.getSkipRowIdx()) {
                continue;
            }
            Map<String, Object> rowData = new HashMap<>();
            for (Cell cell : row) {
                String key = getHeaderKey(cell.getColumnIndex());
                rowData.put(key, getExcelValue(cell));
            }
            addItem(rowData);
        }
    }
 
    public ExcelReader() {
        super();
    }
 
    public ExcelReader(ExcelSettings settings) {
        super();
        initCommon(settings);
    }
 
    public void read() {
        readProcess();
    }
 
    public List<Map<String,Object>> getResult(){
        return this.data;
    }
    
    public <T> List<T> getResultObject(final Class<T> clazz){
        ObjectMapper mapper = new ObjectMapper();
        mapper.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);
        JavaType itemType = mapper.getTypeFactory().constructCollectionType(List.class, clazz);
 
        return mapper.convertValue(getResult(), itemType);
    }
 
}
 
cs

4. 테스트 

Brand

1
2
3
4
5
6
7
8
9
10
@Getter
@Setter
public class Brand extends BaseObject {
 
    private static final long serialVersionUID = 1L;
 
    private String brandCd;
    private String brandNm;
        
}
cs

TestExcelReader

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
package eblo.refactoring.excel;
 
import static org.hamcrest.CoreMatchers.is;
import static org.junit.Assert.assertThat;
 
import java.io.IOException;
import java.io.Serializable;
import java.util.List;
import java.util.Map;
 
import org.apache.commons.lang3.SerializationUtils;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
 
import eblo.refactoring.excel.controller.Brand;
import eblo.refactoring.message.MessageUtil;
import lombok.extern.slf4j.Slf4j;
 
 
@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class ExcelReaderTest {
 
    private static final String SUCCESS_PATH = "static/format/";
    private static final String SUCCESS_FILE = "testExcel.xlsm";
    private static final String FAIL_FILE = "wrongFileName.xlsm";
    private static final int SHEET2_DATA_SIZE = 4;
    private static final String[] FAIL_HEADERS = new String[] {"brandCd"};
    private static final String[] SUCCESS_HEADERS = new String[] {"brandCd", "brandNm"};
    private String rootClassPath;
    private ExcelSettings settings;
    
    @Before
    public void setUp() {
        this.rootClassPath = ExcelReaderTest.class.getClassLoader().getResource("").getPath();
        this.settings = new ExcelSettings();
        this.settings.setExcelFilePath(rootClassPath+SUCCESS_PATH);
        this.settings.setExcelFileName(SUCCESS_FILE);
        this.settings.setFirstRowHeader(false);
        this.settings.setHeader(SUCCESS_HEADERS);
        this.settings.setSheetIdx(2);
        this.settings.setSkipRowCount(0);
    }
    
    private static <T extends Serializable> T clone(final T object) { 
        return SerializationUtils.clone(object);
    }
    
    @Test(expected = IllegalArgumentException.class)
    public void testFileNotFound1(){
        ExcelSettings clonedSettings = clone(this.settings);
        clonedSettings.setExcelFileName(FAIL_FILE);
        new ExcelReader(clonedSettings);
    }
 
    @Test
    public void testFileNotFound2(){
        ExcelSettings clonedSettings = clone(this.settings);
        clonedSettings.setExcelFileName(FAIL_FILE);
        try {
            new ExcelReader(clonedSettings);
        }catch(IllegalArgumentException e) {
            log.error(e.getMessage());;
            assertThat(e.getMessage(), is(MessageUtil.getMessage("excel.file.notfound")));
        }
    }
 
    @Test
    public void testOverSheet(){
        ExcelSettings clonedSettings = clone(this.settings);
        clonedSettings.setSheetIdx(5);
        try {
            ExcelReader excel = new ExcelReader(clonedSettings);
            excel.read();
        }catch(IllegalArgumentException e) {
            System.out.println(e.getMessage());
            assertThat(e.getMessage(), is(MessageUtil.getMessage("excel.sheetidx.notvalid")));
        }
    }
    
    @Test
    public void testWrongSheetName() {
        ExcelSettings clonedSettings = clone(this.settings);
        clonedSettings.setSheetName("WrongName");
        try {
            ExcelReader excel = new ExcelReader(clonedSettings);
            excel.read();
        }catch(IllegalArgumentException e) {
            assertThat(e.getMessage(), is(MessageUtil.getMessage("excel.error.sheet.notexist")));
        }
    }
    
    @Test
    public void testFirstRowHeader() throws IOException {
        ExcelSettings clonedSettings = clone(this.settings);
        clonedSettings.setFirstRowHeader(true);
        ExcelReader excel = new ExcelReader(clonedSettings);
        excel.read();
        List<Map<String, Object>> resultMap = excel.getResult();
        assertThat(resultMap.size(), is(SHEET2_DATA_SIZE));
 
        List<Brand> result = excel.getResultObject(Brand.class);
        assertThat(result.size(), is(SHEET2_DATA_SIZE));
    }
    
    @Test
    public void testNoFirstRowHeader() throws IOException {
        ExcelSettings clonedSettings = clone(this.settings);
        clonedSettings.setHeader(null);
 
        // header 값이 없기 때문에 에러 발생 
        try {
            ExcelReader excelNoHeader = new ExcelReader(clonedSettings);
            excelNoHeader.read();
        }catch(IllegalArgumentException e) {
            assertThat(e.getMessage(), is(MessageUtil.getMessage("excel.error.header.notexist")));
        }
        
        // header 설정은 했지만 수치가 않맞게 되어 에러 발생
        clonedSettings.setHeader(FAIL_HEADERS);
        try {
            ExcelReader excelWrongHeader = new ExcelReader(clonedSettings);
            excelWrongHeader.read();
        }catch(IllegalArgumentException e) {
            assertThat(e.getMessage(), is(MessageUtil.getMessage("excel.header.notfound")));
        }
 
        clonedSettings.setHeader(SUCCESS_HEADERS);
        ExcelReader excelGoodHeader = new ExcelReader(clonedSettings);
        excelGoodHeader.read();
 
        List<Map<String, Object>> resultMap = excelGoodHeader.getResult();
        assertThat(resultMap.size(), is(SHEET2_DATA_SIZE+1));
 
        List<Brand> result = excelGoodHeader.getResultObject(Brand.class);
        assertThat(result.size(), is(SHEET2_DATA_SIZE+1));
    }
    
    @Test
    public void testSkip() throws IOException {
        
        ExcelSettings clonedSettings = clone(this.settings);
        clonedSettings.setSkipRowCount(3);
        ExcelReader excel = new ExcelReader(clonedSettings);
        excel.read();
 
        List<Map<String, Object>> resultMap = excel.getResult();
        assertThat(resultMap.size(), is(SHEET2_DATA_SIZE+1-3));
 
        List<Brand> resultObject = excel.getResultObject(Brand.class);
        for(Brand br : resultObject) {
            log.info(br.toString());
        }
    }
 
}
cs

테스트 엑셀 파일 

testExcel.xlsm


GitHub : https://github.com/kkaok/refactoring

저작자표시 비영리 변경금지