SpringBoot集成Apache POI完成Excel的导入

1.：导入Apache POI依赖

<dependency>
 <groupId>org.apache.poi</groupId>
 <artifactId>poi</artifactId>
 <version>3.17</version>
</dependency>
<dependency>
 <groupId>org.apache.poi</groupId>
 <artifactId>poi-ooxml</artifactId>
 <version>3.17</version>
</dependency>

2.：ResponseTemplate 代码

package com.systop.common.bean;

import lombok.Data;

/**
 * 响应模板
 */
@Data
public class ResponseTemplate {

	// 标示响应状态
	private int code;

	// 提示信息
	private String msg;

	// 对象数据
	private Object data;

	// 请求成功
	public static ResponseTemplate success() {
		ResponseTemplate template = new ResponseTemplate();
		template.setCode(200);
		template.setMsg("请求成功！");
		template.setData(null);
		return template;
	}

	// 请求成功
	public static ResponseTemplate success(String msg, Object data) {
		ResponseTemplate template = new ResponseTemplate();
		template.setCode(200);
		template.setMsg(msg);
		template.setData(data);
		return template;
	}

	// 请求成功
	public static ResponseTemplate success(String msg) {
		ResponseTemplate template = new ResponseTemplate();
		template.setCode(200);
		template.setMsg(msg);
		return template;
	}
	
	// 请求失败
	public static ResponseTemplate fail() {
		ResponseTemplate template = new ResponseTemplate();
		template.setCode(500);
		template.setMsg("请求失败！");
		template.setData(null);
		return template;
	}

	public static ResponseTemplate fail(String message, Object data) {
		ResponseTemplate template = new ResponseTemplate();
		template.setCode(500);
		template.setMsg(message);
		template.setData(data);
		return template;
	}

}

3.：controller代码

@RequestMapping("importExcel")
	public ResponseTemplate importExcel(HttpServletRequest request, HttpServletResponse response,
										@RequestParam(value="file")MultipartFile file) {
		return consultationService.importExcel(request,response,getCurrentUser(),file);
	}

