엑셀파일 업로드 후 List로 반환하기

앞서 포스트에서 jQuery를 사용해서 파일 업로드하는 글을 포스팅 했습니다.

그 다음에 할 일이 엑셀 파일을 업로드 한 후에 안에 있는 내용을 DB에 넣는 작업을 진행했습니다.

물론 기존 프로젝트에도 엑셀 업로드한 후에 안에 있는 내용을 읽어와서 DB에 넣는 로직이 있긴 했지만 엑셀 형식에 따라서 매번 controller이나 내용도 달라져야 했기에 해당 작업이 필요할 때마다 컨트롤 + c, 컨트롤 + v 를 한 후에 입맛에 맛게끔 변형하는 과정이 필요했습니다.

그래서 깔끔하게 범용으로 쓸 수 있게 만들어보았습니다. 물론 더 좋은 것도 많이 있겠지만 이런 방법도 있구나 하는 정도로만 봐주시고 군더더기가 있다면 본인 업무에 맞게끔 수정해서 쓰시면 좋을 것 같습니다.

JavaScript

var formData = new FormData();
  formData.append("excelFile", $("#excelFile")[0].files[0]);
  formData.append("cellInfo", JSON.stringify({
            useColsNumber: [0, 1, 2, 6, 10, 11, 12],
            useColsField: ["delivery_end_date", "order_item_seq", "ols_region_cd", "box_cnt", "car_no", "driver_contact", "remark"],
            startRow : 5
          }
  ));

  $.ajax({
    url: "${contextPath}/file/excelToDB.do",
    data: formData,
    processData: false,
    contentType: false,
    type: "POST",
    async: false,
    success: function(data){
      var json = JSON.parse(data);
      if( json.result ) {
        alert("정상적으로 처리되었습니다.");
      }
      else {
        alert("오류 : " + json.resultMsg);
      }
    }
  });

  $("#mdlExcelUpload").modal("toggle");
})

Controller

@RequestMapping(value="excelToDB.do")
public void excelToDB(HttpServletRequest req, HttpServletResponse res, @RequestParam Map<String,Object> paramMap, Model model) throws Exception {
   logger.info(" > paramMap : " + paramMap);

   JSONObject json = new JSONObject();
   try {
      MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) req;
      CommonsMultipartFile file = (CommonsMultipartFile) multipartRequest.getFile("excelFile");

      List<Map<String, Object>> excelDataList = Util.fnGetExcelToListMap(file, paramMap);

      paramMap.put("dataList", excelDataList);
      excelService.insertDataList(paramMap);

      json.put("result", true);

   }
   catch(Exception e) {
      json.put("result", false);
      json.put("resultMsg", e.getMessage());
   }

   res.setCharacterEncoding("UTF-8");
   res.getWriter().print(json);
}

Util

public static List<Map<String, Object>> fnGetExcelToListMap(CommonsMultipartFile pFile, Map<String,Object> paramMap) throws Exception {
    String fileName = pFile.getOriginalFilename();

    JSONParser jsonParser = new JSONParser();
    JSONObject jsonObject = (JSONObject)jsonParser.parse((String) paramMap.get("cellInfo"));
    JSONArray jsonColsNumberArray = (JSONArray)jsonObject.get("useColsNumber");
    JSONArray jsonColsFieldArray = (JSONArray)jsonObject.get("useColsField");

    // 예외처리
    if( jsonColsFieldArray.size() == 0 ) {
        throw new IllegalAccessError("useColsField의 의 값이 없습니다.");
    }

    if( jsonColsNumberArray.size() == 0 ) {
        throw new IllegalAccessError("useColsNumber의 값이 없습니다.");
    }

    if( jsonColsFieldArray.size() != jsonColsNumberArray.size() ) {
        throw new IllegalAccessError("useColsNumber과 useColsField의 사이즈가 일치하지 않습니다.");
    }

    Workbook tempWorkbook;
    if( fileName.endsWith("xls") ) {
        tempWorkbook = new HSSFWorkbook(pFile.getInputStream());
    }
    else if( fileName.endsWith("xlsx") ) {
        tempWorkbook = new XSSFWorkbook(pFile.getInputStream());
    }
    else {
        throw new IllegalAccessError("Excel 파일(xls, xlsx)만 가능합니다.");
    }

    List<Map<String, Object>> excelDatas = new ArrayList<>();

    try {
        Sheet sheet = tempWorkbook.getSheetAt(0);
        FormulaEvaluator evaluator = tempWorkbook.getCreationHelper().createFormulaEvaluator();

        int iRow, iCol, iStartRow;

        // jsonObject 에 담긴 startRow 는 디버깅을 해보면 Long 형식이다. 그래서 int형으로 바꿔서 한다.
        iStartRow = (int)((long)jsonObject.get("startRow"));

        for( iRow = iStartRow; iRow < sheet.getPhysicalNumberOfRows(); iRow++ ) {
            Row row = sheet.getRow(iRow);
            Map<String, Object> excelData = new HashMap<>();
            String cellString = "";

            excelData.clear();

            for( iCol = 0; iCol < jsonColsNumberArray.size(); iCol++ ) {
                Cell cell = row.getCell((int)(long)jsonColsNumberArray.get(iCol));

                switch( cell.getCellType() ) {
                    case Cell.CELL_TYPE_FORMULA :
                        cellString = cell.getCellFormula();
                        break;

                    case Cell.CELL_TYPE_STRING :
                        cellString = cell.getStringCellValue();
                        break;

                    case Cell.CELL_TYPE_NUMERIC :
                        if( org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell) ) {
                            Date date = cell.getDateCellValue();
                            cellString = new SimpleDateFormat("yyyy-MM-dd").format(date);
                        }
                        else {
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cellString = cell.getStringCellValue();
                        }
                        break;

                    case Cell.CELL_TYPE_BOOLEAN :
                        cellString = String.valueOf(cell.getBooleanCellValue());
                        break;

                    case Cell.CELL_TYPE_ERROR :
                        cellString = String.valueOf(cell.getErrorCellValue());
                        break;

                    case Cell.CELL_TYPE_BLANK :
                        cellString = "";
                        break;

                    default :
                        CellValue evaluate = evaluator.evaluate(cell);
                        if( evaluate != null )
                            cellString = evaluate.formatAsString();
                        else
                            cellString = "";
                        break;
                }

                excelData.put((String)jsonColsFieldArray.get(iCol), cellString.replaceAll("\\s+$", ""));
            }

            excelDatas.add(excelData);
        }
    }
    catch(Exception e) {
        logger.error(e.getMessage());
    }

    return excelDatas;
}

도움이 되었길 바랍니다.