[POI] JAVA로 엑셀을 다뤄보자 : 네이버 카페

http://poi.apache.org/spreadsheet/quick-guide.html#NewWorkbook  API

poi

java 프레임워크 안에서 엑셀 데이터를 입출력할수 있도록 도와주는 라이브러리 이다.

보통 간단한 엑셀 입출력의 경우 jxl 라이브러리를 사용하지만, poi에 비해서 기능이 적고

(셀을 조작할수 있는 기술이 무궁무진하다)

업데이트 빈도가 낮은편이다

 

poi의 경우 톰캣을 받았던 자카르타 홈페이지에서 다운받을 수 있다.

http://poi.apache.org/

 

엑셀은 97~2003버전과 2007버전은 서로 파일명이 틀리다.

그로인해 poi에서도 불러들일수 있는 호출방법이 각각틀리다.

 

*.xml 파일 관련 방법

  HSSFWorkbook workBook  =  new HSSFWorkbook(new FileInputStream(new File(filePath)));

  HSSFSheet sheet    =  null;

  HSSFCell cell    =  null;

  HSSFRow row     =  null;

 

*.xlsm 파일 관련 방법

   XSSFWorkbook workBook  =  new XSSFWorkbook(file);

   XSSFSheet  sheet    =  null;

   XSSFRow row     =  null;

   XSSFCell cell    =  null;

 

* xls, xlsx 파일 관련 방법

  Workbook workBook = WorkbookFactory.create(file);

  Sheet sheet = null;

  Row row = null;

  Cell cell = null;

 

위에서 순서대로 파일을 가져오는 함수와, 시트를 설정하고 조작하는 함수,

셀을 조작하는 함수 (각 줄의 칸을 의미한다), 줄을 조작하는 함수(db의 row와 동일한 역활)

 

이외에도 여러 확장자 설정이 있지만 대표적인것이 위이므로 생략

 

엑셀 가져오기

 

  // 내가 가져올 파일명을 입력한다

  String excelfile = "C:\\Tomcat 5.0\\webapps\\ROOT\\example.xls";

  try {

//파일을 들인다
       POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelfile));             
       HSSFWorkbook workbook = new HSSFWorkbook(fs);

// 총 시트의 갯수를 가져온다
       int sheetNum = workbook.getNumberOfSheets(); 

// 반복문을 돌려서 시트에 있는 데이터를 가져오려고 한다. 

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

// k번째 시트를 가져온다
            HSSFSheet sheet = workbook.getSheetAt(k);

// k번째 시트의 값이 존재하는 총 row 갯수를 가져온다
            int rows = sheet.getPhysicalNumberOfRows();

// row마다의 데이터를 가져오기 위한 반복문을 실행한다.

// 첫번째 로우는 보통 제목표시줄(이름, 전화번호) 이런 것이기 때문에 r을 1로 기존하는 경우도 있다.

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

// r번째 row를 가져온다
                HSSFRow row   = sheet.getRow(r);

// r번째 row가 null이 아닐 경우 값이 있는 셀을 가져온다.
                if (row != null) { 
                     int cells = row.getPhysicalNumberOfCells();

// 셀의 데이터를 가져오기 위한 반복문

for (short c = 0; c < cells; c++) {

// c번째 셀의 데이터를 가져오기위한 셀의 정의

// 각 row에서 중간중간 null값이 있는경우 cells 변수에 혼동이 생겨 데이터를 잘 못받아오는 경우가 생긴다.

// 이럴경우 cells 자체를 수동으로 숫자를 입력채준다. 각 row에 cells이 8개일 경우 c<8로 정의해준다.
                         HSSFCell cell  = row.getCell(c);
                         if (cell != null) { 
                              String value = null;

// 받아온 셀의 타입에 따라 value값에다 넣어준다.           

switch (cell.getCellType()) 

{

   case HSSFCell.CELL_TYPE_FORMULA : // 수식처리를 한 셀(A1+B1)을 가져올경우 이것을 이용한다.  

       value = "FORMULA value=" + cell.getCellFormula();
       break;
   case HSSFCell.CELL_TYPE_NUMERIC : // 엑셀타입에서 숫자형의 경우를 받아온다.

       value = "NUMERIC value=" + cell.getNumericCellValue(); //double
        break;    // 엑셀타입에서 문자형을 받아온다.

   case HSSFCell.CELL_TYPE_STRING :
        value = "STRING value=" + cell.getStringCellValue(); //String
        break; // 값이 없을 경우의 처리

   case HSSFCell.CELL_TYPE_BLANK :
        value = null;
        break; // BOOLEAN형의 처리

   case HSSFCell.CELL_TYPE_BOOLEAN :
        value = "BOOLEAN value=" + cell.getBooleanCellValue(); //boolean
        break; // 에러 바이트를 출력한다.

   case HSSFCell.CELL_TYPE_ERROR :
        value = "ERROR value=" + cell.getErrorCellValue(); // byte
        break;

   default :
 } 
                        } 
                    }
                }
            }
       }
   } catch (Exception e) { 
       e.printStackTrace();
    }

 

