员工导入

实现批量导入员工功能，页面端上传excel表格，服务端解析表格获取数据，批量新增用户

搭建环境

父模块pom文件添加依赖

 <!--POI--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.0.1</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.0.1</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>4.0.1</version></dependency>

实现Excel上传

（1）用户实体类配置构造方法

public User(Object [] values) {//用户名	手机号	工号	聘用 形式	入职 时间	部门编码this.username = values[1].toString();this.mobile = values[2].toString();this.workNumber = new DecimalFormat("#").format(values[3]).toString();this.formOfEmployment =((Double) values[4]).intValue();this.timeOfEntry = (Date) values[5];this.departmentId = values[6].toString(); //部门编码 != 部门id}

（2）在system微服务 UserController 中添加上传方法

    /*** 导入Excel，添加用户*  文件上传：springboot*/@RequestMapping(value="/user/import",method = RequestMethod.POST)public Result importUser(@RequestParam(name="file") MultipartFile file) throws Exception {//1.解析Excel//1.1.根据Excel文件创建工作簿Workbook wb = new XSSFWorkbook(file.getInputStream());//1.2.获取SheetSheet sheet = wb.getSheetAt(0);//参数：索引//1.3.获取Sheet中的每一行，和每一个单元格//2.获取用户数据列表List<User> list = new ArrayList<>();System.out.println(sheet.getLastRowNum());for (int rowNum = 1; rowNum<= sheet.getLastRowNum() ;rowNum ++) {Row row = sheet.getRow(rowNum);//根据索引获取每一个行Object [] values = new Object[row.getLastCellNum()];for(int cellNum=1;cellNum< row.getLastCellNum(); cellNum ++) {Cell cell = row.getCell(cellNum);Object value = getCellValue(cell);values[cellNum] = value;}User user = new User(values);list.add(user);}//3.批量保存用户userService.saveAll(list,companyId,companyName);return new Result(ResultCode.SUCCESS);}public static Object getCellValue(Cell cell) {//1.获取到单元格的属性类型CellType cellType = cell.getCellType();//2.根据单元格数据类型获取数据Object value = null;switch (cellType) {case STRING:value = cell.getStringCellValue();break;case BOOLEAN:value = cell.getBooleanCellValue();break;case NUMERIC:if(DateUtil.isCellDateFormatted(cell)) {//日期格式value = cell.getDateCellValue();}else{//数字value = cell.getNumericCellValue();}break;case FORMULA: //公式value = cell.getCellFormula();break;default:break;}return value;}

调用企业微服务获取部门数据

（1）在Ihrm_system模块创建com.ihrm.system.client包，包下创建接口

/*** 声明接口，通过feign调用其他微服务*/
//声明调用的微服务名称
@FeignClient("ihrm-company")
public interface DepartmentFeignClient {/*** 调用微服务的接口*/@RequestMapping(value="/company/department/{id}",method = RequestMethod.GET)public Result findById(@PathVariable(value="id") String id);@RequestMapping(value="/company/department/search",method = RequestMethod.POST)public Department findByCode(@RequestParam(value="code") String code, @RequestParam(value="companyId") String companyId);
}

保存全部用户

    /*** 批量保存用户*/@Transactionalpublic void saveAll(List<User> list ,String companyId,String companyName){for (User user : list) {//默认密码user.setPassword(new Md5Hash("123456",user.getMobile(),3).toString());//iduser.setId(idWorker.nextId()+"");//基本属性user.setCompanyId(companyId);user.setCompanyName(companyName);user.setInServiceStatus(1);user.setEnableState(1);user.setLevel("user");//填充部门的属性Department department = departmentFeignClient.findByCode(user.getDepartmentId(), companyId);if(department != null) {user.setDepartmentId(department.getId());user.setDepartmentName(department.getName());}userDao.save(user);}}

人事报表导出

（1）excel实体类

package com.ihrm.domain.employee.response;import com.ihrm.domain.employee.EmployeeResignation;
import com.ihrm.domain.employee.UserCompanyPersonal;
import com.ihrm.domain.poi.ExcelAttribute;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;
import org.springframework.beans.BeanUtils;@Getter
@Setter
@NoArgsConstructor
@ToString
public class EmployeeReportResult {@ExcelAttribute(sort = 0)private String userId;@ExcelAttribute(sort = 1)private String username;private String departmentName;@ExcelAttribute(sort = 2)private String mobile;@ExcelAttribute(sort = 9)private String timeOfEntry;private String companyId;private String sex;/*** 出生日期*/private String dateOfBirth;/*** 最高学历*/@ExcelAttribute(sort = 3)private String theHighestDegreeOfEducation;/*** 国家地区*/@ExcelAttribute(sort = 4)private String nationalArea;/*** 护照号*/@ExcelAttribute(sort = 5)private String passportNo;/*** 身份证号*/private String idNumber;/*** 身份证照片-正面*/private String idCardPhotoPositive;/*** 身份证照片-背面*/private String idCardPhotoBack;/*** 籍贯*/@ExcelAttribute(sort = 6)private String nativePlace;/*** 民族*/private String nation;/*** 英文名*/private String englishName;/*** 婚姻状况*/private String maritalStatus;/*** 员工照片*/private String staffPhoto;/*** 生日*/@ExcelAttribute(sort = 7)private String birthday;/*** 属相*/@ExcelAttribute(sort = 8)private String zodiac;/*** 年龄*/private String age;/*** 星座*/private String constellation;/*** 血型*/private String bloodType;/*** 户籍所在地*/private String domicile;/*** 政治面貌*/private String politicalOutlook;/*** 入党时间*/private String timeToJoinTheParty;/*** 存档机构*/private String archivingOrganization;/*** 子女状态*/private String stateOfChildren;/*** 子女有无商业保险*/private String doChildrenHaveCommercialInsurance;/*** 有无违法违纪行为*/private String isThereAnyViolationOfLawOrDiscipline;/*** 有无重大病史*/private String areThereAnyMajorMedicalHistories;/*** QQ*/private String qq;/*** 微信*/private String wechat;/*** 居住证城市*/private String residenceCardCity;/*** 居住证办理日期*/private String dateOfResidencePermit;/*** 居住证截止日期*/private String residencePermitDeadline;/*** 现居住地*/private String placeOfResidence;/*** 通讯地址*/private String postalAddress;/*** 联系手机*/private String contactTheMobilePhone;/*** 个人邮箱*/private String personalMailbox;/*** 紧急联系人*/private String emergencyContact;/*** 紧急联系电话*/private String emergencyContactNumber;/*** 社保电脑号*/private String socialSecurityComputerNumber;/*** 公积金账号*/private String providentFundAccount;/*** 银行卡号*/private String bankCardNumber;/*** 开户行*/private String openingBank;/*** 学历类型*/private String educationalType;/*** 毕业学校*/private String graduateSchool;/*** 入学时间*/private String enrolmentTime;/*** 毕业时间*/private String graduationTime;/*** 专业*/private String major;/*** 毕业证书*/private String graduationCertificate;/*** 学位证书*/private String certificateOfAcademicDegree;/*** 上家公司*/private String homeCompany;/*** 职称*/private String title;/*** 简历*/private String resume;/*** 有无竞业限制*/private String isThereAnyCompetitionRestriction;/*** 前公司离职证明*/private String proofOfDepartureOfFormerCompany;/*** 备注*/private String remarks;/*** 离职时间*/@ExcelAttribute(sort = 12)private String resignationTime;/*** 离职类型*/@ExcelAttribute(sort = 10)private String typeOfTurnover;/*** 申请离职原因*/@ExcelAttribute(sort = 11)private String reasonsForLeaving;public EmployeeReportResult(UserCompanyPersonal personal, EmployeeResignation resignation) {BeanUtils.copyProperties(personal,this);if(resignation != null) {BeanUtils.copyProperties(resignation,this);}}
}

package com.ihrm.domain.employee;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;//详情实体类
@Entity
@Table(name = "em_user_company_personal")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserCompanyPersonal implements Serializable {private static final long serialVersionUID = -8414369362479539578L;/*** 用户ID*/@Idprivate String userId;private String username;private String departmentName;private String mobile;private String timeOfEntry;/*** 企业ID*/private String companyId;/*** 性别*/private String sex;/*** 出生日期*/private String dateOfBirth;/*** 最高学历*/private String theHighestDegreeOfEducation;/*** 国家地区*/private String nationalArea;/*** 护照号*/private String passportNo;/*** 身份证号*/private String idNumber;/*** 身份证照片-正面*/private String idCardPhotoPositive;/*** 身份证照片-背面*/private String idCardPhotoBack;/*** 籍贯*/private String nativePlace;/*** 民族*/private String nation;/*** 英文名*/private String englishName;/*** 婚姻状况*/private String maritalStatus;/*** 员工照片*/private String staffPhoto;/*** 生日*/private String birthday;/*** 属相*/private String zodiac;/*** 年龄*/private String age;/*** 星座*/private String constellation;/*** 血型*/private String bloodType;/*** 户籍所在地*/private String domicile;/*** 政治面貌*/private String politicalOutlook;/*** 入党时间*/private String timeToJoinTheParty;/*** 存档机构*/private String archivingOrganization;/*** 子女状态*/private String stateOfChildren;/*** 子女有无商业保险*/private String doChildrenHaveCommercialInsurance;/*** 有无违法违纪行为*/private String isThereAnyViolationOfLawOrDiscipline;/*** 有无重大病史*/private String areThereAnyMajorMedicalHistories;/*** QQ*/private String qq;/*** 微信*/private String wechat;/*** 居住证城市*/private String residenceCardCity;/*** 居住证办理日期*/private String dateOfResidencePermit;/*** 居住证截止日期*/private String residencePermitDeadline;/*** 现居住地*/private String placeOfResidence;/*** 通讯地址*/private String postalAddress;/*** 联系手机*/private String contactTheMobilePhone;/*** 个人邮箱*/private String personalMailbox;/*** 紧急联系人*/private String emergencyContact;/*** 紧急联系电话*/private String emergencyContactNumber;/*** 社保电脑号*/private String socialSecurityComputerNumber;/*** 公积金账号*/private String providentFundAccount;/*** 银行卡号*/private String bankCardNumber;/*** 开户行*/private String openingBank;/*** 学历类型*/private String educationalType;/*** 毕业学校*/private String graduateSchool;/*** 入学时间*/private String enrolmentTime;/*** 毕业时间*/private String graduationTime;/*** 专业*/private String major;/*** 毕业证书*/private String graduationCertificate;/*** 学位证书*/private String certificateOfAcademicDegree;/*** 上家公司*/private String homeCompany;/*** 职称*/private String title;/*** 简历*/private String resume;/*** 有无竞业限制*/private String isThereAnyCompetitionRestriction;/*** 前公司离职证明*/private String proofOfDepartureOfFormerCompany;/*** 备注*/private String remarks;
}

package com.ihrm.domain.employee;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;
import java.util.Date;//员工离职申请
@Entity
@Table(name = "em_resignation")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class EmployeeResignation implements Serializable {private static final long serialVersionUID = 2890789302883962744L;/*** 员工Id*/@Idprivate String userId;/*** 离职时间*/private String resignationTime;/*** 离职类型*/private String typeOfTurnover;/*** 申请离职原因*/private String reasonsForLeaving;/*** 补偿金*/private String compensation;/*** 代通知金*/private String notifications;/*** 社保减员月*/private String socialSecurityReductionMonth;/*** 公积金减员月*/private String providentFundReductionMonth;/*** 图片*/private String picture;/*** 创建时间*/private Date createTime;
}

（2）download工具类

package com.ihrm.common.utils;import org.apache.poi.ss.usermodel.Workbook;import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;public class DownloadUtils {public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException {response.setContentType("application/octet-stream");returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));			//保存的文件名,必须和页面编码一致,否则乱码response.addHeader("content-disposition","attachment;filename=" + returnName);response.setContentLength(byteArrayOutputStream.size());ServletOutputStream outputstream = response.getOutputStream();	//取得输出流byteArrayOutputStream.writeTo(outputstream);					//写到输出流byteArrayOutputStream.close();									//关闭outputstream.flush();											//刷数据}
}

（3）export方法

    /*** 当月人事报表导出*  参数：*      年月-月（2018-02%）*/@RequestMapping(value = "/export/{month}", method = RequestMethod.GET)public void export(@PathVariable String month) throws Exception {//1.获取报表数据List<EmployeeReportResult> list = userCompanyPersonalService.findByReport(companyId,month);//2.构造Excel//创建工作簿//SXSSFWorkbook : 百万数据报表//Workbook wb = new XSSFWorkbook();SXSSFWorkbook wb = new SXSSFWorkbook(100); //阈值，内存中的对象数量最大数量//构造sheetSheet sheet = wb.createSheet();//创建行//标题String [] titles = "编号,姓名,手机,最高学历,国家地区,护照号,籍贯,生日,属相,入职时间,离职类型,离职原因,离职时间".split(",");//处理标题Row row = sheet.createRow(0);int titleIndex=0;for (String title : titles) {Cell cell = row.createCell(titleIndex++);cell.setCellValue(title);}int rowIndex = 1;Cell cell=null;for(int i=0;i<10000;i++){for (EmployeeReportResult employeeReportResult : list) {row = sheet.createRow(rowIndex++);// 编号,cell = row.createCell(0);cell.setCellValue(employeeReportResult.getUserId());// 姓名,cell = row.createCell(1);cell.setCellValue(employeeReportResult.getUsername());// 手机,cell = row.createCell(2);cell.setCellValue(employeeReportResult.getMobile());// 最高学历,cell = row.createCell(3);cell.setCellValue(employeeReportResult.getTheHighestDegreeOfEducation());// 国家地区,cell = row.createCell(4);cell.setCellValue(employeeReportResult.getNationalArea());// 护照号,cell = row.createCell(5);cell.setCellValue(employeeReportResult.getPassportNo());// 籍贯,cell = row.createCell(6);cell.setCellValue(employeeReportResult.getNativePlace());// 生日,cell = row.createCell(7);cell.setCellValue(employeeReportResult.getBirthday());// 属相,cell = row.createCell(8);cell.setCellValue(employeeReportResult.getZodiac());// 入职时间,cell = row.createCell(9);cell.setCellValue(employeeReportResult.getTimeOfEntry());// 离职类型,cell = row.createCell(10);cell.setCellValue(employeeReportResult.getTypeOfTurnover());// 离职原因,cell = row.createCell(11);cell.setCellValue(employeeReportResult.getReasonsForLeaving());// 离职时间cell = row.createCell(12);cell.setCellValue(employeeReportResult.getResignationTime());}}//3.完成下载ByteArrayOutputStream os = new ByteArrayOutputStream();wb.write(os);new DownloadUtils().download(os,response,month+"人事报表.xlsx");}

package com.ihrm.employee.service;import com.ihrm.common.utils.IdWorker;
import com.ihrm.domain.employee.UserCompanyPersonal;
import com.ihrm.domain.employee.response.EmployeeReportResult;
import com.ihrm.employee.dao.UserCompanyPersonalDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.List;
import java.util.Map;/*** Created with IDEA* Author:xzengsf* Date:2018/10/19 9:52* Description:*/
@Service
public class UserCompanyPersonalService {@Autowiredprivate UserCompanyPersonalDao userCompanyPersonalDao;public void save(UserCompanyPersonal personalInfo) {userCompanyPersonalDao.save(personalInfo);}public UserCompanyPersonal findById(String userId) {return userCompanyPersonalDao.findByUserId(userId);}public List<EmployeeReportResult> findByReport(String companyId,String month) {return userCompanyPersonalDao.findByReport(companyId,month+"%");}}

package com.ihrm.employee.dao;import com.ihrm.domain.employee.UserCompanyPersonal;
import com.ihrm.domain.employee.response.EmployeeReportResult;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;import java.util.List;
import java.util.Map;/*** 数据访问接口*/
public interface UserCompanyPersonalDao extends JpaRepository<UserCompanyPersonal, String>, JpaSpecificationExecutor<UserCompanyPersonal> {UserCompanyPersonal findByUserId(String userId);@Query(value="select new com.ihrm.domain.employee.response.EmployeeReportResult(a,b) from UserCompanyPersonal a "+  "LEFT JOIN EmployeeResignation b on a.userId=b.userId "+  "where a.companyId=?1 and a.timeOfEntry like?2 or ("+  "b.resignationTime like ?2)")List<EmployeeReportResult> findByReport(String companyId,String month);
}

源代码

前端
链接：https://pan.baidu.com/s/1Lr2SEFoydh9xui6RhFqsEQ
提取码：3kuc
–来自百度网盘超级会员V3的分享
后端
链接：https://pan.baidu.com/s/17GxMDGLxtcM6OJFTdeV_iQ
提取码：q3si
–来自百度网盘超级会员V3的分享
数据库
链接：https://pan.baidu.com/s/1HmSU7IucvApuN_ywNI_NRQ
提取码：4gso
–来自百度网盘超级会员V3的分享