需要的jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
根据Excel中的超链接下载文件到本地,并指定文件名为对应的列
package com.mwclg.ltd.eureka.test;
import org.apache.poi.ss.usermodel.*;
import java.io.*;
import java.net.URL;
/**
* @author: zlb
* create time: 2021-08-10 15:08
* description:
*/
public class test {
public static void main(String[] args) throws Exception {
// 需要解析的Excel文件
String fileDir = "C:\\Users\\08711\\Desktop\\sku1027(1).xlsx";
Workbook book = null;
// 获取Excel工作簿对象
book = getExcelWorkbook(fileDir);
// 获取Excel第一个sheet
Sheet sheet = getSheetByIdx(book, 0);
// 获取sheet中有多少行数据
int lastRowNum = sheet.getLastRowNum();
System.out.println("the row count is : " + (lastRowNum + 1));
// 按行循环遍历数据
for (int i = 0; i < lastRowNum + 1; i++) {
Row row = null;
// 获取行对象
row = sheet.getRow(i);
if (row != null) {
System.out.println("row NO." + (i + 1));
// 获取一行有多少列
int lastCellNum = row.getLastCellNum();
System.out.println("the column count is : " + lastCellNum);
Cell cell = null;
// 遍历每行的列
for (int j = 0; j < lastCellNum; j++) {
System.out.println("column No." + (j + 1));
// 获取单元格对象(指定行和列的数据)
cell = row.getCell(j);
// 指定列为文件名
Cell name = row.getCell(1);
if (cell != null) {
// 获取链接的文件
Hyperlink link = cell.getHyperlink();
// 如果不为null就说明是链接文件
if (null != link) {
// 获取超连接地址
String urlName = link.getAddress();
URL url = new URL(urlName);
System.out.println("Linked file path: " + name);
// 通过字节流写入指定目录下的文件内
InputStream fis = null;
try {
File dest = new File("D:\\1\\" + name);
fis = url.openStream();
byte[] data = readInputStream(fis);
//创建输出流
FileOutputStream outStream = new FileOutputStream(dest);
//写入数据
outStream.write(data);
System.out.println("正在保存:"+i+"的照片");
//关闭输出流
outStream.close();
} finally {
if (fis != null) {
fis.close();
}
}
}
}
}
}
}
}
public static byte[] readInputStream(InputStream inStream) throws Exception{
ByteArrayOutputStream outStream = new ByteArrayOutputStream();
//创建一个Buffer字符串
byte[] buffer = new byte[1024];
//每次读取的字符串长度,如果为-1,代表全部读取完毕
int len = 0;
//使用一个输入流从buffer里把数据读取出来
while( (len=inStream.read(buffer)) != -1 ){
//用输出流往buffer里写入数据,中间参数代表从哪个位置开始读,len代表读取的长度
outStream.write(buffer, 0, len);
}
//关闭输入流
inStream.close();
//把outStream里的数据写入内存
return outStream.toByteArray();
}
// 获取指定sheet对象
public static Sheet getSheetByIdx(Workbook book, int idx) {
Sheet sheet = null;
try {
sheet = book.getSheetAt(idx);
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
return sheet;
}
// 根据文件路径获取Excel工作簿对象
public static Workbook getExcelWorkbook(String filePath) throws IOException {
Workbook book = null;
File file = null;
FileInputStream fis = null;
try {
file = new File(filePath);
if (!file.exists()) {
throw new RuntimeException("文件不存在");
} else {
fis = new FileInputStream(file);
book = WorkbookFactory.create(fis);
}
} catch (Exception e) {
throw new RuntimeException();
} finally {
if (fis != null) {
fis.close();
}
}
return book;
}
}