poi 解析excel

 

 

package com.synnex.web.common.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Properties;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.log4j.Logger;
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.springframework.core.io.support.PropertiesLoaderUtils;

import com.synnex.orm.marketing.event.model.Hotel;

public class ExcelParser implements Iterator<Map<String,String>> {


	private static final Logger logger = Logger.getLogger(ExcelParser.class);

	private HSSFWorkbook book;
	private HSSFSheet sheet;

    private InputStream is;
    private Iterator<?> rowIterator;

    private int index;

    String[] buildHeader;

    private Properties keyMap;

    public ExcelParser(String excelFile) throws IOException{
	    is = new FileInputStream(excelFile);
	    book = new HSSFWorkbook(is);
	    init();
    }

    public ExcelParser(InputStream is) throws IOException{
    	book = new HSSFWorkbook(is);
    	init();
    }

    private void init() throws IOException{
    	loadProperties();
    	sheet = book.getSheetAt(0);
        rowIterator = sheet.rowIterator();
        buildHeader = buildTile();
    }

    public void loadProperties() throws IOException{
    	keyMap = PropertiesLoaderUtils.loadAllProperties("convert_map.properties");
    }

    private String[] buildTile(){
    	HSSFRow row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        String[] title = new String[colNum];
        for (int i=0; i<colNum; i++) {
        	String val = getCellValue(row.getCell(i));
            title[i] = keyMap.getProperty(val, val);
        }
        return title;
    }


    public boolean hasNext(){
        return rowIterator.hasNext();
    }

    public Map<String, String> next(){
        HSSFRow row = (HSSFRow)rowIterator.next();
        Iterator<?> cellIterator = row.cellIterator();
        Map<String,String> rowMap = new HashMap<String,String>();
        while(cellIterator.hasNext()){
            HSSFCell cell = (HSSFCell)cellIterator.next();
            if(++index==1){
            	break;
            }
            rowMap.put(buildHeader[cell.getColumnIndex()],getCellValue(cell));
        }
        return rowMap;
    }

    public void remove(){
        throw new UnsupportedOperationException("Sorry,only support read...");
    }

    private String getCellValue(HSSFCell cell){
        String value = null;
        switch(cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                long dd = (long)cell.getNumericCellValue();
                value = dd+"";
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                value = "";
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                value = String.valueOf(cell.getCellFormula());
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                value = String.valueOf(cell.getErrorCellValue());
                break;
            default:
                break;
        }
        return value;
    }

    public void close(){
        if(is != null){
            try {
                is.close();
            } catch (IOException e) {
                logger.error(e.getMessage(), e);
            }
        }
    }

    public static void main(String[] args) throws IllegalAccessException, InvocationTargetException, IOException{
    	ExcelParser ep = new ExcelParser("c:/hotel.xls");
        while(ep.hasNext()){
            Map<String,String> row = ep.next();
            if(!row.isEmpty()){
            	Hotel hotel = new Hotel();
            	BeanUtils.populate(hotel, row);
            	System.out.println(hotel);
            }
        }
        ep.close();
    }
}

 

 

  

package com.synnex.web.common.util;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.log4j.Logger;

import com.synnex.orm.marketing.event.model.Hotel;

public class ExcelReader {

	private static final Logger logger = Logger.getLogger(ExcelReader.class);

	private ExcelParser xls_parser;

	public ExcelReader(String filenName) throws IOException{
		xls_parser = new ExcelParser(filenName);
	}

	public ExcelReader(InputStream is) throws IOException{
		xls_parser = new ExcelParser(is);
	}

	public void close(){
		if(xls_parser!=null){
			xls_parser.close();
		}
	}

	public <T> List<T> convert(Class<T> clz) {
		List<T> ret = new ArrayList<T>();
		while(xls_parser.hasNext()){
            Map<String,String> row = xls_parser.next();
            if(!row.isEmpty()){
            	try {
            		T obj = clz.newInstance();
                	BeanUtils.populate(obj, row);
                	ret.add(obj);
				} catch (InstantiationException e) {
					logger.error(e.getMessage());
					throw new RuntimeException(e.getMessage());
				} catch (IllegalAccessException e) {
					logger.error(e.getMessage());
					throw new RuntimeException(e.getMessage());
				} catch (InvocationTargetException e) {
					logger.error(e.getMessage());
					throw new RuntimeException(e.getMessage());
				}
            }
        }
		close();
		return ret;
	}


	public static void main(String[] args) throws InstantiationException, IllegalAccessException, InvocationTargetException, IOException{
		ExcelReader reader = new ExcelReader("c:/hotel.xls");
//		System.out.println(reader.conver(Hotel.class));
		for(Hotel hotel:reader.convert(Hotel.class)){
			System.out.println(hotel.getAddress());
		}
	}
}

 

    今天发现原来poi已经有了类似的实现 

    http://myjeeva.com/read-excel-through-java-using-xssf-and-sax-apache-poi.html