[JAVA] 엑셀 읽을 때, 형변환 에러가 발생할 경우

엑셀을 읽는 방식은 상당히 스마트 하지 않다는 것을 다들 잘 알고 있을 것이다. 셀 영역 하나하나를 무슨 형인지 지정을 해야 하는데 마치 DB에서 getString, getInt하는 모양새와 유사하다. 데이터는 숫자가 들어가 있을 수 있고 문자 등이 들어갈 수 있는데 현재 내가 발생한 문제는 하나의 열에 숫자와 문자가 공존하는 형태이다.

예를 들어 이런 경우가 있을 수 있을 것이다. 

첫번째 컬럼은 분류값이 들어있고, 두번째 컬럼에는 분류의 결과값이 들어가 있다고 가정을 해보자

첫번째 로우(row)의 분류값에 월급이라는 값이 들어가 있어서 결과값 컬럼에 숫자가 들어갔다고 하고

두번째 로우의 분류값에 이름이라는 값이 들어가 있어서 결과값 컬럼에 문자가 들어가 있다고 가정을 할 시 어떻게 해야 할까?

일반적으로 DB라는 모두 문자열로 변환하면 될 것이다. 

소스 예제

public static List<HashMap<String, Object>> read() {
        List<HashMap<String, Object>> result = new ArrayList<HashMap<String, Object>> ();
        String filePath = "c:/Project/test.xlsx";
        
        try {
            FileInputStream excelFile = new FileInputStream(new File(filePath));
            Workbook workbook = new XSSFWorkbook(excelFile);
            Sheet datatypeSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = datatypeSheet.iterator();
    
            HashMap<String, Object> map = null;
            int idx = 0;
            while (iterator.hasNext()) {
    
                Row currentRow = iterator.next();
                Iterator<Cell> cellIterator = currentRow.iterator();
                map = new HashMap<String, Object> ();
                idx = 0;
                
                while (cellIterator.hasNext()) {
                    Cell currentCell = cellIterator.next();
                    
                    switch(idx) {
                        case 0:    // code
                            map.put("code", currentCell.getStringCellValue());
                            break;
                        case 1:    // value
                            map.put("value", currentCell.getStringCellValue());
                            break;                        
                    }
                    idx++;
                }
                result.add(map);
                map = null;
            }
            
            workbook.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        
        return result;
    }      

위 소스 예제처럼 해당 셀을 getStringCellValue()를 사용해서 가지고 와봤지만, 아래와 같이 에러가 발생되고야 말았다.

에러발생

Exception in thread "main" java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell

at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:1050)

at org.apache.poi.xssf.usermodel.XSSFCell.getRichStringCellValue(XSSFCell.java:404)

at org.apache.poi.xssf.usermodel.XSSFCell.getStringCellValue(XSSFCell.java:356)

at Main.read(Main.java:50)

at Main.main(Main.java:20)

해결과정

이 문제를 해결하기 위해서, 일단 여러가지 방법이 떠올랐지만 첫번째로 소스를 수정하기 귀찮았기 때문에 셀서식을 바꿔보기로 하였다.

가. 엑셀 파일 수정

숫자가 있는 컬럼을 지정 후, 셀 서식을 "텍스트"로 변경해보았다. 될거라 생각했지만, 내 엑셀 파일이 이상한 것인지 여전히 숫자로 인식을 하고 있었다. 스택오버플로우에는 이렇게 해결하라는 사람이 있었지만, 일단 내 파일은 되지 않는 것으로...

나. 강제 셀서식 지정

위와 같은 해결책을 제시하는 부분이 있어서, 위와 같이 소스를 수정해보도록 하였다. 위의 방식대로 해니 성공적으로 결과가 출력되는 것을 확인되었다. 강제로, 아무래도 getStringCellValue 부분의 에러다 보니, 강제로 String 지정 방식이 먹히는 것 같다.

switch(idx) {
    case 0:    // first
        map.put("code", currentCell.getStringCellValue());
        break;
    case 1:    // first
        currentCell.setCellType(currentCell.CELL_TYPE_STRING);
        map.put("value", currentCell.getStringCellValue());
        break;                        
}

다. Cell Type 체크

가장 많이들 쓰는 방식으로 Cell 타입을 체크해서, 해당 타입에 맞게 변형하는 방식이 있다.

if(currentCell.getCellType() == currentCell.CELL_TYPE_NUMERIC) {
    map.put("value", Integer.toString((int)currentCell.getNumericCellValue()));
} else {
    map.put("value", currentCell.getStringCellValue());
}

여기서 만족할 수도 있겠지만, 고전적인 방식인 Exception 처리 메소드로 처리를 해보고 싶었다.

다. Exception 메소드

/**
 * cell의 데이터를 string으로 변경
 * 
 * @param cell
 * @return
 */
public static String getStringValue(Cell cell) {
    String rtnValue = "";
    try {
        rtnValue = cell.getStringCellValue();
    } catch(IllegalStateException e) {
        rtnValue = Integer.toString((int)cell.getNumericCellValue());            
    }
    
    return rtnValue;
}

위와 같은 Exception 메소드를 만들어서, getString이 실패할 때 IllegalStateException이 발생하면, 형변환하여 저장한다.

위 방식 역시 최종적으로, 성공적으로 출력이 되는 것을 확인할 수 있었다. 

최종 소스 (Exception 메소드 호출 방식)

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
 
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;
 
public class Main {
 
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        List<HashMap<String, Object>> result = read();
        
        for(HashMap<String, Object> map : result) {
            System.out.println(map.get("code") + "," + map.get("value"));
        }
    }
 
    public static List<HashMap<String, Object>> read() {
        List<HashMap<String, Object>> result = new ArrayList<HashMap<String, Object>> ();
        String filePath = "c:/Project/test.xlsx";
        
        try {
            FileInputStream excelFile = new FileInputStream(new File(filePath));
            Workbook workbook = new XSSFWorkbook(excelFile);
            Sheet datatypeSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = datatypeSheet.iterator();
    
            HashMap<String, Object> map = null;
            int idx = 0;
            while (iterator.hasNext()) {
    
                Row currentRow = iterator.next();
                Iterator<Cell> cellIterator = currentRow.iterator();
                map = new HashMap<String, Object> ();
                idx = 0;
                
                while (cellIterator.hasNext()) {
                    Cell currentCell = cellIterator.next();
                    
                    switch(idx) {
                        case 0:    // first
                            map.put("code", currentCell.getStringCellValue());
                            break;
                        case 1:    // first
                            map.put("value", getStringValue(currentCell));
                            break;                        
                    }
                    idx++;
                }
                result.add(map);
                map = null;
            }
            
            workbook.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        
        return result;
    }      
    
    
    /**
     * cell의 데이터를 string으로 변경
     * 
     * @param cell
     * @return
     */
    public static String getStringValue(Cell cell) {
        String rtnValue = "";
        try {
            rtnValue = cell.getStringCellValue();
        } catch(IllegalStateException e) {
            rtnValue = Integer.toString((int)cell.getNumericCellValue());            
        }
        
        return rtnValue;
    }
}
 

포스팅에 도움이 된, 참고자료

스택오버플로우