现在有一张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 goUploadExcelthrows 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，数据表就在数据库里生成了。

<!--表名-->