文章詳情頁

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

瀏覽：185日期：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.b3g6.com/bcjs/<%=basePath%>book/dishes/backstageversion/list!exportOrder' rel='external nofollow' rel='external nofollow' >下載模板</a> </form></div>

下面是js

<link href='http://www.b3g6.com/bcjs/<%=path %>/res/admin/css/bootstrap.min.css' rel='external nofollow' rel='stylesheet' type='text/css' /> <link href='http://www.b3g6.com/bcjs/<%=path %>/res/admin/css/xy_css.css' rel='external nofollow' rel='stylesheet' type='text/css'> <link href='http://www.b3g6.com/bcjs/<%=path %>/res/admin/css/font-awesome.min.css' rel='external nofollow' rel='stylesheet' type='text/css'> <script src='http://www.b3g6.com/bcjs/<%=path %>/res/admin/js/jquery.min.js'></script> <script src='http://www.b3g6.com/bcjs/<%=path %>/res/admin/js/bootstrap.min.js'></script> <link href='http://www.b3g6.com/bcjs/<%=path %>/res/admin/jquery-select2/3.4/select2.css' rel='external nofollow' rel='stylesheet' type='text/css' /> <script src='http://www.b3g6.com/bcjs/<%=path %>/res/admin/jquery-select2/3.4/select2.min.js'></script> <script src='http://www.b3g6.com/bcjs/<%=path %>/res/admin/jquery-select2/3.4/select2_locale_zh-CN.js'></script> <script type='text/javascript' src='http://www.b3g6.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. wps任務窗格在哪里怎么調出來2. 美圖秀秀如何更換底色3. 谷歌瀏覽器如何禁止網站發送通知4. 手機qq會員贈送記錄在哪里5. Notepad如何設置制表符寬度6. 企業微信實名認證怎么解除7. GOMPlayer怎么設置視頻滿屏播放8. 麥可如何查看禮物明細9. Excel滾動鼠標滾輪為何變成放大縮小10. 口袋寫作刪除的作品如何恢復

排行榜

					
					GOMPlayer怎么設置視頻滿屏播放
Excel滾動鼠標滾輪為何變成放大縮小
手機qq會員贈送記錄在哪里
Notepad如何設置制表符寬度
wps任務窗格在哪里怎么調出來
口袋寫作刪除的作品如何恢復
麥可如何查看禮物明細
美圖秀秀如何更換底色
谷歌瀏覽器如何禁止網站發送通知
企業微信實名認證怎么解除
3dmax2022安裝序列號和產品密鑰
				