对应excel字段名
package com.sf.controller;
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
import javax.persistence.Column;
import javax.persistence.Table;
import java.io.Serializable;
/**
* @Author: yachun
* @Date: 2019/5/23 13:29
*/
@Data
@Table(name = "tb_business_cooperation")
public class BusinessCooperationE implements Serializable {
/**
* 企业名称
*/
@Column(name = "enterprise_name")
@Excel(name = "供应商")
private String enterpriseName;
/**
* 业务联系人
*/
@Column(name = "business_contact")
@Excel(name = "姓名")
private String businessContact;
/**
* 联系方式
*/
@Column(name = "contact_information")
@Excel(name = "电话号")
private String contactInformation;
/**
* 办公城市
*/
@Column(name = "office_city")
@Excel(name = "接单城市")
private String officeCity;
}
controller层
@PostMapping(value = "importExcelTest", produces = {MediaType.APPLICATION_JSON_UTF8_VALUE}, consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
public Response importExcel(@RequestPart(value = "file") MultipartFile file,
HttpServletRequest request, HttpServletResponse response) throws IOException {
log.info("接收到上传的信息");
ImportParams importParams = new ImportParams();
// 需要验证
importParams.setNeedVerfiy(false);
try {
ExcelImportResult<BusinessCooperationE> result = ExcelImportUtil.importExcelMore(
file.getInputStream(), BusinessCooperationE.class, importParams);
List<BusinessCooperationE> successList = result.getList();
List<BusinessCooperationE> failList = result.getFailList();
log.info("是否存在验证未通过的数据:" + result.isVerfiyFail());
log.info("验证通过的数量:" + successList.size());
log.info("验证未通过的数量:" + failList.size());
//调用方法把表格插入到数据库
int i = 0;
int k = 0;
for (BusinessCooperationE record : successList) {
BusinessCooperationDO businessCooperationDO = new BusinessCooperationDO();
BeanUtils.copyProperties(record, businessCooperationDO);
businessCooperationDO.setAuditStatus(1);
IdWorker id = new IdWorker();
businessCooperationDO.setId(id.nextId());
//TODO 供应商代码规则-- sup + 城市代码+ 001
String officeCityTemp = record.getOfficeCity();
String codeByCityTemp = CityAndCodeUtil.getCodeByCity(officeCityTemp);
Integer providerCodeCount = businessCooperationService.countProviderCode("sup".concat(codeByCityTemp));
log.info("获取到的总编码个数是---->" + providerCodeCount);
String resultProviderCode= null;
if (providerCodeCount > 0) {
//中间数字
String sandwichDigit = "00";
if (providerCodeCount >= BusinessConstant.NINE) {
sandwichDigit = "0";
}
if (providerCodeCount >= BusinessConstant.NINETY_NINE) {
sandwichDigit = "";
}
String newProviderCode = BusinessConstant.SUP + codeByCityTemp + sandwichDigit + (providerCodeCount + 1);
resultProviderCode = newProviderCode;
businessCooperationDO.setProviderCode(newProviderCode);
businessCooperationDO.setSupplierAccount(newProviderCode);
} else {
String officeCity = record.getOfficeCity();
String codeByCity = CityAndCodeUtil.getCodeByCity(officeCity);
resultProviderCode = BusinessConstant.SUP.concat(codeByCity.concat(BusinessConstant.INIT_PROVIDER_CODE));
businessCooperationDO.setProviderCode(BusinessConstant.SUP.concat(codeByCity.concat(BusinessConstant.INIT_PROVIDER_CODE)));
businessCooperationDO.setSupplierAccount(BusinessConstant.SUP.concat(codeByCity.concat(BusinessConstant.INIT_PROVIDER_CODE)));
}
int j = businessCooperationService.insertSelective(businessCooperationDO);
if (j == 0) {
k++;
}
i += j;
}
log.info("上传文件成功");
return ResponseHelper.buildOk("验证通过的数量:" + successList.size() + "验证未通过的数量:" + failList.size() + "重复或数据错误的数量" + i + "上传操作成功,上传数据" + i + "条");
} catch (Exception e) {
log.info("导入异常,请检查手机号是否已存在", e);
return ResponseHelper.buildFail("导入异常,请检查手机号是否已存在");
}
}