文章詳情頁

java 使用poi 導入Excel數據到數據庫的步驟

瀏覽：154日期：2022-05-25 14:32:51

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

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

2 模板建好了后，先實現模板下載功能下面是頁面jsp代碼在這里只貼出部分代碼

<div class='' style='display: none;'> <form action='<%=basePath%>book/dishes/backstageversion/list!importExcel' method='post' enctype='multipart/form-data' onsubmit='loading(’正在導入，請稍等...’);'><br/> <input name='file' type='file' /><br/><br/> <input type='submit' value=' 導入 '/> <input type='hidden' name='importCompanyId' value=''/> <input type='hidden' name='importStallId' value=''/> <a href='http://www.gepszalag.com/bcjs/<%=basePath%>book/dishes/backstageversion/list!exportOrder' rel='external nofollow' rel='external nofollow' >下載模板</a> </form></div>

下面是js

<link href='http://www.gepszalag.com/bcjs/<%=path %>/res/admin/css/bootstrap.min.css' rel='external nofollow' rel='stylesheet' type='text/css' /> <link href='http://www.gepszalag.com/bcjs/<%=path %>/res/admin/css/xy_css.css' rel='external nofollow' rel='stylesheet' type='text/css'> <link href='http://www.gepszalag.com/bcjs/<%=path %>/res/admin/css/font-awesome.min.css' rel='external nofollow' rel='stylesheet' type='text/css'> <script src='http://www.gepszalag.com/bcjs/<%=path %>/res/admin/js/jquery.min.js'></script> <script src='http://www.gepszalag.com/bcjs/<%=path %>/res/admin/js/bootstrap.min.js'></script> <link href='http://www.gepszalag.com/bcjs/<%=path %>/res/admin/jquery-select2/3.4/select2.css' rel='external nofollow' rel='stylesheet' type='text/css' /> <script src='http://www.gepszalag.com/bcjs/<%=path %>/res/admin/jquery-select2/3.4/select2.min.js'></script> <script src='http://www.gepszalag.com/bcjs/<%=path %>/res/admin/jquery-select2/3.4/select2_locale_zh-CN.js'></script> <script type='text/javascript' src='http://www.gepszalag.com/bcjs/<%=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 數據到數據庫的資料請關注好吧啦網其它相關文章！

excel

上一條：小米云盤和阿里云盤的區別是什么下一條：蘋果手機微信分身怎么弄的步驟教程

相關文章：

1. 拼多多已售罄商品如何恢復2. 怎么申請京東網約車司機申請司機教程3. 釘釘怎么換聊天背景4. WPS制作故事書的方法5. 支付寶兩個賬號怎么合并6. 天貓2019合貓貓理想貓喵幣獲取方法7. 快手轉盤怎么玩8. 華為mate40pro微信支付人臉識別不了9. 支付寶收款碼怎么隱藏真實姓名10. 釘釘如何設置釘釘號？釘釘號設置方法分享

排行榜

					
					拼多多已售罄商品如何恢復
怎么申請京東網約車司機 申請司機教程
華為mate40pro微信支付人臉識別不了
支付寶收款碼怎么隱藏真實姓名
多多買菜可以退貨嗎
秀米如何授權微信公眾號
企業微信朋友圈權限怎么設置
如何成為拼多多新用戶
抖音聊天怎么添加朋友
解決逍遙安卓模擬器無法打開快手的方法
手機淘寶APP關閉指紋支付的方法
				