加载Excel
package cn.itcast.poi.test; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.IOException; /** * 读取excel并解析 * sheet.getLastRowNum() : 最后一行的索引 * row.getLastCellNum() ： 最后一个单元格的号码 */ public class PoiTest05 { public static void main(String[] args) throws Exception { //1.根据Excel文件创建工作簿 Workbook wb = new XSSFWorkbook("E:\\excel\\poi\\demo.xlsx"); //2.获取Sheet Sheet sheet = wb.getSheetAt(0);//参数：索引 //3.获取Sheet中的每一行，和每一个单元格 for (int rowNum = 0; rowNum<= sheet.getLastRowNum() ;rowNum ++) { Row row = sheet.getRow(rowNum);//根据索引获取每一个行 StringBuilder sb = new StringBuilder(); for(int cellNum=2;cellNum< row.getLastCellNum(); cellNum ++) { //根据索引获取每一个单元格 Cell cell = row.getCell(cellNum); //获取每一个单元格的内容 Object value = getCellValue(cell); sb.append(value).append("-"); } System.out.println(sb.toString()); } } //根据单元格数据类型获取数据 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; } }
骚戴理解：这里要注意Excel里面单元格有很多类型，所以需要判断是什么类型然后对应Java代码，其中日期和数字类型都是“NUMERIC”类型，所以需要调用DateUtil.isCellDateFormatted（）去判断这个单元格的类型是不是日期类型，如果不是那就是数字类型，上面的getCellValue方法是可以提取处理作为一个工具类的
POI报表导入
需求分析
实现批量导入员工功能，页面端上传excel表格，服务端解析表格获取数据，批量新增用户
员工导入
搭建环境
父模块pom文件添加依赖
<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上传
- 用户实体类配置构造方法
//objs数据位置和excel上传位置一致。 public User(Object []objs,String companyId,String companyName) { //用户名 手机号 工号 聘用 形式 入职 时间 部门编码 this.username = values[1].toString(); this.mobile = values[2].toString(); this.createTime = new Date(); //默认手机号excel读取为字符串会存在科学记数法问题，转化处理 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 }
骚戴理解：这里代码设计的就有问题，这个Excel里面搞了一个部门编码，然后在User里面压根就没有部门编码这个属性，所以就把部门编码的属性放到了departmentId里面，才会有部门编码 != 部门id，我一开始都被绕晕了，这样搞应该是为了用到远程调用，为了调用ihrm_company微服务来根据部门编码和公司id去查询部门id，这样设计太牵强了！
this.workNumber = new DecimalFormat("#").format(values[3]).toString(); //默认手机号excel读取为字符串会存在科学记数法问题，转化处理。
DecimalFormat("#") 在这里创建了一个 DecimalFormat 的对象，其中 # 是特殊字符，表示数字的占位符。
.format(values[3]) 表示将values[3]这个数字转换为字符串，并按照构造函数中制定的格式进行格式化。
举个例子：如果 objs[2]=1234.5678 ，那么 new DecimalFormat("#").format(objs[2]) 的返回结果就是一个字符串 "1235"，因为格式中只有整数部分的占位符 #
- 在系统微服务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; }
调用企业微服务获取部门数据
- 在Ihrm_system模块创建com.ihrm.system.client包，包下创建接口
@FeignClient(value = "ihrm-company") package com.ihrm.system.client; import com.ihrm.common.entity.Result; import com.ihrm.domain.company.Department; import org.springframework.cloud.openfeign.FeignClient; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RequestParam; /** * 声明接口，通过feign调用其他微服务 */ //声明调用的微服务名称 @FeignClient("ihrm-company") public interface DepartmentFeignClient { /** * 调用微服务的接口 */ @RequestMapping(value="/company/department/{id}",method = RequestMethod.GET) public Result findById(@PathVariable(value="id") String id); //远程调用企业微服务，根据企业编码code和企业名称获取企业信息 @RequestMapping(value="/company/department/search",method = RequestMethod.POST) public Department findByCode(@RequestParam(value="code") String code, @RequestParam(value="companyId") String companyId); }
- 修改UserService，注入DepartmentFeignClient
@Autowired private DepartmentFeignClient departmentFeignClient;
保存全部用户
UserService中添加保存全部的方法
@Transactional public void save(List<User> users) throws Exception { for (User user : users) { //配置密码 user.setPassword(new Md5Hash("123456",user.getMobile(),3).toString()); //配置id user.setId(idWorker.nextId()+""); //其他基本属性 user.setInServiceStatus(1); user.setEnableState(1); user.setLevel("user"); //获取部门信息 Department dept = departmentFeignClient.findById(user.getDepartmentId(),user.getCompanyId()); if(dept != null) { user.setDepartmentId(dept.getId()); user.setDepartmentName(dept.getName()); } userDao.save(user); } }
POI报表导出
需求分析
完成当月人事报表的导出：包含当月入职员工信息，离职员工信息
骚戴理解：也就是要把em_user_company_personal（员工详细信息表）和em_resignation（离职申请表）进行左外连接
人事报表导出
步骤分析
- 构造Excel表格数据创建工作簿
- 创建sheet 创建行对象
- 创建单元格对象
- 填充数据，设置样式下载
代码实现
- EmployeeReportResult
package com.ihrm.domain.employee.response; import com.ihrm.domain.employee.EmployeeResignation; import com.ihrm.domain.employee.UserCompanyPersonal; import lombok.Getter; import lombok.NoArgsConstructor; import lombok.Setter; import lombok.ToString; import org.springframework.beans.BeanUtils; /** * 导出报表构造的人事报表对象 */ @Getter @Setter @NoArgsConstructor @ToString public class EmployeeReportResult { private String userId; private String username; private String departmentName; private String mobile; private String timeOfEntry; 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; /** * 离职时间 */ private String resignationTime; /** * 离职类型 */ private String typeOfTurnover; /** * 申请离职原因 */ private String reasonsForLeaving; public EmployeeReportResult(UserCompanyPersonal personal, EmployeeResignation resignation) { BeanUtils.copyProperties(personal,this); if(resignation != null) { BeanUtils.copyProperties(resignation,this); } } }
- 配置EmployeeController
/** * 导出人事报表 * @param month * @throws Exception */ @RequestMapping(value = "/export/{month}", method = RequestMethod.GET) public void export(@PathVariable(name = "month") String month) throws Exception { //1.构造数据 List<EmployeeReportResult> list = userCompanyPersonalService.findByReport(companyId,month+"%"); //2.创建工作簿 XSSFWorkbook workbook = new XSSFWorkbook(); //3.构造sheet String[] titles = {"编号", "姓名", "手机","最高学历", "国家地区", "护照号", "籍贯", "生日", "属相","入职时间","离职类型","离职原因","离职时间"}; Sheet sheet = workbook.createSheet(); Row row = sheet.createRow(0); AtomicInteger headersAi = new AtomicInteger(); for (String title : titles) { Cell cell = row.createCell(headersAi.getAndIncrement()); cell.setCellValue(title); } AtomicInteger datasAi = new AtomicInteger(1); Cell cell = null; for (EmployeeReportResult report : list) { Row dataRow = sheet.createRow(datasAi.getAndIncrement()); //编号 cell = dataRow.createCell(0); cell.setCellValue(report.getUserId()); //姓名 cell = dataRow.createCell(1); cell.setCellValue(report.getUsername()); //手机 cell = dataRow.createCell(2); cell.setCellValue(report.getMobile()); //最高学历 cell = dataRow.createCell(3); cell.setCellValue(report.getTheHighestDegreeOfEducation()); //国家地区 cell = dataRow.createCell(4); cell.setCellValue(report.getNationalArea()); //护照号 cell = dataRow.createCell(5); cell.setCellValue(report.getPassportNo()); //籍贯 cell = dataRow.createCell(6); cell.setCellValue(report.getNativePlace()); //生日 cell = dataRow.createCell(7); cell.setCellValue(report.getBirthday()); //属相 cell = dataRow.createCell(8); cell.setCellValue(report.getZodiac()); //入职时间 cell = dataRow.createCell(9); cell.setCellValue(report.getTimeOfEntry()); //离职类型 cell = dataRow.createCell(10); cell.setCellValue(report.getTypeOfTurnover()); //离职原因 cell = dataRow.createCell(11); cell.setCellValue(report.getReasonsForLeaving()); //离职时间 cell = dataRow.createCell(12); cell.setCellValue(report.getResignationTime()); } String fileName = URLEncoder.encode(month+"人员信息.xlsx", "UTF-8"); response.setContentType("application/octet-stream"); response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes("ISO8859-1"))); response.setHeader("filename", fileName); workbook.write(response.getOutputStream()); }
骚戴理解：AtomicInteger datasAi = new AtomicInteger(1);和datasAi.getAndIncrement()这是Java中使用AtomicInteger类来实现原子操作的例子。
AtomicInteger datasAi = new AtomicInteger(1);创建了一个对象datasAi，它是AtomicInteger类型的并初始化为1。因此，如果多个线程同时访问这个对象进行操作，则可以保证对其进行操作时是线程安全的。
datasAi.getAndIncrement() 可以理解为"获取并增加"，它的作用是先获取当前值（即值为1），然后将值自增1，返回的结果是二者相加后的值。这个操作是原子性的，也就是说，在同时有多个线程操作该对象时，只有一个线程能够成功执行它，其他线程则被阻塞等待。
export方法里的response是父控制器BaseController里的定义的
package com.ihrm.common.controller; import com.ihrm.domain.system.response.ProfileResult; import io.jsonwebtoken.Claims; import org.apache.shiro.SecurityUtils; import org.apache.shiro.subject.PrincipalCollection; import org.apache.shiro.subject.Subject; import org.springframework.web.bind.annotation.ModelAttribute; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public class BaseController { protected HttpServletRequest request; protected HttpServletResponse response; protected String companyId; private String userId; protected String companyName; protected Claims claims; //使用jwt方式获取 // @ModelAttribute // public void setResAnReq(HttpServletRequest request,HttpServletResponse response) { // this.request = request; // this.response = response; // // Object obj = request.getAttribute("user_claims"); // // if(obj != null) { // this.claims = (Claims) obj; // this.companyId = (String)claims.get("companyId"); // this.companyName = (String)claims.get("companyName"); // } // } //使用shiro获取 @ModelAttribute public void setResAnReq(HttpServletRequest request,HttpServletResponse response) { this.request = request; this.response = response; //获取session中的安全数据 Subject subject = SecurityUtils.getSubject(); //1.subject获取所有的安全数据集合 PrincipalCollection principals = subject.getPrincipals(); if(principals != null && !principals.isEmpty()){ //2.获取安全数据 ProfileResult result = (ProfileResult)principals.getPrimaryPrincipal(); this.companyId = result.getCompanyId(); this.companyName = result.getCompany(); this.userId = result.getUserId(); System.out.println(companyId); } } }
- 添加service
//根据企业id和年月查询 public List<EmployeeReportResult> findByReport(String companyId, String month) { return userCompanyPersonalDao.findByReport(companyId,month); }
- dao层实现
@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);
骚戴理解：这是一个使用JPA自定义查询语句的例子，可以通过该语句查询符合条件的员工信息。
该查询语句包含了两个数据表 UserCompanyPersonal 和 EmployeeResignation，并依据它们中的特定字段获取数据。其中：
new com.ihrm.domain.employee.response.EmployeeReportResult() 指定查询结果返回的对象为 EmployeeReportResult 对象。
LEFT JOIN 表示使用左连接查询，根据a.userId=b.userId实现两张表的关联查询。
ON a.userId=b.userId 为JOIN语句中的ON条件，表示UserCompanyPersonal和EmployeeResignation表中userId字段相等时关联查询两个表的员工信息。
WHERE 子句中的 a.companyId = ?1 条件表示筛选companyId等于第一个参数的员工信息。
AND 连接符号指定 后续筛选条件需要满足该条件，后面跟着的 a.timeOfEntry LIKE ?2 OR (b.resignationTime LIKE ?2) 是一个复杂的逻辑运算符，经过或与运算判断timeOfEntry或resignationTime是否匹配，具体如下：
a.timeOfEntry LIKE ?2 表示筛选timeOfEntry字段类似于第二个参数的查询值（即时间模糊匹配），满足该条件之一即可。
OR 表示或逻辑运算符。
(b.resignationTime LIKE ?2) 表示筛选resignationTime字段类似于第二个参数的查询值。
因此，该自定义查询语句最终返回符合条件的实体集合，并且每个EmployeeReportResult对象包含UserCompanyPersonal和EmployeeResignation表中领取的一行数据。
SELECT * FROM em_user_company_personal p LEFT JOIN em_resignation r ON p.user_id = r.user_id WHERE p.company_id = 1 AND p.time_of_entry LIKE '2023-01%' OR r.resignation_time LIKE '2023-01%'
骚戴理解：这里我更喜欢转成sql语句更好理解，看着更舒服，需要注意的是2023-01%能够查出90多条数据，但是2023-1%只能查出3条数据，我一开始以为这两种写法查出来的结果是一样的！
