Excel File 데이터 DB에 import하는 로직(JAVA)

# Excel File 데이터 DB에 import하는 로직

excel file에 기재되어 있는 데이터를 읽어 DB 테이블과 mapping하여 insert 또는 update 로직을 구현할 수 있다.

sample excel file) TEST_FILE.xlsx

dependency)

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.7</version>
</dependency>
 

※ 준비물

import할 excel의 filePath를 Controller영역 importExcel 메소드 파라메터로 넘겨주는 로직 필요

ex) filePath : C:\test\excel_import/TEST_FILE.xlsx

source) -Controller.java

@Controller
public class Controller {
 
    ...
 
    @Autowired
    ImportExcelManager importExcelManager;
 
    @RequestMapping(value = "/.../.../importExcel")
    public void importExcel(@RequestParam("filePath") String filePath) {
        try {
            File file = new File(filePath.trim());
 
            if (!file.exists() || !file.isFile() || !file.canRead()) {
                throw new IOException(filePath);
            }
 
            
            String ext = filePath.substring(filePath.lastIndexOf(".") + 1).trim();
 
            if ("xls".equals(ext)) {
                importExcelManager.readExcelXLS(filePath);
            } else if ("xlsx".equals(ext)) {
                importExcelManager.readExcelXLSX(filePath);
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
    ...
 
}

source) -Manager.java

public interface ImportExcelManager {
    ...
    
    public void readExcelXLS(String excel);
    public void readExcelXLSX(String excel);
    
    ...
}

source) -ManagerImpl.java

@Service(value = "importExcelManager")
public class ImportExcelManagerImpl implements ImportExcelManager {
    ...
    
    public void readExcelXLS(String excel) {
        try {
            String[] tempFileName = excel.split("/");
            String fileName = tempFileName[excel.split("/").length - 1];
 
            ctxRoot = excel.substring(0, excel.lastIndexOf("/"));
            File file = new File(excel.trim());
            HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
 
            ExcelExtractor extractor = new ExcelExtractor(wb);
            extractor.setFormulasNotResults(true);
            extractor.setIncludeSheetNames(false);
 
            makeExcelToObject(wb.getSheetAt(0), "xls", fileName);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public void readExcelXLSX(String excel) {
        try {
            String[] tempFileName = excel.split("/");
            String fileName = tempFileName[excel.split("/").length - 1];
 
            ctxRoot = excel.substring(0, excel.lastIndexOf("/"));
            File file = new File(excel.trim());
 
            if (!file.exists() || !file.isFile() || !file.canRead()) {
                throw new IOException(excel.trim());
            }
 
            XSSFWorkbook wb = new XSSFWorkbook(excel.trim());
            XSSFExcelExtractor extractor = new XSSFExcelExtractor(wb);
            extractor.setFormulasNotResults(true);
            extractor.setIncludeSheetNames(false);
 
            makeExcelToObject(wb.getSheetAt(0), "xlsx", fileName);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    private void makeExcelToObject(Object sheet, String flag, String fileName) throws IOException {
        Row titles = null;
 
        for (Row row : (flag.equals("xls") ? (HSSFSheet) sheet : (XSSFSheet) sheet)) {
            if (row.getRowNum() == 0) {
                titles = row;
                continue;
            }
 
            updateData(titles, row);
        }
    }
    
    @Transactional
    private void updateData(Row titles, Row row) throws IOException {
        String colName = null;
        String colValue = null;
        String name = null;
        String value = null;
 
        // FOR Section (S)
        for (Cell cell : row) {
            if (titles.getCell(cell.getColumnIndex()) == null || titles.getCell(cell.getColumnIndex()).equals("")) {
                break;
            }
 
            colName = titles.getCell(cell.getColumnIndex()).getRichStringCellValue().getString().trim();
 
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                colValue = cell.getRichStringCellValue().getString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    colValue = cell.getDateCellValue().toString();
                } else {
                    Long roundVal = Math.round(cell.getNumericCellValue());
                    Double doubleVal = cell.getNumericCellValue();
                    if (doubleVal.equals(roundVal.doubleValue())) {
                        colValue = String.valueOf(roundVal);
                    } else {
                        colValue = String.valueOf(doubleVal);
                    }
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                colValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                colValue = cell.getCellFormula();
                break;
 
            default:
                colValue = "";
            }
 
            colValue = colValue.trim();
 
            if (colName.equals("NAME")) {
                if (!StringUtil.nullToBlank(colValue).isEmpty()) {
                    name = colValue;
                }
            }
 
            if (colName.equals("VALUE")) {
                if (!StringUtil.nullToBlank(colValue).isEmpty()) {
                    value = colValue;
                }
            }
        }
        // FOR Section (E)
 
        // TODO : DAO 영역 메소드 호출 후 Data insert 또는 update 로직 구현
        // excel로부터 읽어들인 name, value 값을 DB에 insert하거나 update하는 로직을 구현할 수 있다.
 
    }
}