需求
使用POI读取Excel文件中的图片数据。我的需求是导入一份Excel，读取Excel中的图片信息并将其上传至服务器，把图片上传的路径存入数据库，关键是WPS的嵌入式格式图片真的难读，用WPS打开可以看到图片，使用office的Excel打开就是一段代码无法预览图片。参考了很多大佬的博客，我自己也是慢慢摸索后完美实现需求！
实现
读取普通图片信息
直接上代码
import cn.hutool.json.JSONUtil;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
public class ExcelImportDemo1 {
public static void main(String[] args) throws IOException {
String path = "D:\\模板测试2.xlsx";// 替换为你自己的文件
String imageTargetPath = "D:\\download\\test\\";// 替换为你自己的输出路径
File file = new File(path);
// 获取图片数据
Map<String, PictureData> map = getPictureFromExcel(file, 1);
// 图片保存结果list
LinkedList<String> pictureList = new LinkedList<>();
// 遍历图片数据，将图片写入磁盘
for (Map.Entry<String, PictureData> entry : map.entrySet()) {
String key = entry.getKey();
PictureData pictureData = entry.getValue();
String extension = pictureData.suggestFileExtension();
// 构建图片文件名，使用行列作为文件名
String[] coordinates = key.split("-");
// 文件名规则 行号-列号.后缀名 列号不变
String imageFileName = coordinates[0] + "-" + coordinates[1] + "." + extension;
pictureList.add(imageFileName);
}
System.out.println(JSONUtil.toJsonStr(pictureList));
// 根据键的第一个数字进行分组
Map<Character, List<PictureData>> groupedImages = new HashMap<>();
for (Map.Entry<String, PictureData> entry : map.entrySet()) {
char firstDigit = entry.getKey().charAt(0);
groupedImages.computeIfAbsent(firstDigit, k -> new ArrayList<>()).add(entry.getValue());
}
// 打印分组结果
for (Map.Entry<Character, List<PictureData>> entry : groupedImages.entrySet()) {
char key = entry.getKey();
List<PictureData> images = entry.getValue();
for (PictureData image : images) {
byte[] data = image.getData();
System.out.println(data);
}
}
// 遍历图片数据，将图片写入磁盘
for (Map.Entry<String, PictureData> entry : map.entrySet()) {
String key = entry.getKey();
PictureData pictureData = entry.getValue();
String extension = pictureData.suggestFileExtension();
// 构建图片文件名，使用行列作为文件名
String[] coordinates = key.split("-");
String imageFileName = coordinates[0] + "-" + coordinates[1] + "." + extension;
// 将图片数据写入文件
FileOutputStream out = new FileOutputStream(imageTargetPath + File.separator + imageFileName);
out.write(pictureData.getData());
out.close();
}
}
private static String getCellValueAsString(Cell cell) {
String cellValue = "";
if (cell != null) {
switch (cell.getCellType()) {
case STRING:
cellValue = cell.getStringCellValue();
break;
case NUMERIC:
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
// 根据需要处理其他单元格类型
}
}
return cellValue;
}
/**
* 获取excel表中的图片
*
* @return
* @throws IOException
* @throws InvalidFormatException
* @throws EncryptedDocumentException
* @Param fis 文件输入流
* @Param sheetNum Excel表中的sheet编号
*/
public static Map<String, PictureData> getPictureFromExcel(File file, int sheetNum) throws EncryptedDocumentException, IOException {
// 获取图片PictureData集合
String fileName = file.getName();
Workbook workbook = null;
if (StringUtils.isEmpty(fileName)) {
return null;
}
FileInputStream fileInputStream = new FileInputStream(file);
if (fileName.endsWith("xls")) {
// 2003
workbook = new HSSFWorkbook(fileInputStream);
HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(sheetNum - 1);
Map<String, PictureData> pictures = getPictures(sheet);
return pictures;
} else if (fileName.endsWith("xlsx")) {
// 2007
workbook = new XSSFWorkbook(fileInputStream);
XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(sheetNum - 1);
Map<String, PictureData> pictures = getPictures(sheet);
return pictures;
}
return new HashMap();
}
/**
* 获取图片和位置 (xls版)
*
* @param sheet
* @return
* @throws IOException
*/
public static Map<String, PictureData> getPictures(HSSFSheet sheet) throws IOException {
Map<String, PictureData> map = new HashMap<String, PictureData>();
List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
for (HSSFShape shape : list) {
if (shape instanceof HSSFPicture) {
HSSFPicture picture = (HSSFPicture) shape;
HSSFClientAnchor cAnchor = picture.getClientAnchor();
PictureData pdata = picture.getPictureData();
// 行号-列号
String key = cAnchor.getRow1() + "-" + cAnchor.getCol1();
map.put(key, pdata);
}
}
return map;
}
/**
* 获取图片和位置 (xlsx版)
*
* @param sheet
* @return
* @throws IOException
*/
public static Map<String, PictureData> getPictures(XSSFSheet sheet) throws IOException {
Map<String, PictureData> map = new HashMap<String, PictureData>();
List<POIXMLDocumentPart> list = sheet.getRelations();
for (POIXMLDocumentPart part : list) {
if (part instanceof XSSFDrawing) {
XSSFDrawing drawing = (XSSFDrawing) part;
List<XSSFShape> shapes = drawing.getShapes();
for (XSSFShape shape : shapes) {
XSSFPicture picture = (XSSFPicture) shape;
XSSFClientAnchor anchor = picture.getPreferredSize();
CTMarker marker = anchor.getFrom();
// 行号-列号
String key = marker.getRow() + "-" + marker.getCol();
map.put(key, picture.getPictureData());
}
}
}
return map;
}
}
读取嵌入式图片信息
新建一个读取数据实体类：ExcelDataEntry
import java.io.File;
import java.util.Date;
public class ExcelDataEntry {
private Integer row;
private Integer col;
private Object data;
private String dataType;
public Integer getRow() {
return row;
}
public void setRow(Integer row) {
this.row = row;
}
public Integer getCol() {
return col;
}
public void setCol(Integer col) {
this.col = col;
}
public Object getData() {
return data;
}
public void setData(Object data) {
this.data = data;
if (data instanceof String) {
setDataType("String");
} else if (data instanceof Double) {
setDataType("Double");
} else if (data instanceof Integer) {
setDataType("Integer");
} else if (data instanceof File) {
setDataType("File");
} else if (data instanceof Date) {
setDataType("Date");
} else if (data instanceof Boolean) {
setDataType("Boolean");
} else {
setDataType("String");
}
}
public String getDataType() {
return dataType;
}
public void setDataType(String dataType) {
this.dataType = dataType;
}
@Override
public String toString() {
return "ExcelDataEntry{" +
"row=" + row +
", col=" + col +
", data=" + data +
", dataType='" + dataType + '\'' +
'}';
}
}
接下来直接上demo
import cn.hutool.json.JSONUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.*;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipInputStream;
public class ExcelReadImagesDemo {
private static Logger logger = LoggerFactory.getLogger(ExcelReadImagesDemo.class);
/**
* key :Rid value ImagePath
*/
private static Map<String, String> imageMap = new HashMap<>();
/**
* key imageId value Rid
*/
private static Map<String, String> imageIdMappingMap = new HashMap<>();
/**
* 文件根目录
*/
private static String rootPath = "";
public static void main(String[] args) {
Map<String, Object> map = new HashMap<>();
String imageTargetPath = "D:\\download\\image";// 替换自己输出图片的路径
String fileRootPath = "D:\\test11.xlsx";// 替换读取的文件路径
XSSFWorkbook workbook = null;
try {
FileInputStream fis = new FileInputStream(fileRootPath);
workbook = new XSSFWorkbook(fis);
fis.close();
rootPath = fileRootPath;
ridWithIDRelationShip(rootPath);
ridWithImagePathRelationShip(rootPath);
} catch (IOException e) {
throw new RuntimeException(e);
}
XSSFSheet sheet = workbook.getSheetAt(0);
// 0是表头 1开始读取数据行
int rowIndex = 1;
// 数据结果集
List<Map<Integer, ExcelDataEntry>> rowDataList = new ArrayList<>();
// 循环读取每一行
for (; ; ) {
HashMap<Integer, ExcelDataEntry> resuletMap = new HashMap<>();
if (rowIndex >= sheet.getPhysicalNumberOfRows()) {
break;
}
Row row = sheet.getRow(rowIndex);
int colIndex = 0;
int rowPhysicalNumberOfCells = 0;
if (row != null) {
rowPhysicalNumberOfCells = row.getPhysicalNumberOfCells();
}
// 循环读取每一列
for (; ; ) {
if (colIndex > rowPhysicalNumberOfCells) {
break;
}
ExcelDataEntry colDataItem = new ExcelDataEntry();
colDataItem.setRow(rowIndex);
colDataItem.setCol(colIndex);
String RCindex = rowIndex + "-" + colIndex;
if (rowPhysicalNumberOfCells != 0) {
// 获取数据，但是我们获取的cell类型
Cell cell = row.getCell(colIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
if (cell == null) {
continue;
}
// 获取数据类型
String dataType = getCellDataType(cell);
// 转换为字符串类型
Object currentData = getCellValueByCell(cell);
// 获取得到字符串
// String currentData = cell.getStringCellValue();
// 时间格式化定义
DateFormat df = new SimpleDateFormat("yyyyMMdd");
if ("FORMULA".equals(dataType)) {
if (currentData.toString().contains("DISPIMG")) {
try {
String imageId = subImageId(currentData.toString());
String picPath = getImplantPicById(imageId);
InputStream picInputStream = openFile("xl/" + picPath);
String fileName = imageTargetPath + "/" + getFileRealName(picPath);
File pic = saveFile(picInputStream, fileName);
map.put(RCindex, pic);
colDataItem.setData(pic);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
} else if (currentData != null) {
colDataItem.setData(currentData);
}
}
if (colDataItem.getData() != null) {
resuletMap.put(colIndex, colDataItem);
}
colIndex++;
}
rowDataList.add(resuletMap);
rowIndex++;
}
logger.info("图片信息:{}", JSONUtil.toJsonStr(map));
logger.info("读取全部数据:{}", JSONUtil.toJsonStr(rowDataList));
}
private static String getCellDataType(Cell cell) {
CellType cellType = cell.getCellType();
String type = null;
switch (cellType) {
case STRING:
type = "STRING";
break;
case NUMERIC:
type = "NUMERIC";
break;
case BOOLEAN:
type = "BOOLEAN";
break;
case FORMULA:
type = "FORMULA";
break;
default:
type = "STRING";
}
return type;
}
/**
* 获取单元格各类型值，返回字符串类型
*
* @param cell
* @return
*/
public static String getCellValueByCell(Cell cell) {
// 判断是否为null或空串
if (cell == null || cell.toString().trim().equals("")) {
return "";
}
String cellValue = "";
switch (cell.getCellType()) {
case NUMERIC: // 数字
short format = cell.getCellStyle().getDataFormat();
if (DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = null;
if (format == 20 || format == 32) {
sdf = new SimpleDateFormat("HH:mm");
} else if (format == 14 || format == 31 || format == 57 || format == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil
.getJavaDate(value);
cellValue = sdf.format(date);
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
try {
cellValue = sdf.format(cell.getDateCellValue());// 日期
} catch (Exception e) {
try {
throw new Exception("exception on get date data !".concat(e.toString()));
} catch (Exception e1) {
e1.printStackTrace();
}
} finally {
sdf = null;
}
} else {
BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
cellValue = bd.toPlainString();// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
}
break;
case STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue() + "";
;
break;
case FORMULA: // 公式
cellValue = cell.getCellFormula();
break;
case BLANK: // 空值
cellValue = "";
break;
case ERROR: // 故障
cellValue = "ERROR VALUE";
break;
default:
cellValue = "UNKNOW VALUE";
break;
}
return cellValue;
}
/**
* 截取函数中图片的ID
*
* @param imageId
* @return
*/
private static String subImageId(String imageId) {
return imageId.substring(imageId.indexOf("ID_") + 3, imageId.lastIndexOf("\""));
}
/**
* 使用嵌入式图片ID获取嵌入式图片地址
*
* @param imageId
* @return
* @throws IOException
*/
private static String getImplantPicById(String imageId) throws IOException {
String imageRid = imageIdMappingMap.get(imageId);
String imagePath = imageMap.get(imageRid);
return imagePath;
}
/**
* 打开文件根目录
*
* @param filePath
* @return
*/
private static InputStream openFile(String filePath) {
try {
// 文件根目录
File file = new File(rootPath);
ZipFile zipFile = new ZipFile(file);
ZipInputStream zipInputStream = new ZipInputStream(new FileInputStream(file));
ZipEntry nextEntry = null;
while ((nextEntry = zipInputStream.getNextEntry()) != null) {
String name = nextEntry.getName();
if (name.equalsIgnoreCase(filePath)) {
return zipFile.getInputStream(nextEntry);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
private static File saveFile(InputStream inputStream, String fileName) throws IOException {
OutputStream outputStream = null;
try {
File outputFile = new File(fileName);
if (!outputFile.getParentFile().exists()) {
outputFile.getParentFile().mkdirs();
}
outputStream = new FileOutputStream(outputFile);
byte[] buffer = new byte[1024];
int length;
while ((length = inputStream.read(buffer)) > 0) {
outputStream.write(buffer, 0, length);
}
return outputFile;
} catch (Exception e) {
e.printStackTrace();
} finally {
if (outputStream != null) {
outputStream.close();
}
if (inputStream != null) {
inputStream.close();
}
}
return null;
}
private static String getFileRealName(String fileName) {
try {
fileName.replace("\\", "/");
fileName = fileName.substring(fileName.lastIndexOf("/") + 1);
} catch (Exception e) {
return fileName;
}
return fileName;
}
/**
* 整理图片函数中的ID和Rid的关系
*
* @param path
* @throws IOException
*/
private static void ridWithIDRelationShip(String path) throws IOException {
InputStream inputStream = null;
try {
// 读取关系xml文件
inputStream = openFile("xl/cellimages.xml");
// 创建SAXReader对象
SAXReader reader = new SAXReader();
// 加载xml文件
Document dc = reader.read(inputStream);
// 获取根节点
Element rootElement = dc.getRootElement();
// 获取子节点 每一个图片节点
List<Element> cellImageList = rootElement.elements();
// 循环处理每一个图片
for (Element cellImage : cellImageList) {
Element pic = cellImage.element("pic");
Element nvPicPr = pic.element("nvPicPr");
Element cNvPr = nvPicPr.element("cNvPr");
// 图片id
String imageId = cNvPr.attribute("name").getValue().replace("ID_", "");
// imageId = subImageId(imageId);
Element blipFill = pic.element("blipFill");
Element blip = blipFill.element("blip");
// 图片Rid
String imageRid = blip.attribute("embed").getValue();
// 存入map中
imageIdMappingMap.put(imageId, imageRid);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (inputStream != null) {
inputStream.close();
}
}
}
/**
* 整理Rid和图片地址的关系
*
* @param path
* @throws IOException
*/
private static void ridWithImagePathRelationShip(String path) throws IOException {
InputStream inputStream = null;
try {
// 读取关系文件
inputStream = openFile("xl/_rels/cellimages.xml.rels");
// 创建SAXReader对象
SAXReader reader = new SAXReader();
// 加载xml文件
Document dc = reader.read(inputStream);
// 获取根节点
Element rootElement = dc.getRootElement();
List<Element> imageRelationshipList = rootElement.elements();
// 处理每个关系
for (Element imageRelationship : imageRelationshipList) {
String imageRid = imageRelationship.attribute("Id").getValue();
String imagePath = imageRelationship.attribute("Target").getValue();
imageMap.put(imageRid, imagePath);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (inputStream != null) {
inputStream.close();
}
}
}
}
总结
至此，完成。
第一段读取普通图片信息代码直接参照原文链接1实现，其他博客也有很多，几乎实现方法都一样。
第二段读取嵌入式图片信息代码是基于参考原文链接2实现，我嫌源代码太复杂，于是乎自己精简了一下代码实现读取Excel数据和图片信息。
经过我自己修改可以直接拿来使用读取Excel文件内容和嵌入式图片信息。有什么问题欢迎大家评论区讨论，如果对你有帮助麻烦动动发财小手点个赞。
参考原文链接1：java 读取excel图片导入(亲测有效)
参考原文链接2：java读取Excel,(支持WPS嵌入式图片)
468
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
