現在有一張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,數據表就在數據庫裡生成了。
<!--表名-->