导入execl

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;// 读取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);// 总行数 // 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;}@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()));// 属性 校验 转换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);} }

配置以及导入的配置

@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 {} }

只是大概整体结构，具体逻辑根据实际业务添加

总结

以上是生活随笔为你收集整理的导入execl的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。