- public void saveWageEmployeeInfo() throws FileNotFoundException {
- //wage_employee_info 先删除 M1,M2
- String sqlM1M2 = "delete wage.wage_employee_info where wage_level in('M1','M2')";
- int sqlM1M2Count = wageJobMapper.delSql(sqlM1M2);
- System.out.println("删除 wage.wage_employee_info 包含 M1M2 的数据"+sqlM1M2Count);
- String time = getMonday();
- // String filename = "D:/ 微信文件 / WeChat Files/Huangpeideng2015/FileStorage/File/2019-12 / 人员明细表 +"+time+".XLSX";
- String filename = "D:\\ 微信文件 \\WeChat Files\\Huangpeideng2015\\FileStorage\\File\\2019-12\\12.13\\12.13\\ 人员明细表 + 20191216.XLSX";
- FileInputStream in = new FileInputStream(filename);
- Workbook wk = StreamingReader.builder()
- .rowCacheSize(10) // 缓存到内存中的行数, 默认是 10
- .bufferSize(4096) // 读取资源时, 缓存到内存的字节大小, 默认是 1024
- .open(in); // 打开资
- String sqlEmployeeInfo = "insert into wage.wage_employee_info(employee_nbr,hrms_id,employee_group,employee_subgroup," +
- "wage_range,center_code,major_department_code,major_department,small_department," +
- "functional_group,[group],position_description,wage_level) values";
- String delEmployeeInfo = "delete wage.wage_employee_info\n" +
- "where id in(select min(id)\n" +
- "from wage.wage_employee_info\n" +
- "group by hrms_id\n" +
- "having count(1)>1) or hrms_id = 0 or hrms_id is null";
- String sqlPT = "insert into wage.employee_pt_st_info(hrms_id,wage_level) values";
- String delPT = "delete wage.employee_pt_st_info\n" +
- "where id in(select min(id)\n" +
- "from wage.employee_pt_st_info\n" +
- "group by hrms_id\n" +
- "having count(1)>1) or hrms_id = 0 or hrms_id is null";
- // 遍历工作表
- Sheet sheet = wk.getSheetAt(0);
- int i = 0;
- List<String> containIPT = new ArrayList<>();
- containIPT.add("IH");
- containIPT.add("PH");
- containIPT.add("TH");
- List<String> contain = Arrays.asList("M","M1","M2");
- for (Row row:sheet) {
- if(i>0){
- List<String> list = new ArrayList<>();
- for(int j=0;j<14;j++){
- if (j != 2){// 排除门店
- list.add(row.getCell(j).getStringCellValue());
- }
- }
- if (row.getCell(3).getStringCellValue().equals("学生")&&!contain.contains(row.getCell(13).getStringCellValue())){
- list.set(12,"IH");
- }
- if (row.getCell(3).getStringCellValue().equals("临时用工")&&!contain.contains(row.getCell(13).getStringCellValue())){
- list.set(12,"TH");
- }
- if (row.getCell(11).getStringCellValue()==null||row.getCell(11).getStringCellValue().equals("")){
- list.set(10,row.getCell(10).getStringCellValue());
- }
- sqlEmployeeInfo += String.format("\n ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s'),",list.toArray());
- String wage_level = list.get(12);
- if (containIPT.contains(wage_level)){
- sqlPT += String.format("\n ('%s','%s'),",list.get(1),wage_level);
- }
- if (i%1000==0){
- sqlEmployeeInfo = save2(sqlEmployeeInfo);
- sqlPT = save(sqlPT);
- }
- }
- i++;
- }
- save2(sqlEmployeeInfo);
- save(sqlPT);
- int delCount = wageJobMapper.delSql(delEmployeeInfo);
- System.out.println("EmployeeInfo 去重"+delCount);
- int delCount2 = wageJobMapper.delSql(delPT);
- System.out.println("employee_pt_st_info 去重"+delCount2);
- System.out.println("操作完成..........");
- }
- private String save(String sql){
- int num = wageJobMapper.addSql(sql.substring(0,sql.length()-1));
- System.out.println(String.format("成功保存 %s 条信息",num));
- sql = "insert into wage.employee_pt_st_info(hrms_id,wage_level) values";
- return sql;
- }
- private String save2(String sql) {
- int num = wageJobMapper.addSql(sql.substring(0, sql.length() - 1));
- System.out.println(String.format("成功保存 %s 条信息", num));
- sql = "insert into wage.wage_employee_info(employee_nbr,hrms_id,employee_group,employee_subgroup," +
- "wage_range,center_code,major_department_code,major_department,small_department," +
- "functional_group,[group],position_description,wage_level) values";
- return sql;
- }
来源: http://www.bubuko.com/infodetail-3346171.html