文章詳情頁

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

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

下面是js

<link href='http://www.lshqa.cn/bcjs/<%=path %>/res/admin/css/bootstrap.min.css' rel='external nofollow' rel='stylesheet' type='text/css' /> <link href='http://www.lshqa.cn/bcjs/<%=path %>/res/admin/css/xy_css.css' rel='external nofollow' rel='stylesheet' type='text/css'> <link href='http://www.lshqa.cn/bcjs/<%=path %>/res/admin/css/font-awesome.min.css' rel='external nofollow' rel='stylesheet' type='text/css'> <script src='http://www.lshqa.cn/bcjs/<%=path %>/res/admin/js/jquery.min.js'></script> <script src='http://www.lshqa.cn/bcjs/<%=path %>/res/admin/js/bootstrap.min.js'></script> <link href='http://www.lshqa.cn/bcjs/<%=path %>/res/admin/jquery-select2/3.4/select2.css' rel='external nofollow' rel='stylesheet' type='text/css' /> <script src='http://www.lshqa.cn/bcjs/<%=path %>/res/admin/jquery-select2/3.4/select2.min.js'></script> <script src='http://www.lshqa.cn/bcjs/<%=path %>/res/admin/jquery-select2/3.4/select2_locale_zh-CN.js'></script> <script type='text/javascript' src='http://www.lshqa.cn/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. 華為手機收到微信消息不提示怎么辦5. 滴滴怎么取消預付車費6. 抖音熊貓人喝飲料GIF表情包分享_熊貓人喝飲料動態表情大全7. 釘釘怎么退群啊8. solidworks原點坐標不見了的設置方法9. 使用Java讀取Excel文件內容的方法10. 微信新表情吃瓜意思介紹

排行榜

					
					抖音熊貓人喝飲料GIF表情包分享_熊貓人喝飲料動態表情大全
微信新表情吃瓜意思介紹
同一個微信賬號可以在兩個手機上同時登陸嗎
支付寶分期付款怎么取消
華為手機收到微信消息不提示怎么辦
淘寶怎么使用天貓購物劵_淘寶使用天貓購物劵方法教程
華為智慧助手上線健康碼 /行程卡雙碼合一 全國通行
solidworks原點坐標不見了的設置方法
釘釘怎么退群啊
使用Java讀取Excel文件內容的方法
微信步數如何刷
				