一.配置文件名称、路径、内容:
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"> <property name="defaultEncoding" value="utf-8"></property> <property name="maxUploadSize" value="104857600"></property><!-- 最大上传文件大小:100M --> <property name="maxInMemorySize" value="10240"></property> </bean>
二.controller
@RequestMapping(value = "/importAccountManager") @ResponseBody public ModelAndView importAccountManager(HttpServletRequest request, HttpServletResponse response, @RequestParam("file") CommonsMultipartFile file) { ModelAndView mav = new ModelAndView(new MappingJackson2JsonView()); try (InputStream inputXls = file.getInputStream(); InputStream inputXML = new BufferedInputStream(getClass().getClassLoader().getResourceAsStream(ACCOUNT_TEMPLATE_PATH));) { XLSReader mainReader = ReaderBuilder.buildFromXML(inputXML); List<AccountManagerModel> dataList = new ArrayList<>(); Map<String, Object> params = new HashMap<>(); params.put("dataList", dataList); mainReader.read(inputXls, params); String message = accountManagerService.importAccountManager(dataList); if(StringUtils.isNotBlank(message)) { mav.addObject("success", false); } else { mav.addObject("success", true); } mav.addObject("message", message); } catch (Throwable e) { LOGGER.error("importAccountManager causes inner exception:", e); mav.addObject("success", false); mav.addObject("message", e.getMessage()); } return mav; }
三.service
//serviceImpl @Override public String importAccountManager(List<AccountManagerModel> accountList) throws Exception { List<OrgModel> orgList = orgMapper.queryOrg(); String message = validate(accountList, orgList); if(message == null) { for(AccountManagerModel account : accountList) { String consNo = account.getConsNo(); AccountManagerModel oldAccount = accountManagerMapper.queryByConsNo(consNo); if(oldAccount == null) { insertAccountManager(account); } else { editAccountManager(account, oldAccount); } } } return message; } //interface /** * 导入客户数据 * * @param accountList * @return * @throws Exception */ public String importAccountManager(List<AccountManagerModel> accountList) throws Exception;
四.mapper
//interface /** * 通过客户号查询企业 * * @param consNo * @return * @throws throws Exception */ public AccountManagerModel queryByConsNo(String consNo) throws Exception; //mapper映射xml
<select id="queryByConsNo" parameterType="java.lang.String" resultMap="AccountManagerResultMap"> select a.cons_id, a.cons_no, a.cons_name, a.cons_address, a.legal_person, a.legal_person_phone, a.bill_person, a.bill_person_phone, a.electric_person, a.electric_person_phone, a.entrusted_person, a.entrusted_person_phone, a.cons_state, a.org_no, b.org_name from be_cons_info a, o_org b where a.org_no = b.org_no and a.cons_no = #{consNo} </select>
五.Html
<div class="tool_wrapper"> <button class="ui-btn ui-btn-primary" onclick="addAccountManager();">新增</button> <button class="ui-btn ui-btn-primary left20" onclick="editAccountManager();">修改</button> <!-- <button class="ui-btn ui-btn-primary left20" onclick="cancelAccountManager();">注销</button> --> <button class="ui-btn ui-btn-primary left20 fileinput-button"> <span>导入Excel</span> <input id="fileupload" type="file" name="file" accept="application/vnd.ms-excel"> </button> <button class="ui-btn ui-btn-primary left20" onclick="exportXls()">导出Excel</button> <button class="ui-btn ui-btn-primary left20" onclick="downloadXlsTemplate()">下载模板</button> </div>
六.js
function bindFileUploadEvent() { $('#fileupload').fileupload({ url: basePath + 'accountManager/importAccountManager', type: 'post', dataType: 'json', beforeSend: function(xhr){ xhr.setRequestHeader(header, token); }, send: function(e, data) { var file = data.files[0]; var fileName = file.name; var fileSize = file.size; var errorMsg = "请选择Excel文件!"; var pass = validateFileName(fileName, ".xls"); if(pass && fileSize >= 10485760) { pass = false; errorMsg = "选择的Excel文件须小于10M!"; } if(!pass) { $('#fileupload').val(''); errorDialog(errorMsg); } else { if(parent.progress) { parent.progress.show(); } } return pass; }, done: function(e, data) { var result = data.result; if(result.success) { queryAccountManager(); succDialog("客户数据导入成功!"); } else { errorDialog(result.message); } }, fail: function(e, data) { if(data.textStatus != null) { errorDialog("系统发生故障,请联系系统管理员或稍后重试!"); } }, always: function(e, data) { if(parent.progress) { parent.progress.hide(); } } }); }