java实现excel批量导入图片到服务器

这里主要是通过easypoi注解的方式进行导入excel数据，导入后的图片上传到自己的服务器路径上面去，过程中需要对file和MultipartFile的相互转换和服务器的连接，具体过程如下:

1.引入依赖

	//file和MultipartFile互转
	<dependency>
			<groupId>org.apache.httpcomponents</groupId>
			<artifactId>httpcore</artifactId>
			<version>4.4.9</version>
	</dependency>
	<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-test</artifactId>
			<version>5.1.6.RELEASE</version>
	</dependency>
//easypoi 
	<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-spring-boot-starter</artifactId>
			<version>4.0.0</version>
	</dependency>
//服务器连接
		<!--sftp文件上传-->
		<dependency>
			<groupId>com.jcraft</groupId>
			<artifactId>jsch</artifactId>
			<version>0.1.54</version>
		</dependency>

2.实体类demo

@Data
@ExcelTarget("user")
public class User  implements Serializable {

    @Excel(name = "id")
    private String id;

    @Excel(name = "username")
    private String username;

    @Excel(name = "password")
    private String password;

    /**
     * 这里的type：2 是表示图片
     *      imageType:1 表示从file读取
     *      name : 表示当前excel对应的表头名称
     */
    @Excel(name = "photo",type = 2,width = 20,height = 20,imageType = 1)
    private String photo;

}

3.测试的excel文档样式

### 4.yml配置文件

#远程服务器连接配置
remote-config:
  host: xxx.xx.xx.xxx#这里用你个人的服务器地址
  port: 22
  username: root
  password: xxxxxx #密码

#远程服务器文档管理，用来回调图片，使用的话服务器要配置jetty
remote-file:
  prefix_path: /data0/jetty/xtyWebApp/yq/uploadFiles                   #文件/图片 存储路径前缀
  image_prefix_path: http://${remote-config.host}:8082/yq/uploadFiles   #图片地址映

5.服务器文件操作工具类

package com.easypoi;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import com.jcraft.jsch.*;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.Resource;
import org.springframework.core.io.ResourceLoader;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.Collection;
import java.util.Properties;

/**
 * Description：文件操作工具类
 */
@Component
public class FileOperateUtil {

    @Value("${remote-file.prefix_path:}")
    public String PREFIX_PATH;//文件/图片 存储路径前缀

    @Value("${remote-file.image_prefix_path:}")
    public String IMAGE_PREFIX_PATH;//图片地址映射前缀

    @Autowired
    public ResourceLoader resourceLoader;

    /**
     * moduleName 模块名：
     */
    public String PARK_INFO = "parkInfo";//园区信息管理模块

    //连接属性
    @Value("${remote-config.host:}")
    private String host;
    @Value("${remote-config.port:-1}")
    private int port;
    @Value("${remote-config.username:}")
    private String user;
    @Value("${remote-config.password:}")
    private String password;

    /**
     * 获取sftp连接
     * @return sftp
     */
    public ChannelSftp getConnect(){
        ChannelSftp sftp = null;
        try {
            JSch jsch = new JSch();
            //获取sshSession  账号-ip-端口
            Session sshSession = jsch.getSession(user, host, port);
            //添加密码
            sshSession.setPassword(password);
            Properties sshConfig = new Properties();
            //严格主机密钥检查
            sshConfig.put("StrictHostKeyChecking", "no");
            sshSession.setConfig(sshConfig);
            //开启sshSession链接
            sshSession.connect();
            //获取sftp通道
            Channel channel = sshSession.openChannel("sftp");
            //开启
            channel.connect();
            sftp = (ChannelSftp) channel;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sftp;
    }

    /**
     * 上传附件
     * @param file              附件
     * @param moduleName        模块名
     * @param sftp              sftp连接
     * @return databaseFileUri  数据库表附件存储uri
     */
    public String uploadFile(MultipartFile file, String moduleName, ChannelSftp sftp) {
        String databaseFileUri = "";
        try{
            String prefixPath = PREFIX_PATH + "/" + moduleName;
            try {
                sftp.cd(prefixPath);//文件默认上传到当前目录，所以需要切到需要文件上传所在目录
            } catch (SftpException e) {
                //产生异常即不存在该路径  创建目录并进入
                String[] dirs = prefixPath.split("/");
                StringBuilder tempPath = new StringBuilder();
                for (String dir : dirs) {
                    try {
                        tempPath.append("/").append(dir);
                        try {
                            sftp.mkdir(dir);
                        } catch (SftpException sftpException) {
                            System.out.println("目录已存在，无需创建，程序继续执行...");
                        }
                        sftp.cd(tempPath.toString());
                    } catch (SftpException sftpException) {
                        sftpException.printStackTrace();
                    }
                }
            }
            //获取原本文件的文件名
            String fileName;
            if (file.getOriginalFilename() == null && "".equals(file.getOriginalFilename())) {
                 fileName = file.getOriginalFilename();
            }else {
                 fileName = file.getName();
            }
            //生成自定义的文件名
            String databaseFileName = IDUtils.getRandomId() + fileName.substring(fileName.lastIndexOf("."));

            //存储在数据库中的文件url
            databaseFileUri = "/" + moduleName + "/" + databaseFileName;
            //上传文件到服务器指定文件夹
            sftp.put(file.getInputStream(), PREFIX_PATH + databaseFileUri);
            //关闭连接
            sftp.disconnect();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sftp != null && sftp.isConnected()) {
                sftp.disconnect();
            }
        }
        return databaseFileUri;
    }

