配置maven依赖读取excel
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.15</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.15</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.15</version> </dependency>yml 配置阿里oss
#oss aliyun_oss_endPoint: https://oss-cn-shanghai.aliyuncs.com aliyun_oss_ak: xxxxxxxxxx aliyun_oss_sk: xxxxxxxxxx aliyun_oss_bucketName: xxxxxxxxxx aliyun_oss_folder: xxx/oss上传工具类
@Component @PropertySource("classpath:application-${spring.profiles.active}.yml") @Slf4j public class AliYunOSSClientUtil { @Value("${aliyun_oss_ak}") public String ak = "LTAI4Fh7ZhAzUeapAHCydamS"; @Value("${aliyun_oss_sk}") public String sk = "J20qeghKlyAFrcoFVtqULjGG3GTIpf"; @Value("${aliyun_oss_endPoint}") public String endPoint; @Value("${aliyun_oss_bucketName}") public String bucketName; @Value("${aliyun_oss_folder}") public String folder; private OSSClient ossClient; /** * 初始化 */ @PostConstruct public void init() { ossClient = new OSSClient(endPoint, ak, sk); } /** * 创建存储空间 * @param ossClient OSS连接 * @param bucketName 存储空间 * @return */ public String createBucketName(OSSClient ossClient, String bucketName){ //存储空间 final String bucketNames=bucketName; if(!ossClient.doesBucketExist(bucketName)){ //创建存储空间 Bucket bucket=ossClient.createBucket(bucketName); log.info("成功创建存储空间"); return bucket.getName(); } return bucketNames; } /** * 删除存储空间buckName * @param ossClient oss对象 * @param bucketName 存储空间 */ public void deleteBucket(OSSClient ossClient, String bucketName){ ossClient.deleteBucket(bucketName); log.info("删除" bucketName "Bucket成功"); } /** * 创建模拟文件夹 * @param ossClient oss连接 * @param bucketName 存储空间 * @param folder 模拟文件夹名如"qj_nanjing/" * @return 文件夹名 */ public String createFolder(OSSClient ossClient, String bucketName, String folder){ //文件夹名 final String keySuffixWithSlash =folder; ///判断文件夹是否存在，如果不存在，则创建 if(!ossClient.doesObjectExist(bucketName, keySuffixWithSlash)){ //创建文件夹 ossClient.putObject(bucketName, keySuffixWithSlash, new ByteArrayInputStream(new byte[0])); log.info("成功创建文件夹"); //获取文件夹名 OSSObject object = ossClient.getObject(bucketName, keySuffixWithSlash); String fileDir=object.getKey(); return fileDir; } return keySuffixWithSlash; } /** * 根据key删除OSS服务器上的文件 * @param ossClient oss连接 * @param bucketName 存储空间 * @param folder 模拟文件夹名 如"qj_nanjing/" * @param key Bucket以下文件的路径名 文件名 如："upload/cake.jpg" */ public void deleteFile(OSSClient ossClient, String bucketName, String folder, String key){ ossClient.deleteObject(bucketName, folder key); log.info("删除" bucketName "下的文件" folder key "成功"); } public String uploadImgOss(MultipartFile file, String fileName) throws Exception { log.info("图片大小如下：" file.getSize()); if (file.getSize() > 8 * 1024 * 1024) { throw new UploadImgException("上传图片大小不超过8M！"); } String originalFilename = file.getOriginalFilename(); String substring = originalFilename.substring(originalFilename.lastIndexOf(".")).toLowerCase(); String name = fileName substring; try { InputStream inputStream = file.getInputStream(); this.uploadFile2OSS(inputStream, name); return name; } catch (Exception e) { throw new Exception("图片上传失败"); } } /** * 上传到OSS服务器 如果同名文件将覆盖服务器 * * @param instream * 文件流 * @param fileName * 文件名称 包括后缀名 * @return 出错返回"" ,唯一MD5数字签名 */ public String uploadFile2OSS(InputStream instream, String fileName) { String ret = ""; try { // 创建上传Object的Metadata ObjectMetadata objectMetadata = new ObjectMetadata(); objectMetadata.setContentLength(insteam.available());
objectMetadata.setCacheControl("max-age="+24*60*60);
objectMetadata.setHeader("Pragma", "no-cache");
objectMetadata.setContentType(getContentType(fileName.substring(fileName.lastIndexOf("."))));
objectMetadata.setContentDisposition("inline;filename=" + fileName);
// 上传文件
PutObjectResult putResult = ossClient.putObject(bucketName, folder + fileName, instream, objectMetadata);
ret = putResult.getETag();
} catch (IOException e) {
log.error(e.getMessage(), e);
} finally {
try {
if (instream != null) {
instream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return ret;
}
/**
* 上传图片至OSS
* @param file 上传文件（文件全路径如：D:\\image\\cake.jpg）
* @return String 返回的唯一MD5数字签名
* */
public String uploadObject2OSS(MultipartFile file) {
String resultStr = null;
try {
//以输入流的形式上传文件
InputStream is = file.getInputStream();
//文件名
String fileName = file.getName();
//文件大小
//创建上传Object的Metadata
ObjectMetadata metadata = new ObjectMetadata();
//上传的文件的长度
metadata.setContentLength(is.available());
//指定该Object被下载时的网页的缓存行为
metadata.setCacheControl("no-cache");
//指定该Object下设置Header
metadata.setHeader("Pragma", "no-cache");
//指定该Object被下载时的内容编码格式
metadata.setContentEncoding("utf-8");
//文件的MIME，定义文件的类型及网页编码，决定浏览器将以什么形式、什么编码读取文件。如果用户没有指定则根据Key或文件名的扩展名生成，
//如果没有扩展名则填默认值application/octet-stream
metadata.setContentType(getContentType(fileName));
//上传文件 (上传文件流的形式)
PutObjectResult putResult = ossClient.putObject(bucketName, folder + fileName, is, metadata);
//解析结果
resultStr = putResult.getETag();
} catch (Exception e) {
e.printStackTrace();
log.error("上传阿里云OSS服务器异常." + e.getMessage(), e);
}
return resultStr;
}
/**
* 通过文件名判断并获取OSS服务文件上传时文件的contentType
* @param fileName 文件名
* @return 文件的contentType
*/
public String getContentType(String fileName){
//文件的后缀名
String fileExtension = fileName.substring(fileName.lastIndexOf("."));
if(".bmp".equalsIgnoreCase(fileExtension)) {
return "image/bmp";
}
if(".gif".equalsIgnoreCase(fileExtension)) {
return "image/gif";
}
if(".jpeg".equalsIgnoreCase(fileExtension) || ".jpg".equalsIgnoreCase(fileExtension) || ".png".equalsIgnoreCase(fileExtension) ) {
return "image/jpeg";
}
if(".html".equalsIgnoreCase(fileExtension)) {
return "text/html";
}
if(".txt".equalsIgnoreCase(fileExtension)) {
return "text/plain";
}
if(".vsd".equalsIgnoreCase(fileExtension)) {
return "application/vnd.visio";
}
if(".ppt".equalsIgnoreCase(fileExtension) || "pptx".equalsIgnoreCase(fileExtension)) {
return "application/vnd.ms-powerpoint";
}
if(".doc".equalsIgnoreCase(fileExtension) || "docx".equalsIgnoreCase(fileExtension)) {
return "application/msword";
}
if(".xml".equalsIgnoreCase(fileExtension)) {
return "text/xml";
}
//默认返回类型
return "image/jpeg";
}
/**
* 获得图片路径
*
* @param fileUrl
* @return
*/
public String getImgUrl(String fileUrl) {
System.out.println(fileUrl);
if (!StringUtil.isEmpty(fileUrl)) {
String[] split = fileUrl.split("/");
return this.getUrl(this.folder + split[split.length - 1]);
}
return null;
}
/**
* 获得url链接
*
* @param key
* @return
*/
public String getUrl(String key) {
// 设置URL过期时间为10年 3600l* 1000*24*365*10
Date expiration = new Date(System.currentTimeMillis() + 3600L * 1000 * 24 * 365 * 10);
// 生成URL
URL url = ossClient.generatePresignedUrl(bucketName, key, expiration);
if (url != null) {
return url.toString();
}
return null;
}
}读取excel上传oss测试类
/**
* @author by will
* @Classname ImageFromNetWork
* @Description TODO
* @Date 2022/6/30 13:38
*/
@RunWith(SpringRunner.class)
@SpringBootTest(classes = CmsApplication.class)
public class ImageFromNetWork {
@Resource
private AliYunOSSClientUtil aliYunOSSClientUtil;
@Test
public void test12312312() throws IOException {
FileInputStream fileInputStream = new FileInputStream("D:\\img\\excel\\tu.xlsx");//开启文件读取流
XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream);//读取文件
//获取sheet
XSSFSheet sheet = sheets.getSheetAt(0);
//获取行数
int rows = sheet.getPhysicalNumberOfRows();
for(int i=0;i<rows;i++){
//获取列数
XSSFRow row = sheet.getRow(i);
XSSFCell cell = row.getCell((short) 0);
String url =cell.getStringCellValue();
System.out.println(url);
String name = url.substring(url.lastIndexOf("/")+1,url.length()).replace("tims_uat","");
System.out.println(name);
String filePath = "D:\\img\\"+name;
String pash1 = url.substring(0,url.lastIndexOf("/")+1)+URLEncoder.encode(url.substring(url.lastIndexOf("/")+1,url.length()));
System.out.println(pash1);
writeImageToDisk(getImageFromNetByUrl(pash1),filePath);
File file = new File(filePath);
String s = aliYunOSSClientUtil.uploadFile2OSS(new FileInputStream(file), name);
System.out.println(s);
}
}
/**
* 将获取的字节数组保存为文件写入硬盘
*
* @param data
* @param fileName
*/
public static void writeImageToDisk(byte[] data, String fileName) {
try {
File file = new File(fileName); // 本地目录
File fileParent = file.getParentFile();
if (!fileParent.exists()) {
fileParent.mkdirs();
file.createNewFile();
}
FileOutputStream fops = new FileOutputStream(file);
fops.write(data);
fops.flush();
fops.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取远程http地址视图片
*
* @param strUrl
* @return
*/
public static byte[] getImageFromNetByUrl(String strUrl) {
try {
URL url = new URL(strUrl);
HttpURLConnection conn = (HttpURLConnection) url.openConnection();
conn.setRequestMethod("GET");
conn.setConnectTimeout(5 * 1000);
InputStream inStream = conn.getInputStream();
byte[] btData = readInputStream(inStream);
return btData;
}catch (FileNotFoundException e) {
System.out.println("文件没找到："+strUrl);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 读取流
*
* @param inStream
* @return
* @throws Exception
*/
public static byte[] readInputStream(InputStream inStream) throws Exception {
ByteArrayOutputStream outStream = new ByteArrayOutputStream();
byte[] buffer = new byte[1024];
int len = 0;
while ((len = inStream.read(buffer)) != -1) {
outStream.write(buffer, 0, len);
}
inStream.close();
return outStream.toByteArray();
}
}