Java POI를 사용해서 엑셀내용 DB입력

1) 이전에는 HSSFWorkbook(xls 확장자 지원), XSSFWorkbook(xlsx 확장자 지원) 두가지로 나눠서 사용되었지만 새로 등장한 Workbook으로 통합되어 두가지 확장자를 한번에 지원함.

2) POI 버전을 4.1.1, 3.17, 3.16 등 여러가지 시도해 보았지만 라이브러리에서 클래스를 찾지 못하거나 기타 오류들이 뜨지 않고 제대로 실행된 것은 3.10 버전이었음, xlsx 확장자를 지원하면서도 안정화된 버전인듯 하다.

POI Version	JDK Version	Description
Up to 3.4	Up to JDK 1.4.x	xlsx 미지원
3.5 ~ 3.10	JDK 1.5 ~	xlsx 지원
3.11 ~ 4.1.1(latest version)	JDK 1.6 ~	xlsx 지원

 

■ Library

http://poi.apache.org/download.html

Apache POI - Download Release Artifacts

위 URL로 접속하면 APACHE POI 최신버전을 다운받는 사이트가 나온다.

이전에 릴리즈된 버전을 받는 곳(화면 최하단)

poi-3.10-FINAL-20140208.jar

poi-examples-3.10-FINAL-20140208.jar

poi-excelant-3.10-FINAL-20140208.jar

poi-ooxml-3.10-FINAL-20140208.jar

poi-ooxml-schemas-3.10-FINAL-20140208.jar

poi-scratchpad-3.10-FINAL-20140208.jar

dom4j-1.6.1.jar

stax-api-1.0.1.jar

xmlbeans-2.3.0.jar

 

