[excel] - Apache POI 를 이용하여 Excel 가지고 놀기

흠.. 3.5 버전으로 작업함. 3.6 부터는 밑에 코드를 사용할 수 없음. 사용할 수 없는 클래스가 존재하는 듯..

참조

http://poi.apache.org/spreadsheet/index.html

오피스 버전에 따라서 사용하는 API가 다름.
   : 2007 을 기준으로( ooo.xls, ooo.xlsx)
 
본 문서는 ooo.xls 버전을 기준으로 작업할거임. 뭐.. 상위 버전이랑 큰 차이는 없는듯


1. workbook.xls 만들기

package com.excel.sample;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;
import java.util.Date;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class Launcher {

 final static int rowNumber  = 100;
 
 public void makeRowHeader(CreationHelper createHelper, Sheet sheet) {
  Row row = sheet.createRow(0);
 
  row.createCell(0).setCellValue(createHelper.createRichTextString("번호"));
     row.createCell(1).setCellValue(createHelper.createRichTextString("value"));
     row.createCell(2).setCellValue(createHelper.createRichTextString("Description(설 명)"));
     row.createCell(3).setCellValue(createHelper.createRichTextString("상태 값"));
     row.createCell(4).setCellValue(createHelper.createRichTextString("Date - new Date()"));
     row.createCell(5).setCellValue(createHelper.createRichTextString("Date - m/d/yy h:mm"));
     row.createCell(6).setCellValue(createHelper.createRichTextString("Date - Calendar.getInstance()"));
   
     row.createCell(7).setCellValue(createHelper.createRichTextString("Date - 비고"));
 }
 
 public void makeRow(Workbook wb, CreationHelper createHelper, Sheet sheet) {
  for(int i = 1;i<rowNumber;i++) {
   Row row = sheet.createRow(i);
  
   Cell cell = row.createCell(0);
      cell.setCellValue(1);
      row.createCell(1).setCellValue(1.2);
      row.createCell(2).setCellValue(createHelper.createRichTextString("This is a string"));
      row.createCell(3).setCellValue(true);
    
      row.createCell(4).setCellValue(new Date());
    
      // we style the second cell as a date (and time).  It is important to
      // create a new cell style from the workbook otherwise you can end up
      // modifying the built in style and effecting not only this cell but other cells.
      CellStyle cellStyle = wb.createCellStyle();
      cellStyle.setDataFormat(
          createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
      cell = row.createCell(5);
      cell.setCellValue(new Date());
      cell.setCellStyle(cellStyle);

    
      //you can also set date as java.util.Calendar
      cell = row.createCell(6);
      cell.setCellValue(Calendar.getInstance());
      cell.setCellStyle(cellStyle);
    
    
      row.createCell(7).setCellType(HSSFCell.CELL_TYPE_ERROR);
    
  }
 }
 
 public void makeExcel() throws IOException {
  Workbook wb = new HSSFWorkbook();

  CreationHelper createHelper = wb.getCreationHelper();
  Sheet sheet = wb.createSheet("sheet1");
     
  makeRowHeader(createHelper, sheet);
 
  makeRow(wb, createHelper, sheet);
 
 

   
     FileOutputStream fileOut;
     fileOut = new FileOutputStream("D:\\Launcher\\excel\\workbook.xls");

  wb.write(fileOut);
  fileOut.close();
 }
 
 public static void main(String[] args) throws IOException {
 
  new Launcher().makeExcel();
 }
}

 
2. 만든넘 읽기.

public void readExcel() throws InvalidFormatException, IOException {
InputStream inp = new FileInputStream("D:\\Launcher\\excel\\workbook.xls");

   Workbook wb = WorkbookFactory.create(inp);
   Sheet sheet = wb.getSheetAt(0);

   for(Row row : sheet) {
    for(Cell cell : row) {
     CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
     System.out.print(cellRef.formatAsString());
     System.out.print(" - ");
     switch(cell.getCellType()) {
      case Cell.CELL_TYPE_STRING:
             System.out.println("string : " + cell.getRichStringCellValue().getString());
             break;
           case Cell.CELL_TYPE_NUMERIC:
             if(DateUtil.isCellDateFormatted(cell)) {
               System.out.println("date : " + cell.getDateCellValue());
             } else {
               System.out.println("number : " + cell.getNumericCellValue());
             }
             break;
           case Cell.CELL_TYPE_BOOLEAN:
             System.out.println("boolean : " + cell.getBooleanCellValue());
             break;
           case Cell.CELL_TYPE_FORMULA:
             System.out.println("formula : " + cell.getCellFormula());
             break;
           default:
             System.out.println(cell.toString());
     }
    }
   }
}

3.6이상 부터는 위 코드를 사용할 수 없었음.. 뭐.. 소스를 다운로드 받으면 그 속에 예제가 있기 때문에 그것보고 사용해도 좋을듯.