导入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;
}