DB의 경우는 String이나 number형이 어려개인 경우가있다.

이경우는 빨간부분을 switch 처리가 아닌for (short c = 0; c < cells; c++) {}의 c의 값에 따라 처리해주면된다.

 

c는 cells의 번지수를 의미한다고 보면되기때문에

이름, 나이, 전화번호, 주소의 셀이 있다고 치면

String name = "";

int age = 0;

String phone = "";

String address = "";

if(c=0){name = cell.getStringCellValue(); }

else if(c=1){age =(int)cell.getNumericCellValue(); }.... 등으로 변경해주면된다.

 

나이의 경우 Integer로 정의하면 엑셀자체가 long타입의 숫자형 이기때문에 에러가 난다.

엑셀에 있는 데이터에 소수점이없어도 java로 가져올경우(19.0, 20.0 이런식으로 뒤에 소수점이 붙기때문이다)

int로 정의를 해줄 경우 뒤에 소수점이 깔끔하게 짤려서 나오게된다.

 

만약 디비를 Insert해줄 경우

 for (int r = 0; r < rows; r++) {} row절 에서 cell을 호출하고 가져온 값을 저장한 다음에

엑셀에서 가져온변수를 파라메터로 받아서 넣어주면된다.

 

public class InsertTbOnsiteReportItem {

 private String tableName = "TB_ONSITEREPORTITEM";

