java 使用poi 導入Excel數(shù)據(jù)到數(shù)據(jù)庫的步驟

更新時間：2020年12月14日 11:55:33 作者：阿若蜜意

這篇文章主要介紹了java 使用poi 導入Excel 數(shù)據(jù)到數(shù)據(jù)庫的步驟，幫助大家更好的理解和使用Java，感興趣的朋友可以了解下

由于我個人電腦裝的Excel是2016版本的，所以這地方我使用了XSSF 方式導入。

1 先手要制定一個Excel 模板把模板放入javaWeb工程的某一個目錄下如圖：

2 模板建好了后，先實現(xiàn)模板下載功能下面是頁面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 (){//加載頁面時執(zhí)行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]; // 緩沖區(qū)
   int bytesToRead = -1;
   // 通過循環(huán)將讀入的Excel文件的內(nèi)容輸出到瀏覽器中
   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數(shù)據(jù) 
  * @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>();
  //循環(huán)工作表sheet
  //List<XSSFPictureData> picturesList = getPicturesList(workBook);//獲取所有圖片
  for(int numShett = 0;numShett<workBook.getNumberOfSheets();numShett++){
   XSSFSheet sheet = workBook.getSheetAt(numShett);
             //調(diào)用獲取圖片             Map<String, PictureData> pictureDataMap = getPictureDataMap(sheet, workBook);
if(sheet==null){
    continue;
   }
   //循環(huán)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)));//優(yōu)惠價格
    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)));//限購數(shù)量
    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 數(shù)據(jù)到數(shù)據(jù)庫的步驟的詳細內(nèi)容，更多關(guān)于Java 導入Excel 數(shù)據(jù)到數(shù)據(jù)庫的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: