Spring-MVC jsp导入excel到数据库

来源：互联网发布：mac浏览器打不开编辑：程序博客网时间：2024/06/06 01:16

被导入excel到数据库整了两天，终于搞定了，分享出来给大家，希望有些帮组，肯定有更好的方法，希望大神指教：

前台jsp：

提交之后到controller

/**
* 导入数据
* @throws IOException
*/
@RequestMapping("importData")
public ModelAndView importData(ModelMap modelMap,
HttpServletRequest request,
@RequestParam("uploadfile") CommonsMultipartFile file,
@RequestParam("ModelExpId") String id) throws IOException{
boolean flsg=this.costModelServices.impData(request,file,id);
return new ModelAndView("proj/assistant/portCostModel/portCostModel");
}

然后重要的services：

/**
* 导入数据
* 拼装sql，形成String[] 进行批量更新sql
*/
@Override
public boolean impData(HttpServletRequest request,
CommonsMultipartFile file, String id) {
if (!file.isEmpty()) {
UserBean user=(UserBean)request.getSession().getAttribute("USERBEAN");
String userId=user.getCode();//当前登录人编码
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
String date=formatter.format(new Date());
try{
InputStream fis =file.getInputStream();
Workbook workbook = WorkbookFactory.create(fis);
int sheetQuantity = workbook.getNumberOfSheets();//获取多个sheet
Sheet sheet = workbook.getSheetAt(0);/** 得到第一个shell */
String[] resultSql=new String[sheet.getLastRowNum()];
for(int i=1;i<sheet.getLastRowNum()+1;i++){
Row row1 = sheet.getRow(i);
String areaType=""; String buildSence="";
String buildType=""; String buildProp="";
String joinType=""; String splitType="";
String bulid_area=""; String lay_type="";
String sect_cost="";
StringBuffer sql=new StringBuffer();
sql.append("update pc.T_PORT_COST_INFO ");
for(int j=19;j>0;j=j-2){
Cell cell = row1.getCell(j);
if(cell!=null){
cell.setCellType(Cell.CELL_TYPE_STRING);
if(j==1){
areaType=cell.getStringCellValue().toString();
sql.append(" and area_type='"+areaType+"' ");
}
if(j==3){
buildSence=cell.getStringCellValue().toString();
sql.append(" and build_scene= '"+buildSence+"' ");}
if(j==5){
buildType=cell.getStringCellValue().toString();
sql.append("and bulid_type='"+buildType+"' ");
}
if(j==7){
buildProp=cell.getStringCellValue().toString();
sql.append("and bulid_prop='"+buildProp+"' ");
}
if(j==9){
joinType=cell.getStringCellValue().toString();
sql.append("and join_type='"+joinType+"' ");
}
if(j==11){
splitType=cell.getStringCellValue().toString();
sql.append("and split_type='"+splitType+"' ");
}
if(j==13){
bulid_area=cell.getStringCellValue().toString();
sql.append("and bulid_area='"+bulid_area+"' ");
}
if(j==15){
lay_type=cell.getStringCellValue().toString();
sql.append("and lay_type='"+lay_type+"' ");
}
if(j==17){
sect_cost=cell.getStringCellValue().toString();
sql.append("set sect_cost='"+sect_cost+"',last_update_by='"+userId+"',last_update_date=to_date('"+date+"','yyyy-mm-dd')

where port_model_id='"+id+"' ");

}
System.out.print(cell.getRowIndex()+"行"+cell.getColumnIndex()+"列:");
System.out.println(",单元格是字符串，值是： " + cell.getStringCellValue().toString());
}
}
resultSql[i-1]=sql.toString();
}
// System.out.println(resultSql);
this.portCostModelDao.impDta(resultSql,id);//执行dao层里面的批量处理sql
}catch(Exception e) {
e.printStackTrace();
}
}
return true;
}

接下来就是dao层运行这个 sql数组了；

0 0