导入excel实体类
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.*;
import org.hibernate.validator.constraints.Length;
import org.oreframework.commons.office.easyexcel.BaseExcelImportVo;
import javax.validation.constraints.NotEmpty;
import javax.validation.constraints.Pattern;
@AllArgsConstructor
@NoArgsConstructor
@Builder
@EqualsAndHashCode(callSuper = true)
@Data
@ToString
public class OrderImportVo extends BaseExcelImportVo{
private static final long serialVersionUID = 220988549223591539L;
@NotEmpty(message = "园区店不能为空")
@ExcelProperty(index = 0,value = "*园区店")
private String yqMerchantName;
@NotEmpty(message = "园区店商户号不能为空")
@Pattern(regexp="(^44[0-9]{6}$)",message = "园区店商户号格式不正确")
@ExcelProperty(index = 1,value = "*园区店商户号")
private String yqMerchantId;
@NotEmpty(message = "转化人员不能为空")
@ExcelProperty(index = 2,value = "*转化人员")
private String userName;
@NotEmpty(message = "4S店不能为空")
@ExcelProperty(index = 3,value = "*4S店")
private String merchantName;
@NotEmpty(message = "4S店商户号不能为空")
@Pattern(regexp="(^44[0-9]{6}$)",message = "4S店商户号格式不正确")
@ExcelProperty(index = 4,value = "*4S店商户号")
private String merchantId;
@NotEmpty(message = "销售顾问不能为空")
@ExcelProperty(index = 5,value = "*销售顾问")
private String employeeName;
@NotEmpty(message = "客户姓名不能为空")
@Length(max=16,message="客户姓名最多为16个字符")
@ExcelProperty(index = 6,value = "*客户姓名")
private String customerName;
@ExcelProperty(index = 7,value = "*性别")
private String customerSexStr;
@NotEmpty(message = "电话不能为空")
@Pattern(regexp="^((0\\d{2,3}-\\d{7,8})|(1[3456789]\\d{9}))$",message = "电话格式不正确")
@ExcelProperty(index = 8,value = "*电话")
private String customerPhone;
// @NotEmpty(message = "车架号不能为空")
// @Pattern(regexp="^(([0-9,a-z,A-Z]{6})|([0-9,a-z,A-Z]{17}))$",message = "车架号必须为6位或17位的数字或字母")
@ExcelProperty(index = 9,value = "车架号")
private String standNo;
@NotEmpty(message = "产品编码不能为空")
@ExcelProperty(index = 10,value = "*产品编码")
private String goodsCode;
@NotEmpty(message = "单价不能为空")
@ExcelProperty(index = 11,value = "*单价")
private String unitPrice;
@NotEmpty(message = "总价不能为空")
@Pattern(regexp="(^[1-9](\\d+)?(\\.\\d{1,2})?$)|(^0$)|(^\\d\\.\\d{1,2}$)",message = "总价格式不正确")
@ExcelProperty(index = 12,value = "*总价")
private String totalPrice;
@NotEmpty(message = "订单时间不能为空")
@ExcelProperty(index = 13,value = "*订单时间")
private String orderTimeStr;
@NotEmpty(message = "支付时间不能为空")
@ExcelProperty(index = 14,value = "*支付时间")
private String payTimeStr;
}
读取excel,异步导入
public RopResponse<String> importOrder(SingleUploadRequest request) {
// 上传文件校验
RopResponse validateResp = uploadFileService.validate(request.getFile(), "excel");
if (validateResp.getData() == null) {
logger.warn("订单导入-上传文件校验不通过");
return validateResp;
}
// 读取excel
InputStream inputstream = new ByteArrayInputStream(request.getFile().getContent());
EasyExcelImportResult<OrderImportVo> result = org.oreframework.commons.office.easyexcel.EasyExcelUtil.importExcel(inputstream, OrderImportVo.class);
String uuid = UUID.randomUUID().toString();
//异步处理数据
ExecutorService executorService = Executors.newSingleThreadExecutor();
executorService.submit(() -> {
// 导入
goodsOrderService.importOrder(result, uuid, CommonUtil.getAccountName());
});
executorService.shutdown();
return RopResponse.success(uuid);
}
导入正确数据;导出错误数据
public void importOrder(EasyExcelImportResult<OrderImportVo> result, String uuid ,String createBy) {
List<OrderImportVo> list = result.getList();
List<OrderImportVo> errorList = result.getFailList();
//过期时间
final int EXPIRATION_TIME = 60 * 5;
if (CollectionUtils.isEmpty(list)) {
if (CollectionUtils.isNotEmpty(errorList)) {
List<OrderErrorExportVo> exportVos = new ArrayList<>();
dataConvert(errorList, exportVos);
String exportExcelUrl = EasyExcelUtil.exportExcel(exportVos, "失败原因", OrderErrorExportVo.class);
ExcelImportProgressVo excelImportProgressVo = new ExcelImportProgressVo(COMPLETED_PROGRESS, 0, errorList.size(), "导入失败", exportExcelUrl);
redisService.set(uuid + "result", JSONObject.toJSONString(excelImportProgressVo), EXPIRATION_TIME);
return;
}
//空数据返回成功
ExcelImportProgressVo excelImportProgressVo = new ExcelImportProgressVo(COMPLETED_PROGRESS, 0, 0, "导入成功", null);
redisService.set(uuid + "result", JSONObject.toJSONString(excelImportProgressVo), EXPIRATION_TIME);
return;
}
int succNum = 0;
BigDecimal progress = BigDecimal.ZERO;
redisService.set(uuid, String.valueOf(progress), EXPIRATION_TIME);
BigDecimal step = COMPLETED_PROGRESS.divide(BigDecimal.valueOf(list.size()), 2, RoundingMode.HALF_UP);
Date now = new Date();
for (OrderImportVo importVo : list) {
try {
// 1.校验、组装数据
RopResponse<OrderImportVo> validResp = checkAndInitDTO(importVo);
if (validResp.getData() == null) {
importVo.appendErrorMsg(validResp.getMessage());
errorList.add(importVo);
logger.info(validResp.getMessage());
continue;
}
// 2.订单数据保存
saveOfflinePaidOrder(validResp.getData(),createBy);
// if(!res.hasResult()){
// importVo.appendErrorMsg(res.getMessage());
// errorList.add(importVo);
// continue;
// }
succNum++;
} catch (Exception e) {
importVo.setErrorMsg(e.getMessage());
errorList.add(importVo);
logger.info(e.getMessage(), e);
}
progress = progress.add(step);
redisService.set(uuid, String.valueOf(progress), EXPIRATION_TIME);
}
//导入完成
String exportExcelUrl = null;
if (CollectionUtils.isNotEmpty(errorList)){
List<OrderErrorExportVo> exportVos = new ArrayList<>();
dataConvert(errorList, exportVos);
exportExcelUrl = EasyExcelUtil.exportExcel(exportVos, "失败原因", OrderErrorExportVo.class);
}
ExcelImportProgressVo importProgressVo = new ExcelImportProgressVo(COMPLETED_PROGRESS, succNum, errorList.size(), succNum > 0 ? "导入成功" : "导入失败", exportExcelUrl);
redisService.set(uuid + "result", JSONObject.toJSONString(importProgressVo), EXPIRATION_TIME);
}
EasyExcelUtil.exportExcel
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.DateUtils;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.oreframework.commons.office.easyexcel.ExcelMergeEntity;
import org.oreframework.commons.office.easyexcel.annotation.ExcelMergeRow;
import org.oreframework.commons.office.easyexcel.annotation.ExcelValueReplace;
import org.oreframework.commons.office.easyexcel.converter.ExcelMergeEntityToStringConvert;
import org.oreframework.commons.office.easyexcel.mergeStrategy.MergeRowStrategy;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Component;
import java.io.File;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.*;
@Component
public class EasyExcelUtil {
private final static Logger logger = LoggerFactory.getLogger(EasyExcelUtil.class);
private static Environment env;
public static <T> String exportExcel(List<T> list,String fileName,Class<T> titleClass){
long startTime = System.currentTimeMillis();
try {
File savePath = new File(env.getProperty("gop.excel.tem.path"));
String name = org.oreframework.commons.office.easyexcel.EasyExcelUtil.exportExcel(list, titleClass, savePath, fileName);
return env.getProperty("gop.excel.tem.url") + name;
}finally{
logger.info("export {} excel use {}ms",fileName,System.currentTimeMillis() - startTime);
}
}
public static <T> String exportExcel(List<T> list, String fileName, InputStream template, int startRow){
long startTime = System.currentTimeMillis();
try {
File savePath = new File(env.getProperty("gop.excel.tem.path"));
String name = exportExcel(list, savePath, fileName, template, startRow);
return env.getProperty("gop.excel.tem.url") + name;
}finally{
logger.info("export {} excel use {}ms",fileName,System.currentTimeMillis() - startTime);
}
}
//以下代码为ore包中代码,因需求的文件名有特殊要求,拉出来重写一份
public static <T> String exportExcel(List<T> dataList, File savePath, String fileName, InputStream template, int startRow) {
long startTime = System.currentTimeMillis();
String var13;
try {
String name = fileName + DateUtils.format(new Date(), "yyyy年MM月dd日") + ExcelTypeEnum.XLSX.getValue();
if (!savePath.exists()) {
savePath.mkdir();
}
File file = new File(savePath, name);
List<LinkedHashMap<String, ExcelMergeEntity>> list = new ArrayList();
convertMergeData(dataList, list, (String)null, -1);
ExcelWriter excelWriter = ((ExcelWriterBuilder)((ExcelWriterBuilder) EasyExcel.write(file).registerWriteHandler(new MergeRowStrategy(list, startRow))).registerConverter(new ExcelMergeEntityToStringConvert())).withTemplate(template).build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
excelWriter.fill(list, fillConfig, writeSheet);
excelWriter.finish();
var13 = name;
} finally {
logger.info("export {} excel use {}ms", fileName, System.currentTimeMillis() - startTime);
}
return var13;
}
private static void convertMergeData(Collection dataList, List<LinkedHashMap<String, ExcelMergeEntity>> newDataList, String parentName, int rowIndex) {
if (!CollectionUtils.isEmpty(dataList)) {
List<Field> fieldList = new ArrayList();
int curRowIndex = rowIndex >= 0 ? rowIndex : 0;
int temRowIndex;
for(Iterator var6 = dataList.iterator(); var6.hasNext(); curRowIndex = temRowIndex + 1) {
Object d = var6.next();
Class tempClass;
if (fieldList.isEmpty()) {
for(tempClass = d.getClass(); tempClass != null && tempClass != Object.class; tempClass = tempClass.getSuperclass()) {
Collections.addAll(fieldList, tempClass.getDeclaredFields());
}
}
tempClass = null;
LinkedHashMap data;
if (rowIndex >= 0 && newDataList.size() > curRowIndex) {
data = (LinkedHashMap)newDataList.get(curRowIndex);
} else {
data = new LinkedHashMap();
newDataList.add(data);
}
Map<String, Collection> collectionMap = new HashMap();
int maxSize = 0;
Iterator var11 = fieldList.iterator();
Field f;
Collection collection;
while(var11.hasNext()) {
f = (Field)var11.next();
if (Collection.class.isAssignableFrom(f.getType())) {
try {
Object value = FieldUtils.readDeclaredField(d, f.getName(), true);
if (value != null) {
collection = (Collection)value;
if (collection.size() > maxSize) {
maxSize = collection.size();
}
collectionMap.put(f.getName(), collection);
}
} catch (IllegalAccessException var16) {
logger.error(var16.getMessage(), var16);
}
}
}
var11 = fieldList.iterator();
while(var11.hasNext()) {
f = (Field)var11.next();
if (!Collection.class.isAssignableFrom(f.getType()) && !f.getClass().isArray()) {
ExcelMergeEntity entity = new ExcelMergeEntity();
entity.setName((parentName != null ? parentName.concat("_") : "").concat(f.getName()));
try {
Object value = FieldUtils.readDeclaredField(d, f.getName(), true);
entity.setValue(converterValue(value, f));
} catch (IllegalAccessException var15) {
logger.error(var15.getMessage(), var15);
}
data.put(entity.getName(), entity);
ExcelMergeRow excelMergeRow = (ExcelMergeRow)f.getAnnotation(ExcelMergeRow.class);
if (excelMergeRow != null) {
entity.setMerge(true);
entity.setIndex(excelMergeRow.columIndex());
entity.setMergeRowCount(maxSize);
}
}
}
temRowIndex = curRowIndex;
for(Iterator var19 = collectionMap.keySet().iterator(); var19.hasNext(); temRowIndex = newDataList.size() - 1) {
String name = (String)var19.next();
collection = (Collection)collectionMap.get(name);
convertMergeData(collection, newDataList, name, curRowIndex);
}
}
}
}
private static Object converterValue(Object value, Field f) {
ExcelProperty property = (ExcelProperty)f.getAnnotation(ExcelProperty.class);
ExcelValueReplace replace = (ExcelValueReplace)f.getAnnotation(ExcelValueReplace.class);
DateTimeFormat dateTimeFormat = (DateTimeFormat)f.getAnnotation(DateTimeFormat.class);
try {
if (dateTimeFormat != null) {
value = DateUtils.format((Date)value, dateTimeFormat.value());
} else if (property != null && property.converter() != null && replace != null) {
ExcelContentProperty excelContentProperty = new ExcelContentProperty();
excelContentProperty.setField(f);
CellData cellData = ((Converter)property.converter().newInstance()).convertToExcelData(value, excelContentProperty, (GlobalConfiguration)null);
value = cellData.getStringValue();
}
} catch (Exception var7) {
logger.error(var7.getMessage(), var7);
logger.error("excel转换字段异常");
}
return value;
}
@Autowired
public void setEnv(Environment env) {
EasyExcelUtil.env = env;
}
}