java Excel upload 및 DB 연동

오랜만에 글을 써본다.. 이직으로 개발이 주 업무가 아니라 운영업무만 하다보니...

엑셀 파일을 읽어서 DB연동하는것 까지 기록해본다.

## 기본 class 파일 (그냥 갖다 생성해서 쓰면 된다) ##

1.ExcelCellRef.java

package egovframework.comm;


import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellReference;
 
public class ExcelCellRef {
 
    /**
     * Cell에 해당하는 Column Name을 가져온다(A,B,C..)
     * 만약 Cell이 Null이라면 int cellIndex의 값으로
     * Column Name을 가져온다.
     * @param cell
     * @param cellIndex
     * @return
     */
    public static String getName(Cell cell, int cellIndex) {
        int cellNum = 0;
        if(cell != null) {
            cellNum = cell.getColumnIndex();
        }
        else {
            cellNum = cellIndex;
        }
 
        return CellReference.convertNumToColString(cellNum);
    }
 
    @SuppressWarnings("deprecation")
    public static String getValue(Cell cell) {
        String value = "";
 
        if(cell == null) {
            value = "";
        }
        else {
            if( cell.getCellType() == Cell.CELL_TYPE_FORMULA ) {
                value = cell.getCellFormula();
            }
            else if( cell.getCellType() == Cell.CELL_TYPE_NUMERIC ) {
                value = cell.getNumericCellValue() + "";
            }
            else if( cell.getCellType() == Cell.CELL_TYPE_STRING ) {
                value = cell.getStringCellValue();
            }
            else if( cell.getCellType() == Cell.CELL_TYPE_BOOLEAN ) {
                value = cell.getBooleanCellValue() + "";
            }
            else if( cell.getCellType() == Cell.CELL_TYPE_ERROR ) {
                value = cell.getErrorCellValue() + "";
            }
            else if( cell.getCellType() == Cell.CELL_TYPE_BLANK ) {
                value = "";
            }
            else {
                value = cell.getStringCellValue();
            }
        }
 
        return value;
    }
}

2.ExcelFileType.java

package egovframework.comm;


import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
 
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
public class ExcelFileType {
    public static Workbook getWorkbook(String filePath) {
 
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(filePath);
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e.getMessage(), e);
        }
 
        Workbook wb = null;
 
        if(filePath.toUpperCase().endsWith(".XLS")) {
            try {
                wb = new HSSFWorkbook(fis);
            } catch (IOException e) {
                throw new RuntimeException(e.getMessage(), e);
            }
        }
        else if(filePath.toUpperCase().endsWith(".XLSX")) {
            try {
                wb = new XSSFWorkbook(fis);
            } catch (IOException e) {
                throw new RuntimeException(e.getMessage(), e);
            }
        }
 
        return wb;
 
    }
}

 

3.ExcelRead.java

package egovframework.comm;


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.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
 
public class ExcelRead {
    
    public static List<Map<String, String>> read(ExcelReadOption excelReadOption) {
 
        // 엑셀 파일 자체
        // 엑셀파일을 읽어 들인다.
        // FileType.getWorkbook() <-- 파일의 확장자에 따라서 적절하게 가져온다.
        Workbook wb = ExcelFileType.getWorkbook(excelReadOption.getFilePath());
 
        //    엑셀 파일에서 첫번째 시트를 가지고 온다.
        Sheet sheet = wb.getSheetAt(0);
 
        System.out.println("Sheet 이름: " + wb.getSheetName(0));
        System.out.println("데이터가 있는 Sheet의 수 :" + wb.getNumberOfSheets());
 
        // sheet에서 유효한(데이터가 있는) 행의 개수를 가져온다.
        int numOfRows = sheet.getPhysicalNumberOfRows()+2; //빈 cell은 카운터를 안하길래 +2를 추가 시켜줌
        
        int numOfCells = 0;
 
        Row row = null;
        Cell cell = null;
 
        String cellName = "";
        /**
         * 각 row마다의 값을 저장할 맵 객체 저장되는 형식은 다음과 같다. put("A", "이름"); put("B",
         * "게임명");
         */
        Map<String, String> map = null;
        /*
         * 각 Row를 리스트에 담는다. 하나의 Row를 하나의 Map으로 표현되며 List에는 모든 Row가 포함될 것이다.
         */
        List<Map<String, String>> result = new ArrayList<Map<String, String>>();
 
 
        /**
         * 각 Row만큼 반복을 한다.
         */
        for (int rowIndex = excelReadOption.getStartRow() - 1; rowIndex < numOfRows; rowIndex++) {
          /*
           * 워크북에서 가져온 시트에서 rowIndex에 해당하는 Row를 가져온다. 하나의 Row는 여러개의 Cell을 가진다.
           */
          row = sheet.getRow(rowIndex);
 
          if (row != null) {
            /*
             * 가져온 Row의 Cell의 개수를 구한다.
             */
            numOfCells = row.getPhysicalNumberOfCells();
            System.out.println("row의 cell의 갯수 : "+numOfCells);
            /*
             * 데이터를 담을 맵 객체 초기화
             */
            map = new HashMap<String, String>();
            /*
             * cell의 수 만큼 반복한다.
             */
            for (int cellIndex = 0; cellIndex < numOfCells; cellIndex++) {
              /*
               * Row에서 CellIndex에 해당하는 Cell을 가져온다.
               */
              cell = row.getCell(cellIndex);
              /*
               * 현재 Cell의 이름을 가져온다 이름의 예 : A,B,C,D,......
               */
              cellName = ExcelCellRef.getName(cell, cellIndex);
              /*
               * 추출 대상 컬럼인지 확인한다 추출 대상 컬럼이 아니라면, for로 다시 올라간다
               */
              if (!excelReadOption.getOutputColumns().contains(cellName)) {
                continue;
              }
              /*
               * map객체의 Cell의 이름을 키(Key)로 데이터를 담는다.
               */
              map.put(cellName, ExcelCellRef.getValue(cell));
            }
            /*
             * 만들어진 Map객체를 List로 넣는다.
             */
            result.add(map);
          }
        }
        return result;
      }
}

 

