由于我个人电脑装的Excel是2016版本的,所以这地方我使用了XSSF 方式导入 。 1 先手要制定一个Excel 模板 把模板放入javaWeb工程的某一个目录下如图: 2 模板建好了后,先实现模板下载功
由于我个人电脑装的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 数据到数据库的资料请关注易盾网络其它相关文章!