Java导入Excel的简单实现

java导入使用Apache导入Excel的简单实现

 

 

 

package com.bruce.geekway.tonglian.job;

import java.io.File;
import java.io.FileInputStream;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.Date;
import java.util.Iterator;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.bruce.geekway.utils.SeasonUtils;

public class TestImportHS300ExcelData {

	public static void main(String[] args) throws Exception {
		
        String[] fieldList = new String[]{"stockDate","open","high","low","close","turnover","volume"};
		TestImportHS300ExcelData t = new TestImportHS300ExcelData();
		t.importExcel(new File("/Users/yanghl/Downloads/hs30002.xlsx"),HS300StockData.class,fieldList);
	}
	
	
	 public void importExcel(File file,Class clazz,String[] fieldList)  throws Exception {  
 	        try {  
 	        	
 	        	Workbook book = null;
 	        	Sheet sheet = null;
	            
 	        	// 得到工作表  
 	        	if(file.getName().endsWith("xlsx")){
 		            book = new XSSFWorkbook(new FileInputStream(file));  
 	        	}else if(file.getName().endsWith("xls")){
 	        		book = new HSSFWorkbook(new FileInputStream(file));
 	        	}
 	        	
 	        	// 得到第一页  
 	        	sheet = book.getSheetAt(0);  
 	        	
	            // 得到第一面的所有行  
	            Iterator<Row> row = sheet.rowIterator();  
	            
	            /** 
	             * 标题解析 
	             */  
	            // 得到第一行，也就是标题行  
	            Row title = row.next();  
	            // 得到第一行的所有列  
	            Iterator<Cell> cellTitle = title.cellIterator();  
	            // 循环标题所有的列  
	            StringBuilder sb = new StringBuilder();
	            while (cellTitle.hasNext()) {  
	                Cell cell = cellTitle.next();  
	                sb.append(cell.getStringCellValue().trim()).append(" ");
	            }  
	            System.out.println(sb.toString());
	            
	            /** 
	             * 解析内容行 
	             */  
	            while (row.hasNext()) {  
	            	
	                // 标题下的第一行  
	                Row rown = row.next();  
	                System.out.println(" row num = " + rown.getRowNum());
	                // 行的所有列  
	                Iterator<Cell> cellbody = rown.cellIterator();  
	                Object result =  clazz.newInstance();
	                // 遍历一行的列
	                while (cellbody.hasNext()) {  
	                	
//	                	System.out.println("lastRow = " + sheet.getLastRowNum() + " nowRown =" +rown.getRowNum());
	                	
	                	//cell.getNumericCellValue() 会有默认值    cell.toString() 不会有默认值
	                	
	                	//遍历每一列
	                    Cell cell = cellbody.next();
	                    
	                    //反射获取对象的字段
	                    Field filed = result.getClass().getDeclaredField(fieldList[cell.getColumnIndex()]);
	                    filed.setAccessible(true);
	                    
	                    if(filed.getType() == Date.class){
	                    	filed.set(result,  cell.getDateCellValue());
	                    	continue;
	                    }
	                    
	                    if(filed.getType() == Double.class){
	                    	filed.set(result, cell.getNumericCellValue()==0 ?  null : cell.getNumericCellValue());
	                    	continue;
	                     }
	                    
	                    if(filed.getType() == BigDecimal.class){
	                    	filed.set(result, getRoundForString(cell.toString()));
	                    	continue;
	                    }
	                    
	                    if(filed.getType() == Integer.class){
	                    	filed.set(result, StringUtils.isBlank(cell.toString()) ? null : Integer.parseInt(cell.toString()));
	                    	continue;
	                     }
	                    
	                    if(filed.getType() == String.class){
	                    	filed.set(result, cell.getStringCellValue());
	                    }
	                } 
	                System.out.println("++++" + SeasonUtils.getGson().toJson(result));
	            }  
	        } catch (Exception e) {  
	            e.printStackTrace();  
	            throw e;  
	        }  
	    }  
	 
		public static BigDecimal getRoundForString(String val) {
			if(StringUtils.isBlank(val)) return null;
			return new BigDecimal(val).setScale(2, BigDecimal.ROUND_HALF_UP);
		}
}



class HS300StockData implements Serializable{
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private Date stockDate;
	private String stockCode;
	private String stockName;
	private Double open ; //开盘价
	private BigDecimal close; //收盘价
	private BigDecimal high; //最低
	private BigDecimal low; //最高
	private BigDecimal preClose; //早日收盘价
	private BigDecimal volume;//成交量
	private BigDecimal turnover;//成交额
	
	
	public Date getStockDate() {
		return stockDate;
	}
	public void setStockDate(Date stockDate) {
		this.stockDate = stockDate;
	}
	public String getStockCode() {
		return stockCode;
	}
	public void setStockCode(String stockCode) {
		this.stockCode = stockCode;
	}
	public String getStockName() {
		return stockName;
	}
	public void setStockName(String stockName) {
		this.stockName = stockName;
	}
	
	
	public Double getOpen() {
		return open;
	}
	public void setOpen(Double open) {
		this.open = open;
	}
	public BigDecimal getClose() {
		return close;
	}
	public void setClose(BigDecimal close) {
		this.close = close;
	}
	public BigDecimal getHigh() {
		return high;
	}
	public void setHigh(BigDecimal high) {
		this.high = high;
	}
	public BigDecimal getLow() {
		return low;
	}
	public void setLow(BigDecimal low) {
		this.low = low;
	}
	public BigDecimal getPreClose() {
		return preClose;
	}
	public void setPreClose(BigDecimal preClose) {
		this.preClose = preClose;
	}
	public BigDecimal getVolume() {
		return volume;
	}
	public void setVolume(BigDecimal volume) {
		this.volume = volume;
	}
	public BigDecimal getTurnover() {
		return turnover;
	}
	public void setTurnover(BigDecimal turnover) {
		this.turnover = turnover;
	}
}

 

参考地址：

http://blog.csdn.net/hiping_cao/article/details/24460423

http://www.cnblogs.com/mabaishui/p/5826864.html