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

作者:阿若蜜意 时间:2024-01-19 11:21:34 

由于我个人电脑装的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 版本 不然读取图片代码会报错

来源:https://www.cnblogs.com/SHMILYHP/p/8327861.html

标签:java,poi,excel,数据库
0
投稿

猜你喜欢

  • js实现黑色简易的滑动门网页tab选项卡效果

    2024-04-23 09:05:53
  • 教你隐藏ACCESS数据库的表名

    2008-05-09 19:45:00
  • Python机器学习之决策树算法

    2022-06-07 06:38:43
  • 详解pyenv下使用python matplotlib模块的问题解决

    2023-08-08 20:25:01
  • asp 快钱网关接口 支付宝接口 财付通接口代码

    2011-03-08 10:55:00
  • 什么是响应式Web设计?怎样进行?

    2011-11-21 17:00:40
  • python 列表、字典和集合的添加和删除操作

    2022-06-03 14:25:30
  • python人工智能tensorflow函数tf.get_collection使用方法

    2023-08-09 14:27:54
  • python爬取代理IP并进行有效的IP测试实现

    2021-05-08 15:31:38
  • python+opencv实现文字颜色识别与标定功能

    2023-09-05 02:25:27
  • apache和nginx下vue页面刷新404的解决方案

    2024-04-26 17:37:16
  • 几个javascript显示加载进度条代码

    2008-01-19 10:52:00
  • Python实现动态绘图的示例详解

    2021-07-08 16:44:40
  • 使用Python获取网段IP个数以及地址清单的方法

    2021-02-25 03:28:21
  • asp数字或者字符排序函数代码

    2011-02-24 11:00:00
  • python opencv实现图像矫正功能

    2022-05-22 17:00:13
  • python中利用Future对象异步返回结果示例代码

    2021-09-10 06:59:52
  • Python爬虫抓取手机APP的传输数据

    2021-10-04 03:26:51
  • Pygame游戏开发之太空射击实战入门篇

    2023-07-17 23:12:42
  • Python第三方库undetected_chromedriver的使用

    2022-12-06 14:29:27
  • asp之家 网络编程 m.aspxhome.com