public List<Salary> parseXlsFile(String fileName, Integer opTime,
InputStream ioFile) {
//获得文件结尾的标志。
String suffix=fileName.substring(fileName.indexOf(".")+1).toLowerCase();
List<Salary> salaryList=null;
if("xlsx".equals(suffix)){
salaryList=parseXlsxFile(ioFile,opTime);
}else if("xls".equals(suffix)){
salaryList=parseXlsFile(ioFile,opTime);
}else{
return null;
}
return salaryList;
}
//去获取以.xlsx结尾的excel表格文件
private List<Salary> parseXlsxFile(InputStream ioFile,Integer opTime){
List<Salary>salaryList=null;
XSSFSheet sheet;
try {
XSSFWorkbook xssf = new XSSFWorkbook(ioFile);
salaryList=new ArrayList<Salary>();
sheet=xssf.getSheetAt(0);
int totalRows=sheet.getLastRowNum();
Map<Long, String> userIdMap=queryUserDepartment();
String deptName="";
for(int i=2;i<totalRows-1;i++){
XSSFRow row = sheet.getRow(i);
Salary salary = getSalaryByRow(row,opTime);
if(salary!=null){
if(salary.getUserId()==0l||"".equals(salary.getUserName().trim())){
continue;
}
deptName=userIdMap.get(salary.getUserId());
if(deptName==null){
deptName=" ";
}
salary.setDepartmentName(deptName);
salaryList.add(salary);
}
}
} catch (IOException e) {
logger.error(e);
}
return salaryList;
}
//获取以xls结尾的excel表格文件
private List<Salary> parseXlsFile(InputStream ioFile,Integer opTime){
List<Salary>salaryList=null;
HSSFSheet sheet;
try {
HSSFWorkbook xssf = new HSSFWorkbook(ioFile);
salaryList=new ArrayList<Salary>();
sheet=xssf.getSheetAt(0);
int totalRows=sheet.getLastRowNum();
Map<Long, String> userIdMap=queryUserDepartment();
String deptName="";
for(int i=2;i<totalRows-1;i++){
HSSFRow row = sheet.getRow(i);
Salary salary = getSalaryByRow(row,opTime);
if(salary!=null){
if(salary.getUserId()==0l||"".equals(salary.getUserName().trim())){
continue;
}
deptName=userIdMap.get(salary.getUserId());
if(deptName==null){
deptName=" ";
}
salary.setDepartmentName(deptName);
salaryList.add(salary);
}
}
} catch (IOException e) {
logger.error(e);
}
return salaryList;
}
//更新数据库中excel表格的一行数据
public boolean updateSalarySummaryList(List<Salary> salaryList){
if (salaryList != null && salaryList.size() > 0) {
Session session=getSession();
for(Salary tempsSalary:salaryList){
session.update(tempsSalary);;
}
session.flush();
}
return true;
}
//保存excel表格中的一行数据
public boolean saveSalarySummaryList(List<Salary> salaryList){
if (salaryList != null && salaryList.size() > 0) {
Session session=getSession();
Salary tempSalary=null;
for (int i = 0; i < salaryList.size(); i++) {
tempSalary = salaryList.get(i);
session.save(tempSalary);
}
session.flush();
}
return true;
}
//导入excel表格
@RequestMapping(value = "/importSalary/{month}")
@ResponseBody
public Map<String, Boolean> importSalary(HttpServletRequest request,@PathVariable("month") Long month) throws Exception {
Boolean bool = false;
UploadFile upload = FileUpDownUtils.getUploadFile(request, "importFile");
Integer opTime = Integer.parseInt(request.getParameter("month"));
String fileName = upload.getFileName();
InputStream fis = new FileInputStream(upload.getFile());
List<Salary> xlsSalaryList = salaryManager.parseXlsFile(fileName,opTime, fis/* multipartFile.getInputStream() */);
if (xlsSalaryList != null && xlsSalaryList.size() > 0) {
List<Salary> dbList = salaryManager.getMonthSalaryByMonth(opTime);
List<Salary> addList = null;
List<Salary> updateList = null;
if (dbList != null && dbList.size() > 0) {
addList = new ArrayList<Salary>();
updateList = new ArrayList<Salary>();
Map<Long, Salary> dbSalaryMap = new HashMap<Long, Salary>();
for (Salary tempSal : dbList) {
dbSalaryMap.put(tempSal.getUserId(), tempSal);
}
Salary tempDbBean = null;
Double sumAmount = 0.0;
for (Salary tempBean : xlsSalaryList) {
sumAmount = sumAmount+tempBean.getAmount(); // 得到全部的金额
tempDbBean = dbSalaryMap.get(tempBean.getUserId());
if (tempDbBean != null) {
salaryManager.updateSalaryBean(tempDbBean, tempBean);
updateList.add(tempDbBean);
} else {
addList.add(tempBean);
}
}
/* 保存总金额 */
SalarySummary s = new SalarySummary();
s.setAmount(sumAmount);
s.setOpTime(201512);
s.setWorkDays(18);
salarySummaryManager.save(s);
if (updateList.size() > 0) {
bool = salaryManager.updateSalarySummaryList(updateList);
}
if (addList.size() > 0) {
bool = salaryManager.saveSalarySummaryList(addList);
}
} else {
bool = salaryManager.saveSalarySummaryList(xlsSalaryList);
}
}
Map<String, Boolean> modelMap = new HashMap<String, Boolean>();
modelMap.put("valid", bool);
return modelMap;
}