■ Example

  /*
   * InsertExcelDataForm.java
   */
  //Struts에서 FormFile이 파일업로드를 지원한다고 해서 한번 써봄
  public class InsertExcelDataForm extends ActionForm {

      private static final long serialVersionUID = 1;

      private FormFile file;

      public FormFile getFile() {
          return file;
      }
      public void setFile(FormFile file) {
          this.file = file;
      }
  }

  /*
  * InsertExcelDataAction.java
  */
  public class InsertExcelDataAction extends Action {

      public ActionForward execute( ActionMapping mapping,
                                    ActionForm form,
                                    HttpServletRequest request,
                                    HttpServletResponse response ) throws Exception {
          PostgresDBConnection pool = new PostgresDBConnection();
          Connection con = null;
          ActionErrors errors = new ActionErrors();
          String success = "fail";

          try {
              con = pool.getConnection();
              ApplicationForm application = new ApplicationForm();
              InsertExcelDataForm insertExcelDataForm = (InsertExcelDataForm)form;
              InsertExcelDataDAO insertExcelDataDAO = new InsertExcelDataDAO(con);

              ArrayList<String> list = new ArrayList<String>();

              FormFile formFile = insertExcelDataForm.getFile(); //웹 페이지에서 폼으로 MultipartFile을 받아옴

              //웹상에서 업로드 되어 MultipartFile인 경우 바로 InputStream으로 변경하여 사용.
              InputStream inputStream = formFile.getInputStream();

              //Workbook을 사용하면 xls, xlsx 구분없이 엑셀 로드 가능
              Workbook workbook = WorkbookFactory.create(inputStream);
              //Sheet 역시 Workbook과 마찬가지로 엑셀 버전에 관계없이 사용 가능, 파라미터로 0을 주어 첫번째 시트를 로드함
              Sheet sheet = workbook.getSheetAt(0);
              Iterator<Row> rowItr = sheet.iterator();
              //행이 존재하면 수행
              while(rowItr.hasNext()){
                  Row row = rowItr.next();

                  //첫 번째 행이 헤더인 경우 스킵, 2번째 행부터 data 로드
                  if(row.getRowNum() == 0){
                      continue;
                  }

                  Iterator<Cell> cellItr = row.cellIterator();
                  //현재 접근한 행에 속해있는 열이 존재하면 수행
                  while(cellItr.hasNext()){
                      Cell cell = cellItr.next();
                      int index = cell.getColumnIndex(); //index는 열 순서를 의미, 0부터 시작
                      switch(index){
                          case 0: //번호(정수인 경우 Double로 처리)
                              application.setApp_idx(((Double)getValueFromCell(cell)).intValue());
                              break;
                          case 1: //학원이름
                              application.setSacademy((String)getValueFromCell(cell));
                              break;
                          case 2: //이름
                              application.setSname((String)getValueFromCell(cell));
                              break;
                          case 3: //전화번호
                              application.setSphone((String)getValueFromCell(cell));
                              break;
                          case 4: //날짜
                              application.setSperiod((String)getValueFromCell(cell));
                              break;
                      }
                  }

                  //쿼리 생성
                  String sql = "INSERT INTO application (app_idx, sacademy, sname, sphone, speriod) VALUES (" + application.getApp_idx() + ", '" + application.getSacademy() + "', '" + application.getSname()
                                  + "', '" + application.getSphone() + "', '" + application.getSperiod() + "')";

                  list.add(sql);
              }

              //insertExcelDataDAO.runDeleteSql() : 기존 데이터 모두 제거
              //insertExcelDataDAO.runInsertSql(list) : 리스트에 저장된 모든 INSERT 쿼리 실행
              if("success".equals(insertExcelDataDAO.runDeleteSql())){
                  if("success".equals(insertExcelDataDAO.runInsertSql(list))){
                      success = "success";
                  }
              }

          } catch (Throwable e) {
              e.printStackTrace();
              ActionError error = new ActionError(e.getMessage());
              errors.add(ActionErrors.GLOBAL_ERROR,error);
          } finally {
              try {
                  if (con != null) con.close();
              } catch (SQLException sqle) {
                  sqle.printStackTrace();
                  throw new RuntimeException("errors.database.notclose");
              }
          }
          saveErrors(request,errors);
          return mapping.findForward(success);
      }

      //셀에 정해진 데이터 타입별로 변환
      private static Object getValueFromCell(Cell cell){
          switch(cell.getCellType()){
              case Cell.CELL_TYPE_STRING:
                  return cell.getStringCellValue();
              case Cell.CELL_TYPE_BOOLEAN:
                  return cell.getBooleanCellValue();
              case Cell.CELL_TYPE_NUMERIC:
                  if(DateUtil.isCellDateFormatted(cell)){
                      return cell.getDateCellValue();
                  }
                  return cell.getNumericCellValue();
              case Cell.CELL_TYPE_FORMULA:
                  return cell.getCellFormula();
              case Cell.CELL_TYPE_BLANK:
                  return "";
              default:
                  return "";
          }
      }
  }

  /*
   * InsertExcelDataDAO.java
   */
  public class InsertExcelDataDAO {

      private Connection con = null;

      /**
       * 커넥션을 받아오는 기본생성자
       * @param con
       */
      public InsertExcelDataDAO(Connection con) {
          this.con = con;
      }

      /**
       * 엑셀 파일을 INSERT 하기 전 기존 데이터를 모두 DELETE 하는 함수
       * @return String success
       * @throws CreateException 
       * @throws SQLException
       */
      public String runDeleteSql() throws CreateException {
          PreparedStatement ps = null;
          String success = "fail";

          String sql = "DELETE FROM application";

          try {
              if (con.isClosed()) {
                  throw new IllegalStateException("errors.dbnotconnect");
              }

              ps = con.prepareStatement(sql);
              if (ps.executeUpdate() <= 0) {
                  throw new CreateException("errors.delete.application");
              }

              success = "success";
          } catch (SQLException e) {
              e.printStackTrace();
              throw new RuntimeException("errors.database");
          } finally {
              try {
                  if (ps != null) ps.close();
              } catch (SQLException e) {
                  e.printStackTrace();
                  throw new RuntimeException("errors.database.notclose");
              }
          }

          return success;
      }

      /**
       * 엑셀 파일을 기반으로 만들어진 SQL INSERT문을 실행하는 함수
       * @param ArrayList<String> sqls
       * @return String success
       * @throws CreateException 
       * @throws SQLException
       */
      public String runInsertSql(ArrayList<String> sqls) throws CreateException{
          PreparedStatement ps = null;
          String success = "fail";

          try {
              if (con.isClosed()) {
                  throw new IllegalStateException("errors.dbnotconnect");
              }

              for(int i=0; i<sqls.size(); i++){
                  ps = con.prepareStatement(sqls.get(i));
                  if (ps.executeUpdate() != 1) {
                      throw new CreateException("errors.create.application");
                  }
              }

              success = "success";
          } catch (SQLException e) {
              e.printStackTrace();
              throw new RuntimeException("errors.database");
          } finally {
              try {
                  if (ps != null) ps.close();
              } catch (SQLException e) {
                  e.printStackTrace();
                  throw new RuntimeException("errors.database.notclose");
              }
          }

          return success;
      }
  }