利用poi从excel中导入数据到数据库

最近遇到了从excel向数据库导入数据的情况，利用POI技术实现数据的导入：

前台页面代码如下：

<input type="file" name="" id="file" style="width: 165px">
<button type="button" οnclick="improtMessage()" style="width: 48px">导入</button>
<button type="button" οnclick="exportMessage()" style="width: 48px">导出</button>
<button type="button" οnclick="dowloadMessage()" style="width: 70px">模板下载</button>

前台js代码：

/**
 * 导入配置
 */
function improtMessage() {
	$.messager.confirm('提示','确定您要导入配置？',function(r){
		if (r){
			var fileName=$('#file').val();
			var file=fileName.split(".")[0];
		
			$('#file').attr("name",$('#file').val());
			$.ajaxFileUpload({
				url: path+'/systemConfigureController/importSysConfig.do',
				secureuri: false,//异步
				fileElementId: 'file',//上传控件ID
				dataType: 'text',//返回的数据信息格式
				success: function(data){
					$.messager.alert('提示','导入配置成功！');
					if(file == "小区"){
						selectVillageMessage();						
					}if(file == "国家地区码"){
						selectCountryMessage();					
					}if(file == "位置区"){
						selectPositionMessage();					
					}if(file == "终端信息"){
						selectIMEIMessage();					
					}
				}
			})
		}
	});
}

控制层代码如下：

/**
	 * 导入配置
	 * @param request
	 * @param response
	 * @throws IOException
	 */

	@RequestMapping("importSysConfig")
	public void importData(HttpServletRequest request, HttpServletResponse response) throws IOException{
		logger.info("**********into method importData*************");
		// 设置上下文
	    CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(
	        request.getSession().getServletContext());
	    // 检查form是否有enctype="multipart/form-data"
	    if (multipartResolver.isMultipart(request)) {
	    	MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
	    	Iterator<String> iter = multiRequest.getFileNames();
	    	while (iter.hasNext()) {
	    		List objList = new ArrayList();
	    		MultipartFile file = multiRequest.getFile(iter.next());
	    		logger.info("print out file:  "+file+"  fileName:  "+file.getName()+" fileSize: "+file.getSize());
    			version = (file.getName().endsWith(".xls") ? version2003 : version2007);
    			if (version == 2003) {
    				 POIFSFileSystem fs = new POIFSFileSystem(file.getInputStream());  
                     HSSFWorkbook wb = new HSSFWorkbook(fs);  
                     HSSFSheet sheet = wb.getSheetAt(0); 
                     readObject(file.getName(), sheet);
                     logger.info("print out readExcel: " + objList);
    			}else if (version == 2007){
    				XSSFWorkbook hwk = new XSSFWorkbook(file.getInputStream());// 将is流实例到 一个excel流里
    				XSSFSheet sheet = hwk.getSheetAt(0);// 得到book第一个工作薄sheet
    				readObject(file.getName(), sheet);
    				logger.info("print out readExcel: " + objList);
    			}
	    	}
	    }
	    response.getWriter().print("1");

	}
	
	/**
	 * 根据不同的excle表名，调用不同的读取方法
	 * @param name
	 * @param sheet
	 * @return
	 */
	public void readObject(String name, Sheet sheet){
		List objList = new ArrayList();
		String _name = name.substring(0, name.indexOf("."));
		if (_name.equals("国家地区码")){
			objList = readCountryExcel(sheet);
			systemConfigureService.addCountryMEessage(objList);
		}
		else if (_name.equals("位置区")){
			objList = readPositionExcel(sheet);
			systemConfigureService.addPositionMessage(objList);
		}
		else if(_name.equals("小区")){
			objList = readVillageExcel(sheet);
			systemConfigureService.addVillageMessage(objList);
		}
		else if(_name.equals("终端信息")){
			objList = readIMEIExcel(sheet);
			systemConfigureService.addIMEIMessage(objList);
		}
	}
	

