java实现上传excle数据

具体实现方法：

	/**
	    * @Title: extracted  
	    * @Description: 上传
	 */
	public BaseVO<Object> extracted(MultipartFile file) {
		String fileName;
		BaseVO<Object> vo = new BaseVO<Object>();
 		
		fileName = "上传.xlsx";
		
		try
		{
			// 设置 id
			
			ImportExcelUtil importExcelUtil = new ImportExcelUtil();
			// exlce 数据集合
		   	List<List<Object>>  lists = importExcelUtil.getBankListByExcel(file.getInputStream(), fileName);
		   	// 对象集合
		   	List<SystemPrice> resultList = new ArrayList<SystemPrice>();
		   	
		   	if(lists.size() >= 2){   					// 第一条数据为 表头 ,第二条为真实的数据
		   		
		   		Object  id = lists.get(0).get(0);
		   		if(id != null ){
		   			if( "id".equals(String.valueOf(id).trim().toLowerCase()))
		   			{
		   				throw new Exception("不允许出现id 字段");
		   			}
		   		}
		   		
		   		lists.remove(0);
		   		// 从excle 第二行开始导入数据
		   		for(List<Object> temp :  lists ){
		   			if(temp != null && temp.size() >= 1){  
		   			
		   				try
						{
		   					// 转换对象
		   					SystemPrice tempEntity = toEnttiy(temp);
		   					// 加入集合
							resultList.add(tempEntity);
						} 
		   				catch (Exception e)
						{
		   					
						}
		   				
		   			}
		   		}
		   		
		   		if(resultList.size() >= 1)
		   		{
		   			for(SystemPrice entity : resultList)
		   			{	
		   				// 保存数据到数据库
		   				systemPriceService.createOrUpdateSystemPrice(entity);
		   			}
		   			
		   		}
		   	}
			   	
		} 
		catch (Exception e)
		{
		 vo.setData(false);
		 vo.setMess(ResponseEnum.FAIL.getMsg());
		 vo.setCode(ResponseEnum.FAIL.getCode());
		}
		vo.setData(true);
		vo.setMess(ResponseEnum.SUCCESS.getMsg());
		vo.setCode(ResponseEnum.SUCCESS.getCode());
		return vo;
	}

里面所用到的poi utils方法：

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

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;


public class ImportExcelUtil {
	
	private final static String excel2003L =".xls";    //2003- 版本的excel
	private final static String excel2007U =".xlsx";   //2007+ 版本的excel
	
	/**
	 * 描述：获取IO流中的数据，组装成List<List<Object>>对象
	 * @param in,fileName
	 * @return
	 * @throws IOException 
	 */
	public  List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{
		List<List<Object>> list = null;
		
		//创建Excel工作薄
		Workbook work = this.getWorkbook(in,fileName);
		if(null == work){
			throw new Exception("创建Excel工作薄为空！");
		}
		
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		
		list = new ArrayList<List<Object>>();
		sheet = work.getSheetAt(0);  										//读取第一页
		
		int headLength = 0;
		
		
		for (int j = 1;j <= sheet.getLastRowNum(); j++)						//遍历当前sheet中的所有行 从表头开始读起
		{					
			row = sheet.getRow(j);											遍历所有的列
			List<Object> li = new ArrayList<Object>();				
			
			int bodyRowLength = 0;
			for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++)   //装载列
			{
				cell = row.getCell(y);
				Object obj = null ; 
					
				try{
					obj = this.getCellValue(cell);
					
					if(obj != null)
					{
						obj = String.valueOf(obj).trim();
						if(obj.equals(""))
						{
							obj = null;
						}
					}
					
				}catch(Exception e){
					e.printStackTrace();
				}
				
				/对第一行进行特别处理，如果遇到 空则中断
				if(j == 1 && (obj == null || "".equals(String.valueOf(obj))))
				{
					break;
				}
				else
				{
					///数据区域
					if(j > 1)
					{
						if(bodyRowLength >= headLength +1)
						{
							break;
						}
						else
						{
							bodyRowLength++ ;	
						}
					}
					
					li.add(obj);
					
				}
				
			}
			
			if(ParamUtils.filterList(li))
			{
				///获取第一个行 标题的长度
				if(list.size() <= 0)
				{
					headLength = li.size() -1;
				}
				
				list.add(li);
			}
			else
			{
				break;
			}
			
		}
		
		in.close();
		return list;
	}
	
	/**
	 * 描述：根据文件后缀，自适应上传文件的版本 
	 * @param inStr,fileName
	 * @return
	 * @throws Exception
	 */
	public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
		Workbook wb = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if(excel2003L.equals(fileType)){
			wb = new HSSFWorkbook(inStr);  //2003-
		}else if(excel2007U.equals(fileType)){
			wb = new XSSFWorkbook(inStr);  //2007+
		}else{
			throw new Exception("解析的文件格式有误！");
		}
		return wb;
	}

	/**
	 * 描述：对表格中数值进行格式化
	 * @param cell
	 * @return
	 */
	public  Object getCellValue(Cell cell){
		Object value = null;
		DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
		SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化
		DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字
		
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			value = cell.getRichStringCellValue().getString();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if("General".equals(cell.getCellStyle().getDataFormatString())){
				value = df.format(cell.getNumericCellValue());
			}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
				value = sdf.format(cell.getDateCellValue());
			}else{
				value = df2.format(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			value = cell.getBooleanCellValue();
			break;
		case Cell.CELL_TYPE_BLANK:
			value = "";
			break;
		default:
			break;
		}
		return value;
	}
	

}

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;

import com.ihwdz.core.DateUtils;


