POI를 이용한 Excel ( *.xls, *.xlsx ) 읽기

excel.zip

apache poi site :  http://poi.apache.org/

필요 Libraries

-- *.xls 을 위한 library

poi-3.7-20101029.jar

-- *.xlsx를 위한 libraries

poi-ooxml-3.7-20101029.jar

xmlbeans-2.3.0.jar

poi-ooxml-schemas-3.7-20101029.jar

ooxml-lib\dom4j-1.6.1.jar

-- 사용 ( Map 에 Data 저장 .. sample )

*.xls 의 경우 excel 파일 읽어오는 방법

POIFSFileSystem fileSystem = new POIFSFileSystem(new FileInputStream(new File(excelFile)));

SSFWorkbook work = new HSSFWorkbook(fileSystem);

int sheetNum = work.getNumberOfSheets();

 

log.error("\n# sheet num : " + sheetNum);

 

for( int loop = 0; loop < sheetNum; loop++){

  HSSFSheet sheet = work.getSheetAt(loop);

 

  int rows = sheet.getPhysicalNumberOfRows();

 

  log.error("\n# sheet rows num : " + rows);

 

  for( int rownum = 0; rownum < rows; rownum++){

    HSSFRow row = sheet.getRow(rownum);

   

    if(row != null){

      int cells = row.getPhysicalNumberOfCells();

     

      log.error("\n# row = " + row.getRowNum() + " / cells = " + cells);

     

      for(int cellnum =0; cellnum < cells; cellnum++){

        HSSFCell cell = row.getCell(cellnum);

       

        if(cell != null){

         

          switch (cell.getCellType()) {

         

          case HSSFCell.CELL_TYPE_FORMULA:

                 params.put("CELL_TYPE_FORMULA"+cellnum, cell.getNumericCellValue());

                 break;

                

          case HSSFCell.CELL_TYPE_STRING:

                 params.put("CELL_TYPE_STRING"+cellnum, cell.getStringCellValue());

                 break;

                

          case HSSFCell.CELL_TYPE_BLANK:

                 params.put("CELL_TYPE_BLANK"+cellnum, cell.getBooleanCellValue());

                 break;

                

          case HSSFCell.CELL_TYPE_ERROR :

                 params.put("CELL_TYPE_ERROR"+cellnum, cell.getErrorCellValue());

                 break;

         ......

          default:

                 break;

          }

        }

        log.error("\n CELL __ [params ] => " + params.toString());

      }

    }

  }

}

*.xlsx 의 경우 excel 파일 읽어오는 방법

XSSFWorkbook work = new XSSFWorkbook(new FileInputStream(new File(excelFile)));

int sheetNum = work.getNumberOfSheets();

 

log.error("\n# sheet num : " + sheetNum);

 

for( int loop = 0; loop < sheetNum; loop++){

  XSSFSheet sheet = work.getSheetAt(loop);

 

  int rows = sheet.getPhysicalNumberOfRows();

 

  log.error("\n# sheet rows num : " + rows);

 

  for( int rownum = 0; rownum < rows; rownum++){

    XSSFRow row = sheet.getRow(rownum);

   

    if(row != null){

      int cells = row.getPhysicalNumberOfCells();

     

      log.error("\n# row = " + row.getRowNum() + " / cells = " + cells);

     

      for(int cellnum =0; cellnum < cells; cellnum++){

        XSSFCell cell = row.getCell(cellnum);

       

        if(cell != null){

         

          switch (cell.getCellType()) {

         

          case XSSFCell.CELL_TYPE_FORMULA:

                 params.put("CELL_TYPE_FORMULA"+cellnum, cell.getNumericCellValue());

                 break;

                

          case XSSFCell.CELL_TYPE_STRING:

                 params.put("CELL_TYPE_STRING"+cellnum, cell.getStringCellValue());

                 break;

                

          case HSSFCell.CELL_TYPE_BLANK:

                 params.put("CELL_TYPE_BLANK"+cellnum, cell.getBooleanCellValue());

                 break;

                

          case XSSFCell.CELL_TYPE_ERROR :

                 params.put("CELL_TYPE_ERROR"+cellnum, cell.getErrorCellValue());

                 break;

          ...... 

          default:

                 break;

          }

        }

        log.error("\n CELL __ [params ] => " + params.toString());

      }

    }

  }

}

출처 - http://enosent.tistory.com/30