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