一、java利用poi实现导入导出excel表格demo1.引入依赖
<dependency> <groupid>org.apache.poi</groupid> <artifactid>poi-ooxml</artifactid> <version>4.1.2</version></dependency>
2.导入demo
2.1 controller层
/** * excel导入 */ @postmapping("/import") public result userimport2(@requestparam("file") multipartfile file) throws exception{ result result=userservice.userimportexcel(file); return result; }
2.2 service实现类层
public result userimportexcel(multipartfile file){ try { inputstream inputstream = file.getinputstream(); xssfworkbook sheets = new xssfworkbook(inputstream); //获取表单sheet 第一个 xssfsheet sheetat = sheets.getsheetat(0); //获取第一行 int firstrownum = sheetat.getfirstrownum(); //最后一行 int lastrownum = sheetat.getlastrownum(); //存入数据集合 list<user> users=new arraylist<>(); //遍历数据 for(int i=firstrownum+1;i<lastrownum+1;i++){ xssfrow row = sheetat.getrow(i); if(row!=null){ /* //获取第一行的第一列 int firstcellnum = row.getfirstcellnum(); //获取第一行的最后列 short lastcellnum = row.getlastcellnum(); for (int j=firstcellnum;j<lastcellnum+1;j++){ //放入集合中需要可以用这种方法 string cellvalue = getvalue(row.getcell(firstcellnum)); }*/ //这里我就直接赋值 user user = new user(); user.setuname(row.getcell(0).getstringcellvalue()); user.setupassword(row.getcell(1).getstringcellvalue()); user.setusex(row.getcell(2).getstringcellvalue()); user.setrole(row.getcell(3).getstringcellvalue()); user.setulove((int) row.getcell(4).getnumericcellvalue()); user.setuphoto(row.getcell(5).getstringcellvalue()); user.setuaddress(row.getcell(6).getstringcellvalue()); users.add(user); } } //保存数据 savebatch(users); return result.success(); }catch (exception e){ e.printstacktrace(); log.info("error:{}",e); } return result.error("300","导入失败");}/** * 判断值的类型 */public string getvalue(hssfcell cell) { if(cell==null){ return ""; } string cellvalue= ""; try { decimalformat df=new decimalformat("0.00"); if(cell.getcelltype()== celltype.numeric){ //日期时间转换 if(hssfdateutil.iscelldateformatted(cell)){ cellvalue=dateformatutils.format(cell.getdatecellvalue(),"yyyy-mm-dd"); }else{ numberformat instance = numberformat.getinstance(); cellvalue=instance.format(cell.getnumericcellvalue()).replace(",",""); } }else if(cell.getcelltype() == celltype.string){ //字符串 cellvalue=cell.getstringcellvalue(); }else if(cell.getcelltype() == celltype.boolean){ //boolean cellvalue= string.valueof(cell.getbooleancellvalue()); }else if(cell.getcelltype() == celltype.error){ //错误 }else if(cell.getcelltype() == celltype.formula){ //转换公式 保留两位 cellvalue=df.format(cell.getnumericcellvalue()); }else{ cellvalue=null; } } catch (exception e) { e.printstacktrace(); cellvalue="-1"; } return cellvalue;}
3.导出demo
3.1 controller层
/** * 导出 * @param response * @return * @throws exception */@getmapping("/export")public result userexport2(httpservletresponse response) throws exception{ result result=userservice.userexportexcel(response); return result;}
3.2 service实现类
public result userexportexcel(httpservletresponse response) { try { //创建excel xssfworkbook sheets = new xssfworkbook(); //创建行 xssfsheet sheet = sheets.createsheet("用户信息"); //格式设置 xssfcellstyle cellstyle = sheets.createcellstyle(); //横向居中 cellstyle.setalignment(horizontalalignment.center); //创建单元格第一列 xssfrow row = sheet.createrow(0); //表头 this.titleexcel(row,cellstyle); //查询全部的用户数据 mybatis-plus list<user> list = list(); //遍历设置值 for(int i=0;i<list.size();i++){ xssfrow rows = sheet.createrow(i+1); user user=list.get(i); //表格里赋值 this.titleexcelvalue(user,rows,cellstyle); } //设置浏览器响应格式 response.setcontenttype("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); string filname= urlencoder.encode("用户信息","utf-8"); response.setheader("content-disposition","attachment;filename="+filname+".xls"); servletoutputstream outputstream=response.getoutputstream(); sheets.write(outputstream); outputstream.close(); sheets.close(); return result.success(); }catch (exception e){ e.printstacktrace(); log.info("error:{}",e); } return result.error("300","导出失败");}/***表格里赋值**/public void titleexcelvalue(user user, xssfrow row,xssfcellstyle cellstyle) { xssfcell cellid = row.createcell(0); cellid.setcellvalue(user.getuid()); cellid.setcellstyle(cellstyle); xssfcell cellusername = row.createcell(1); cellusername.setcellvalue(user.getuname()); cellusername.setcellstyle(cellstyle); xssfcell cellpassword = row.createcell(2); cellpassword.setcellvalue(user.getupassword()); cellpassword.setcellstyle(cellstyle); xssfcell cellsex = row.createcell(3); cellsex.setcellvalue(user.getusex()); cellsex.setcellstyle(cellstyle); xssfcell cellrole = row.createcell(4); cellrole.setcellvalue(user.getrole()); cellrole.setcellstyle(cellstyle); xssfcell celllovevalue = row.createcell(5); celllovevalue.setcellvalue(user.getrole()); celllovevalue.setcellstyle(cellstyle); xssfcell cellphone = row.createcell(6); cellphone.setcellvalue(user.getuphoto()); cellphone.setcellstyle(cellstyle); xssfcell celladdress = row.createcell(7); celladdress.setcellvalue(user.getuaddress()); celladdress.setcellstyle(cellstyle);}/** 表头**/public void titleexcel(xssfrow row,xssfcellstyle cellstyle){ xssfcell cellid = row.createcell(0); cellid.setcellvalue("用户id"); cellid.setcellstyle(cellstyle); xssfcell cellusername = row.createcell(1); cellusername.setcellvalue("用户名"); cellusername.setcellstyle(cellstyle); xssfcell cellpassword = row.createcell(2); cellpassword.setcellvalue("密码"); cellpassword.setcellstyle(cellstyle); xssfcell cellsex = row.createcell(3); cellsex.setcellvalue("性别"); cellsex.setcellstyle(cellstyle); xssfcell cellrole = row.createcell(4); cellrole.setcellvalue("角色"); cellrole.setcellstyle(cellstyle); xssfcell celllovevalue = row.createcell(5); celllovevalue.setcellvalue("爱心值"); celllovevalue.setcellstyle(cellstyle); xssfcell cellphone = row.createcell(6); cellphone.setcellvalue("电话号码"); cellphone.setcellstyle(cellstyle); xssfcell celladdress = row.createcell(7); celladdress.setcellvalue("地址"); celladdress.setcellstyle(cellstyle);}
二、hutool工具类封装方法导出导入excel1.引入依赖
把poi封装到工具类方法里面
<!-- hutool --> <dependency> <groupid>cn.hutool</groupid> <artifactid>hutool-all</artifactid> <version>5.7.20</version> </dependency> <dependency> <groupid>org.apache.poi</groupid> <artifactid>poi-ooxml</artifactid> <version>4.1.2</version></dependency>
2.导入demo
/** * excel导入 */@postmapping("/import")public result userimport(@requestparam("file") multipartfile file) throws exception{ system.out.println(file.tostring()); //inputstream inputstream = multipartfile.getinputstream(); inputstream inputstream = file.getinputstream(); excelreader reader = excelutil.getreader(inputstream); //读取表的内容 list<list<object>> list = reader.read(1); list<user> users = new arraylist<>(); for(list<object> row : list){ user user = new user(); user.setuname(row.get(0).tostring()); user.setupassword(row.get(1).tostring()); user.setusex(row.get(2).tostring()); user.setrole(row.get(3).tostring()); user.setulove(integer.valueof(row.get(4).tostring())); user.setuphoto(row.get(5).tostring()); user.setuaddress(row.get(6).tostring()); users.add(user); } //批量插入用户信息 mybatis-plus userservice.savebatch(users); return result.success(); }
3.导出demo
/** * excel导出 方法一 */ @getmapping("/export") public result userexport(httpservletresponse response) throws exception{ //查询全部的用户数据 list<user> list = userservice.list(); //在内存里做操作，保存到浏览器 excelwriter writer = excelutil.getwriter(true); //自定义标题别名 writer.addheaderalias("uname","用户名"); writer.addheaderalias("upassword","密码"); writer.addheaderalias("usex","性别"); writer.addheaderalias("role","角色"); writer.addheaderalias("ulove","爱心值"); writer.addheaderalias("uphoto","电话号码"); writer.addheaderalias("uaddress","地址"); //一次性写出list内的对象的excel，使用默认样式，强制输出标题 writer.write(list,true); //设置浏览器响应格式 response.setcontenttype("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); string filname= urlencoder.encode("用户信息","utf-8"); response.setheader("content-disposition","attachment;filename="+filname+".xls"); servletoutputstream outputstream=response.getoutputstream(); writer.flush(outputstream,true); outputstream.close(); writer.close(); return result.success(); }
以上就是java怎么利用poi实现导入导出excel表格的详细内容。