javascript
SpringBoot中使用POI实现Excel导入到数据库(图文教程已实践)
場景
IDEA中開發SpringBoot項目時需要將Excel模板中數據導入的到數據庫。
Excel模板如下
實現思想
首先將模板上傳到服務器中某路徑,并將當前路徑存儲,然后使用POI自帶的
工具類獲取到sheet以及行數以及每個cell,然后分別給數據庫中對應的對象的屬性賦值,然后插入到數據庫中并返回執行結果。
實現
實現文件上傳
頁面代碼
實現點擊導入按鈕彈窗
<div class="modal inmodal" id="apImportModel" tabindex="-1" role="dialog" aria-hidden="true"><div class="modal-dialog" id="apImportDiv" th:fragment="apImportDiv"><div class="modal-content animated fadeIn"><button type="button" class="close" data-dismiss="modal"><span aria-hidden="true">×</span><span class="sr-only">Close</span></button><form class="dropzone" id="dropzoneForm" enctype="multipart/form-data"><div class="fallback"><input name="file" type="file" id="file_id" /></div></form><button id="uploladBtn" class="btn btn-info mt-2" type="button" onclick="return uploadExcel()"><i class="fa fa-reply"></i>上傳</button><button id="parseBtn" class="btn btn-info mt-2" type="button" onclick="return parseExcel()"><i class="fa fa-reply"></i>導入</button></div></div></div>因為這里使用的是thymeleaf模板
thymeleaf中文參考手冊下載:
https://download.csdn.net/download/badao_liumang_qizhi/11021427 ?
在當前頁面需要引入js文件
<!DOCTYPE html> <html xmlns:th="http://www.thymeleaf.org" xmlns:shiro="http://www.pollix.at/thymeleaf/shiro"th:replace="layout/layout(title='測試數據',cssPaths='/public/css/plugins/datapicker/datepicker3.css',jsPaths='/public/js/plugins/dataTables/datatables.min.js,/modular/receiveOrder/wmsReceiveOrder.js')"><!-- layout文件路徑-->?
來到/modular/receiveOrder/wmsReceiveOrder.js此目錄下的js文件
在js中實現點擊導入按鈕彈窗功能
//導入按鈕點擊事件$("#importBtn").click(function () {importExcel();});點擊事件調用下面的方法
?? function importExcel(){var url = "/wmsReceiveOrder/doExcelImport.html";$('#apImportDiv').load(url, function (response,status,xhr) {$("#apImportModel").modal('show');});}該方法按照url請求后臺,其中load方法是讓隱藏的彈窗部分顯示 。
來到后臺url對應的方法中
@Description("獲取收獲單信息列表頁面")@RequestMapping("/wmsReceiveOrder.html")public String page(Model model) {return "receiveOrder/wmsReceiveOrder.html";}返回當前頁面并將彈窗顯示
開始設置文件上傳的點擊事件
在彈窗部分的代碼中
<div class="modal inmodal" id="apImportModel" tabindex="-1" role="dialog"? aria-hidden="true"><div class="modal-dialog" id="apImportDiv" th:fragment="apImportDiv"><div class="modal-content animated fadeIn"><button type="button" class="close" data-dismiss="modal"><span aria-hidden="true">×</span><span class="sr-only">Close</span></button><form? class="dropzone"? id="dropzoneForm" enctype="multipart/form-data"><div class="fallback"><input name="file" type="file" id="file_id" /></div></form><button id="uploladBtn" class="btn btn-info mt-2" type="button" onclick="return uploadExcel()"><i class="fa fa-reply"></i>上傳</button><button id="parseBtn" class="btn btn-info mt-2" type="button" onclick="return parseExcel()"><i class="fa fa-reply"></i>導入</button></div></div></div>其中
<button id="uploladBtn" class="btn btn-info mt-2" type="button" onclick="return uploadExcel()"><i class="fa fa-reply"></i>上傳</button>
是調用js中對應的方法uploadExcel()
?
選擇文件后,會上傳到url中對應的位置
要實現文件上傳,需要引入commons-fileupload包
找到項目下的pom.xml
添加如下依賴
?<!--文件上傳--><dependency><groupId>commons-fileupload</groupId><artifactId>commons-fileupload</artifactId><version>1.3</version></dependency>來到url對應的后臺方法
?@Description("獲取前端導入excel")@ResponseBody@RequestMapping(value = "/doImportExcel")public Map<String, Object> doUpload(HttpServletRequest request, @RequestParam("file") MultipartFile file) {Map<String, Object> result = new HashMap<String, Object>();String fileName = "";try {//將當前上下文初始化給? CommonsMutipartResolver (多部分解析器)CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(request.getSession().getServletContext());//檢查form中是否有enctype="multipart/form-data"if (multipartResolver.isMultipart(request)) {//將request變成多部分requestMultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;//獲取multiRequest 中所有的文件名Iterator iter = multiRequest.getFileNames();while (iter.hasNext()) {//一次遍歷所有文件MultipartFile file1 = multiRequest.getFile(iter.next().toString());if (file != null) {//獲取上傳文件名fileName = file1.getOriginalFilename();//獲取后綴名String sname = fileName.substring(fileName.lastIndexOf("."));//時間格式化格式SimpleDateFormat simpleDateFormat =new SimpleDateFormat("yyyyMMddHHmmssSSS");//獲取當前時間并作為時間戳String timeStamp=simpleDateFormat.format(new Date());//拼接新的文件名String newName ="收貨單"+timeStamp+sname;//指定上傳文件的路徑String path = "F:\\" + newName;//上傳保存file.transferTo(new File(path));//保存當前文件路徑request.getSession().setAttribute("currFilePath", path);}}}result.put("statusCode", "200");result.put("message", "上傳成功!");result.put("filename", fileName);} catch (Exception ex) {result.put("statusCode", "300");result.put("message", "上傳失敗:" + ex.getMessage());}return result;}實現效果
點擊選擇文件
選擇文件后
然后點擊上傳
這時在后臺controller打斷點查看,可以看到后臺已經獲取到上傳的文件,并以當前時間戳重名名存放在F盤下,將存放路徑存在當前
session中。
可以看到F盤 下已經有了剛才上傳的文件
?
解析并導入Excel實現
彈窗頁面的解析Excel按鈕代碼
<div class="modal inmodal" id="apImportModel" tabindex="-1" role="dialog"? aria-hidden="true"><div class="modal-dialog" id="apImportDiv" th:fragment="apImportDiv"><div class="modal-content animated fadeIn"><button type="button" class="close" data-dismiss="modal"><span aria-hidden="true">×</span><span class="sr-only">Close</span></button><form? class="dropzone"? id="dropzoneForm" enctype="multipart/form-data"><div class="fallback"><input name="file" type="file" id="file_id" /></div></form><button id="uploladBtn" class="btn btn-info mt-2" type="button" onclick="return uploadExcel()"><i class="fa fa-reply"></i>上傳</button><button id="parseBtn" class="btn btn-info mt-2" type="button" onclick="return parseExcel()"><i class="fa fa-reply"></i>導入</button></div></div></div>點擊導入按鈕后調用js中的parseExcel()方法
//解析Excel操作 function parseExcel(){var url = "/wmsReceiveOrder/parseExcel";$.ajax({type: 'POST',url: url,cache: false,? //禁用緩存contentType: false,dataType: "json",processData:false,success: function (result) {debuggeralert(result.message)}})return false; }ajax請求后臺的url對應的方法
前提要實現Excel的導入和導出需要引入POI的包
打開項目的pom.xml
<!--excel導入導出--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.9</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.9</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>3.9</version></dependency>剛才請求到后臺url對應的方法
@ResponseBody@RequestMapping("/parseExcel")public Map<String, Object> parseExcel(HttpServletRequest request) {Map<String, Object> result = new HashMap<String, Object>();Workbook workbook = null;//獲取文件路徑String path = (String)request.getSession().getAttribute("currFilePath");//獲取文件格式String fileType = path.substring(path.lastIndexOf(".") + 1, path.length());try {InputStream stream = new FileInputStream(path);//如果后綴名為xls,使用HSSFif (fileType.equals("xls")) {workbook = new HSSFWorkbook(stream);//如果后綴名是xlsx,使用XSSF}else if (fileType.equals("xlsx")){workbook = new XSSFWorkbook(stream);}Sheet sheet= workbook.getSheet("sheet1");//獲取行數int rows=sheet.getPhysicalNumberOfRows();//List<WmsReceiveOrder> receiveOrderList =new ArrayList<WmsReceiveOrder>();WmsReceiveOrder receiveOrder =new WmsReceiveOrder();//獲取第一行數據Row row1 =sheet.getRow(0);if(row1!=null){//獲取采購訂單號row1.getCell(1).setCellType(Cell.CELL_TYPE_STRING);String purchaseCode =row1.getCell(1).getStringCellValue();receiveOrder.setPurchaseCode(purchaseCode);}//獲取第二行數據Row row2 =sheet.getRow(1);if(row2!=null){//獲取供應商送貨單號row2.getCell(1).setCellType(Cell.CELL_TYPE_STRING);String deliveryCode =row2.getCell(1).getStringCellValue();receiveOrder.setDeliveryCode(deliveryCode);//獲取送貨日期Date deliveryTime =row2.getCell(3).getDateCellValue();receiveOrder.setDeliveryTime(deliveryTime);//獲取供應商名稱row2.getCell(5).setCellType(Cell.CELL_TYPE_STRING);String supplierName =row2.getCell(5).getStringCellValue();receiveOrder.setSupplierName(supplierName);}//獲取第三行數據Row row3 =sheet.getRow(2);if(row3!=null){//獲取ERP入貨單號row3.getCell(1).setCellType(Cell.CELL_TYPE_STRING);String erpInCode =row3.getCell(1).getStringCellValue();receiveOrder.setErpInCod(erpInCode);//獲取入庫日期Date inTime =row3.getCell(3).getDateCellValue();receiveOrder.setInTime(inTime);}receiveOrder.setRemark("測試備注");receiveOrder.setType(1);receiveOrder.setStatus("1");//插入receiveOrder表數據boolean isSaveReceiveOrder =receiveOrderService.save(receiveOrder);List<WmsReceiveOrderDetails> receiveOrderDetailsList = new ArrayList<WmsReceiveOrderDetails>();if(isSaveReceiveOrder){//如果插入表頭成功,獲取插入數據的ID并插入詳情表Long receiveId=receiveOrder.getId();for (int currentRow=4;currentRow<rows;currentRow++) {WmsReceiveOrderDetails wmsReceiveOrderDetails =new WmsReceiveOrderDetails();wmsReceiveOrderDetails.setReceiveId(receiveId);//獲取物料編號sheet.getRow(currentRow).getCell(1).setCellType(Cell.CELL_TYPE_STRING);String materielId = sheet.getRow(currentRow).getCell(1).getStringCellValue();wmsReceiveOrderDetails.setMaterielId(materielId);//獲取物料名稱sheet.getRow(currentRow).getCell(2).setCellType(Cell.CELL_TYPE_STRING);String materielName = sheet.getRow(currentRow).getCell(2).getStringCellValue();wmsReceiveOrderDetails.setMaterielName(materielName);//獲取供應商批次sheet.getRow(currentRow).getCell(3).setCellType(Cell.CELL_TYPE_STRING);String supplierBatch = sheet.getRow(currentRow).getCell(3).getStringCellValue();wmsReceiveOrderDetails.setSupplierBatch(supplierBatch);//獲取生產日期Date productDate = sheet.getRow(currentRow).getCell(4).getDateCellValue();wmsReceiveOrderDetails.setProductDate(productDate);//獲取數量sheet.getRow(currentRow).getCell(5).setCellType(Cell.CELL_TYPE_STRING);Long num =Long.parseLong(sheet.getRow(currentRow).getCell(5).getStringCellValue());wmsReceiveOrderDetails.setNum(num);//獲取托盤編號String salverCode = sheet.getRow(currentRow).getCell(6).getStringCellValue();wmsReceiveOrderDetails.setSalverCode(salverCode);wmsReceiveOrderDetails.setStatus("1");wmsReceiveOrderDetails.setRemark("測試備注");wmsReceiveOrderDetails.setDeletedFlag(false);receiveOrderDetailsList.add(wmsReceiveOrderDetails);}if(receiveOrderDetailsList!=null){for (WmsReceiveOrderDetails wmsReceiveOrderDetails:receiveOrderDetailsList) {boolean isSaveReceiveOrderDetails =receiveOrderDetailsService.save(wmsReceiveOrderDetails);if(!isSaveReceiveOrderDetails){result.put("statusCode", "300");result.put("message", "導入物料編號為:"+wmsReceiveOrderDetails.getMaterielId()+"出錯了!");}else{result.put("statusCode", "200");result.put("message", "導入收貨單成功!");}}}}else{result.put("statusCode", "300");result.put("message", "導入收貨單失敗!");}} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}return result;}這里是結合具體的業務實現的Excel的導入,這里要導入的表是兩個關聯表。
基本思想就是通過行與列的坐標定位獲取到cell中的值。
比如:
1.獲取sheet頁
Workbook workbook= null;?中間需要根據獲取的后綴名進行初始化
?workbook = new HSSFWorkbook(stream);?或者
workbook = new XSSFWorkbook(stream);然后
?
這里的sheet1是sheet頁的名字
2.獲取行數
int rows=sheet.getPhysicalNumberOfRows();3.獲取第一行數據
Row row1 =sheet.getRow(0);4.獲取第一行的第2個Cell對象
row1.getCell(1)注意行與Cell都是從0開始的。
5.給第一行的第二個Cell設置值的類型
?row1.getCell(1).setCellType(Cell.CELL_TYPE_STRING);具體類型在IDEA代碼提示中會有。
6.獲取第一行第二個Cell的String類型的值。
String purchaseCode =row1.getCell(1).getStringCellValue();7.獲取日期格式的Cell的值
Date inTime =row3.getCell(3).getDateCellValue();?
實現效果
?
總結
以上是生活随笔為你收集整理的SpringBoot中使用POI实现Excel导入到数据库(图文教程已实践)的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: Eclipse中实现SpringBoot
- 下一篇: 一行Java代码判断文件夹是否存在,不存