[2020.01.30] Excel upload 후 db저장

 

엑셀 다운로드할 때는 controller에 소스를 추가해주면 됐지만

엑셀 업로드는 추가해주어야할 class들이 많다.

 

 

 

1. multipart fileupload로 엑셀 파일을 받아서 

2. 파일을 읽고 db에 insert한다. 

 

<파일을 읽는 방법>

1. 엑셀파일을 업로드할 폴더를 만들어주고 (나는 c드라이브에 upload폴더를 만들어줬다.)

2. upload한 엑셀 파일을 c드라이브 upload폴더에 저장시켰다가 알아서 엑셀 파일 안에 data들을 읽고 insert 후 삭제까지 시킨다. 

 

 

 

---

 

 

 

[pom.xml]: excel upload 하는데 필요한 dependency를 추가해준다.

<!-- 엑셀다운로드 -->     <dependency>         <groupId>org.apache.poi</groupId>         <artifactId>poi</artifactId>         <version>3.17</version>     </dependency>          <dependency>         <groupId>org.apache.poi</groupId>         <artifactId>poi-ooxml</artifactId>         <version>3.17</version>     </dependency>              <!--Multipart File Up&Download -->         <dependency>             <groupId>commons-fileupload</groupId>             <artifactId>commons-fileupload</artifactId>             <version>1.3.2</version>         </dependency>                  <dependency>             <groupId>org.apache.commons</groupId>             <artifactId>commons-lang3</artifactId>             <version>3.3.2</version>             <!--$NO-MVN-MAN-VER$-->         </dependency>                  <dependency>             <groupId>commons-io</groupId>             <artifactId>commons-io</artifactId>             <version>2.5</version>         </dependency>

 

[dispatcher-servlet.xml]: bean추가 (egov)

<!-- MULTIPART RESOLVERS -->     <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">         <property name="maxUploadSize" value="100000000" />         <property name="maxInMemorySize" value="100000000" />      </bean>

 

 

 

[jsp]: 버튼 추가

<form id="excelUploadForm" name="excelUploadForm" enctype="multipart/form-data"
        method="post" action= "excelUploadAjax.do">
        <div class="contents">
        <div>첨부파일은 한개만 등록 가능합니다.</div>
    
        <dl class="vm_name">
          <dt class="down w90">첨부 파일</dt>
            <dd><input id="excelFile" type="file" name="excelFile" /></dd>
          </dl>        
        </div>
    
        <div class="bottom">
          <button type="button" id="addExcelImpoartBtn" class="btn" onclick="check()" ><span>추가</span></button>
        </div>
      </form>

 

 

 

 

 

[jsp]: 추가 버튼을 누르면 검사를 거친 후에 ajax로 submit을 해준다.

/*************excel upload*************/
        
        function checkFileType(filePath) {
            var fileFormat = filePath.split(".");
 
            if (fileFormat.indexOf("xls") > -1 || fileFormat.indexOf("xlsx") > -1) {
              return true;
              } else {
              return false;
            }
          }
 
          function check() {
 
            var file = $("#excelFile").val();
 
            if (file == "" || file == null) {
            alert("파일을 선택해주세요.");
 
            return false;
            } else if (!checkFileType(file)) {
            alert("엑셀 파일만 업로드 가능합니다.");
 
            return false;
            }
 
            if (confirm("업로드 하시겠습니까?")) {
 
              var options = {
 
                success : function(data) {
                    console.log(data);
                  alert("모든 데이터가 업로드 되었습니다.");
 
                },
                type : "POST"
                };
              
              $("#excelUploadForm").ajaxSubmit(options);
              
            }
          }

 

이때 나는 에러

.ajaxSubmit not a function

 

해결

<script src="http://malsup.github.com/jquery.form.js"></script>

->ajaxSubmit을 쓸 수 있는 환경을 만들어주어야 했다.

 

 

[controller]: ajax로 보내기 때문에 @ResponseBody 어노테이션을 추가해주어야 한다.

@ResponseBody
    @RequestMapping(value = "/excelUploadAjax.do", method = RequestMethod.POST)
        public ModelAndView excelUploadAjax(MultipartFile testFile, MultipartHttpServletRequest request) throws  Exception{
        
        System.out.println("업로드 진행");
        
        MultipartFile excelFile = request.getFile("excelFile");
        
        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);
        }
        
        //업로드를 진행하고 다시 지우기
        sampleService.excelUpload(destFile);
        
        destFile.delete();
        
        ModelAndView view = new ModelAndView();
        view.setViewName("/egovSampleList.do");
        
        return view;
    }

 

[Service]

void excelUpload(File destFile);

 

[ServiceImpl]

/*******excel upload*********/
    @Override
    public void excelUpload(File destFile) {
        
        ExcelReadOption excelReadOption = new ExcelReadOption();
        
        //파일경로 추가
        excelReadOption.setFilePath(destFile.getAbsolutePath());
        
        //추출할 컬럼명 추가
        excelReadOption.setOutputColumns("A", "B", "C");
        
        //시작행
        excelReadOption.setStartRow(2);
        
        List<Map<String, String>>excelContent  = ExcelRead.read(excelReadOption);
        
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("excelContent", excelContent);
        
        try {
            sampleDAO.insertExcel(paramMap);
        }catch(Exception e) {
            e.printStackTrace();
        }
    }

serviceImpe에서 쓸 파일 경로, 추출할 컬럼명, 시작행을 가져오려면 다른 class들이 필요하다.

 

 

[ExcelFileType] : ExcelRead.class에서 확장자를 가져올 때 사용한다.

package egovframework.example.sample.service.impl;
 
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;
 
    }
}

 

[ExcelCellRef]: upload 된 파일에서 cell 추출.

package egovframework.example.sample.service.impl;
 
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;
    }
}

 

[ExcelRead]: 엑셀 파일을 읽어온다.

package egovframework.example.sample.service.impl;
 
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();
        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();
            /*
             * 데이터를 담을 맵 객체 초기화
             */
            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;
      }
}

 

[ExcelReadOption]

package egovframework.example.sample.service.impl;
 
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;
    }
  }

 

[error]

++db에는 값이 들어갔는데

LOADING CLASS `COM.MYSQL.JDBC.DRIVER’. THIS IS DEPRECATED. THE NEW DRIVER CLASS IS `COM.MYSQL.CJ.JDBC.DRIVER’. THE DRIVER IS AUTOMATICALLY REGISTERED VIA THE SPI AND MANUAL LOADING OF THE DRIVER CLASS IS GENERALLY UNNECESSARY.

 

:=> driver 이름 변경 필요

 

– 해결 –

driver 명 수정 (egov기준 context-datasource.xml)

변경 전: com.mysql.jdbc.Driver

변경 후: com.mysql.cj.jdbc.Driver

 

 

 

 

[Mapper]

void insertExcel(Map<String, Object> paramMap) throws Exception;

 

[sql]

<insert id="insertExcel" parameterType="sampleVO">         INSERT INTO list (                 useCode,                  useDate,                  money                 )                  VALUES              <foreach collection="excelContent" item="item" separator=",">                     (                 #{item.A},                  #{item.B},                 #{item.C}                 )             </foreach>     </insert>