본문 바로가기

Programming/Java

[Beginner] Excel Read - POI

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.poifs.filesystem.POIFSFileSystem;


public class excel {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
		String fpath = "D:\\Project\\Excel.xls";
		
		File file = new File(fpath);
		
		List lst = null;
		
		try {
			lst = read(file, 0, true);
			
			for ( int i = 0 ;  i < lst.size(); i++ ) {
				System.out.println(lst.get(i));
			}
		} catch ( Exception e ) {
			System.out.println(e);
		}
		
		
	}
	
	/*===============================================================================================
	 * 
	 * Excel 파일을 읽어 List로 리턴한다.
	 * 
	 * Parameter
	 * 	File 			: Excel 파일
	 *  nSheetIndex 	: 읽어올 Excel 파일의 Sheet Index
	 *  bDefineTitle 	: true->첫 번째 Row를 Key로 지정
	 *  				  false->Cell_x 로 임의의 Key 지정
	 * 
	 *===============================================================================================
	 * */
    public static List read(File file, int nSheetIndex, boolean bDefineTitle) throws Exception {
    	
    	POIFSFileSystem fs 		= new POIFSFileSystem(new FileInputStream(file));
        HSSFWorkbook 	wb 		= new HSSFWorkbook(fs);
        HSSFSheet 		sheet 	= wb.getSheetAt(nSheetIndex);
        
        HSSFRow 	row 	= null;
        HSSFCell 	cell 	= null;

        List 		lst 	= null;
        HashMap 	map 	= null;
        HashMap		t_map	= null;
        
        int nRow, nCell;
        
        // Row Count Read
        nRow = sheet.getPhysicalNumberOfRows();
        
        lst 	= new ArrayList();
        t_map 	= new HashMap();
        
        for ( int i = 0 ; i < nRow ; i++ ) {
        	
            row = sheet.getRow(i);
            
            if ( row != null ) {
            	
        		// Read Row -> Cell Count Read
                nCell = row.getPhysicalNumberOfCells();
                
        		if ( i == 0 ) {
                    for ( int j = 0 ; j < nCell ; j++ ) {
                        cell = row.getCell(j);
                        t_map.put(String.valueOf(j), (bDefineTitle)?getValue(cell):"Cell_"+j);                                
                    }
                    
        		} else {
        			
        			map = new HashMap();

        			for ( int j = 0 ; j < nCell ; j++ ) {
                        cell = row.getCell(j);
                        map.put(t_map.get(String.valueOf(j)), getValue(cell));                                
                    }
        			lst.add(map);
        		}

            }
        }
        
        
        return lst;
    }
    /*===============================================================================================
     * 
     * 	Cell Type 에 알맞은 형식으로 데이터를 읽어와 String 리턴한다.
     * 
     *=============================================================================================== 
     * */
    public static String getValue(HSSFCell cell) {
    	
    	String value = "";
            
        if ( cell != null ) { 

            switch (cell.getCellType()) {

                case HSSFCell.CELL_TYPE_FORMULA :
                    value = cell.getCellFormula();
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC :
                    value = String.valueOf(cell.getNumericCellValue()); //double
                    break;
                case HSSFCell.CELL_TYPE_STRING :
                    value = cell.getStringCellValue(); //String
                    break;
                case HSSFCell.CELL_TYPE_BLANK :
                    value = "";
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN :
                    value = String.valueOf(cell.getBooleanCellValue()); //boolean
                    break;
                case HSSFCell.CELL_TYPE_ERROR :
                    value = String.valueOf(cell.getErrorCellValue()); // byte
                    break;
                default :
            }
        }
        return value.trim();
    }

}

'Programming > Java' 카테고리의 다른 글

[Log4j] Logger Properties 설정  (0) 2010.03.04
[Beginner] Text Read  (0) 2010.01.28
[Secret] Encrypt / Decrypt  (0) 2010.01.19
[Beginner] HashMap, List GetDataString  (0) 2010.01.19
[Beginner] HttpServletRequest - HashMap, List Convert  (1) 2010.01.19