現(xiàn)在有一張Excel表格:
存入數(shù)據(jù)庫(kù)時(shí)需要這樣存放：
現(xiàn)在需要將Excel表格做處理，將每一行拆分成多條數(shù)據(jù)存入數(shù)據(jù)庫(kù)。
1.首先在前臺(tái)jsp頁(yè)面畫(huà)一個(gè)按鈕：，加入點(diǎn)擊事件：
<td style="vertical-align:top;padding-left:2px;"> <a class="btn btn-light btn-xs" onclick="fromExcel;" title="從 EXCEL導(dǎo)入"> </a> </td>
定義對(duì)應(yīng)的URL：
//打開(kāi)上傳excel頁(yè)面 function fromExcel{ top.jzts; var diag = new top.Dialog; diag.Drag=true; diag.Title ="EXCEL 導(dǎo)入到數(shù)據(jù)庫(kù)"; //定義對(duì)應(yīng)的URL地址 diag.URL = '<%=basePath%>man/goUploadManExcel.do'; diag.Width = 300; diag.Height = 150; diag.CancelEvent = function{ //關(guān)閉事件 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.在對(duì)應(yīng)的Controller里返回一個(gè)上傳頁(yè)面：
/**打開(kāi)上傳EXCEL頁(yè)面 * @return * @throws Exception */ @RequestMapping(value="/goUploadManExcel") public ModelAndView goUploadExcelthrows Exception{ ModelAndView mv = this.getModelAndView; mv.setViewName("manpower/man/man_uploadexcel"); return mv; }
對(duì)應(yīng)的文件目錄：
返回一個(gè)頁(yè)面：
這里只寫(xiě)關(guān)鍵代碼：
<form action="man/readManExcel.do" name="Form" id="Form" method="post" enctype="multipart/form-data"> ... <a class="btn btn-mini btn-primary" onclick="save;">導(dǎo)入</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>
點(diǎn)擊導(dǎo)入時(shí)提交此form表單:
$("#Form").submit;
在后臺(tái)攔截URL，進(jìn)行處理（這里是樓主主要想表達(dá)的對(duì)Excel表格一行數(shù)據(jù)拆成多行的處理）：
/**從EXCEL導(dǎo)入到數(shù)據(jù)庫(kù) * @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導(dǎo)入到數(shù)據(jù)庫(kù)"); 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"); //執(zhí)行上傳 List<PageData> listPd = (List)ObjectExcelRead.readExcel(filePath, fileName, 0, 0, 0); //執(zhí)行讀EXCEL操作,讀出的數(shù)據(jù)導(dǎo)入List 2:從第1行開(kāi)始；0:從第A列開(kāi)始；0:第0個(gè)sheet //存放每行月份及實(shí)際投入 List<String> monthList = new ArrayList<String>; List<String> actualList = new ArrayList<String>; //遍歷表格每一行 for(int i=1;i<listPd.size;i++){ //從第四列開(kāi)始，列頭5個(gè)月 for(int j=3;j<8;j++){ String actual_input = listPd.get(i).getString("var"+ j); //存入有投入的月份及實(shí)際投入 if(actual_input.length != 0){ monthList.add(listPd.get(0).getString("var"+ j)); actualList.add(actual_input); } } //生成每行數(shù)據(jù) 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); } /*存入數(shù)據(jù)庫(kù)操作======================================*/ mv.addObject("msg","success"); } mv.setViewName("save_result"); return mv; }
對(duì)應(yīng)的service:
@Override public void saveMan(PageData pd) throws Exception { dao.save("ManMapper.saveMan", pd); }
到對(duì)應(yīng)的ManMapper.xml里寫(xiě)sql語(yǔ)句：
<!--表名--> <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，數(shù)據(jù)表就在數(shù)據(jù)庫(kù)里生成了。
<!--表名-->
