java 使用poi 导入Excel数据到数据库的步骤
程序员文章站
2022-07-05 20:09:02
由于我个人电脑装的excel是2016版本的,所以这地方我使用了xssf 方式导入 。1 先手要制定一个excel 模板 把模板放入javaweb工程的某一个目录下如图:2 模板建好了后,先实现模板下...
由于我个人电脑装的excel是2016版本的,所以这地方我使用了xssf 方式导入 。
1 先手要制定一个excel 模板 把模板放入javaweb工程的某一个目录下如图:
2 模板建好了后,先实现模板下载功能 下面是页面jsp代码在这里只贴出部分代码
<!-- excel 导入小模块窗口 --> <div id="importbox" class="" style="display: none;"> <form id="importform" action="<%=basepath%>book/dishes/backstageversion/list!importexcel" method="post" enctype="multipart/form-data" class="form-search" style="padding-left:20px;text-align:center;" onsubmit="loading('正在导入,请稍等...');"><br/> <input id="uploadfile" name="file" type="file" style="width:330px"/><br/><br/> <input id="btnimportsubmit" class="btn btn-primary" type="submit" value=" 导 入 "/> <input type="hidden" id="importcompanyid" name="importcompanyid" value=""/> <input type="hidden" id="importstallid" name="importstallid" value=""/> <a href="<%=basepath%>book/dishes/backstageversion/list!exportorder" rel="external nofollow" rel="external nofollow" >下载模板</a> </form> </div>
<!-- excel 导入小模块窗口 --> <div id="importbox" class="" style="display: none;"> <form id="importform" action="<%=basepath%>book/dishes/backstageversion/list!importexcel" method="post" enctype="multipart/form-data" class="form-search" style="padding-left:20px;text-align:center;" onsubmit="loading('正在导入,请稍等...');"><br/> <input id="uploadfile" name="file" type="file" style="width:330px"/><br/><br/> <input id="btnimportsubmit" class="btn btn-primary" type="submit" value=" 导 入 "/> <input type="hidden" id="importcompanyid" name="importcompanyid" value=""/> <input type="hidden" id="importstallid" name="importstallid" value=""/> <a href="<%=basepath%>book/dishes/backstageversion/list!exportorder" rel="external nofollow" rel="external nofollow" >下载模板</a> </form> </div>
下面是js
<!-- bootstrap --> <link href="<%=path %>/res/admin/css/bootstrap.min.css" rel="external nofollow" rel="stylesheet" type="text/css" /> <link href="<%=path %>/res/admin/css/xy_css.css" rel="external nofollow" rel="stylesheet" type="text/css"> <link href="<%=path %>/res/admin/css/font-awesome.min.css" rel="external nofollow" rel="stylesheet" type="text/css"> <script src="<%=path %>/res/admin/js/jquery.min.js"></script> <script src="<%=path %>/res/admin/js/bootstrap.min.js"></script> <link href="<%=path %>/res/admin/jquery-select2/3.4/select2.css" rel="external nofollow" rel="stylesheet" type="text/css" /> <script src="<%=path %>/res/admin/jquery-select2/3.4/select2.min.js"></script> <script src="<%=path %>/res/admin/jquery-select2/3.4/select2_locale_zh-cn.js"></script> <script type="text/javascript" src="<%=basepath%>res/admin/js/layer/layer.js"></script> <script type="text/javascript"> $(document).ready(function (){//加载页面时执行select2 $("select").select2(); //弹出导出窗口 $("#btnimport").click(function(){ var importstallid = $("#stallid option:selected").val(); var importcompanyid = $("#companyid option:selected").val(); $("#importcompanyid").val(importcompanyid); $("#importstallid").val(importstallid); if(importstallid==null || importstallid==""){ alert("请选择档口"); }else{ layer.open({ type: 1, skin: 'layui-layer-rim', //加上边框 area: ['600px', '350px'], //宽高 content: $('#importbox') }); } }); });
3 下面是后台代码action 类
一:下载模板代码
/** * 下载模板 * @throws ioexception */ public void exportorder() throws ioexception{ httpservletrequest request = servletactioncontext.getrequest(); httpservletresponse response = servletactioncontext.getresponse(); file file = null; inputstream inputstream = null; servletoutputstream out = null; try { request.setcharacterencoding("utf-8"); string realpath = servletactioncontext.getservletcontext().getrealpath("/"); file = new file(realpath+"web-inf/mailtemplate/dishes.xlsx"); inputstream = new fileinputstream(file); response.setcharacterencoding("utf-8"); response.setcontenttype("application/msexcel"); response.setheader("content-disposition", "attachment;filename=" + urlencoder.encode("菜品导入" + ".xlsx", "utf-8")); out = response.getoutputstream(); byte[] buffer = new byte[512]; // 缓冲区 int bytestoread = -1; // 通过循环将读入的excel文件的内容输出到浏览器中 while ((bytestoread = inputstream.read(buffer)) != -1) { out.write(buffer, 0, bytestoread); } out.flush(); } catch (exception e) { e.printstacktrace(); } finally { if (inputstream != null) inputstream.close(); if (out != null) out.close(); if (file != null) file.delete(); // 删除临时文件 } }
二: 导入代码
/** * 导入 * @throws ioexception */ public void importexcel() throws ioexception { list<dishes> disheslist = getdisheslist(file); if(disheslist !=null && disheslist.size()>0){ for(dishes dishes : disheslist){ targetservice.add(dishes); } } string basepath = servletactioncontext.getservletcontext().getcontextpath(); servletactioncontext.getresponse().sendredirect(basepath + "/book/dishes/backstageversion/list"); } /** * 读取excel数据 * @param filepath * @return list * @throws ioexception */ private list<dishes> getdisheslist(string filepath) throws ioexception { xssfworkbook workbook= null; inputstream is = new fileinputstream(filepath); try { workbook = new xssfworkbook(is); } catch (exception e) { e.printstacktrace(); } dishes dishes=null; list<dishes> disheslist = new arraylist<dishes>(); //循环工作表sheet //list<xssfpicturedata> pictureslist = getpictureslist(workbook);//获取所有图片 for(int numshett = 0;numshett<workbook.getnumberofsheets();numshett++){ xssfsheet sheet = workbook.getsheetat(numshett); //调用获取图片 map<string, picturedata> picturedatamap = getpicturedatamap(sheet, workbook); if(sheet==null){ continue; } //循环row for(int rownum=1;rownum<=sheet.getlastrownum();rownum++){ row row = sheet.getrow(rownum); if(row==null){ continue; } dishes = new dishes(); //cell cell dishesname = row.getcell(0); if(dishesname==null){ continue; } dishes.setname(getvalue(dishesname));//菜品名称 cell price = row.getcell(1); if(price==null){ continue; } dishes.setprice(double.parsedouble(getvalue(price)));//优惠价格 cell oldprice = row.getcell(2); if(oldprice==null){ continue; } dishes.setoldprice(double.parsedouble(getvalue(oldprice)));//原价格 cell summary = row.getcell(3); if(summary==null){ continue; } dishes.setsummary(getvalue(summary));//菜品描述 cell online = row.getcell(4); if(online==null){ continue; } dishes.setonline(integer.parseint(getvalue(online)));//是否上下架 cell packcharge = row.getcell(5); if(packcharge==null){ continue; } dishes.setpackcharge(double.parsedouble(getvalue(packcharge)));//打包费 cell stocknumber = row.getcell(6); if(stocknumber==null){//库存为必填 continue; } dishes.setstocknumber(integer.parseint(getvalue(stocknumber)));//每餐库存 cell immediatestock = row.getcell(7); if(immediatestock==null){//当前库存 continue; } dishes.setimmediatestock(integer.parseint(getvalue(immediatestock)));//当前库存 cell purchaselimit = row.getcell(8); if(purchaselimit==null){ continue; } dishes.setpurchaselimit(integer.parseint(getvalue(purchaselimit)));//限购数量 cell restrictiontype = row.getcell(9); if(restrictiontype==null){ continue; } dishes.setrestrictiontype(integer.parseint(getvalue(restrictiontype)));//限购方式 cell sort = row.getcell(10); if(sort==null){ continue; } dishes.setsort(integer.parseint(getvalue(sort)));//排序 cell contents = row.getcell(11); if(contents==null){ continue; } dishes.setcontents(getvalue(contents));//菜品详情 dishes.setcreatetime(new date()); company company = companyservice.load(importcompanyid); stall stall = stallservice.load(importstallid); dishes.setcompany(company); dishes.setstall(stall); //set 图片 picturedata picturedata = picturedatamap.get(rownum+""); if(picturedata !=null){ string upimageurl = upimage(picturedata.getdata()); dishes.setimage(upimageurl); } disheslist.add(dishes); } } return disheslist; } /** * 得到excel表中的值 * @param hssfcell * @return string */ @suppresswarnings("unused") private string getvalue(cell cell){ decimalformat df = new decimalformat("###################.###########"); if(cell.getcelltype()==cell.cell_type_boolean){ return string.valueof(cell.getbooleancellvalue()); } if(cell.getcelltype()==cell.cell_type_numeric){ return string.valueof(df.format(cell.getnumericcellvalue())); }else{ return string.valueof(cell.getstringcellvalue()); } }
4 get set 方法
private string file; private long importcompanyid; private long importstallid;
public string getfile() { return file; } public void setfile(string file) { this.file = file; } public long getimportcompanyid() { return importcompanyid; } public void setimportcompanyid(long importcompanyid) { this.importcompanyid = importcompanyid; } public long getimportstallid() { return importstallid; } public void setimportstallid(long importstallid) { this.importstallid = importstallid; }
公司需求改变要增加导入图片到又拍云服务器,所以下面增加读取excel图片
/** * 读取excel 中图片 * @param sheet * @param workbook * @return */ private map<string, picturedata> getpicturedatamap(xssfsheet sheet,xssfworkbook workbook){ map<string, picturedata> map = new hashmap<string,picturedata>(); for(poixmldocumentpart dr : sheet.getrelations()){ if(dr instanceof xssfdrawing){ xssfdrawing drawing = (xssfdrawing) dr; list<xssfshape> shapeslist = drawing.getshapes(); if(shapeslist !=null && shapeslist.size()>0){ for(xssfshape shape : shapeslist){ xssfpicture pic = (xssfpicture) shape; xssfclientanchor anchor = pic.getpreferredsize(); ctmarker ctmarker = anchor.getfrom(); string picindex = ctmarker.getrow()+""; map.put(picindex, pic.getpicturedata()); } } } } return map; }
/** * 上传图片到又拍云 * @param bytes * @return */ private string upimage(byte[] bytes){ string filename = uuid.randomuuid().tostring() + ".jpg"; string uploadurl = upyunclient.upload(filename, bytes); return uploadurl; }
注意:请用poi jar 3.9 版本 不然读取图片代码会报错
以上就是java 使用poi 导入excel 数据到数据库的步骤的详细内容,更多关于java 导入excel 数据到数据库的资料请关注其它相关文章!
上一篇: 南方小年怎么过
下一篇: 微信小程序 监听手势滑动切换页面实例详解