导入excel和图片其实很简单,就是导入包与调用几个简单的方法,主要对接业务操作比较繁琐。
在excel中填入图片的名称(不用带后缀)
对应的zip包
导入依赖
<!-- zip压缩和解压 --> <dependency> <groupId>org.apache.ant</groupId> <artifactId>ant</artifactId> <version>1.10.5</version> </dependency>
Controller层
/** * @Description: 导入excel和图片zip包 * @Param: * @return: * @Author: jianhan * @Date: 2022-11-14 13:45:02 **/ @ApiOperation(value = "导入excel和图片zip包", notes = "导入excel和图片zip包") @PostMapping("/import/picture") public Result importExcelPic(@RequestPart MultipartFile excel, @RequestPart MultipartFile picture) throws Exception { StringBuffer sb = peopleService.importPicture(excel, picture, identity); if (sb!= null&&sb.length()>0) { return Result.failed(sb.toString()); }else{ return Result.success(); } }
service层
@Value("${image.excelShortPath}")
public String excelShortPath;
@Value("${image.excelNginxPath}")
public String excelNginxPath;
@Override public StringBuffer importPicture(MultipartFile excel, MultipartFile picture) throws Exception { String excelName = UUID.randomUUID() + ".xlsx"; String qrCodeZIPName = UUID.randomUUID() + ".zip"; StringBuffer sb = new StringBuffer(); FileUtils.byte2File(excel.getBytes(), excelShortPath + excelName); String s1 = FileUtils.byte2File(picture.getBytes(), excelShortPath + qrCodeZIPName); String prefix = qrCodeZIPName.substring(qrCodeZIPName.lastIndexOf(".")); int num = prefix.length();//得到后缀名长度 String s2 = s1.substring(0, s1.length() - num);//得到文件名。去掉了后缀 List<PeopleDto> getOriginalPic = ZipUtil.getOriginalPic(excelShortPath + s1, excelShortPath + s2); ExcelUtil<PeopleDto> util = new ExcelUtil<PeopleDto>(PeopleDto.class); List<PeopleDto> peopleDtoList = util.importExcel(excel.getInputStream()); List<String> errorList = new ArrayList<String>(); List<String> existCode = new ArrayList<String>(); if (peopleDtoList.get(0).getCode().equals("123456") || peopleDtoList.get(0).getName().equals("示例")) { peopleDtoList.remove(0);//这里是删除excel第一条示例 } for (int i = 0; i < peopleDtoList.size(); i++) { List<PeopleModel> peopleModelList = baseMapper.selectList(new QueryWrapper<PeopleModel>().eq("code", peopleDtoList.get(i).getCode())); if (peopleModelList != null && peopleModelList.size() > 0) { existCode.add(peopleDtoList.get(i).getCode()); peopleDtoList.remove(i);//这里是删除重复数据 continue; } int isWarn = 0; for (int j = 0; j < getOriginalPic.size(); j++) { if (peopleDtoList.get(i).getPictureName() == null) { sb.append("第" + i + "行导入图片名称为空"); } String fileName = getOriginalPic.get(j).getPictureName(); String fix = fileName.substring(fileName.lastIndexOf(".")); int number = fix.length();//得到后缀名长度 String fileOtherName = fileName.substring(0, fileName.length() - number);//得到文件名。去掉了后缀 if (peopleDtoList.get(i).getPictureName().equals(fileOtherName)) { isWarn = 1; peopleDtoList.get(i).setFaceImage(getOriginalPic.get(j).getFaceImage()); peopleDtoList.get(i).setPicture(excelNginxPath + s2 + "/" + getOriginalPic.get(j).getPictureName());//这里是存进数据库的url } } if (isWarn == 0) { errorList.add(peopleDtoList.get(i).getCode()); } } if (existCode != null && existCode.size() > 0) { sb.append("学号为 " + existCode + " 的学号已存在数据库。"); } if (errorList != null && errorList.size() > 0) { sb.append("学号为 " + errorList + " 的图片不存在或没有人脸。"); } List<String> errorString = batchAdd(peopleDtoList); if (errorString.size() == 0) { return sb; } else { sb.append("失败学号列表:" + errorString); } return sb; }
FileUtils.byte2File
采用若依框架自带的文件处理FileUtils.byte2File
ZipUtil.getOriginalPic
public static List<PersonDto> getOriginalPic(String zipPath,String unzipPath) throws Exception { unZip(zipPath); List<PersonDto> personDtoList=readfile(unzipPath); return personDtoList; }
ZipUtil.unZip
public static void unZip(String path) { int count = -1; String savepath = ""; File file = null; InputStream is = null; FileOutputStream fos = null; BufferedOutputStream bos = null; savepath = path.substring(0, path.lastIndexOf(".")) + File.separator; //保存解压文件目录 new File(savepath).mkdir(); //创建保存目录 ZipFile zipFile = null; try { zipFile = new ZipFile(path, "gbk"); //解决中文乱码问题 Enumeration < ? > entries = zipFile.getEntries(); while (entries.hasMoreElements()) { byte buf[] = new byte[buffer]; ZipEntry entry = (ZipEntry) entries.nextElement(); String filename = entry.getName(); boolean ismkdir = false; if (filename.lastIndexOf("/") != -1) { //检查此文件是否带有文件夹 ismkdir = true; } filename = savepath + filename; if (entry.isDirectory()) { //如果是文件夹先创建 file = new File(filename); file.mkdirs(); continue; } file = new File(filename); if (!file.exists()) { //如果是目录先创建 if (ismkdir) { new File(filename.substring(0, filename.lastIndexOf("/"))).mkdirs(); //目录先创建 } } file.createNewFile(); //创建文件 is = zipFile.getInputStream(entry); fos = new FileOutputStream(file); bos = new BufferedOutputStream(fos, buffer); while ((count = is.read(buf)) > -1) { bos.write(buf, 0, count); } bos.flush(); bos.close(); fos.close(); is.close(); } zipFile.close(); } catch (IOException ioe) { ioe.printStackTrace(); } finally { try { if (bos != null) { bos.close(); } if (fos != null) { fos.close(); } if (is != null) { is.close(); } if (zipFile != null) { zipFile.close(); } } catch (Exception e) { e.printStackTrace(); } } }
ZipUtil.readfile
public static List<PeopleDto> readfile(String filepath) throws Exception { File file = new File(filepath); List<PeopleDto> personDtoList = new LinkedList<PeopleDto>(); if (!file.isDirectory()) { return null; } else if (file.isDirectory()) { String[] filelist = file.list(); for (int i = 0; i < filelist.length; i++) { //windows 的读取方法 File readfile = new File(filepath + "\\" + filelist[i]); // // linux 的读取方法 // File readfile = new File(filepath + "/" + filelist[i]); if (!readfile.isDirectory()) { String fileName = readfile.getName(); //System.out.println("fileOtherName:"+fileOtherName); File file1Save = new File(readfile.getAbsolutePath()); byte[] byteBuffer = FileUtil.readBytes(file1Save); PeopleDto peopleDto = new PeopleDto(); peopleDto.setFaceImage(byteBuffer); peopleDto.setPictureName(fileName); personDtoList.add(peopleDto); } else if (readfile.isDirectory()) { return null; } } } return personDtoList; }
util.importExcel
这里采用ruoyi框架的通用的excel导入,也可以用其他Excel导入方式完成这部分
batchAdd
private List<String> batchAdd(List<PeopleDto> peopleDtoList) { List<String> errorString = new LinkedList<String>(); List<PeopleModel> peopleModels = new ArrayList<>(); if (peopleDtoList.size() > 0 && peopleDtoList != null) { for (PeopleDto personDto : peopleDtoList) { PeopleModel peopleModel = new PeopleModel(); BeanUtils.copyProperties(personDto, peopleModel); //这里可以判断数据库是否有重叠数据,和一些具体业务 peopleModels.add(peopleModel); } if (this.saveBatch(peopleModels)) { for (PeopleModel peopleModel : peopleModels) { //这里可以做一些外接设备的业务处理 //errorString.add(peopleModel.getCode()); } } else { errorString.add("保存数据库失败"); } } return errorString; }
标签:String,zip,--,List,Excel,File,peopleDtoList,new,null From: https://www.cnblogs.com/SjhCode/p/excelAndPic.html