【四二学堂】springmvc使用poi插件实现excel表格导入

话不多说，一言不合直接上代码！

框架：ssm

要导入的模板

 

html页面

 

<form action="${ctx}/employees/employees/import" enctype="multipart/form-data" method="post">
	<input type="file" name="file" value="">
	<input type="submit"  value="导入">
</form>

 

控制层代码

 

@Controller
@RequestMapping(value = "/employees/employees")
public class EmployeesController{

	@Autowired
	private EmployeesService employeesService;

	/**
	 * 导入用户数据
	 */
	@ResponseBody
	@RequestMapping(value = "import",method = RequestMethod.POST)
	public String importFile(@RequestParam("file")MultipartFile file) {
		try {
			//获取文件输入流
			InputStream in = file.getInputStream();
			//调用工具类读取Excel表格
			ExcelImportUtils u = new ExcelImportUtils();
			//返回一个集合
			List<Employees> list = u.readExcel(in);
			for (Employees employees : list) {
				employeesService.save(employees);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

}

 

 

 

工具类代码


我这里要导入的字段较多，可根据自己情况自行修改。

 

需要处理的包括数据类型转换，数据库与Excel表格字段不一致的情况（1代表男，2代表女）。

 

public class ExcelImportUtils {

	public List<Employees> readExcel(InputStream in) throws ParseException {
		List<Employees> list = new ArrayList<Employees>();
		Employees employees = null;
		try {
			@SuppressWarnings("resource")
			XSSFWorkbook hssfWorkbook = new XSSFWorkbook(in);
			for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {

				XSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
				if (hssfSheet == null) {
					continue;
				}
			
				for (int rowNum = 2; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
					XSSFRow hssfRow = hssfSheet.getRow(rowNum);

					employees = new Employees();

				
					for (int i = 0; i < hssfRow.getLastCellNum(); i++) {
						XSSFCell cell = hssfRow.getCell(i);
						hssfRow.getCell(i).setCellType(
								cell.CELL_TYPE_STRING);
						if (i == 1) {
							//姓名
							String name = hssfRow.getCell(0)
									.getStringCellValue();
							if (name.equals("")) {
								return list;
							}
							employees.setName(name);
						} else if (i == 2) {
							//年纪
							String age = hssfRow.getCell(1)
									.getStringCellValue();
							employees.setAge(Long.parseLong(age));
						} else if (i == 3) {
							//政治面貌
							String political = hssfRow.getCell(2)
									.getStringCellValue();
							if(political.equals("群众")){
								employees.setPolitical("1");
							}else if(political.equals("党员")){
								employees.setPolitical("2");
							}
							
						} else if (i == 4) {
							//出生日期
							String birthday = hssfRow.getCell(3)
									.getStringCellValue();
							
							SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
							Date date = sdf.parse(birthday); 
							employees.setBirthday(date);
						} else if (i == 5) {
							//血型
							String blood = hssfRow.getCell(4)
									.getStringCellValue();
							
							if(blood.equals("O型")){
								employees.setBlood("4");
							}else if(blood.equals("B型")){
								employees.setBlood("1");
							}else if(blood.equals("A型")){
								employees.setBlood("2");
							}else if(blood.equals("AB型")){
								employees.setBlood("3");
							}
						} else if (i == 6) {
							//星座
							String constellation = hssfRow.getCell(5)
									.getStringCellValue();
							employees.setConstellation(constellation);
						} else if (i == 7) {
							//兴趣爱好
							String hobby = hssfRow.getCell(6)
									.getStringCellValue();
							String[] split = hobby.split("\\  ");
							StringBuffer str = new StringBuffer();
							
							for (String string : split) {
								if("打球".equals(string)){
									str.append("1,");
								}
								if("上网".equals(string)){
									str.append("2,");
								}
								if("看书".equals(string)){
									str.append("3,");
								}
								if("打麻将".equals(string)){
									str.append("4,");
								}
							}
							
							employees.setHobby(str.toString());
						} else if (i == 8) {
							//户口所在地
							String residence = hssfRow.getCell(7)
									.getStringCellValue();
							employees.setResidence(residence);
						} else if (i == 9) {
							//户籍
							String householdregister = hssfRow.getCell(8)
									.getStringCellValue();
							
							if(householdregister.equals("农业")){
								employees.setHouseholdregister("1");
							}else if(householdregister.equals("非农")){
								employees.setHouseholdregister("2");
							}
						}else if (i == 10) {
							//档案所在地
							String archives = hssfRow.getCell(9)
									.getStringCellValue();
							employees.setArchives(archives);
						}else if (i == 11) {
							//外语
							String foreignlanguages = hssfRow.getCell(10)
									.getStringCellValue();
							employees.setForeignlanguages(foreignlanguages);
						}else if (i == 12) {
							//外语水平
							String level = hssfRow.getCell(11)
									.getStringCellValue();
							employees.setLevel(level);
						}else if (i == 13) {
							//电话
							String phone = hssfRow.getCell(12)
									.getStringCellValue();
							employees.setPhone(phone);
						}else if (i == 14) {
							//qq
							String qq = hssfRow.getCell(13)
									.getStringCellValue();
							employees.setQq(qq);
						}else if (i == 15) {
							//email
							String email = hssfRow.getCell(14)
									.getStringCellValue();
							employees.setEmail(email);
						}else if (i == 16) {
							//微信
							String wechat = hssfRow.getCell(15)
									.getStringCellValue();
							employees.setWechat(wechat);
						}else if (i == 17) {
							//地址
							String address = hssfRow.getCell(16)
									.getStringCellValue();
							employees.setAddress(address);
						}else if (i == 18) {
							//目前待遇
							String presenttreatment = hssfRow.getCell(17)
									.getStringCellValue();
							employees.setPresenttreatment(presenttreatment);
						}else if (i == 19) {
							//期望待遇
							String expectedtreatment = hssfRow.getCell(18)
									.getStringCellValue();
							employees.setExpectedtreatment(expectedtreatment);
						}else if (i == 20) {
							//获取途径
							String channel = hssfRow.getCell(19)
									.getStringCellValue();
							if(channel.equals("前程无忧")){
								employees.setChannel("1");
							}else if(channel.equals("智联招聘")){
								employees.setChannel("2");
							}else if(channel.equals("boss直招")){
								employees.setChannel("3");
							}
						}else if (i == 21) {
							//申请职位
							String jobapplication = hssfRow.getCell(20)
									.getStringCellValue();
							employees.setJobapplication(jobapplication);
						}else if (i == 22) {
							//其他技能
							String otherskill = hssfRow.getCell(21)
									.getStringCellValue();
							employees.setOtherskill(otherskill);
						}
						
					}
					list.add(employees);
				}
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
}

业务层省略。


持久层省略。