Excel表格导入Mysql数据库,一行存入多条数据的前后台完整实现思路(使用mybatis框架)
现在有一张Excel表格:
存入数据库时需要这样存放:
现在需要将Excel表格做处理,将每一行拆分成多条数据存入数据库。
1.首先在前台jsp页面画一个按钮:,加入点击事件:
<td style="vertical-align:top;padding-left:2px;"> <a class="btn btn-light btn-xs" onclick="fromExcel();" title="从 EXCEL导入"> </a> </td>
定义对应的URL:
//打开上传excel页面 function fromExcel(){ top.jzts(); var diag = new top.Dialog(); diag.Drag=true; diag.Title ="EXCEL 导入到数据库"; //定义对应的URL地址 diag.URL = '<%=basePath%>man/goUploadManExcel.do'; diag.Width = 300; diag.Height = 150; diag.CancelEvent = function(){ //关闭事件 if(diag.innerFrame.contentWindow.document.getElementById('zhongxin').style.display == 'none'){ if('${page.currentPage}' == '0'){ top.jzts(); setTimeout("self.location.reload()",100); }else{ nextPage(${page.currentPage}); } } diag.close(); }; diag.show(); }
2.在对应的Controller里返回一个上传页面:
/**打开上传EXCEL页面 * @return * @throws Exception */ @RequestMapping(value="/goUploadManExcel") public ModelAndView goUploadExcel()throws Exception{ ModelAndView mv = this.getModelAndView(); mv.setViewName("manpower/man/man_uploadexcel"); return mv; }
对应的文件目录:
返回一个页面:
这里只写关键代码:
<form action="man/readManExcel.do" name="Form" id="Form" method="post" enctype="multipart/form-data"> ... <a class="btn btn-mini btn-primary" onclick="save();">导入</a> <a class="btn btn-mini btn-danger" onclick="top.Dialog.close();">取消</a> <a class="btn btn-mini btn-success" onclick="window.location.href='<%=basePath%>/man/downManExcel.do'">下载模版</a> </form>
点击导入时提交此form表单:
$("#Form").submit();
在后台拦截URL,进行处理(这里是楼主主要想表达的对Excel表格一行数据拆成多行的处理):
/**从EXCEL导入到数据库 * @param file * @return * @throws Exception */ @RequestMapping(value="/readManExcel") public ModelAndView readExcel( @RequestParam(value="excel",required=false) MultipartFile file ) throws Exception{ FHLOG.save(Jurisdiction.getUsername(), "从EXCEL导入到数据库"); ModelAndView mv = this.getModelAndView(); PageData pd = new PageData(); PageData pdman = new PageData(); if(!Jurisdiction.buttonJurisdiction(menuUrl, "add")){return null;} if (null != file && !file.isEmpty()) { String filePath = PathUtil.getClasspath() + Const.FILEPATHFILE; //文件上传路径 String fileName = FileUpload.fileUp(file, filePath, "manexcel"); //执行上传 List<PageData> listPd = (List)ObjectExcelRead.readExcel(filePath, fileName, 0, 0, 0); //执行读EXCEL操作,读出的数据导入List 2:从第1行开始;0:从第A列开始;0:第0个sheet //存放每行月份及实际投入 List<String> monthList = new ArrayList<String>(); List<String> actualList = new ArrayList<String>(); //遍历表格每一行 for(int i=1;i<listPd.size();i++){ //从第四列开始,列头5个月 for(int j=3;j<8;j++){ String actual_input = listPd.get(i).getString("var"+ j); //存入有投入的月份及实际投入 if(actual_input.length() != 0){ monthList.add(listPd.get(0).getString("var"+ j)); actualList.add(actual_input); } } //生成每行数据 for(int z=0;z<actualList.size();z++){ pd.put("MAN_ID", this.get32UUID());//ID pd.put("MAN_NAME", listPd.get(i).getString("var0")); pd.put("MAN_NUMBER", listPd.get(i).getString("var1")); pd.put("PRJECT_NAME",listPd.get(i).getString("var2")); pd.put("PUT_MONTH",monthList.get(z)); pd.put("ACTUAL_INPUT",actualList.get(z)); manService.saveMan(pd); } monthList.removeAll(monthList); actualList.removeAll(actualList); } /*存入数据库操作======================================*/ mv.addObject("msg","success"); } mv.setViewName("save_result"); return mv; }
对应的service:
@Override public void saveMan(PageData pd) throws Exception { dao.save("ManMapper.saveMan", pd); }
到对应的ManMapper.xml里写sql语句:
<!--表名--> <sql id="manpowertableName"> man_power </sql> <!--字段--> <sql id="ManpowerField"> MAN_ID, MAN_NAME, MAN_NUMBER, PRJECT_NAME, PUT_MONTH, ACTUAL_INPUT </sql> <sql id="ManpowerFieldValue"> #{MAN_ID}, #{MAN_NAME}, #{MAN_NUMBER}, #{PRJECT_NAME}, #{PUT_MONTH}, #{ACTUAL_INPUT} </sql> <!-- 新增--> <insert id="saveMan" parameterType="pd"> insert into <include refid="manpowertableName"></include> ( <include refid="ManpowerField"></include> ) values ( <include refid="ManpowerFieldValue"></include> ) </insert>
重启Tomcat,数据表就在数据库里生成了。
<!--表名-->