if (r.getCellType()==Cell.CELL_TYPE_NUMERIC){
if(HSSFDateUtil.isCellDateFormatted(r)){
//用于转化为日期格式
Date d = r.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM");
value = formater.format(d);
}else {
BigDecimal bigDecimal = new BigDecimal(r.getNumericCellValue());
value = bigDecimal.toString();
}
}else if (r.getCellType()==Cell.CELL_TYPE_STRING){
value=StringUtil.replaceBlank(r.getStringCellValue());
}else if(r.getCellType()==Cell.CELL_TYPE_BLANK){
}
、、、、、、、、、、、、、、、、、、、、、、、、、
package com.jianwu.manager.impl;
import com.google.common.base.Strings;
import com.google.common.collect.Lists;
import com.jianwu.constant.Constant;
import com.jianwu.dao.*;
import com.jianwu.domain.*;
import com.jianwu.manager.QiNiuManager;
import com.jianwu.manager.SalaryLeadManager;
import com.jianwu.rongClound.util.HttpUtil;
import com.jianwu.util.StringUtil;
import com.jianwu.util.excel.ExcelUtil;
import com.jianwu.util.excel.ExcelValidate;
import com.jianwu.util.excel.PoiUtil;
import com.jianwu.xct.domain.LoginMember;
import com.nodewind.core.web.cookyjar.Cookyjar;
import com.nodewind.service.result.Result;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Pattern;
/**
* @Author lijin <tookbra@outlook.com>
* @Date 2017/9/22 12:07
* @Version
*/
@Service
@Transactional(readOnly = false)
public class SalaryLeadManagerImpl implements SalaryLeadManager {
private static final Logger logger = LoggerFactory.getLogger(SalaryLeadManagerImpl.class);
@Autowired
QiNiuManager qiNiuManager;
@Autowired
PaySalaryDao paySalaryDao;
@Autowired
PaySalaryTemplateDao paySalaryTemplateDao;
@Autowired
PaySalaryHomepageDao paySalaryHomepageDao;
@Autowired
PaySalaryLeadRecordDao paySalaryLeadRecordDao;
@Autowired
PayUserDao payUserDao;
/* @Override
public Result readExcel(MultipartFile file, String suffix) {
InputStream inputStream = null;
try {
inputStream = file.getInputStream();
} catch (IOException e) {
System.out.println("文件提取错误:{}" + e);
return Result.error("文件提取错误");
}
return this.uploadEmployee(inputStream, suffix);
}*/
public Result uploadEmployee(InputStream inputStream, String suffix) {
if (suffix.equals(Constant.OFFICE_EXCEL_XLS)) {
try {
POIFSFileSystem fs = new POIFSFileSystem(inputStream);
HSSFWorkbook wb = new HSSFWorkbook(fs, true);
return this.preImportEmployee(wb);
} catch (IOException e) {
System.out.println("文件提取错误:{}" + e);
return Result.error("文件解析错误");
}
} else if (suffix.equals(Constant.OFFICE_EXCEL_XLSX)) {
System.out.println("文件提取错误:{}" + suffix);
return Result.error("文件解析错误");
}
System.out.println("文件解析错误");
return Result.error("文件解析错误");
}
private Result<ExcelResult1> preImportEmployee(HSSFWorkbook wb) {
// Pattern p = Pattern.compile("^((13[0-9])|(15[^4,\\D])|(18[0,5-9]))\\d{8}$");
// String pattern = "\\d+";
String pattern = "^1\\d{10}";//手机号以1开头11位长度
ExcelResult1 excelResult = new ExcelResult1();
int sheets = wb.getNumberOfSheets();
if (sheets != 1) {
return Result.error("Excel模板错误,请下载最新的Excel模板文件后按格式增加员工信息1");
}
//Sheet sheet = wb.getSheet(tempName);
Sheet sheet = wb.getSheetAt(0);
if (sheet == null) {
return Result.error("Excel模板错误,请下载最新的Excel模板文件后按格式增加员工信息2");
}
if(sheet.getLastRowNum() < 1) {
return Result.error("Excel表格中没有数据");
}
ExcelInfo1 excelInfo = null;
List<ExcelInfo1> excelInfos = new ArrayList<>();
List<ExcelInfo1> excelErrorInfos = new ArrayList<>();
List titleList = Lists.newArrayList();
//读取表头
Row titleRow = sheet.getRow(0);
//头部放入list
int len = sheet.getLastRowNum();
List<UploadSalary> uploadSalaries=new ArrayList<UploadSalary>();
for (int i = 1; i <=len; i++) {
UploadSalary uploadSalary = new UploadSalary();
uploadSalaries.add(uploadSalary);
Row row = sheet.getRow(i);
for (Cell r : row) {
String title = StringUtil.replaceBlank(titleRow.getCell(r.getColumnIndex()).getStringCellValue());
String value="";
if (r.getCellType()==Cell.CELL_TYPE_NUMERIC){
if(HSSFDateUtil.isCellDateFormatted(r)){
//用于转化为日期格式
Date d = r.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM");
value = formater.format(d);
}else {
BigDecimal bigDecimal = new BigDecimal(r.getNumericCellValue());
value = bigDecimal.toString();
}
}else if (r.getCellType()==Cell.CELL_TYPE_STRING){
value=StringUtil.replaceBlank(r.getStringCellValue());
}else if(r.getCellType()==Cell.CELL_TYPE_BLANK){
}
if ("姓名".equals(title)) {
uploadSalary.setName(value);
} else if ("手机号".equals(title)) {
if(Pattern.matches(pattern, value)){
uploadSalary.setPhone(value);
}else{
return Result.error("存在格式不正确的手机号码,请修改后重新导入!");
}
} else if ("月份".equals(title)) {
uploadSalary.setMonth(value);
}else{
PaySalaryItem paySalaryItem=new PaySalaryItem();
paySalaryItem.setItemName(title);
Double v=0d;
try {
v = Double.valueOf(value);
}catch (Exception e){
e.printStackTrace();
}
paySalaryItem.setSalary(BigDecimal.valueOf(v));
uploadSalary.addPaySalaryItem(paySalaryItem);
}
}
System.out.println(uploadSalaries);
}
Cell cell = null;
//验证必填字段
try {
int errorCount = 0;
for (UploadSalary uploadSalary : uploadSalaries) {
if (errorCount > 50) {
break;
}
excelInfo = new ExcelInfo1();
String errorMsg = ExcelValidate.valid(uploadSalary);
uploadSalary.setErrorMsg(errorMsg);
excelInfo.setEmpInfo(uploadSalaries);
if (!Strings.isNullOrEmpty(errorMsg)) {
excelInfo.setError(true);
excelInfo.setErrorMsg(errorMsg);
excelErrorInfos.add(excelInfo);
} else {
excelInfo.setError(false);
excelInfos.add(excelInfo);
}
}
} catch (Exception e) {
System.out.println("[preImportEmployee]: " + e);
e.printStackTrace();
return Result.error("");
}
excelResult.setExcelInfos(excelInfos);
excelResult.setExcelErrorInfos(excelErrorInfos);
excelResult.setTotal(uploadSalaries.size());
excelResult.setSheetName(sheet.getSheetName());
return Result.success(excelResult);
}
@Override
@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
public Result importEmployee(MultipartFile f, LoginMember loginMember,String tempName) throws Exception {
PayUser payUser = payUserDao.selectByPrimaryKey(loginMember.getId().intValue());
if(payUser.getStatus() == 0){
return Result.error("平台管理员不能导入!");
}
//todo 选择公司
Integer companyId = null;
if(StringUtils.isNotBlank(payUser.getCompanyIds())){
companyId = Integer.valueOf(payUser.getCompanyIds().split(",")[0]);
}
PaySalaryHomepage paySalaryHomepage = paySalaryHomepageDao.queryByName(tempName, companyId);
if(null!=paySalaryHomepage){
// if(paySalaryHomepage.getOperId()==loginMember.getId().intValue()){
if(companyId.toString().equals(paySalaryHomepage.getCompanyIds()) && tempName.equals(paySalaryHomepage.getFilename())){
return Result.error("已经有此文件");
}else {
Result<ExcelResult1> result = this.uploadEmployee(f.getInputStream(), ".xls");
int size_ = 0;
if (result.isSuccess()) {
ExcelResult1 excelResult = result.getData();
if (excelResult.getExcelErrorInfos().size() > 0) {
return Result.error("错误");
} else {
Long templateId = paySalaryTemplateDao.queryId(excelResult.getSheetName(), companyId);
if(null==templateId){
return Result.error("此模板已经被删除!");
}
List<ExcelInfo1> excelInfoList = excelResult.getExcelInfos();
size_ = excelInfoList.size();
PaySalaryLeadRecord paySalaryLeadRecord1 = new PaySalaryLeadRecord();
String time = null;
for (ExcelInfo1 excelInfo : excelInfoList) {
List<UploadSalary> uploadSalary = excelInfo.getEmpInfo();
time = uploadSalary.get(0).getMonth();
for (UploadSalary uploadSalary2:uploadSalary){
if(!uploadSalary2.getMonth().equals(time)){
return Result.error("工资单人员月份不一样!");
}
}
for (UploadSalary uploadSalary1:uploadSalary){
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM");//小写的mm表示的是分钟
PaySalary paySalary = new PaySalary();
paySalary.setPhone(uploadSalary1.getPhone());
paySalary.setName(uploadSalary1.getName());
paySalary.setMonth(sdf.parse(uploadSalary1.getMonth()));
paySalary.setOperId(loginMember.getId().intValue());
paySalary.setTemplateId(templateId.intValue());
SimpleDateFormat sdf1=new SimpleDateFormat("yyyy-MM-dd");
Date date = sdf1.parse(uploadSalary1.getMonth()+"-01");
System.out.println("==============>月份:"+date);
List<String> strings=new ArrayList<>();
String salarys = "";
for(PaySalaryItem paySalaryItem:uploadSalary1.getItemName()){
strings.add(paySalaryItem.getItemName()+":"+paySalaryItem.getSalary());
}
salarys= StringUtils.join(strings,",");
// Integer id = paySalaryDao.queryByName(uploadSalary1.getPhone(),sdf1.parse(uploadSalary1.getMonth()+"-01"));
if(payUser.getStatus() != 0){
paySalary.setCompanyIds(companyId.toString());
}
if(StringUtils.isNotEmpty(salarys)){
paySalary.setSalarytext(salarys);
paySalaryDao.insertSelective(paySalary);
//paySalaryDao.insertSalary(salarys,id);
}
Integer id = paySalaryDao.queryByName(uploadSalary1.getPhone(),sdf1.parse(uploadSalary1.getMonth()+"-01"));
paySalaryLeadRecord1.setEmpId(id);
paySalaryLeadRecord1.setFilename(tempName);
if(payUser.getStatus() != 0){
paySalaryLeadRecord1.setCompanyIds(companyId.toString());
}
paySalaryLeadRecordDao.insertSelective(paySalaryLeadRecord1);
//leadCount=leadCount++;
}
break;
}
PaySalaryHomepage paySalaryHomepage1 = new PaySalaryHomepage();
//Long leadCount = paySalaryDao.queryCountById(templateId);
Long leadCount = paySalaryLeadRecordDao.queryLikeSum(tempName,"","");
if(null!=leadCount){
paySalaryHomepage1.setLeadCount(leadCount);
}else {
paySalaryHomepage1.setLeadCount(0L);
}
paySalaryHomepage1.setFilename(tempName);
paySalaryHomepage1.setTemplateId(templateId.intValue());
paySalaryHomepage1.setOperId(loginMember.getId().intValue());
if(payUser.getStatus() != 0){
paySalaryHomepage1.setCompanyIds(companyId.toString());
}
paySalaryHomepageDao.insertSelective(paySalaryHomepage1);//导入工资单后添加一条记录
}
}
return result;
}
}else{
Result<ExcelResult1> result = this.uploadEmployee(f.getInputStream(), ".xls");
int size_ = 0;
if (result.isSuccess()) {
ExcelResult1 excelResult = result.getData();
if (excelResult.getExcelErrorInfos().size() > 0) {
return Result.error("错误");
} else {
Long templateId = paySalaryTemplateDao.queryId(excelResult.getSheetName(), companyId);
if(null==templateId){
return Result.error("此模板已经被删除!");
}
List<ExcelInfo1> excelInfoList = excelResult.getExcelInfos();
size_ = excelInfoList.size();
PaySalaryLeadRecord paySalaryLeadRecord1 = new PaySalaryLeadRecord();
String time = null;
for (ExcelInfo1 excelInfo : excelInfoList) {
List<UploadSalary> uploadSalary = excelInfo.getEmpInfo();
time = uploadSalary.get(0).getMonth();
for (UploadSalary uploadSalary2:uploadSalary){
if(time == null || uploadSalary2.getMonth() == null){
return Result.error("工资单月份不能为空且格式为:yyyy-MM,如:2018-01");
}
if(!uploadSalary2.getMonth().equals(time)){
return Result.error("工资单人员月份不一样!");
}
}
for (UploadSalary uploadSalary1:uploadSalary){
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM");//小写的mm表示的是分钟
PaySalary paySalary = new PaySalary();
paySalary.setPhone(uploadSalary1.getPhone());
paySalary.setName(uploadSalary1.getName());
paySalary.setMonth(sdf.parse(uploadSalary1.getMonth()));
paySalary.setOperId(loginMember.getId().intValue());
paySalary.setTemplateId(templateId.intValue());
SimpleDateFormat sdf1=new SimpleDateFormat("yyyy-MM-dd");
Date date = sdf1.parse(uploadSalary1.getMonth()+"-01");
System.out.println("==============>月份:"+date);
List<String> strings=new ArrayList<>();
String salarys = "";
for(PaySalaryItem paySalaryItem:uploadSalary1.getItemName()){
strings.add(paySalaryItem.getItemName()+":"+paySalaryItem.getSalary());
}
salarys= StringUtils.join(strings,",");
// Integer id = paySalaryDao.queryByName(uploadSalary1.getPhone(),sdf1.parse(uploadSalary1.getMonth()+"-01"));
if(payUser.getStatus() != 0){
paySalary.setCompanyIds(companyId.toString());
}
if(StringUtils.isNotEmpty(salarys)){
paySalary.setSalarytext(salarys);
paySalaryDao.insertSelective(paySalary);
//paySalaryDao.insertSalary(salarys,id);
}
Integer id = paySalaryDao.queryByName(uploadSalary1.getPhone(),sdf1.parse(uploadSalary1.getMonth()+"-01"));
paySalaryLeadRecord1.setEmpId(id);
paySalaryLeadRecord1.setFilename(tempName);
if(payUser.getStatus() != 0){
paySalaryLeadRecord1.setCompanyIds(companyId.toString());
}
paySalaryLeadRecordDao.insertSelective(paySalaryLeadRecord1);
//leadCount=leadCount++;
}
break;
}
PaySalaryHomepage paySalaryHomepage1 = new PaySalaryHomepage();
//Long leadCount = paySalaryDao.queryCountById(templateId);
Long leadCount = paySalaryLeadRecordDao.queryLikeSum(tempName,"","");
if(null!=leadCount){
paySalaryHomepage1.setLeadCount(leadCount);
}else {
paySalaryHomepage1.setLeadCount(0L);
}
paySalaryHomepage1.setFilename(tempName);
paySalaryHomepage1.setTemplateId(templateId.intValue());
paySalaryHomepage1.setOperId(loginMember.getId().intValue());
if(payUser.getStatus() != 0){
paySalaryHomepage1.setCompanyIds(companyId.toString());
}
paySalaryHomepageDao.insertSelective(paySalaryHomepage1);//导入工资单后添加一条记录
}
}
return result;
}
}
}