excel-从excel导入数据到数据库

2019-11-08 02:04:23

字体：大中小

来源：转载

供稿：网友

0.需要使用jxl.jar 这个包

1.页面是

function exportcomp(meetingId){ //导入参会人员 url = '$appServer/meeting/uploadPage.htm?meetingId='+meetingId; window.open(url,'导入','status,top=250,left=300,height=200,width=500,scrollbars=yes');} <a href="javascript:void(0)" onclick='exportcomp($!meeting.id)' style="margin-right:245px;">导入参会人员</a>

2.然后是上传Excel到服务器的页面

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=gb2312"><title>文件导入</title><link href="$appServer/CSS/main.css" rel="stylesheet" type="text/css" /><script language="Javascript"> function checkfile(){ var path = document.uploadform.path.value; if(path==''){ alert('请选择文件'); return false; } var i = path.lastIndexOf('.'); if(path.substring(i+1,path.length)!='xls'){ alert('只能上传excel文件'); return false; } document.getElementById('waitbar').style.display = ""; return true; } function init(){ var t='$!ms'; if(t=='Y'){ alert('导入成功！'); var url = "$appServer/meeting/meetingDetail.htm?id=$!meetingId"; window.opener.location.href=url; window.close(); } } </script></head><body onload="init()"><br /><form name="uploadform" method="post" action="$appServer/meeting/upload.htm" enctype="multipart/form-data" onsubmit="return checkfile()"><div style="margin-top:15px; margin:0 auto; width:500px;"> <fieldset style=" width:400px; padding:10px;padding-top:5px;text-align:left; border:1px solid #ae0001; "> <legend style="width:50px; color:#cb2121; font-weight:bolder; ">信息上传</legend><div> <div class="wai"> <label for="fbwh" class="tt1 tt">请选择附件：</label> <div class="edit_r"> <input type="file" name="path" class=" sc-td" /></div> </div> <div class="wai"></div> <table border="1" width="400" align="center" cellspacing="0" cellpadding="4" style="border-collapse: collapse" height="80"style="display:none;" id="waitbar"> <tr> <td style="font-size:12px;line-height:200%" align=center>企业导入中... <marquee style="border:1px solid #000000" direction="right" width="340" scrollamount="5" scrolldelay="10" bgcolor="#fcf5f5"> <table cellspacing="1" cellpadding="0"> <tr height=8> <td bgcolor=#3399FF width=8></td> <td></td> <td bgcolor=#3399FF width=8></td> <td></td> <td bgcolor=#3399FF width=8></td> <td></td> <td bgcolor=#3399FF width=8></td> <td></td> <td bgcolor=#3399FF width=8></td> <td></td> </tr></table></marquee></td> </tr></table></div> <div class="edit_botton"> <input type="hidden" name="meetingId" value="$!meetingId"/> <input type="submit" value="上传"><input type="reset" value="重置"></div> </fieldset></div> </div> </fieldset></div></form></body></html>

3.然后是

@RequestMapping(value = "/upload.htm") public ModelAndView upload(Long meetingId,HttpServletRequest request,Model model,UserAgent user) throws Exception { MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; MultipartFile multipartFile = multipartRequest.getFile("path"); String path=uploadService.UploadCompFile(multipartFile); List list=ExcelUtil.readexToMeetingNotice(new File(path)); for(int i=0;i<list.size();i++){ System.out.PRintln("list"+list.get(i).toString()); MeetingPerson s = (MeetingPerson) list.get(i); s.setMeetId(meetingId); s.setOwner(user.getId()); meetingPersonService.addMeetingPerson(s);//这个就是简单地循环插入就不介绍了 } model.addAttribute("ms", "Y"); model.addAttribute("meetingId", meetingId); return null; } public String UploadCompFile(MultipartFile multipartFile) throws Exception { checkUploadFile(multipartFile); int position = multipartFile.getOriginalFilename().lastIndexOf("."); String suffix = multipartFile.getOriginalFilename().substring(position); String filename = DateUtils.llFormat(new Date())+suffix; try { File f = new File(rootPath+filename); if (!f.exists()) { f.mkdirs(); } multipartFile.transferTo(f); } catch (IllegalStateException e) { logger.error("保存文件失败", e); throw new Exception("保存文件失败"); } catch (IOException e) { logger.error("保存文件失败", e); throw new Exception("保存文件失败"); } return rootPath+filename; }public class ExcelUtil { public static List readex(File file) { Workbook wb = null; try { // 构造Workbook（工作薄）对象 wb = Workbook.getWorkbook(file); } catch (BiffException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } if (wb == null) return null; List<Complist> list = new ArrayList<Complist>(); int sheernum = wb.getNumberOfSheets(); for (int i = 0; i < sheernum; i++) { Sheet sheet = wb.getSheet(i); //Sheet sheet = wb.getSheet(0);// 只取第一个excel表格 Complist complist = new Complist(); complist.setAreaid(0l); List<Comp> temp = new ArrayList<Comp>(); // 得到当前工作表的行数 int rowNum = sheet.getRows(); for (int j = 1; j < rowNum; j++) { Cell[] cells = sheet.getRow(j); if (cells != null && cells.length > 0) { String firstcell = cells[0].getContents().trim(); if (firstcell.equals("0")) {// 代码为0 String area = cells[1].getContents().trim(); Long id = getidbyarea(area); if (id != 0) {// 各地区 if (complist.getAreaid() != 0l) {// 不是第一个地区 complist.setList(temp); temp = new ArrayList<Comp>();// temp 清空 list.add(complist); complist = new Complist();// complist 清空 } complist.setAreaid(id);// 地区id } } else if (firstcell.equals("")) { // 无操作 } else if (firstcell.equals("代码")) { // 无操作 } else { Comp comp = new Comp(); comp.setCompcode(cells[0].getContents().trim());//公司代码 comp.setComp(cells[1].getContents().trim());// 公司名称 if(StringUtils.isNotBlank(cells[2].getContents().trim())){ comp.setMoney(Double.parseDouble(cells[2].getContents().trim()));// 金额 }else{ comp.setMoney(0.0);// 金额 }// if (cells.length!=3||cells[2].getContents().trim().equals("否")||cells[2].getContents()==null||cells[2].getContents().equals("")) {// comp.setStep(1);// 不应诉// } else if (cells[2].getContents().trim().equals("是")) {// comp.setStep(2);// 应诉// } temp.add(comp); } } } if (complist.getAreaid() != 0l) {// 不是空表格时将最后一个地区加入列表 complist.setList(temp); list.add(complist); } } // 最后关闭资源，释放内存 wb.close(); return list; } public static List readexToMeetingNotice(File file) { List<MeetingPerson> temp = new ArrayList<MeetingPerson>(); Workbook wb = null; try { // 构造Workbook（工作薄）对象 wb = Workbook.getWorkbook(file); } catch (BiffException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } if (wb == null) return null; List<Complist> list = new ArrayList<Complist>(); int sheernum = wb.getNumberOfSheets(); for (int i = 0; i < sheernum; i++) { Sheet sheet = wb.getSheet(i); //Sheet sheet = wb.getSheet(0);// 只取第一个excel表格 // 得到当前工作表的行数 int rowNum = sheet.getRows(); for (int j = 1; j < rowNum; j++) { Cell[] cells = sheet.getRow(j); //排除掉空行 if (cells != null && cells.length > 0) { String firstcell = cells[0].getContents().trim(); //说明是第一行提示姓名电话职务等说明行 if (firstcell.equals("姓名")) { // 无操作 } else if (firstcell.equals("")) { // 无操作 } else { MeetingPerson meetingPerson = new MeetingPerson(); meetingPerson.setName(cells[0].getContents().trim()); meetingPerson.setDepName(cells[1].getContents().trim()); meetingPerson.setPost(cells[2].getContents().trim()); meetingPerson.setPhone(cells[3].getContents().trim()); meetingPerson.setMobile(cells[4].getContents().trim()); meetingPerson.setEmail(cells[5].getContents().trim()); temp.add(meetingPerson); } } } } // 最后关闭资源，释放内存 wb.close(); return temp; } public static void main(String args[]) { List list = readex(new File("D://1.xls")); System.out.println("size:" + list.size()); for (int i = 0; i < list.size(); i++) { Complist comp = (Complist) list.get(i); System.out.println(comp.getAreaid()); } }}

上一篇：截图工具——涂鸦

下一篇：LeetCode(43) Multiply Strings