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條數(shù)據(jù)!");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 擴展參數(shù)* @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;// 讀取excelWorkbook 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);// 總行數(shù) // rowCount = sheet.getLastRowNum();rowCount = getExcelRealRow(sheet); //獲取真實行數(shù)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// }}// 業(yè)務行超過最大配置，直接返回錯誤if ((rowCount - importerConfig.getStartRowNumber()) > importerConfig.getMaxRow()) {ErrorInfo errorInfo = new ErrorInfo();errorInfo.setContent(String.format("數(shù)據(jù)行超過[%s],請減少行數(shù)重新上傳", importerConfig.getMaxRow()));errorInfo.setFileName(fileName);error.add(errorInfo);result.setError(error);return result;}// 循環(huán)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;}// 根據(jù)配置查找需要解析的列，設置到實體對象中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;}// 轉(zhuǎn)換參數(shù)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;}}}}}// 處理其他業(yè)務數(shù)據(jù)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表的真實行數(shù)private int getExcelRealRow(Sheet sheet) {boolean flag = false;for (int i = 1; i <= sheet.getLastRowNum(); ) {Row r = sheet.getRow(i);if (r == null) {// 如果是空行（即沒有任何數(shù)據(jù)、格式），直接把它以下的數(shù)據(jù)往上移動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 {// 如果是空白行（即可能沒有數(shù)據(jù)，但是有一定格式）if (i == sheet.getLastRowNum())// 如果到了最后一行，直接將那一行remove掉sheet.removeRow(r);else//如果還沒到最后一行，則數(shù)據(jù)往上移一行sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);}}return sheet.getLastRowNum();}/*** @ClassName: Result* @Description: 結(jié)果集*/public class Result<T> {private List<T> list = new ArrayList<>();// 返回數(shù)據(jù)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]行數(shù)據(jù)", 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(",機構(gòu)名稱[").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;// 出錯行數(shù)private String name;// 出錯人private String content;// 出錯內(nèi)容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;}@Overridepublic 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;}@Overridepublic 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()));// 屬性 校驗 轉(zhuǎn)換borker.put(i++, new SimpleImporterEntity("destination"));borker.put(i++, new SimpleImporterEntity("goodsCount",new NotNullValidation("件數(shù)"),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);} }

配置以及導入的配置

@Beanpublic 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{@Overridepublic 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 "";}@Overridepublic <T> void set(T bean, Sheet sheet, Row row, Object... args) throws Exception {} }

只是大概整體結(jié)構(gòu)，具體邏輯根據(jù)實際業(yè)務添加

總結(jié)

以上是生活随笔為你收集整理的导入execl的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。