    /**
     * 下载附件
     * @param fileName          附件名
     * @param databaseFileUri   数据库表附件存储uri
     * @param response          响应
     * @param sftp              sftp连接
     */
    public void downloadFile(String fileName, String databaseFileUri, HttpServletResponse response, ChannelSftp sftp) {
        InputStream inputStream = null;
        ServletOutputStream outputStream = null;
        try {
            //获取文件相对路径(除去文件名的路径)
            String relativePath = databaseFileUri.substring(0, databaseFileUri.lastIndexOf("/"));
            //进入服务器存储文件所在目录
            sftp.cd(PREFIX_PATH + relativePath);
            //获取文件输入流
            inputStream = sftp.get(PREFIX_PATH + databaseFileUri);

            response.addHeader("Cache-Control", "no-cache, no-store, must-revalidate");
            response.addHeader("charset", "utf-8");
            response.addHeader("Pragma", "no-cache");
            String encodeName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString());
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + encodeName + "\"; filename*=utf-8''" + encodeName);

            outputStream = response.getOutputStream();
            IOUtils.copy(inputStream, outputStream);
            response.flushBuffer();
            //关闭sftp连接
            sftp.disconnect();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sftp != null && sftp.isConnected()) {
                sftp.disconnect();
            }
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 删除附件
     * @param databaseFileUri 数据库表附件存储uri
     * @param sftp            sftp连接
     * @return boolean        删除结果
     */
    public boolean deleteFile(String databaseFileUri, ChannelSftp sftp) {
        try {
            //获取文件相对路径(除去文件名的路径)
            String relativePath = databaseFileUri.substring(0, databaseFileUri.lastIndexOf("/"));
            //进入服务器存储文件所在目录
            sftp.cd(PREFIX_PATH + relativePath);
            //删除文件
            sftp.rm(PREFIX_PATH + databaseFileUri);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sftp != null && sftp.isConnected()) {
                sftp.disconnect();
            }
        }
        return false;
    }

    /**
     * 获取图片地址
     * @param databaseFileUri 数据库表附件存储uri
     * @return 图片地址
     */
    public String getImageUrl(String databaseFileUri) {
        return IMAGE_PREFIX_PATH + databaseFileUri;
    }


    /**
     * 将数据导出到excel
     * @param fileName          生成excel文件的名称
     * @param data              生成excel的源数据
     * @param templateClass     生成excel的自定义模板
     * @param response          响应
     */
    public void excelExport(String fileName, Collection<?> data, Class<?> templateClass, HttpServletResponse response) {
        ServletOutputStream outputStream = null;
        Workbook sheets = null;
        try {
            sheets = ExcelExportUtil.exportExcel(new ExportParams(), templateClass, data);

            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            String encodeName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString());
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + encodeName + "\"; filename*=utf-8''" + encodeName);
            outputStream = response.getOutputStream();
            sheets.write(outputStream);
            outputStream.flush();
            outputStream.close();
            sheets.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (sheets != null) {
                try {
                    sheets.close();
                    sheets = null;
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 下载模板
     * @param filename  模板文件名
     * @param path      模板所在项目下的相对路径
     * @param response  HTTP响应
     */
    public void downloadTemplate(String filename, String path, HttpServletResponse response) {

        InputStream inputStream = null;
        ServletOutputStream servletOutputStream = null;
        try {
            response.setContentType("application/vnd.ms-excel");
            response.addHeader("Cache-Control", "no-cache, no-store, must-revalidate");
            response.addHeader("charset", "utf-8");
            response.addHeader("Pragma", "no-cache");
            String encodeName = URLEncoder.encode(filename, StandardCharsets.UTF_8.toString());
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + encodeName + "\"; filename*=utf-8''" + encodeName);
            Resource resource = resourceLoader.getResource("classpath:" + path);
            inputStream = resource.getInputStream();
            servletOutputStream = response.getOutputStream();
            IOUtils.copy(inputStream, servletOutputStream);
            response.flushBuffer();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (servletOutputStream != null) {
                    servletOutputStream.close();
                    servletOutputStream = null;
                }
                if (inputStream != null) {
                    inputStream.close();
                    inputStream = null;
                }
                // 召唤jvm的垃圾回收器
                System.gc();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

6.IDUtils工具类

package com.easypoi;

import java.util.Random;

public class IDUtils {

	/**
	 * 生成19位的随机Long类型id
	 */
	public static Long getRandomId() {
		//取当前时间的长整形值包含毫秒
		long millis = System.currentTimeMillis();
		//加上六位位随机数
		int end3 = new Random().nextInt(899999) + 100000;
		return Long.valueOf(millis + String.format("%03d", end3));
	}
}

7.excel导入实现类

实现导入功能

@RestController
public class EasyPoiTest {


    @Autowired
    private FileOperateUtil fileOperateUtil;  //上传服务器文件工具类


    public String PHOTO_TEST = "photo_test";//园区门户信息  //服务器模块名称

 /**
     * excel批量上传图片
     * @param filename
     * @return
     * @throws Exception
     */
    @PostMapping("photoTest")
    public List<User> demo1(@RequestBody MultipartFile filename) throws Exception {
        ImportParams params = new ImportParams();

        File toFile = multipartFileToFile(filename);
        // List<User> list = ExcelImportUtil.importExcel(new File("D:\\javaBasic\\filetest\\test.xlsx"), User.class, params);
        List<User> list = ExcelImportUtil.importExcel(toFile, User.class, params);
        for (User user : list) {
            File file=new File(user.getPhoto());
            InputStream inputStream = new FileInputStream(file);
            MultipartFile multipartFile = new MockMultipartFile(file.getName(), inputStream);
            String uploadFile = uploadFile(multipartFile);
            user.setPhoto(uploadFile);
        }
        return list;
    }


    /**
     * 处理单个文件上传到服务器
     *
     * @param file
     * @return
     */
    @PostMapping("/uploadFile")
    public String uploadFile(MultipartFile file) {
        //处理传入图片
        //上传文件到服务器上面
        String databaseFileUri = fileOperateUtil.uploadFile(file, PHOTO_TEST, fileOperateUtil.getConnect());
        if (databaseFileUri != null && !"".equals(databaseFileUri)) {
            System.out.println("this picture is true");
        }
        return databaseFileUri;
    }


    /**
     * MultipartFile转换成file
     * @param file
     * @return
     * @throws Exception
     */
    public  File multipartFileToFile(MultipartFile file) throws Exception {

        File toFile = null;
        if(file.equals("")||file.getSize()<=0){
            file = null;
        }else {
            InputStream ins = null;
            ins = file.getInputStream();
            toFile = new File(file.getOriginalFilename());
            inputStreamToFile(ins, toFile);
            ins.close();
        }
        return toFile;
    }

    public static void inputStreamToFile(InputStream ins, File file) {
        try {
            OutputStream os = new FileOutputStream(file);
            int bytesRead = 0;
            byte[] buffer = new byte[8192];
            while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            ins.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

8.postman返回结果

返回当前接收excel导入的user类
刚刚导入到服务器的图片
总结：功能就实现了，如果需要插入到数据库的话，那么就将上传的地址存入回调的时候访问jetty下面的地址即可，数据库存入就不多做介绍了，具体业务逻辑可自己实现