Controller
@RequestMapping(value = "/importer", method = {RequestMethod.POST})
@ApiOperation(value = "导入订单", notes = "导入订单", response = String.class)
public BaseResult<Object> importer(HttpServletRequest request) throws IOException {
BaseResult<Object> br = new BaseResult<>();
LinkedHashMap<String, Object> data = Maps.newLinkedHashMap();
boolean isMultipart = ServletFileUpload.isMultipartContent(request);
if (isMultipart) {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
List<MultipartFile> fileList = multipartRequest.getFiles("file");
HashMap<String, Long> orgMap = organizationService.findNameId();
for (MultipartFile multipartFile : fileList) {
ImporterFactory.Result<OrderImporter> ir = importerFactory.process(multipartFile.getInputStream(), multipartFile.getOriginalFilename(),
OrderImporter.class, OrderImporterConfig.class, orgMap);
if (CollectionUtils.isNotEmpty(ir.getError())) {
br.setCode(ResultCodeEnum.HTTP_PARAMS_ERROR.getCode());
br.setMsg(ir.getErrorPureMsg());
return br;
}
List<OrderImporter> orderImporterList = ir.getList();
if (orderImporterList.size() > 1000) {
br.setCode(ResultCodeEnum.HTTP_PARAMS_ERROR.getCode());
br.setMsg("导入失败,一次最大1000条数据!");
return br;
}
SysUserRealmDto dto = UserUtils.getCurrentUser();
orderImporterList.forEach(o -> o.setDeliveryOrgId(dto.getOrgId()));
orderImporterService.importData(orderImporterList);
br.setData(data);
}
}
return br;
}
导入工厂
package com.opensesame.platform.web.importer;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import com.opensesame.platform.web.importer.config.AbstractImporterConfig;
import com.opensesame.platform.web.importer.converter.AbstractImporterConverter;
import com.opensesame.platform.web.importer.entity.AbstractImporterEntity;
import com.opensesame.platform.web.importer.validation.AbstractImporterValidation;
/**
* @ClassName: ImporterFactory
* @Description: 导入工厂
* @author sonny
* @date 2017年4月10日 下午2:43:52
*
*/
public class ImporterFactory {
protected Map<Class<? extends AbstractImporterConfig>, AbstractImporterConfig> importerConfigMap;
/**
* @Title: process
* @Description: 处理导入核心方法
* @param input 输入流(excel)
* @param fileName 文件名
* @param outputClassType 输出class 类型
* @param importerVersionClass 使用的模板
* @param args 扩展参数
* @return
* @throws InstantiationException
* @throws IllegalAccessException
* @throws IllegalArgumentException
* @throws InvocationTargetException
* @throws IOException
* @return Result<T>
* @author sonny
* @date 2017年4月10日 下午2:44:03
*/
public <T> Result<T> process(InputStream input, String fileName, Class<T> outputClassType,
Class<? extends AbstractImporterConfig> importerConfigClass, Object... args) {
Result<T> result = new Result<T>();
List<T> list = new ArrayList<T>();
Set<ErrorInfo> error = new LinkedHashSet<ErrorInfo>();
AbstractImporterConfig importerConfig;
// 读取excel
Workbook workBook = null;
int rowCount = 0;
Sheet sheet;
try {
workBook = WorkbookFactory.create(input);
} catch (Exception e) {
ErrorInfo errorInfo = new ErrorInfo();
errorInfo.setContent("不支持的文件格式");
errorInfo.setFileName(fileName);
error.add(errorInfo);
result.setError(error);
return result;
}
try {
// TODO 这里只支持第一个 sheet 以后可以扩展
sheet = workBook.getSheetAt(0);
// 总行数
// rowCount = sheet.getLastRowNum();
rowCount = getExcelRealRow(sheet); //获取真实行数
importerConfig = importerConfigMap.get(importerConfigClass);
if (null == importerConfig) {
throw new RuntimeException();
}
} catch (Exception e) {
ErrorInfo errorInfo = new ErrorInfo();
errorInfo.setContent("模板不正确");
errorInfo.setFileName(fileName);
error.add(errorInfo);
result.setError(error);
return result;
} finally {
// try {
// workBook.close();
// } catch (IOException e) {
// // ig
// }
}
// 业务行超过最大配置,直接返回错误
if ((rowCount - importerConfig.getStartRowNumber()) > importerConfig.getMaxRow()) {
ErrorInfo errorInfo = new ErrorInfo();
errorInfo.setContent(String.format("数据行超过[%s],请减少行数重新上传", importerConfig.getMaxRow()));
errorInfo.setFileName(fileName);
error.add(errorInfo);
result.setError(error);
return result;
}
// 循环excel 行
for (int i = importerConfig.getStartRowNumber(); i <= rowCount; i++) {
Row row = sheet.getRow(i);
try {
T bean = outputClassType.newInstance();
// 如果当前行每列都为空,直接返回
int allCellCount = row.getLastCellNum();
int emptyCellCount = 0;
for (int j = 0; j < allCellCount; j++) {
Cell cell = row.getCell(j);
cell.setCellType(Cell.CELL_TYPE_STRING);
if (StringUtils.isEmpty(cell.getStringCellValue().trim())) {
emptyCellCount++;
} else {
break;
}
}
if (emptyCellCount == allCellCount) {
break;
}
// 根据配置查找需要解析的列,设置到实体对象中
for (Integer key : importerConfig.getImporterMapper().getBorker().keySet()) {
Cell cell = row.getCell(key);
String value = "";
if (null != cell) {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
DecimalFormat df = new DecimalFormat("#.##");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else {
if (DateUtil.isCellDateFormatted(cell)) {
value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
} else {
BigDecimal bigdecimal = new BigDecimal(cell.getNumericCellValue());
bigdecimal = bigdecimal.divide(new BigDecimal(1), 2, BigDecimal.ROUND_HALF_UP);
value = bigdecimal.toString();
}
}
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
value = cell.getStringCellValue().trim();
}
}
AbstractImporterEntity importerEntity = importerConfig.getImporterMapper().getBorker().get(key);
AbstractImporterValidation validation = importerEntity.getValidation();
AbstractImporterConverter converter = importerEntity.getConverter();
// 校验
if (validation != null && !validation.verify(value, row, bean, (i + 1), (rowCount - i),args)) {
ErrorInfo errorInfo = new ErrorInfo();
errorInfo.setName(importerConfig.getName(row));
errorInfo.setRow("" + (i + 1));
errorInfo.setContent(validation.getMessage());
errorInfo.setFileName(fileName);
error.add(errorInfo);
continue;
}
// 转换参数
if (converter != null) {
value = converter.transform(value,args);
}
// 设置对应的实体属性
Method[] methods = outputClassType.getDeclaredMethods();
if (null != methods && methods.length > 0) {
for (Method method : methods) {
String methodName = method.getName();
if (methodName.startsWith("set")) {
methodName = methodName.substring(3).toLowerCase();
if (importerEntity.getAttr().toLowerCase().equals(methodName)) {
if ("int".equals(method.getParameterTypes()[0].getName())
|| "java.lang.Integer".equals(method.getParameterTypes()[0].getName())) {
method.invoke(bean, (null == value || "".equals(value)) ? 0 : Integer.parseInt(value));
} else if ("long".equals(method.getParameterTypes()[0].getName())
|| "java.lang.Long".equals(method.getParameterTypes()[0].getName())) {
method.invoke(bean, (null == value || "".equals(value)) ? 0 : Long.parseLong(value));
} else if ("java.math.BigDecimal".equals(method.getParameterTypes()[0].getName())) {
method.invoke(bean, new BigDecimal(value));
} else {
method.invoke(bean, value);
}
break;
}
}
}
}
}
// 处理其他业务数据
importerConfig.set(bean, sheet, row, args);
list.add(bean);
} catch (Exception e) {
e.printStackTrace();
ErrorInfo errorInfo = new ErrorInfo();
errorInfo.setName(importerConfig.getName(row));
errorInfo.setRow("" + (i + 1));
errorInfo.setContent(e.getMessage());
errorInfo.setFileName(fileName);
error.add(errorInfo);
result.setError(error);
}
}
result.setList(list);
result.setError(error);
return result;
}
// 获取Excel表的真实行数
private int getExcelRealRow(Sheet sheet) {
boolean flag = false;
for (int i = 1; i <= sheet.getLastRowNum(); ) {
Row r = sheet.getRow(i);
if (r == null) {
// 如果是空行(即没有任何数据、格式),直接把它以下的数据往上移动
sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
continue;
}
flag = false;
for (Cell c : r) {
if (c.getCellType() != Cell.CELL_TYPE_BLANK) {
flag = true;
break;
}
}
if (flag) {
i++;
continue;
} else {
// 如果是空白行(即可能没有数据,但是有一定格式)
if (i == sheet.getLastRowNum())// 如果到了最后一行,直接将那一行remove掉
sheet.removeRow(r);
else//如果还没到最后一行,则数据往上移一行
sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
}
}
return sheet.getLastRowNum();
}
/**
* @ClassName: Result
* @Description: 结果集
*/
public class Result<T> {
private List<T> list = new ArrayList<>();// 返回数据
private Set<ErrorInfo> error;// 错误信息
public List<T> getList() {
return list;
}
public void setList(List<T> list) {
this.list = list;
}
public Set<ErrorInfo> getError() {
return error;
}
public void setError(Set<ErrorInfo> error) {
this.error = error;
}
public String getSuccess() {
return String.format("成功录入[%s]行数据", getList().size());
}
public String getErrorMsg() {
if (CollectionUtils.isEmpty(error)) {
return "";
}
StringBuilder sb = new StringBuilder();
sb.append("导入失败:");
for (ErrorInfo errorInfo : error) {
sb.append(errorInfo.getRow()).append(",序列[")
.append(errorInfo.getName()).append("],").append(errorInfo.getContent()).append(";");
}
return sb.toString();
}
public String getErrorPureMsg() {
if (CollectionUtils.isEmpty(error)) {
return "";
}
StringBuilder sb = new StringBuilder();
sb.append("导入失败:");
for (ErrorInfo errorInfo : error) {
sb.append(errorInfo.getRow()).append(",").append(errorInfo.getContent()).append(";");
}
return sb.toString();
}
public String getErrorByMsg() {
if (CollectionUtils.isEmpty(error)) {
return "";
}
StringBuilder sb = new StringBuilder();
sb.append("导入失败:");
for (ErrorInfo errorInfo : error) {
sb.append(errorInfo.getRow()).append(",机构名称[")
.append(errorInfo.getName()).append("],").append(errorInfo.getContent()).append(";");
}
return sb.toString();
}
}
public Map<Class<? extends AbstractImporterConfig>, AbstractImporterConfig> getImporterConfigMap() {
return importerConfigMap;
}
public void setImporterConfigMap(Map<Class<? extends AbstractImporterConfig>, AbstractImporterConfig> importerConfigMap) {
this.importerConfigMap = importerConfigMap;
}
public class ErrorInfo {
private String fileName;// 出错文件名称
private String row;// 出错行数
private String name;// 出错人
private String content;// 出错内容
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public String getRow() {
return row;
}
public void setRow(String row) {
this.row = "第" + row + "行";
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((fileName == null) ? 0 : fileName.hashCode());
result = prime * result + ((row == null) ? 0 : row.hashCode());
result = prime * result + ((name == null) ? 0 : name.hashCode());
result = prime * result + ((content == null) ? 0 : content.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj) {
return true;
}
if (obj == null) {
return false;
}
if (getClass() != obj.getClass()) {
return false;
}
ErrorInfo other = (ErrorInfo) obj;
if (fileName == null) {
if (other.fileName != null) {
return false;
}
} else if (!fileName.equals(other.fileName)) {
return false;
}
if (row == null) {
if (other.row != null) {
return false;
}
} else if (!row.equals(other.row)) {
return false;
}
if (name == null) {
if (other.name != null) {
return false;
}
} else if (!name.equals(other.name)) {
return false;
}
if (content == null) {
if (other.content != null) {
return false;
}
} else if (!content.equals(other.content)) {
return false;
}
return true;
}
}
}
导入字段的mapper
package com.opensesame.platform.web.importer.mapper;
import com.opensesame.platform.web.importer.converter.GoodsCountConverter;
import com.opensesame.platform.web.importer.converter.OrgImporterConverterV2;
import com.opensesame.platform.web.importer.entity.AbstractImporterEntity;
import com.opensesame.platform.web.importer.entity.SimpleImporterEntity;
import com.opensesame.platform.web.importer.validation.NotNullValidation;
import com.opensesame.platform.web.importer.validation.OrgImporterValidationV2;
import java.util.HashMap;
import java.util.Map;
public class OrderImporterMapper extends AbstractImporterMapper{
public OrderImporterMapper() {
Map<Integer, AbstractImporterEntity> borker = new HashMap<>();
int i = 0;
borker.put(i++, new SimpleImporterEntity("receiveOrgId", new OrgImporterValidationV2(), new OrgImporterConverterV2()));// 属性 校验 转换
borker.put(i++, new SimpleImporterEntity("destination"));
borker.put(i++, new SimpleImporterEntity("goodsCount",new NotNullValidation("件数"),new GoodsCountConverter()));
borker.put(i++, new SimpleImporterEntity("goodsName",new NotNullValidation("货物名称")));
borker.put(i++, new SimpleImporterEntity("receiveUserName",new NotNullValidation("收货人")));
borker.put(i++, new SimpleImporterEntity("receiveUserPhone",new NotNullValidation("收货人电话")));
borker.put(i++, new SimpleImporterEntity("receiveAddress",new NotNullValidation("收件人地址")));
borker.put(i++, new SimpleImporterEntity("goodsWeight",new NotNullValidation("重量kg")));
borker.put(i++, new SimpleImporterEntity("goodsVolume",new NotNullValidation("体积")));
borker.put(i++, new SimpleImporterEntity("paidFee"));
borker.put(i++, new SimpleImporterEntity("receiveFee"));
borker.put(i++, new SimpleImporterEntity("insteadGoodsFee"));
borker.put(i++, new SimpleImporterEntity("insuranceCost"));
borker.put(i++, new SimpleImporterEntity("insuranceFee"));
borker.put(i++, new SimpleImporterEntity("paidCarryFee"));
borker.put(i++, new SimpleImporterEntity("pickCarryFee"));
borker.put(i++, new SimpleImporterEntity("receiptCount"));
setBorker(borker);
}
}
配置以及导入的配置
@Bean
public OrderImporterConfig getOrderImporterConfig(){
OrderImporterConfig orderImporterConfig = new OrderImporterConfig();
orderImporterConfig.setStartRowNumber(1);
orderImporterConfig.setImporterMapper(new OrderImporterMapper());
return orderImporterConfig;
}
package com.opensesame.platform.web.importer.config;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
public class OrderImporterConfig extends AbstractImporterConfig{
@Override
public String getName(Row row) {
Cell cell = row.getCell(0);
if (null != cell) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if (StringUtils.isEmpty(cell.getStringCellValue().trim())) {
return "";
}
return cell.getStringCellValue().trim();
}
return "";
}
@Override
public <T> void set(T bean, Sheet sheet, Row row, Object... args) throws Exception {
}
}
只是大概整体结构,具体逻辑根据实际业务添加