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

由于我个人电脑装的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 数据到数据库的资料请关注其它相关文章！