1.前端elementui代码
<el-upload class="upload-demo" drag name="excelFile" :data="postParm" :on-error="uploadFalse" :on-success="uploadSuccess" :before-upload="beforeAvatarUpload" action="api/mes/equment/uploadExcel" multiple> <i class="el-icon-upload"></i> <div class="el-upload__text">将文件拖到此处,或<em>点击上传</em></div> <div class="el-upload__tip" slot="tip">只能上传xlsx/xls文件,且不超过500kb</div> </el-upload>
uploadFalse(response, file, fileList) { alert("文件上传失败!"); }, uploadSuccess(response, file, fileList) { console.log('response',response); //文件导入成功 if (response.code === "0") { this.$message.success(response.msg); this.open1= false; this.getData(); }else { this.$message.success(response.msg); this.open1 = false; } }, // 上传前对文件的大小的判断 beforeAvatarUpload(file) { const extension = file.name.split(".")[1] === "xls"; const extension2 = file.name.split(".")[1] === "xlsx"; const extension3 = file.name.split(".")[1] === "doc"; const extension4 = file.name.split(".")[1] === "docx"; const isLt2M = file.size / 1024 / 1024 < 10; if (!extension && !extension2 && !extension3 && !extension4) { alert("上传模板只能是 xls、xlsx、doc、docx 格式!"); } if (!isLt2M) { console.log("上传模板大小不能超过 10MB!"); } return extension || extension2 || extension3 || (extension4 && isLt2M); }
data() { return { //批量弹出框 open1: false, fileList: [], postParm:{ businessName: "设备excel" } }; },
2.后台java代码
@RequestMapping(value = "/uploadExcel", method = RequestMethod.POST) @ResponseBody public Result uploadExcel( @RequestParam("excelFile") MultipartFile excelFile, @RequestParam(value = "businessName",required = true) String businessName ) throws IOException { try{ String name = excelFile.getOriginalFilename(); if(name.length()<6 || !name.substring(name.length()-5).equals(".xlsx")) { return Result.failure(ResponseCode.ERROR_999, "文件格式错误"); } List<Map<String,Object>> listMap = new ArrayList<Map<String,Object>>(); listMap = ExcelUtils.excelToShopIdList(excelFile.getInputStream()); System.out.println(listMap.size()); for (Map<String, Object> m : listMap) { entity = new equmentEntity(); entity.setEquSN(m.get("c0").toString()); entity.setEquName(m.get("c1").toString()); service.add(entity); } /* @RequestParam(value = "businessStatus",required = true) Integer businessStatus, @RequestParam(value = "businessType",required = true) Integer businessType , */ return Result.success("导出成功"); }catch (Exception ex){ System.out.println(ex.getMessage()); return Result.failure(ResponseCode.SUCCESS, ResponseMsg.QUERY_ERROR); } }
package com.ry.bill.sys.utils; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.*; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.InputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; public class ExcelUtils { private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class); public static List<Map<String,Object>> excelToShopIdList(InputStream inputStream) { List<Map<String,Object>> listMap = new ArrayList<Map<String,Object>>(); Map<String,Object> map=new HashMap<String,Object>(); List list = new ArrayList<>(); Workbook workbook = null; try { workbook = WorkbookFactory.create(inputStream); inputStream.close(); //工作表对象 Sheet sheet = workbook.getSheetAt(0); //总行数 int rowLength = sheet.getLastRowNum() + 1; //工作表的列 Row row = sheet.getRow(0); //总列数 int colLength = row.getLastCellNum(); //得到指定的单元格 Cell cell = row.getCell(0); for (int i = 1; i < rowLength; i++) { row = sheet.getRow(i); for (int j = 0; j < colLength; j++) { cell = row.getCell(j); if (cell != null) { cell.setCellType(Cell.CELL_TYPE_STRING); String data = cell.getStringCellValue(); map.put("c"+j,data); System.out.println(data); data = data.trim(); // if (StringUtils.isNumeric(data)) //list.add(data); } } listMap.add(map); } } catch (Exception e) { LOGGER.error("parse excel file error :", e); } return listMap;//shopIds; } }