POI - Excel Read/Write(xls, xlsx) sample

POI로 엑셀 xls, xlsx파일을 읽고 쓰는 샘플입니다.

관련 jar파일과 원본 소스파일은 첨부파일에서 다운 받으시면 됩니다.

 

import java.awt.Font;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;  
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;  
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;  
import org.apache.poi.hssf.usermodel.HSSFSheet;  
import org.apache.poi.hssf.usermodel.HSSFWorkbook; 
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FontFamily;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class SampleMain {

 public static void main(String[] args) {
  ExcelHandler excelHandler = new ExcelHandler();
  excelHandler.writeXls();
  excelHandler.writeXlsx();
  excelHandler.readXls();
  excelHandler.readXlsx();
 }
 
}

class ExcelHandler {
 public void writeXls() {
  try {
   // Excel Workbook
   HSSFWorkbook wb = new HSSFWorkbook();        
   // Excel Sheet
   HSSFSheet sheet1 = wb.createSheet("sheet1");   
   
   // Cell Style
   HSSFCellStyle cellStyle = wb.createCellStyle();
   HSSFFont font = wb.createFont();
  
   // Font
   font.setColor(HSSFColor.RED.index);
   font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
   font.setFontName("Arial");
   font.setFontHeight((short)300);
   cellStyle.setFont(font);

   // Align
   cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
   
   // Border Line
   cellStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
   cellStyle.setBorderBottom(HSSFCellStyle.BORDER_DASH_DOT);
   cellStyle.setBorderLeft(HSSFCellStyle.BORDER_DASHED);
   cellStyle.setBorderRight(HSSFCellStyle.BORDER_DASHED);
   
   // Foreground color
   cellStyle.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);
   cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
   
   for (int i=0 ; i<10 ; i++) {
    // Row
    HSSFRow row = sheet1.createRow(i);   
    row.setHeight((short)300); // Row Height
    
    for (int j=0 ; j<10 ; j++) {
     // Column
     HSSFCell cell = row.createCell((short)j);
     cell.setCellValue("Row : " + (i+1) + ", Col : " + (j+1));
     
     if (j%2==0) {
      cell.setCellStyle(cellStyle);
     }
    }
   }
   
   // Auto width setting
   for (int i=0 ; i<9 ; i++) {
    sheet1.autoSizeColumn(i, true);
   }
   
   // Width setting
   sheet1.setColumnWidth(9, 30*256); // Cell Width (Character count * 256)
   
   FileOutputStream fileOut = new FileOutputStream("sample1.xls");
   wb.write(fileOut);     
   fileOut.close();
   System.out.println("The file created.");
  } catch (FileNotFoundException e) {
   System.out.println(e);
  } catch (IOException e) {
   System.out.println(e);
  }
 }
 
 public void writeXlsx() {
  try {
   // Excel Workbook
   XSSFWorkbook wb = new XSSFWorkbook();        
   // Excel Sheet
   XSSFSheet sheet1 = wb.createSheet("sheet1");   
   
   // Cell Style
   XSSFCellStyle cellStyle = wb.createCellStyle();
   XSSFFont font = wb.createFont();
  
   // Font
   font.setColor(HSSFColor.RED.index);
   font.setBold(true);
   font.setFamily(FontFamily.MODERN);
   font.setFontHeight((short)300);
   cellStyle.setFont(font);

   // Align
   cellStyle.setAlignment(HorizontalAlignment.CENTER);
   
   // Border Line
   cellStyle.setBorderTop(BorderStyle.THICK);
   cellStyle.setBorderBottom(BorderStyle.DASH_DOT);
   cellStyle.setBorderLeft(BorderStyle.DASHED);
   cellStyle.setBorderRight(BorderStyle.DASHED);
   
   // Foreground color
   cellStyle.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);
   cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
   
   for (int i=0 ; i<10 ; i++) {
    // Row
    XSSFRow row = sheet1.createRow(i);   
    row.setHeight((short)300); // Row Height
    
    for (int j=0 ; j<10 ; j++) {
     // Column
     XSSFCell cell = row.createCell((short)j);
     cell.setCellValue("Row : " + (i+1) + ", Col : " + (j+1));
     
     if (j%2==0) {
      cell.setCellStyle(cellStyle);
     }
    }
   }
   
   // Auto width setting
   for (int i=0 ; i<9 ; i++) {
    sheet1.autoSizeColumn(i, true);
   }
   
   // Width setting
   sheet1.setColumnWidth(9, 30*256); // Cell Width (Character count * 256)
   
   FileOutputStream fileOut = new FileOutputStream("sample1.xlsx");
   wb.write(fileOut);     
   fileOut.close();
   System.out.println("The file created.");
  } catch (FileNotFoundException e) {
   System.out.println(e);
  } catch (IOException e) {
   System.out.println(e);
  }
 }
 
 void readXls() {  
  try {
   FileInputStream fis = new FileInputStream("sample2.xls");  
   POIFSFileSystem fs = new POIFSFileSystem(fis);  
      HSSFWorkbook wb = new HSSFWorkbook(fs);  
      int sheets = wb.getNumberOfSheets();  
  
      for (int sheetIdx=0 ; sheetIdx<sheets ; sheetIdx++) {  
          HSSFSheet sheet = wb.getSheetAt(sheetIdx);        
       
          int firstRow = sheet.getFirstRowNum();
          int lastRow = sheet.getLastRowNum();        
       
          for (int rowIdx = firstRow; rowIdx <= lastRow; rowIdx++) {     
              HSSFRow row = sheet.getRow(rowIdx);     
        
              if (row == null) continue;     
        
              short firstCell = row.getFirstCellNum();     
              short lastCell = row.getLastCellNum();     
        
              for (short cellIdx=firstCell ; cellIdx<=lastCell ; cellIdx++) {     
               String data = null;     
        
               HSSFCell cell = row.getCell(cellIdx);     
        
               if (cell == null) continue;     

               int type = cell.getCellType();     
        
               switch (type) {        
                   case HSSFCell.CELL_TYPE_BOOLEAN:        
                    boolean bdata = cell.getBooleanCellValue();        
                    data = String.valueOf(bdata);        
                    break;        
                   case HSSFCell.CELL_TYPE_NUMERIC:        
                    double ddata = cell.getNumericCellValue();        
                    data = String.valueOf(ddata);        
                    break;   
                   case HSSFCell.CELL_TYPE_STRING:        
                    data = cell.getStringCellValue();        
                    break;        
       case HSSFCell.CELL_TYPE_BLANK:
        data = "[null 아닌 공백]";
        break;
       case HSSFCell.CELL_TYPE_ERROR:
        data = "" + cell.getErrorCellValue();
        break;
                   case HSSFCell.CELL_TYPE_FORMULA:  
                    data = cell.getCellFormula();
        break;
                   default:        
                    continue;        
               }        
       
               System.out.println(data);     
              }        
          }  
      }
  } catch (FileNotFoundException e) {
   System.out.println(e);
  } catch (IOException e) {
   System.out.println(e);
  }
 }

 void readXlsx() {  
  try {
   FileInputStream fis = new FileInputStream("sample2.xlsx");  
   XSSFWorkbook wb = new XSSFWorkbook(fis);  
      int sheets = wb.getNumberOfSheets();  
  
      for (int sheetIdx=0 ; sheetIdx<sheets ; sheetIdx++) {  
       XSSFSheet sheet = wb.getSheetAt(sheetIdx);        
          int firstRow = sheet.getFirstRowNum();
          int lastRow = sheet.getLastRowNum();        
       
          for (int rowIdx = firstRow; rowIdx <= lastRow; rowIdx++) {     
           XSSFRow row = sheet.getRow(rowIdx);     
        
              if (row == null) continue;     
        
              short firstCell = row.getFirstCellNum();     
              short lastCell = row.getLastCellNum();     
        
              for (short cellIdx=firstCell ; cellIdx<=lastCell ; cellIdx++) {     
               String data = null;     
        
               XSSFCell cell = row.getCell(cellIdx);     
        
               if (cell == null) continue;     
        
               int type = cell.getCellType();     
        
               switch (type) {        
                   case XSSFCell.CELL_TYPE_BOOLEAN:        
                    boolean bdata = cell.getBooleanCellValue();        
                    data = String.valueOf(bdata);        
                    break;        
                   case XSSFCell.CELL_TYPE_NUMERIC:        
                    double ddata = cell.getNumericCellValue();        
                    data = String.valueOf(ddata);        
                    break;   
                   case XSSFCell.CELL_TYPE_STRING:        
                    data = cell.getStringCellValue();        
                    break;        
       case XSSFCell.CELL_TYPE_BLANK:
        data = "[null 아닌 공백]";
        break;
       case XSSFCell.CELL_TYPE_ERROR:
        data = "" + cell.getErrorCellValue();
        break;
                   case XSSFCell.CELL_TYPE_FORMULA:  
                    data = cell.getCellFormula();
        break;
                   default:        
                    continue;        
               }        
       
               System.out.println(data);     
              }        
          }  
      }
  } catch (FileNotFoundException e) {
   System.out.println(e);
  } catch (IOException e) {
   System.out.println(e);
  }
 }

} 

 

[출처] POI - Excel Read/Write(xls, xlsx) sample|작성자 IT쟁이