/**
	 * 1读取国家地区码excel表格
	 * @param sheet
	 * @return
	 */
	public List<Country> readCountryExcel(Sheet sheet){
		List<Country> countryList = new ArrayList<Country>();
		 int rowNum = sheet.getPhysicalNumberOfRows();  //获得总共的行数
		 Country country = null;
		 for (int i =1; i<rowNum; i++){
			 country = new Country();
			 Row row = sheet.getRow(i);   //得到第i行数据
			 Cell c = null;
			 if (null != row){
				 c = row.getCell(0);
				 if (null != c){
					 if (c.getCellType() == c.CELL_TYPE_NUMERIC) {
						 c.setCellType(c.CELL_TYPE_STRING);
						 country.setCountry(c.getStringCellValue());
					 }else{
						 country.setCountry(c.getStringCellValue());
					 }
				 }
				 c = row.getCell(1);
				 if (null != c){
					 if (c.getCellType() == c.CELL_TYPE_NUMERIC) {
						 c.setCellType(c.CELL_TYPE_STRING);
						 country.setProvince(c.getStringCellValue());
					 }else {
						 country.setProvince(c.getStringCellValue());
					 }
				 }
				 c = row.getCell(2);
				 if (null != c){
					 if (c.getCellType() == c.CELL_TYPE_NUMERIC) {
						 c.setCellType(c.CELL_TYPE_STRING);
						 country.setZone(c.getStringCellValue());
					 }else {
						 country.setZone(c.getStringCellValue());
					 }
				 }
				 c = row.getCell(3);
				 if (null != c){
					 if (c.getCellType() == c.CELL_TYPE_NUMERIC) {
						 c.setCellType(c.CELL_TYPE_STRING);
						 country.setCode(c.getStringCellValue());
					 }else {
						 country.setCode(c.getStringCellValue());
					 }
				 }
			 }
			 countryList.add(country);
			 logger.info("print out read Excel row" + i + "Country: " + country);
		 }
		return countryList;
	}
	
	/**
	 * 2.读取位置区excel表格
	 * @param sheet
	 * @return
	 */
	public List<Position> readPositionExcel(Sheet sheet){
		List<Position> positionList = new ArrayList<Position>();
		 int rowNum = sheet.getPhysicalNumberOfRows();  //获得总共的行数
		 Position position = null;
		 for (int i =1; i<rowNum; i++){
			 position = new Position();
			 Row row = sheet.getRow(i);   //得到第i行数据
			 Cell c = null;
			 if (null != row){
				 c = row.getCell(0);
				 if (null != c){
					 if (c.getCellType() == c.CELL_TYPE_NUMERIC) {
						 c.setCellType(c.CELL_TYPE_STRING);
						 position.setArea(c.getStringCellValue());
					 }else {
						 position.setArea(c.getStringCellValue());
					 }
				 }
				 c = row.getCell(1);
				 if (null != c){
					 if (c.getCellType() == c.CELL_TYPE_NUMERIC) {
						 c.setCellType(c.CELL_TYPE_STRING);
						 position.setPosition_code(c.getStringCellValue());
					 }else {
						 position.setPosition_code(c.getStringCellValue());
					 }
				 }
				 c = row.getCell(2);
				 if (null != c){
					 if (c.getCellType() == c.CELL_TYPE_NUMERIC) {
						 c.setCellType(c.CELL_TYPE_STRING);
						 position.setPosition_name(c.getStringCellValue());
					 }else {
						 position.setPosition_name(c.getStringCellValue());
					 }
					 
				 }
			 }
			 positionList.add(position);
			 logger.info("print out read Excel row" + i + "position: " + position);
		 }
		return positionList;
	}
	
	/**
	 * 3.读取小区excel表格
	 * @param sheet
	 * @return
	 */
	public List<Village> readVillageExcel(Sheet sheet){
		StringBuffer sss=null;
		List<Village> villageList = new ArrayList<Village>();
		int rowNum = sheet.getPhysicalNumberOfRows();  //获得总共的行数
		   for(int i = 1; i<rowNum; i++){            
			   Row row = sheet.getRow(i);  //得到第i行数据         
			   int cols = row.getLastCellNum();  // 该行的总列数      
			   String[] str = new String[cols];   // 用来存放该行每一列的值    
			   for (int j = 0; j < cols; j++) {               
				   Object col = row.getCell((short)j);   
				   if (null != col){
					   str[j] = col.toString();          
				   }else{
					   str[j] = "";
				   }
				 	  
			   }  
			   Village v = new Village(str); 
			   logger.info("print out read String[] str" +  "str: " + Arrays.toString(str));
			   villageList.add(v);
		   }
		return villageList;
	}
	
	/**
	 * 4.读取终端信息excel表格
	 * @param sheet
	 * @return
	 */
	public List<IMEI> readIMEIExcel(Sheet sheet){
		List<IMEI> IMEIList = new ArrayList<IMEI>();
		 int rowNum = sheet.getPhysicalNumberOfRows();  //获得总共的行数
		 IMEI _imei = null;
		 for (int i =1; i<rowNum; i++){
			 _imei = new IMEI();
			 Row row = sheet.getRow(i);   //得到第i行数据
			 Cell c = null;
			 if (null != row){
				 c = row.getCell(0);
				 if (null != c){
					 if (c.getCellType() == c.CELL_TYPE_NUMERIC){
						 c.setCellType(c.CELL_TYPE_STRING);
						 _imei.setIMEI(c.getStringCellValue());
					 }else{
						 _imei.setIMEI(c.getStringCellValue());
					 }
				 }
				 c = row.getCell(1);
				 if (null != c){
					 if (c.getCellType() == c.CELL_TYPE_NUMERIC){
						 c.setCellType(c.CELL_TYPE_STRING);
						 _imei.setImei_version(c.getStringCellValue());
					 }else{
						 _imei.setImei_version(c.getStringCellValue());
					 }
				 }
				 c = row.getCell(2);
				 if (null != c){
					 if (c.getCellType() == c.CELL_TYPE_NUMERIC){
						 c.setCellType(c.CELL_TYPE_STRING);
						 _imei.setImei_name(c.getStringCellValue());
					 }else{
						 _imei.setImei_name(c.getStringCellValue());
					 }
				 }
				 c = row.getCell(3);
				 if (null != c){
					 if (c.getCellType() == c.CELL_TYPE_NUMERIC){
						 c.setCellType(c.CELL_TYPE_STRING);
						 _imei.setImei_factory_name(c.getStringCellValue());
					 }else{
						 _imei.setImei_factory_name(c.getStringCellValue());
					 }
					 
				 }
			 }
			 IMEIList.add(_imei);
			 logger.info("print out read Excel row" + i + "_imei: " + _imei);
		 }
		return IMEIList;
	}
}

servicedao的代码：

public void addCountryMEessage(List<Country> list);
	public void addIMEIMessage(List<IMEI> list);
	public void addPositionMessage(List<Position> list);
	public void addVillageMessage(List<Village> list);

servicedaoimpl的代码：

@Override
	public void addCountryMEessage(List<Country> list) {
		countryDao.addCountryMessage(list);
		
	}

	@Override
	public void addIMEIMessage(List<IMEI> list) {
		
		imeiDao.addIMEIMessage(list);
		
	}

	@Override
	public void addPositionMessage(List<Position> list) {
		positionDao.addPositionMessage(list);
		
	}

	@Override
	public void addVillageMessage(List<Village> list) {
		villageDao.addVillageMessage(list);
		
	}

xml中sql的代码如下：

<!-- 向数据库imei中插入数据 -->
	<insert id="addIMEIMessage" parameterType="java.util.List">
		insert into imei 
		(IMEI, imei_version, imei_name, imei_factory_name)
		values
		<foreach collection="list" item="item" index="index" separator="," >
			(#{item.IMEI}, #{item.imei_version}, #{item.imei_name}, #{item.imei_factory_name})
		</foreach>
	</insert>