案例效果自定义service实现Excel导入前后,根据业务对Excel中的数据进行处理,此案例以物料管理为例,实现以下业务逻辑:
1、Excel导入前判断Excel中数据填写的是否正确,数据正确的前提下,根据物料编码进行判断,如果数据库中已经存在此物料,就在前台页面给出提示:’此物料已经存在,确定是否导入’;
2、点击【导入】,已经存在的物料就修改库存,没存在的直接导入到数据库中;
实现思路
- 先在系统中做一个物料管理的功能;
- 在’excel数据导入’功能中增加配置,配置添加完成之后,点击【导入数据】按钮,可以把Excel中的数据导入到物料管理这个功能对应的表中;
- 编写后台接口,实现’Excel导入前判断Excel中数据填写的是否正确,数据正确的前提下,根据物料编码进行判断,如果数据库中已经存在此物料,就在前台页面给出提示:”此物料已经存在,确定是否导入!”‘的逻辑;
- 进入导入明细页面,修改数据处理方式为’执行类’,执行类与执行方法中填写上自己写的service名与方法名;
- 编写后台接口,实现’点击【导入】,已经存在的物料就修改库存,没存在的直接导入到数据库中’的业务逻辑;
- 在’excel数据导入’详细页面,将执行后的执行方式修改为’执行类’,并在执行类与执行方法中配置上后台写的service名与方法名;
操作步骤
1、先在系统中做一个物料管理的功能;
2、在’excel数据导入’功能中增加配置,配置添加完成之后,点击【导入数据】按钮,可以把Excel中的数据导入到物料管理这个功能对应的表中
3、编写后台接口,实现’Excel导入前判断Excel中数据填写的是否正确,数据正确的前提下,根据物料编码进行判断,如果数据库中已经存在此物料,就在前台页面给出提示:”此物料已经存在,确定是否导入!”‘的逻辑;
4、进入导入明细页面,修改数据处理方式为’执行类’,执行类与执行方法中填写上自己写的service名与方法名;
5、编写后台接口,实现’点击【导入】,已经存在的物料就修改库存,没存在的直接导入到数据库中’的业务逻辑;
6、在’excel数据导入’详细页面,将执行后的执行方式修改为’执行类’,并在执行类与执行方法中配置上后台写的service名与方法名; 相关代码
- @Override
- public JSONObject impData(String code, String fileKey, String groupTemId, HttpServletRequest request) {
- JSONObject returnVo = new JSONObject();
- List<DynaBean> datas = new ArrayList<DynaBean>();
- //声明返回值
- JSONObject returnObj = new JSONObject();
- if (StringUtil.isNotEmpty(groupTemId)) {
- returnObj.put("groupTemId", groupTemId);
- }
- DynaBean group = serviceTemplate.selectOne("JE_CORE_EXCELGROUP", " AND GROUPCODE='" + code + "'");
- //声明变量集合,用于解析whereSql的通配符
- Set<Map.Entry> ddSet = new HashSet<Map.Entry>();
- //加入登录信息
- EndUser currentUser = SecurityUserHolder.getCurrentUser();
- ddSet.addAll(SecurityUserHolder.getCurrentInfo(currentUser).entrySet());
- //加入用户变量
- ddSet.addAll(FrontCacheManager.getCacheValues().entrySet());
- ddSet.addAll(BackCacheManager.getCacheValues().entrySet());
- //加入系统设置
- ddSet.addAll(WebUtils.getAllSysVar().entrySet());
- ddSet.addAll(getRequestParams(request).entrySet());
- List<ExcelReturnVo> returnMsgs = new ArrayList<ExcelReturnVo>();
- //系统处理方式
- //数据预览
- String dataPreview = group.getStr("EXCELGROUP_PRVIEW");
- //获取文件流
- FileBO fileBO = documentBusService.readFile(fileKey);
- InputStream inputStream = fileBO.getFile();
- byte[] bytes = IoUtil.readBytes(inputStream);
- //声明默认值
- List<DynaBean> sheets = serviceTemplate.selectList("JE_CORE_EXCELSHEET", " AND JE_CORE_EXCELGROUP_ID='" + group.getStr("JE_CORE_EXCELGROUP_ID") + "' ORDER BY SY_ORDERINDEX");
- Map<String, List<DynaBean>> results = new HashMap<>();
- List<String> sheetNames = new ArrayList<>();
- JSONArray previewSheetInfo = new JSONArray();
- Map<String, Map<String, DynaBean>> allFieldInfos = new HashMap<>();
- for (int sheetIndex = 0; sheetIndex < sheets.size(); sheetIndex++) {
- DynaBean sheet = sheets.get(sheetIndex);
- int sheetOrder = sheet.getInt("SY_ORDERINDEX", 1) - 1;
- String key = "sheet" + sheetOrder;
- //声明字段信息
- List<DynaBean> fields = serviceTemplate.selectList("JE_CORE_EXCELFIELD", " AND JE_CORE_EXCELSHEET_ID='" + sheet.getStr("JE_CORE_EXCELSHEET_ID") + "' ORDER BY SY_ORDERINDEX");
- //读取文件
- ExcelReader reader = new ExcelReader(IoUtil.toStream(bytes), sheetOrder, true);
- String tableCode = sheet.getStr("TABLECODE");
- int startRow = sheet.getInt("STARTROWS", 0);
- int endRow = sheet.getInt("EXCELSHEET_JSX", 0);
- String funcCode = sheet.getStr("FUNCCODE");
- String defaultFuncVal = sheet.getStr("DATA_DEFAULTVALUE");
- String defaultCreateInfo = sheet.getStr("DATA_CREATEINFO");
- String sheetName = reader.getSheet().getSheetName();
- sheetNames.add(sheetName);
- sheet.set("SHEETNAME", sheetName);
- //声明默认值
- DynaBean defaultValues = new DynaBean();
- if ("1".equals(defaultFuncVal)) {
- serviceTemplate.buildFuncDefaultValues(funcCode, defaultValues);
- }
- List<List<Object>> lists = null;
- if (endRow > startRow && startRow >= 0) {
- lists = reader.read(startRow, endRow);
- } else {
- lists = reader.read(startRow - 1);
- }
- Map<String, DynaBean> fieldInfos = new HashMap<String, DynaBean>();
- Map<String, DynaBean> fieldCodeInfos = new HashMap<String, DynaBean>();
- //自动带值数据集
- Map<String, List<DynaBean>> dzValues = new HashMap<String, List<DynaBean>>();
- //编号字段
- List<String> codeFields = new ArrayList<String>();
- //带值字段集合
- List<String> dzFields = new ArrayList<String>();
- List<String> kxjsFields = new ArrayList<String>();
- //日期处理类型
- List<String> dateFields = new ArrayList<String>();
- List<String> dateTimeFields = new ArrayList<String>();
- for (DynaBean field : fields) {
- String columnName = field.getStr("EXCELFIELD_COLUMN");
- fieldCodeInfos.put(field.getStr("EXCELFIELD_CODE"), field);
- //处理默认值
- String defaultValue = field.getStr("EXCELFIELD_VALUE");
- if (!Strings.isNullOrEmpty(defaultValue) && StringUtil.isNotEmpty(defaultValue) && defaultValue.startsWith("@") && defaultValue.endsWith("@")) {
- defaultValue = "{" + defaultValue + "}";
- }
- if (!Strings.isNullOrEmpty(defaultValue)) {
- defaultValues.set(field.getStr("EXCELFIELD_CODE"), StringUtil.parseKeyWord(defaultValue, ddSet));
- }
- if (StringUtil.isNotEmpty(columnName)) {
- fieldInfos.put(columnName, field);
- }
- if ("codefield".equals(field.getStr("EXCELFIELD_XTYPE"))) {
- codeFields.add(field.getStr("EXCELFIELD_CODE"));
- }
- if ("datefield".equals(field.getStr("EXCELFIELD_XTYPE"))) {
- dateFields.add(field.getStr("EXCELFIELD_CODE"));
- }
- if ("datetimefield".equals(field.getStr("EXCELFIELD_XTYPE"))) {
- dateTimeFields.add(field.getStr("EXCELFIELD_CODE"));
- }
- if ("1".equals(field.getStr("EXCELFIELD_QCKXJS"))) {
- kxjsFields.add(field.getStr("EXCELFIELD_CODE"));
- }
- excelManager.parseAllDzValues(field, ddSet, dzValues, dzFields, request);
- }
- allFieldInfos.put(key, fieldCodeInfos);
- for (List<Object> vals : lists) {
- DynaBean dynaBean = new DynaBean();
- if ("1".equals(defaultFuncVal)) {
- dynaBean.setValues(defaultValues.clone().getValues());
- }
- if ("1".equals(defaultCreateInfo)) {
- serviceTemplate.buildModelCreateInfo(dynaBean);
- }
- if (StringUtil.isNotEmpty(tableCode)) {
- dynaBean.set(BeanUtils.KEY_TABLE_CODE, tableCode);
- }
- for (int i = 0; i < vals.size(); i++) {
- String nowName = excelManager.getExcelColumnName(i);
- if (fieldInfos.containsKey(nowName)) {
- DynaBean field = fieldInfos.get(nowName);
- String fieldCode = field.getStr("EXCELFIELD_CODE");
- //去除字段值
- Object excelValue = vals.get(i);
- if (!Objects.isNull(excelValue) && !Strings.isNullOrEmpty(excelValue.toString())) {
- dynaBean.set(fieldCode, excelValue);
- }
- }
- }
- for (String dzField : dzFields) {
- DynaBean fieldInfo = fieldCodeInfos.get(dzField);
- excelManager.doSetBeanDzValue(fieldInfo, dynaBean, dzValues, ddSet);
- }
- for (String dateField : dateFields) {
- String dateValue = dynaBean.getStr(dateField);
- if (!Strings.isNullOrEmpty(dateValue)) {
- try {
- dynaBean.setStr(dateField, DateUtils.formatDate(DateUtils.getDate(dateValue)));
- } catch (Exception e) {
- throw new RuntimeException("日期格式错误");
- }
- }
- }
- for (String dateTimeField : dateTimeFields) {
- String dateValue = dynaBean.getStr(dateTimeField);
- if (!Strings.isNullOrEmpty(dateValue)) {
- try {
- dynaBean.setStr(dateTimeField, DateUtils.formatDateTime(DateUtils.getDate(dateValue)));
- } catch (Exception e) {
- throw new RuntimeException("日期格式错误");
- }
- }
- }
- for (String kxjsCode : kxjsFields) {
- String val = dynaBean.getStr(kxjsCode);
- if (StringUtil.isNotEmpty(val)) {
- String regx = "^((-?\\d+.?\\d*)[Ee]{1}(-?\\d+))$";
- Pattern pattern = Pattern.compile(regx);
- if (pattern.matcher(val).matches()) {
- // DecimalFormat df = new DecimalFormat("0");
- // val = df.format(val);
- BigDecimal b = new BigDecimal(val);
- val = b.toPlainString();
- }
- }
- dynaBean.set(kxjsCode, val);
- }
- dynaBean.set("__CODE__", "1");
- dynaBean.set("__MSG__", "正确");
- datas.add(dynaBean);
- }
- String beforeClType = sheet.getStr("BEFORE_CLTYPE");
- }
- ExcelReturnVo excelReturnVo = tainmaExcelDaotuWuliao(datas);
- returnVo.put("excelReturnVo",excelReturnVo);
- return returnVo;
- }
- public ExcelReturnVo tainmaExcelDaotuWuliao(List<DynaBean> list) {
- ExcelReturnVo returnVo = new ExcelReturnVo(1, "");
- List<DynaBean> vals = list;
- StringBuffer errormes = new StringBuffer();
- for (DynaBean val : vals) {
- //物料代码
- String name = val.getStr("WLXX_WLDM");
- //组织代码
- String zzdm = val.getStr("WLXX_ZZDM_CODE");
- //组织分类
- String zzfl = val.getStr("WLXX_ZZFL_CODE");
- //产业基地
- String cyjd = val.getStr("WLXX_CYJD");
- //子库
- String zk = val.getStr("WLXX_ZK");
- //货位
- String hw = val.getStr("WLXX_HW");
- //批号
- String ph = val.getStr("WLXX_PH");
- //数量
- String sl = val.getStr("WLXX_SL");
- //计量单位
- String jldw = val.getStr("WLXX_JLDW_CODE");
- //入库日期
- String rkrq = val.getStr("WLXX_RKRQ");
- if (!Strings.isNullOrEmpty(name)) {
- List<DynaBean> uses =
- metaService.select("PRO_FLOW_WLXX", ConditionsWrapper.builder().eq("WLXX_WLDM", name));
- if (uses.size() > 0) {
- errormes.append(name + "物料已经存在;</br>");
- if (returnVo.getCode() != 2) {
- //信息重复
- returnVo.setCode(3);
- }
- }
- } else {
- errormes.append(name + "信息不全,");
- //信息不全
- returnVo.setCode(3);
- }
- }
- if (errormes.length() > 0) {
- if(returnVo.getCode()==2){
- errormes.append("请检查数据后再导入!");
- }else {
- errormes.append("请确认是否导入!");
- }
- }
- if(list.size()==0){
- errormes = new StringBuffer("没有数据!");
- returnVo.setCode(2);
- }
- if(returnVo.getCode()==1){
- errormes = new StringBuffer("数据正确,是否导入!");
- }
- returnVo.setMsg(errormes.toString());
- return returnVo;
- }
复制代码
- @Override
- public ExcelReturnVo implData(ExcelParamVo paramVo) {
- ExcelReturnVo returnVo = new ExcelReturnVo(1, "成功");
- Map<String, List<DynaBean>> maps = paramVo.getAllValues();
- List<DynaBean> list = maps.get("sheet0");
- for (DynaBean dynaBean : list) {
- String xydm = dynaBean.getStr("WLXX_WLDM");
- List<DynaBean> uses =
- metaService.select("PRO_FLOW_WLXX", ConditionsWrapper.builder().eq("WLXX_WLDM", xydm));
- if (uses.size() > 0) {
- int excelSl = Integer.parseInt( dynaBean.getStr("WLXX_SL"));
- for(DynaBean dynaBean1:uses){
- //获取到数据库原有的库存
- String oldsl = dynaBean1.getStr("WLXX_SL");
- int oldslInt =Integer.parseInt(oldsl);
- // 获取到主键
- String id = dynaBean1.getStr("PRO_FLOW_WLXX_ID");
- // 更改库存:原来的+ excel导入的
- int nowvalue = (oldslInt + excelSl);
- dynaBean.setStr("PRO_FLOW_WLXX_ID",id);
- dynaBean.setStr("WLXX_SL",String.valueOf(nowvalue));
- metaService.update(dynaBean);
- }
- }else {
- if(Strings.isNullOrEmpty(xydm) || xydm.equalsIgnoreCase("<系统自动生成>")){
- //如果是插入新数据,有编号自动生成情况的,获取自动生成字段的编号
- String wulabm = commonService.buildCode("WLXX_WLDM", "PRO_FLOW_WLXX", dynaBean);
- dynaBean.setStr("WLXX_WLDM",wulabm);
- }
- metaService.insert(dynaBean);
- }
- }
- return returnVo;
- }
复制代码
|