4.ExcelReadOption.java

package egovframework.comm;

import java.util.ArrayList;
import java.util.List;
 
public class ExcelReadOption {
    
    //    엑셀파일의 경로
    private String filePath;
    
    //  추출할 컬럼 명  
    private List<String> outputColumns;
    
    //  추출을 시작할 행 번호
    private int startRow;
 
    public String getFilePath() {
        return filePath;
    }
 
    public void setFilePath(String filePath) {
        this.filePath = filePath;
    }
 
    public List<String> getOutputColumns() {
 
        List<String> temp = new ArrayList<String>();
        temp.addAll(outputColumns);
 
        return temp;
    }
 
    public void setOutputColumns(List<String> outputColumns) {
 
  //        이걸 A,B,C,D 이런 식으로 추가하던데
        List<String> temp = new ArrayList<String>();
        temp.addAll(outputColumns);
 
        this.outputColumns = temp;
    }
 
    public void setOutputColumns(String ... outputColumns) {
 
        if(this.outputColumns == null) {
            this.outputColumns = new ArrayList<String>();
        }
 
        for(String ouputColumn : outputColumns) {
            this.outputColumns.add(ouputColumn);
        }
    }
 
    public int getStartRow() {
        return startRow;
    }
    public void setStartRow(int startRow) {
        this.startRow = startRow;
    }
  }

 

 

## jsp ##

<form name="searchForm" id="searchForm" enctype="multipart/form-data" method="post">

<button onClick="javascript:excelClick()" class="editUploadBtn">Excel Upload</button>

</form>

<script>

function excelClick(){
   var insertData = new FormData($('#searchForm')[0]);
   $.ajax({
     enctype : "multipart/form-data",
     type : "POST",
     url : "/management/commutingTimeExcelUpload.do",
     data : insertData,
     processData: false, //엑셀 업로드 하려면 설정해줘야한다
     contentType: false, //엑셀 업로드 하려면 설정해줘야한다
     success : function(data) {
     }
   });
}

</script>

 

## Controller ##

@ResponseBody
@Transactional
@RequestMapping(value = "/management/commutingTimeExcelUpload.do", produces = "application/text; charset=UTF-8")
public String  commutingTimeExcelUpload(MultipartHttpServletRequest request, Model model, MultipartFile testFile) throws Exception{
MultipartFile excelFile = request.getFile("EXCEL_UPLOAD");
String result = "N";
if(excelFile == null || excelFile.isEmpty()) {
throw new RuntimeException("엑셀파일을 선택해주세요");
}

File destFile = new File("c:\\upload\\"+excelFile.getOriginalFilename()); //엑셀을 업로드 할 위치

try {
//설정한 위치에 올린 파일을 만든다
excelFile.transferTo(destFile);
} catch(Exception e) {
throw new RuntimeException("e.getMessage()",e);
}

//업로드 진행
managementService.excelUpload(destFile);
result = "Y";
//업로드 후 삭제
destFile.delete();

return result;
}

 

## service ##

//엑셀 업로드
void excelUpload(File destFile);

 

## serviceImpl ##

@Override
public void excelUpload(File destFile) {
 ExcelReadOption excelReadOption = new ExcelReadOption();
        
        //파일경로 추가
        excelReadOption.setFilePath(destFile.getAbsolutePath());
        System.out.println("#### destFile.getAbsolutePath() : "+destFile.getAbsolutePath());
        //추출할 컬럼명 추가
        excelReadOption.setOutputColumns("A", "B", "C", "D", "E", "F");
        
        //시작행
        excelReadOption.setStartRow(6); //DB에 저장할 시작 위치
        
        //A행~ F행의 내용이 excelContent로 들어감 
        List<Map<String, String>>excelContent  = ExcelRead.read(excelReadOption);
        
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("excelContent", excelContent);
        try {
         managementDAO.excelUpload(paramMap);
        }catch(Exception e) {
            e.printStackTrace();
        }
}

 

## DAO ##

public Integer excelUpload(Map<String , Object>inputMap) {
int seq = (int) insert("managementDAO.excelUpload", inputMap);
return seq;
}

 

## xml ##

<insert id="managementDAO.excelUpload" parameterClass="java.util.Map">
/* managementDAO.excelUpload */
<selectKey keyProperty="seq" resultClass="java.lang.Integer" >
SELECT 1 
</selectKey>
<dynamic> 
<iterate  property="excelContent"  conjunction=";" >
MERGE INTO COMMUTING_TIME C
USING (SELECT 1 AS DUAL) AS B
ON (
C.SYS_CREATE_DATE = #excelContent[].A# 
AND C.EMP_CODE = #excelContent[].C# 
AND C.DEVICE_CODE = #excelContent[].F#
)
WHEN MATCHED THEN 
UPDATE SET C.SYS_CREATE_TIME = #excelContent[].B# 
WHEN NOT MATCHED THEN
INSERT(
SYS_CREATE_DATE,
SYS_CREATE_TIME,
EMP_CODE,
EMP_NAME,
DEVICE_ID,
DEVICE_CODE
) 
VALUES 
(
#excelContent[].A#,
#excelContent[].B#,
#excelContent[].C#,
#excelContent[].D#,
#excelContent[].E#,
#excelContent[].F#
)
</iterate>
</dynamic>
;
</insert>