员工导入
实现批量导入员工功能,页面端上传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.获取Sheet
Sheet 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);
}
保存全部用户
/**
* 批量保存用户
*/
@Transactional
public void saveAll(List<User> list ,String companyId,String companyName){
for (User user : list) {
//默认密码
user.setPassword(new Md5Hash("123456",user.getMobile(),3).toString());
//id
user.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
*/
@Id
private 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
*/
@Id
private 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); //阈值,内存中的对象数量最大数量
//构造sheet
Sheet 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 {
@Autowired
private 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的分享