 public void start() {

  // 테이블명 출력
  System.out.println("[Table Name : " + tableName + "]");

  // tibero Con, Pre, Rs, SB
  Connection tiberoCon = null;
  PreparedStatement tiberoPre = null;
  StringBuffer tiberoBuffer = new StringBuffer();

  // 초기 row설정
  int rowCount = 0;

  try {
   
   

POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:\\현장평가 분류.xls"));

HSSFWorkbook workbook = new HSSFWorkbook(fs);

   tiberoBuffer.append(" UPDATE ").append(tableName).append(" SET");
   tiberoBuffer.append(" ITEM = ? ");
   tiberoBuffer.append(" ,ITEMDETAIL = ? ");
   tiberoBuffer.append(" WHERE GROUPKIND = ? ");
   tiberoBuffer.append(" AND GROUPIDX = ? ");
   tiberoBuffer.append(" AND DOCNUM = ? ");
   
   System.out.println(tiberoBuffer);
   
   int sheetNum = workbook.getNumberOfSheets();
   
   // 메디컬기관 부분과 오류 시트는 제외
   for(int i = 0; i < sheetNum-2; i++){
    System.out.println("Sheet Number : " + i);
    System.out.println("Sheet Name : "+ workbook.getSheetName(i));
    HSSFSheet sheet = workbook.getSheetAt(i);
    
    int rows = sheet.getLastRowNum();
    for(int j = 1; j < rows; j++){
     HSSFRow row = sheet.getRow(j);
     
     System.out.println("Row : "+row.getRowNum());
     
     // null 값이 아닌 셀의 갯수를 받아온다
     int cells = row.getPhysicalNumberOfCells();
     String groupKind = "";
     String groupId = "";
     Integer docNum = 0;
     String item = "";
     String itemDetail = "";
     
     System.out.println(cells);
     //비교셀은 제외하고 셀값을 받아옴
     for(int k = 0; k < 5; k++){
      HSSFCell cell = row.getCell(k);
       if(k==0){
        groupKind = cell.getStringCellValue();
       }else if(k==1){
        groupId = cell.getStringCellValue();
       }else if(k==2){
        docNum = Integer.parseInt(cell.getStringCellValue());
       }else if(k==3){
        if(cell == null)
         item = "";
        else
         item = cell.getStringCellValue();
       }else if(k==4){
        itemDetail = cell.getStringCellValue();
       }else{ 
       }
      
     }
     System.out.println("===========================================");
     System.out.println("[groupKind] "+groupKind);
     System.out.println("[groupId] "+groupId);
     System.out.println("[docNum] "+docNum);
     System.out.println("[item] "+item);
     System.out.println("[itemDetail] "+itemDetail);
     System.out.println("===========================================");

     // tibero columnIndex 초기값 설정
     int parameterIndex = 1;
 
     // Tibero 연결, sql
     tiberoCon = TiberoManager.getConnection();
     tiberoPre = tiberoCon.prepareStatement(tiberoBuffer.toString());
 
     // 오브젝트 삽입
     tiberoPre.setObject(parameterIndex++, item, TbTypes.VARCHAR);
     tiberoPre.setObject(parameterIndex++, itemDetail, TbTypes.VARCHAR);
     tiberoPre.setObject(parameterIndex++, groupKind, TbTypes.VARCHAR);
     tiberoPre.setObject(parameterIndex++, groupId, TbTypes.VARCHAR);
     tiberoPre.setObject(parameterIndex++, docNum, TbTypes.INTEGER);

     
     // Tibero 실행
     tiberoPre.executeUpdate();
     tiberoPre.close();
     tiberoCon.close();
 
     rowCount++;
     if(rowCount%500 == 0)
     System.out.println(rowCount);     
     
    }   
   }
   
   tiberoBuffer = new StringBuffer();
   tableName = "TB_ASSESSOR";
   tiberoBuffer.append(" UPDATE ").append(tableName);
   tiberoBuffer.append(" SET    APPRSTAT = ? ");
   tiberoBuffer.append(" WHERE  ASSESSORSELECTION = ? ");
   tiberoBuffer.append(" AND    APPRSTAT IS NULL ");
   
   tiberoCon = TiberoManager.getConnection();
   tiberoPre = tiberoCon.prepareStatement(tiberoBuffer.toString());
   String apprStat = "1";
   String assessorSelection = "01";
   System.out.println(tiberoBuffer);
   // 오브젝트 삽입
   tiberoPre.setObject(1, apprStat, TbTypes.CHAR);
   tiberoPre.setObject(2, assessorSelection, TbTypes.CHAR);
   // Tibero 실행
   tiberoPre.executeUpdate();
   tiberoPre.close();
   tiberoCon.close();

  } catch (FileNotFoundException e) {
   e.printStackTrace();
  } catch (IOException e) {
   e.printStackTrace();
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
}

 

디비에서 엑셀로 가져오기(다운로드)

 

데이터를 한줄만 가져올 생각이다.

// 파일 생성

FileOutputStream fileOutput = new FileOutputStream("myFirstWorkBook.xls");

// 워크북 생성
HSSFWorkbook objWorkBook = new HSSFWorkbook();
// 워크시트 생성
HSSFSheet objSheet = objWorkBook.createSheet();
// 행생성
HSSFRow objRow = objSheet.createRow((short)0);
// 셀 생성
HSSFCell objCell1 = objRow.createCell((short)0);

HSSFCell objCell2 = objRow.createCell((short)1);

HSSFCell objCell3 = objRow.createCell((short)2);

HSSFCell objCell4 = objRow.createCell((short)3);
엑셀에서 0번째는 1번째 줄과 같다.

 

디비에서 데이터를 받아온 후 변수에다 넣어준다.(소스는 추후 업데이트)

while(rs.next()){

 

objCell1.setEncoding(HSSFCell.ENCODING_UTF_16);
objCell1.setCellValue(rs.getString(1));

 

objCell2.setEncoding(HSSFCell.ENCODING_UTF_16);
objCell2.setCellValue(rs.getString(2));

 

objCell3.setEncoding(HSSFCell.ENCODING_UTF_16);
objCell3.setCellValue(rs.getString(3));

 

objCell4.setEncoding(HSSFCell.ENCODING_UTF_16);
objCell4.setCellValue(rs.getString(4));

}

// 생성된 파일에 불러들어온 워크북 저장

workbook.write(fileOutput);

// 파일 닫기
fileOutput.close();

 

db에서 값을받아올때 타입에 상관없이 String으로 통일하였다.

여러개의 데이터를 가져오고 싶을경우 count 값을 준 다음

row생성을 rs안에 반복문을 돌려서 생성한 후 그안에 cell값을 넣어주면서 마지막으로 count값을 1개씩 늘려나가면

자동으로 row줄이 바뀌면서 cell에 데이터가 들어갈 것이다.

 

poi의 경우 꾸준히 업데이트 되고 자료가 많은 편이라 구글에서 찾아보면서 하다보면 금방 할 수 있음