public class ParamUtils
{
	private static final String regEx_space = "\\s*|\t|\r|\n";
	
	
	public static Map<String, Object> filterEmpty(Map<String, Object> param)
	{
		Set<Map.Entry<String, Object>> entrySet = param.entrySet();
		for (Map.Entry<String, Object> entry : entrySet)
		{
			String key = entry.getKey();
			if(entry.getValue() != null && "".equals(String.valueOf(entry.getValue()))){
				param.put(key, null);
			}else{
				param.put(key, StringUtils.trim(String.valueOf(entry.getValue())));
			}
		}

		return param;
	}
	
	public static Map<String, Object> filterEmptyStrMap(Map<String, String> param)
	{
		Set<Map.Entry<String, String>> entrySet = param.entrySet();
		Map<String, Object> result = new HashMap<String, Object>();
		
		for (Map.Entry<String, String> entry : entrySet)
		{
			String key = entry.getKey();
			if(entry.getValue() != null && "".equals(String.valueOf(entry.getValue()))){
				result.put(key, null);
			}else{
				result.put(key, delHTMLTag(String.valueOf(entry.getValue())));
			}
		}

		return result;
	}
	
	public static List<String> filterIds(String ids){
		
		Boolean success = true;
		
		List<String> listIds = null;
		String[] idsArr = ids.split(",");
		
		if(idsArr != null && idsArr.length >= 1){
			listIds  = Arrays.asList(idsArr);  
		}
		
		for(String id :listIds){
			if(id != null && !"".equals(id)){
				 try
				 {
					 Long.valueOf(id);
				 }catch(Exception e){
					 
					 success = false;
					 break;
				 }
				 
			}else{
				success = false;
				break;
			}
			
		}
		
		if(!success){
			listIds = null;
		}
		
		return listIds;
	}
	
	public static List<Long> filterIdsToLongList(String ids){
		
		Boolean success = true;
		List<Long> listLong = new ArrayList<Long>();
		
		List<String> listIds = null;
		String[] idsArr = ids.split(",");
		
		if(idsArr != null && idsArr.length >= 1){
			listIds  = Arrays.asList(idsArr);  
		}
		
		for(String id :listIds)
		{
			if(id != null && !"".equals(id))
			{
				 try
				 {
					listLong.add(Long.valueOf(id));
				 }
				 catch(Exception e)
				 {
					 success = false;
					 break;
				 }
			}
			else
			{
				success = false;
				break;
			}
		}
		
		if(!success){
			return null;
		}
		
		return listLong;
	}
	
	public static Long filterLongStr(String str){

		if(str.indexOf(".") != -1){
			str = str.substring(0, str.indexOf("."));
		}
		
		return Long.valueOf(str);
	}
	
	public static boolean filterList(List<Object> list){
		boolean success = false;
		
		if(list != null && list.size() >=1){
				for(Object obj : list){
					
					if(obj != null  && !"".equals(String.valueOf(obj))){
						success = true;
						break;
					}
				}
		}

		return success;
	}
	
    private static String delHTMLTag(String str) {
        Pattern p_space = Pattern.compile(regEx_space, Pattern.CASE_INSENSITIVE);
        Matcher m_space = p_space.matcher(str);
        str = m_space.replaceAll("");
        return str.trim(); 
    }
    
    /*
     * 获取字符串中的数字和字母
     */
    public static String getLetterAndNumber(String str){
    	 String rtnStr = "";
         String exp = "\\d+.\\d+|\\w+";
         Pattern  pattern=Pattern.compile(exp);  
         Matcher  ma=pattern.matcher(str);  
         while(ma.find()){  
        	 rtnStr = ma.group();
         }  
         return rtnStr;
    }
    public static List<String> orderByAsc(List<String> scores){  
		  
		  
	    for (int i = 0; i < scores.size() - 1; i++) {  
	        for (int j = 1; j < scores.size() - i; j++) {  
	            String a;  
	            if ((DateUtils.getJustDate(scores.get(j - 1))).compareTo(DateUtils.getJustDate(scores.get(j))) < 0) {  
	  
	                a = scores.get(j - 1);  
	                scores.set((j - 1), scores.get(j));  
	                scores.set(j, a);  
	            }  
	        }  
	    }  
	    return scores;  
	} 
}

需要引入的pom：

<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.9</version>
		</dependency>
		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-excelant</artifactId>
            <version>3.12</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.12</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.9</version>
        </dependency>

具体的封装对象方法：

private SystemPrice toEnttiy(List<Object> list) throws Exception{
 		
 		SystemPrice  entity = new SystemPrice();
 		int i = 0 ;

 		if(list.get(i) != null){												
 			String temp = String.valueOf(list.get(i));
 			
 			entity.setBreed(temp);
 		} i++;
 		
 		if(list.get(i) != null){												
 			
 			String spec = String.valueOf(list.get(i));
 		
 			entity.setSpec(spec);
 			
 		} i++;
 		
 		if(list.get(i) != null){								
 			String temp = String.valueOf(list.get(i));
 			
 			entity.setBrand(temp);
 		} i++;
 		
 		if(list.get(i) != null){												
 			String temp = String.valueOf(list.get(i));
 			
 			entity.setCity(temp);
 		}i++;
 		
 		if(list.get(i) != null){												
 			Double temp = Double.valueOf(list.get(i)+"");
 			
 			entity.setPrice(temp);
 		}i++;
 		
 		entity.setCreateTime(new Date().getTime());
 		entity.setLastAccess(new Date().getTime());
 		entity.setCreateTimeStr(DateUtils.formatDate(new Date(), DateUtils.DATE_FROMAT2));  
 		
 		return entity;
 	}

上传的excle 格式如下，字段栏以此类推封装对象，每一个Object都是excle 的一行数据。