ssm将Excel数据导入数据库

1、controller层

	@RequestMapping(value = "/villageExcelImport", method = RequestMethod.POST)
	public String villageExcelImport(@RequestParam("file")MultipartFile file, Model model) throws Exception {
		System.out.println("进入导入方法---->" + file.getOriginalFilename());
		List<Village> list = villageService.readReport(file.getInputStream());
		for (int i = 0; i < list.size();i++) {
			System.out.print(list.get(i).getvName() + "  ");
		}
		if (list.size() == 0) {
			throw new Exception("插入数据为空");
		}
		villageService.insertImportVillage(list);
		return "redirect:/villagecommitteeSelect";
	}

2、Service层

	//添加数据
	@Override
	public void insertImportVillage(List<Village> list) {
		// TODO Auto-generated method stub
		villageMapper.insertImportVillage(list);
	}
	
//导入方法
	@Override
	public List<Village> readReport(InputStream inputStream) {
		// TODO Auto-generated method stub
		System.out.println("进入readReport...");
		List<Village> userList = new ArrayList<Village>();
		try {
			String cellStr = null;
			Workbook wb = WorkbookFactory.create(inputStream);
			Sheet sheet = wb.getSheetAt(0);
			//从第四行开始读取数据
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				System.out.println("i:"+i);
				Village user = new Village();
				Village addingVillage = new Village();
				Row row = sheet.getRow(i); // 获取行(row)对象
				if (row == null) {
					// row为空的话,不处理
					continue;
				}
				for (int j = 0; j < row.getLastCellNum(); j++) {
					Cell cell = row.getCell(j); // 获得单元格(cell)对象
					// 转换接收的单元格
					cellStr = ConvertCellStr(cell, cellStr);
					// 将单元格的数据添加至一个对象
					addingVillage = addingVillage(j, user, cellStr);
				}
				// 将添加数据后的对象填充至list中
				userList.add(addingVillage);
			}
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (inputStream != null) {
				try {
					inputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			} else {
			}
		}
		return userList;
	}	
	
	private Village addingVillage(int j, Village village, String cellStr) {  
	        switch (j) {  
	        case 0:  
	        	village.setvName(cellStr);
	            break;
	        }
	        return village;  
	    }  
	
	 /** 
     * 把单元格内的类型转换至String类型 
     */
	 private String ConvertCellStr(Cell cell, String cellStr) {
				 switch (cell.getCellType()) {
					 //空值
					 case Cell.CELL_TYPE_BLANK:
						 cellStr="";
						 break;
					 case Cell.CELL_TYPE_STRING:
						 // 读取String
						 cellStr = cell.getStringCellValue().toString();
						 break;
			 case Cell.CELL_TYPE_BOOLEAN:
				 // 得到Boolean对象的方法
				 cellStr = String.valueOf(cell.getBooleanCellValue());
				 break;
			 case Cell.CELL_TYPE_NUMERIC:
				 // 先看是否是日期格式
				 if (DateUtil.isCellDateFormatted(cell)) {
					 // 读取日期格式
					 cellStr = cell.getDateCellValue().toString();
				 } else {
					 // 读取数字
					 int cellStr1=(int)cell.getNumericCellValue();
                /* String cellStr1 =String.valueOf(cell.getNumericCellValue());
                 cellStr=cellStr1.split("\\.")[0];*/
					 cellStr=String.valueOf(cellStr1);
				 }
				 break;
			 case Cell.CELL_TYPE_FORMULA:
				 // 读取公式
				 cellStr = cell.getCellFormula().toString();
				 break;
		 }
		 return cellStr;
	 }

3、dao层

	void insertImportVillage(@Param("list") List<Village> list);

4、Mapper.xml映射文件

<!--执行导入sql文件 -->
	<insert id="insertImportVillage" parameterType="com.pojo.Village">
		insert into village
		(v_name)
		values
		<foreach collection="list" item="item" index="index"
			separator=",">
			(
			#{item.vName}
			)
		</foreach>
	</insert>