欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

java 使用poi 导入Excel数据到数据库的步骤

程序员文章站 2022-07-05 20:09:02
由于我个人电脑装的excel是2016版本的,所以这地方我使用了xssf 方式导入 。1 先手要制定一个excel 模板 把模板放入javaweb工程的某一个目录下如图:2 模板建好了后,先实现模板下...

由于我个人电脑装的excel是2016版本的,所以这地方我使用了xssf 方式导入 。

1 先手要制定一个excel 模板 把模板放入javaweb工程的某一个目录下如图:

java 使用poi 导入Excel数据到数据库的步骤

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 数据到数据库的资料请关注其它相关文章!