vue前端代码：
1.列表按钮 <el-form-item> <el-button v-if="isAuth('salary:jxperreward:downloadFile')" type="primary" @click="createCSV()">生成模板</el-button> </el-form-item> <el-form-item > <el-upload class="upload-demo" ref="upload" :action="uploadurl()" :on-success="uploadSuccess" :on-remove="handleRemove" :on-error="uperror" accept = ".xlsx" :file-list="fileList" :on-change = "fileChange" :show-file-list="false"> <el-button v-if="isAuth('salary:jxperbasepay:importbasepayxlsx')" type="primary" >导入</el-button> </el-upload> </el-form-item>
<el-form-item>
<el-button v-if="isAuth('salary:jxpayroll:export')" type="primary" @click="createExcel()">导出</el-button>
</el-form-item>
2.方法实现
createCSV (val) {
location.href = process.env.BASE_API + 'salary/jxperreward/downloadFile?token=' + Vue.cookie.get('token')
},
uploadurl () {
return process.env.BASE_API + 'salary/jxperreward/importperrewardxlsx?token=' + Vue.cookie.get('token')
},
uploadSuccess (response, file, fileList) {
this.type = '1'
// 上传成功
if (response.data.flag) {
if (response.data.number === 1) {
this.$message({message: '上传失败金额请输入数字或小数格式', type: 'error', duration: 2000})
} if (response.data.number === 2) {
this.percode = response.data.percode
this.$message({message: this.percode + '的揽存号' + '不存在', type: 'error', duration: 2000})
} else {
this.fileInfo.fileName = '奖励.xls'
this.tempFname = response.data.tempFname
this.fileInfo.filedCnName = '奖励'
this.fileInfo.filePath = response.data.filePath
this.fileInfo.realName = response.data.tempFname
this.$message({message: '上传成功', type: 'success', duration: 2000})
const params = {
'fileName': this.tempFname,
'type': this.type
}
this.showRewardVisible = true
this.$nextTick(() => {
this.$refs.RewardFileList.init(params, this.fileInfo)
})
}
} else {
this.$message({message: '上传失败请下载最新模板', type: 'error', duration: 2000})
this.$nextTick(() => {
})
}
},
createExcel () {
const params = []
params.push(this.dataForm.key + ',' + this.dataForm.percode + ',' + this.dataForm.orgName + ',' + this.dataForm.fixwagedate)
return API.creatcsv.createExcel(params)
},
3.后台逻辑实现
（1）生成模板
@RequestMapping("/downloadFile")
@RequiresPermissions("salary:jxrewardimportfileinfo:download")
public void downloadFile(HttpServletRequest request, HttpServletResponse response) throws IOException{
String fileName = "奖励工资"; //模板名称
String[] headers = {"人员揽存号","人员姓名","项目","奖励金额"}; //列标题
HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄
//新建sheet
HSSFSheet sheet1 = wb.createSheet("Sheet1");
//生成sheet1内容
HSSFRow row = sheet1.createRow(0);
//写标题
for (int i = 0; i < headers.length; i++) {
row.createCell(i).setCellValue(headers[i]);
}
//下拉框数据
String type= "1";
String status = "1";
JxProjectEntity jxProject = new JxProjectEntity();
jxProject.setType(type);
jxProject.setStatus(status);
List<JxProjectEntity> list = jxProjectService.queryName(jxProject);
if(list.size()!=0){
String nameList = "";
for(int i=0;i<list.size();i++){
String name = list.get(i).getName();
nameList = nameList+name+",";
}
String[] str1 = null;
if(nameList.contains(",")){
str1 = nameList.split(",");
}
//String[] str1 = {"机动工资、奖金","自助设备计酬"};
//255以内的下拉,参数分别是：作用的sheet、下拉内容数组、起始行、终止行、起始列、终止列
sheet1.addValidationData(setDataValidation(sheet1, str1, 1, 50000, 2 ,2)); //超过255个报错
}
// 文件临时存放路径
String filePath = CSVConfig.getCsvFilePath();
fileName = fileName + ".xls";
FileUtil.createFile(filePath);
// 将文件保存到指定的位置
try {
FileOutputStream fos = new FileOutputStream(CSVConfig.getCsvFilePath() + File.separator + fileName);
wb.write(fos);
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
CSVUtils.exportFile(response, fileName); // 下载生成的CSV文件
}
（2）导入模板
/**
* 上传文件
*/
@RequestMapping("/importFile")
@RequiresPermissions("salary:jxrewardimportfileinfo:importFile")
public R importOrgFile(@RequestParam(value="file") MultipartFile file){
Map<String, Object> data = new HashMap<String, Object>();
if (!file.isEmpty()) {
// 获取文件的后缀名
String suffixName = file.getOriginalFilename();
// 生成临时文件名：数据集_日期_10位uuid 如[BCKB_19990909_xxxxxxxxxx.xlsx]
String fileName = DateUtils.getSysDate() + "_" + UUIDUtil.generateFileID() + suffixName;
// 文件上传路径
String filePath = CSVConfig.getCsvFilePath() + File.separator + DateUtils.getSysDate();
String impFilePath = filePath + File.separator + fileName;
try {
if (FileUtil.createFile(filePath)){
// 转存文件
file.transferTo(new File(impFilePath));
data.put("tempFname", fileName);
data.put("filePath", filePath);
data.put("realName", file.getOriginalFilename());
data.put("flag", true);
data.put("number", 0);
}
} catch (Exception e) {
// 报错删除
FileUtil.delFile(filePath);
data.put("flag", false);
e.printStackTrace();
}
File file1 = new File(impFilePath);
int total1 = 0;
List<JxOrgRewardEntity> listMap = new ArrayList<JxOrgRewardEntity>();
Map<String, Object> projectlist = new HashMap<String,Object>();
try {
List<List<Object>> list = ExcelUtils.readExcel(file1);
for(int x = 1; x < list.size(); x++) {
JxOrgRewardEntity map = new JxOrgRewardEntity();
//Map<String, JxRewardImportFileInfoEntity> map = new HashMap<String, JxRewardImportFileInfoEntity>();
String value = list.get(x).toString();
String value1 = value.substring(1, value.length()-1);
String[] value2 = value1.split(", ");
String percode = value2[0].substring(0, value2[0].length()-3);
map.setPercode(percode);
map.setPername(value2[1]);
map.setProject(value2[2]);
projectlist.put(""+x, value2[2]);
String reg = "^[0,9]+(.[0,9]+)?$";
Pattern pattern = Pattern.compile(reg);
// 忽略大小写的写法
// Pattern pat = Pattern.compile(regEx, Pattern.CASE_INSENSITIVE);
Matcher matcher = pattern.matcher(value2[3]);
// 查找字符串中是否有匹配正则表达式的字符/字符串
if(!matcher.find()){
// 报错删除
FileUtil.delFile(filePath);
data.put("flag", true);
data.put("number", 1);
return R.error().put("data", data);
}
BigDecimal pay = new BigDecimal(value2[3]);
map.setRewardPay(pay);
listMap.add(map);
}
//循环导入的数据，判断人员揽存号是否真实存在，不存在则提示揽存号不存在并返回
for(int z=0;z<listMap.size();z++){
String percode = listMap.get(z).getPercode();
SysPersonEntity perlist = sysPersonService.queryObjectByPercode(percode);
if(perlist == null){
data.put("flag", true);
data.put("number", 2);
data.put("percode", listMap.get(z).getPername());
return R.ok().put("data", data);
}
}
for(int i=0;i<projectlist.size();i++){
String project = projectlist.get(""+i).toString();
JxProjectEntity jxProject = new JxProjectEntity();
jxProject.setName(project);
total1 += jxProjectService.isexit(jxProject);
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(total1==0){
// 报错删除
FileUtil.delFile(filePath);
data.put("flag", false);
return R.error().put("data", data);
}else{
return R.ok().put("data", data);
}
}else{
data.put("flag", false);
return R.error().put("data", data);
}
}
（3）导出模板
@RequestMapping("/createExcel")
@RequiresPermissions("salary:jxpayroll:export")
public void createExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
// 获得业务日期上个月
//String fixwagedate = "2018-07";
String params=request.getParameter("params");
}
//提供导出excel的表头月份展示
DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
Date d = null;
try {
d = format.parse(fixwagedate+"-01");
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
String month = Integer.toString(DateUtils.findMonth(d));
String orgRel = "";
String userId = getUser().getUserId();
List<SysRoleEntity> roleList = sysPersonService.getRoleId(userId);
entity.setOrgRel(orgRel);
List<JxPayrollEntity> dataList = jxPayrollService.queryAllList(entity);
String type = "序号,单位,姓名,奖励工资,绩效工资,薪金,扣除项,应领绩效工资,固定工资,应纳税所得额,工资应纳税额,税后扣除项,扣税后需减除的固定工资,实领工资,核发工资账号,员工本人签字,备注";
String[] type1 = type.split(",");
String[] header = new String[17];
for(int i=0;i<type1.length;i++){
header[i] = type1[i];
}
HSSFWorkbook workbook = new HSSFWorkbook();// excel文件对象
HSSFSheet sheet = workbook.createSheet("sheet1");// 工作表对象
// 设置列头样式
HSSFCellStyle titleStyle = setTitleCellStyles(workbook, sheet);
HSSFCellStyle dataStyle = setDataCellStyles(workbook, sheet);
HSSFRow row00 = sheet.createRow(0);
row00.setHeight((short) 800);//目的是想把行高设置成55px
HSSFCellStyle titleStyle1 = null;
titleStyle1 = workbook.createCellStyle();
// 设置字体
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 18); // 设置字体大小
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
titleStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
titleStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
titleStyle1.setFont(font);
//row00.setRowStyle(titleStyle1);
// 创建第二标题行
HSSFRow row0 = sheet.createRow(2);
row0.setHeight((short) 800);//目的是想把行高设置成80px
for (int i = 0; i < 3; i++) {
HSSFCell cell = row0.createCell(i);
cell.setCellStyle(titleStyle);
}
//从数据库中查询所有已启用的奖励项目
String type0= "1";
String status = "1";
JxProjectEntity jxProject = new JxProjectEntity();
jxProject.setType(type0);
jxProject.setStatus(status);
List<JxProjectEntity> list = jxProjectService.queryName(jxProject);
String[] salary = null;
String[] salaryList = null;
String salary1 = "";
if(list.size()!=0){
String nameList = "";
for(int i=0;i<list.size();i++){
String name = list.get(i).getName();
nameList = nameList+name+",";
}
if(nameList.contains(",")){
salary = nameList.split(",");
for (int i = 3; i < 3+salary.length; i++) {
HSSFCell cell = row0.createCell(i);
cell.setCellValue(salary[i-3]);
//获取到奖励工资的所有二级表头内容，为之后判断人员是否存在该项目并对应插入数据
salary1 = salary1+(cell.getStringCellValue().toString())+",";
cell.setCellStyle(titleStyle);
}
salaryList = salary1.split(",");
}
}else{
salary =new String[1];
salaryList =new String[0];
}
String[] deducount = new String[] {"综合考评计酬","业务量计酬","合计"};
for (int i = 3+salary.length; i < 3+salary.length+deducount.length; i++) {
HSSFCell cell = row0.createCell(i);
cell.setCellValue(deducount[i-3-salary.length]);
cell.setCellStyle(titleStyle);
}
HSSFCell cell6 = row0.createCell(3+salary.length);
cell6.setCellStyle(titleStyle);
//动态合并列单元格
//总表头
Integer startrow00 = Integer.parseInt("0");
Integer overrow00 = Integer.parseInt("0");
Integer startcol00 = Integer.parseInt("0");
Integer overcol00 = 3+salary.length+5;
sheet.addMergedRegion(new CellRangeAddress(startrow00, overrow00,
startcol00, overcol00));
HSSFCell cell00 = row00.createCell(0);
cell00.setCellValue(month+"月份核发工资明细表");
cell00.setCellStyle(titleStyle1);
//合并二级表头的一级表头
Integer startrow0 = Integer.parseInt("1");
Integer overrow0 = Integer.parseInt("1");
Integer startcol0 = Integer.parseInt("3");
Integer overcol0 = 0;
//如果奖励工资的二级表头为0，需要合并奖励工资（不包含二级表头）的表头
if(salary.length!=1){
overcol0 = 3+salary.length-1;
}else{
overcol0 = 3;
salary =new String[1];
}
sheet.addMergedRegion(new CellRangeAddress(startrow0, overrow0,
startcol0, overcol0));
//动态合并行单元格
// 合并除带有二级表头的一级表头
Integer startrow1 = Integer.parseInt("1");
Integer overrow1 = Integer.parseInt("2");
//如果奖励工资项下拉二级表头为0，则需要合并该一级表头
int i11 = 3;
if(salary.length ==1){
i11 = 4;
}
for (int i = 0; i < i11; i++) {
String z0 = Integer.toString(i);
Integer startcol1 = Integer.parseInt(z0);
Integer overcol1 = Integer.parseInt(z0);
sheet.addMergedRegion(new CellRangeAddress(startrow1, overrow1,
startcol1, overcol1));
}
if(deducount1.length ==0){
//如果税前扣除项下拉二级表头为0，则需要合并该一级表头
Integer startcol1 = 3+salary.length+deducount.length+1;
Integer overcol1 = startcol1;
sheet.addMergedRegion(new CellRangeAddress(startrow1, overrow1,
startcol1, overcol1));
}
//poi设置行高列宽
//row00.setHeight((short) 700);//目的是想把行高设置成20px
HSSFRow row = sheet.createRow(1);
row.setHeight((short) 350);//目的是想把行高设置成300px
// 创建标题行并写入内容
// 设置第一标题行的列宽和样式
for (int i = 0; i < 3+salary.length+5; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(titleStyle);
sheet.autoSizeColumn(1, true);
sheet.setColumnWidth(i, sheet.getColumnWidth(i)*17/10);
}
//设置第一列列宽
sheet.setColumnWidth(0, 1500);
//设置奖励工资的列宽
sheet.setColumnWidth(3, 2500);
sheet.setColumnWidth(3+salary.length, 2500);
//跳过合并单元格的多余列
for (int i = 0; i < 4; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(header[i]);
cell.setCellStyle(titleStyle);
}
HSSFCell cell44 = row.createCell(3+salary.length);
cell44.setCellValue(header[4]);
cell44.setCellStyle(titleStyle);
HSSFCell cell55 = row.createCell(3+salary.length+deducount.length);
cell55.setCellValue(header[5]);
cell55.setCellStyle(titleStyle);
HSSFCell cell66 = row.createCell(3+salary.length+deducount.length+1);
cell66.setCellValue(header[6]);
cell66.setCellStyle(titleStyle);
int i0 = 3+salary.length+deducount.length+1+deducount1.length; //第三个二级表头之后的第一个表头
int i1 = 3+salary.length+deducount.length+1+deducount1.length+4; //第三个二级表头之后的最后一个表头
for (int i =i0 ; i < i1; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(header[i-salary.length+1-deducount.length+1-deducount1.length+1]);
cell.setCellStyle(titleStyle);
}
int i2 = 3+salary.length+deducount.length+1+deducount1.length+4; //最后一个二级表头的第一列单元格
HSSFCell cell77 = row.createCell(i2);
cell77.setCellValue(header[3+1+1+1+1+4]);//二级表头合并为一个，所有有几个二级表头就加几个1
cell77.setCellStyle(titleStyle);
int i3 = 3+salary.length+deducount.length+1+deducount1.length+4+otherdeducount.length; //最后一个二级表头之后的表头开始
int i4 = 3+salary.length+deducount.length+1+deducount1.length+4+otherdeducount.length+5; //结束表头
int i5 = salary.length-1+deducount.length-1+deducount1.length-1+otherdeducount.length-1;//i-i5表示循环插入的表头名称序号
for (int i = i3; i < i4; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(header[i-i5]);
cell.setCellStyle(titleStyle);
}
// 内容集合有数组则循环显示增加
if (dataList != null && dataList.size() > 0) {
/*if(){
}*/
// 创建数据
for (int i = 0; i < dataList.size(); i++) {
//遍历集合数据，产生数据行
creatEmployeeData(sheet, i + 4, dataStyle, workbook, dataList.get(i),
salary.length, deducount.length, deducount1.length, otherdeducount.length,salaryList,deducount1List,otherdeducountList);
}
}
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attchment;fileName=模板;filename=" + URLEncoder.encode(month+"月份核发工资明细表.xls", "UTF-8"));
workbook.write(response.getOutputStream());
response.getOutputStream().close();
}
1853
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