4.：service业务逻辑代码

	@Transactional
	@Override
	public ResponseTemplate importExcel(HttpServletRequest request, HttpServletResponse response,
			User user,MultipartFile file) {
		XSSFWorkbook wb = null;
		try {
			InputStream inputStream = file.getInputStream();
			wb = new XSSFWorkbook(inputStream);
			XSSFSheet sheet = wb.getSheetAt(0);
			int rowNum = sheet.getLastRowNum(); // 行
			int columnNum = 0; // 列
			if(rowNum!=0&&sheet.getRow(0)!=null) {
				columnNum = sheet.getRow(0).getPhysicalNumberOfCells();
			}
			for (int i = 2; i <=rowNum; i++) {
				XSSFRow row = sheet.getRow(i);
				if(row == null) {
					continue;
				}
				int j = 0;
				//导入的学员姓名
				String name = row.getCell(j)==null?"":row.getCell(j).getStringCellValue();
				//判断name 和联系人是否为空 不为空就继续往下走
				if(StringUtils.isEmpty(name)) {
					continue;
				}
				if(StringUtils.isEmpty(name)) {
					//抛出异常告诉用户 姓名必须填写
					Object NOTNAME="NO NAME";
					return ResponseTemplate.fail("姓名为必填项!",NOTNAME);
				}
				//导入的学员性别
				String gender = row.getCell(++j)==null?"":row.getCell(j).getStringCellValue();
				if(StringUtils.isEmpty(gender)) {
					//抛出异常告诉用户 姓名必须填写
					Object NOGENDER="NO GENDER";
					return ResponseTemplate.fail("性别为必填项!",NOGENDER);
				}else
				{
					Dictionary dictionary = dictionaryService.findByText(gender.trim());
					if(dictionary==null){
						Object NOTPHONE="NO PHONE";
						return ResponseTemplate.fail("请填写正确的性别!",NOTPHONE);
					}
				}
				//去掉两边的空格返回文字
				//导入学员的联系人
				String contacts = row.getCell(++j)==null?"":row.getCell(j).getStringCellValue();
				++j;
				if(contacts.equals(0)){
					//抛出异常告诉用户 联系人必须填写
					Object NOTcontacts="NO contacts";
					return ResponseTemplate.fail("联系人为必填项!",NOTcontacts);
				}
				//导入学员的联系电话
				String contactPhone = "";
				//转换手机号格式
				if(StringUtils.isEmpty(row.getCell(j)==null?"":row.getCell(j).getNumericCellValue())){
					//抛出异常告诉用户 联系人必须填写
					Object NOTPHONE="NO PHONE";
					return ResponseTemplate.fail("联系电话为必填项!",NOTPHONE);
				}
				DecimalFormat df = new DecimalFormat("#");
				if(row.getCell(j).getCellTypeEnum() == CellType.NUMERIC) {

					String phoneD = df.format(row.getCell(j).getNumericCellValue());
					if(phoneD!=null) {
						contactPhone = phoneD;
					}
				}else {
					contactPhone = row.getCell(j)==null?"":df.format(row.getCell(j).getNumericCellValue());
				}
				//意向度
				String Intentionality = row.getCell(++j)==null?"":row.getCell(j).getStringCellValue();
				//沟通记录
				String Communicate = row.getCell(++j)==null?"":row.getCell(j).getStringCellValue();
				//意向课程
				String IntentionCourse = row.getCell(++j)==null?"":row.getCell(j).getStringCellValue();
				//意向班级
				String IntentionClass = row.getCell(++j)==null?"":row.getCell(j).getStringCellValue();
				//跟进状态
				String followUpStatus=row.getCell(++j)==null?"":row.getCell(j).getStringCellValue();
				//渠道的名称
				String ChannelName=row.getCell(++j)==null?"":row.getCell(j).getStringCellValue();
				//最后跟进的时间
				String LastTime=row.getCell(++j)==null?"":row.getCell(j).getStringCellValue();
				//咨询的校区
				String Campus=row.getCell(++j)==null?"":row.getCell(j).getStringCellValue();
				//销售员
				String SalesPerson=row.getCell(++j)==null?"":row.getCell(j).getStringCellValue();
				if(StringUtils.isEmpty(ChannelName)){
					//抛出异常告诉用户 联系人必须填写
					Object NOTChannelName="NO ChannelName";
					return ResponseTemplate.fail("渠道为必填项!",NOTChannelName);
				}
				//构建了一个StudentConsultation对象 用来存储这个学生的信息
				StudentConsultation consultation = new StudentConsultation();
				//判断状态如果为空 就设置？
				if(StringUtils.isEmpty(followUpStatus)){
					followUpStatus="待跟进";
				}
				if(StringUtils.isEmpty(Intentionality)){
					Intentionality="?";
				}

				//分为两种
				//判断的过程就是 来根据联系人的电话来判断就好了 //联系人的电话就是关键字查询
				//根据这个学校的id 联系人的电话 去查询这个学生存在不存在
				//1.一种是存在的话 是需要修改 销售员就好了、
				//如果这个学员存在就执行操作
				//List<Student> students = studentDao.findByNameAndPhone(name, contactPhone);
				List<Student> students = studentDao.findByNameAndPhone(user.getEmployee().getSchool().getId(), contactPhone);
				//如果查到了学生就继续往下走 没有就跳出循坏
				if(students!=null&&!students.isEmpty()) {
					//当前学生存在就修改当前学生的销售员就ok了
					//判断当前学生是否是这个销售员
					Integer employeeId = channelCategoryMBService.employeeId(user.getUsername(), user.getSuffix());
					//修改当前学生所在的employdId
					for (Student su:students){
						//查询当前登录人的id
						channelCategoryMBService.UpdateStudentCreateId(employeeId,su.getId());
					}
				}else {
					//2.一种是不存在的话 就直接添加就好了
					//直接添加学生 //构建一个学生对象
					Student student = new Student();
					//设置名称
					student.setName(name);
					//设置性别
					if(!StringUtils.isEmpty(gender.trim())) {
						Dictionary dictionary = dictionaryService.findByText(gender.trim());
						if(dictionary!=null) {
							student.setGender(Integer.valueOf(dictionary.getCode()));
						}
					}
					//设置联系人
					if(!StringUtils.isEmpty(contacts.trim())) {
						if(contacts.trim().equals("其他")){
							Dictionary dictionary = dictionaryService.findByTestAndId(contacts.trim());
							student.setContacts(Integer.valueOf(dictionary.getCode()));
						}else
						{
							Dictionary dictionary = dictionaryService.findByText(contacts.trim());
							if(dictionary!=null) {
								student.setContacts(Integer.valueOf(dictionary.getCode()));
							}else {
								//抛出异常没有这个
								Object NOTChannelName="NO ChannelName";
								return ResponseTemplate.fail("请填写正确的联系人!",NOTChannelName);
							}
						}
					}
					//设置联系人的手机号
					student.setContactPhone(contactPhone);
					//设置当前学生所在的学校
					student.setSchool(user.getEmployee().getSchool());
					//设置意向度
					if("?".equals(Intentionality.trim())) {
						consultation.setIntentionality(0);
					}else if("低".equals(Intentionality.trim())) {
						consultation.setIntentionality(1);
					}else if("中".equals(Intentionality.trim())) {
						consultation.setIntentionality(2);
					}else if("高".equals(Intentionality.trim())) {
						consultation.setIntentionality(3);
					}else {
						//抛出异常没有这个
						Object NOTChannelName="NO ChannelName";
						return ResponseTemplate.fail("请填写正确的意向度! 分为 低 高 中",NOTChannelName);
					}
					//设置沟通记录
					consultation.setRemark(Communicate);
					//如果课程不为空的话就去判断
					if(!StringUtils.isEmpty(IntentionCourse)){
						//去查询这个课程
						List<Course> courses = channelCategoryMBService.courseList(user.getEmployee().getSchool().getId(), IntentionCourse);
						if(courses.size()==0){
							Object NOTCHANNE="NO CHANNELNAME";
							return ResponseTemplate.fail("请填写已存在的课程!",NOTCHANNE);
						}else
						{
							consultation.setCourse1(courses.get(0));
						}
					}
					if(!StringUtils.isEmpty(IntentionClass)){
						//去查询这个班级
						// todo
						List<ClassGrade> classrooms = classGradeDao.classgradelist(IntentionClass,user.getEmployee().getSchool());
						if(classrooms.size()==0){
							Object NOTCHANNE="NO CHANNELNAME";
							return ResponseTemplate.fail("请填写已存在的班级!",NOTCHANNE);
						}else
						{
							consultation.setGrade1(classrooms.get(0));
						}
					}
					//跟进状态
					consultation.setFollowUpStatus(FollowUpStatusEnum.valueOf(followUpStatus.trim()));
					//查询渠道名称
					// 根据渠道名称 并且当前校区是否存在渠道来查询
					//定义当前的人登录的是什么
					Long schoolId;
					if(user.getEmployee().getSchool().getSchoolLevel() == SchoolLevelEnum.校区){
						schoolId=user.getEmployee().getSchool().getId();
					}
					else
					{
						schoolId=user.getEmployee().getSchool().getPid();
					}
					List<Channel> channels=channelDao.findBySchoolName(ChannelName,schoolId);
					if(channels.size()!=0) {
						Channel chann = channels.get(0);
						consultation.setSourceChannel(chann);
						chann.setConsultationVolume(chann.getConsultationVolume()+1);
						if(StringUtils.isEmpty(followUpStatus)&&FollowUpStatusEnum.已成交.toString().equals(followUpStatus)) {
							chann.setTurnover(chann.getTurnover() + 1);
						}
						// 成交率
						double rate = (double) chann.getTurnover() / chann.getConsultationVolume() * 100;
						chann.setTransactionRate(MathUtil.roundDouble(rate, 2) + "%");
						consultation.setStu(student);
						consultation.setSalesperson(user.getEmployee());
						consultation.setUpdateTime(consultation.getCreatime());
						studentDao.save(student);
						consultationDao.save(consultation);
						//渠道修改提交
						channelDao.save(chann);
						buildParent2StudentRelation(student, student.getContacts(), contactPhone, user.getEmployee().getSchool().getId());
					}else
					{
						//抛出异常告诉用户 渠道名称必填
						Object NOTCHANNE="NO CHANNELNAME";
						return ResponseTemplate.fail("请填写已存在的渠道名称!",NOTCHANNE);

					}
				}
			}
			wb.close();
			inputStream.close();
			return ResponseTemplate.success("导入成功！");
		}
		catch (IllegalArgumentException e){
			return ResponseTemplate.fail("请输入正确的跟进方式！", e.getMessage());
		}catch (IllegalStateException e){
			e.printStackTrace();
			return ResponseTemplate.fail("只能输入汉字！", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
			return ResponseTemplate.fail("导入失败，请检查数据是否符合导入要求！", e.getMessage());
		}
	}