java导入带图片的excel表格
参考
目前使用 ：Java读取Excel表格中的图片_java读取excel中的图片-CSDN博客
目前使用 ：java使用POI读取Excel表格中的图片（包含嵌入单元格图片，浮动图片，xls-2003,xlsx-2007）_workbook解析excel中的图片-CSDN博客
java导入带图片的excel表格（工具HSSFWorkbook-HSSFSheet）（支持一条数据（单元格）可以导入多个图片，）_java导入excel带图片-CSDN博客
java 读取excel图片导入(亲测有效)_java excel导入图片-CSDN博客
java导入带图片的excel_mob649e816ab022的技术博客_51CTO博客
Java 读取Excel（xlsx）的内容及图片并保存 - 简书 (jianshu.com)
JAVA POI的使用_poi-3.10-final-CSDN博客
JAVA POI的excel中包含图片进行读取保存,单张图片,多张图片_java获取单元格是多个图片,保存数据库中-CSDN博客
java读取Excel,(支持WPS嵌入式图片)_java读取excel中的图片-CSDN博客
POI 读取excel图片并定位图片需要提前注意的事项_sheet.getrelations()-CSDN博客
Java读取Excel数值内容带.0或变科学计数法的解决办法 - Yaaaaa - 博客园 (cnblogs.com)
读取图片
不同版本的excel有不同的拓展名（.[xls](https://so.csdn.net/so/search?q=xls&spm=1001.2101.3001.7020) .xlsx），对应不同的方法。
所有的图片在excel中保存在一个集合中，以行列号为key，图片为value，将结果封装到map集合中，图片的格式为“HSSFPictureData”“XSSFPictureData”。
|
/**
* 获取图片和位置 (xls)
* @param sheet
* @return
* @throws IOException
*/
public static Map<String, HSSFPictureData> getPictures (HSSFSheet sheet) throws IOException {
Map<String, HSSFPictureData> map = new HashMap<String, HSSFPictureData>();
List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
for (HSSFShape shape : list) {
if (shape instanceof HSSFPicture) {
HSSFPicture picture = (HSSFPicture) shape;
HSSFClientAnchor cAnchor = picture.getClientAnchor();
HSSFPictureData 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, XSSFPictureData> getPictures (XSSFSheet sheet) throws IOException {
Map<String, XSSFPictureData> map = new HashMap<String, XSSFPictureData>();
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;
}
|
从map拿到图片使用输出流将图片保存在本地
PictureData picData = map.get(hang-1+":"+20);
String ext = picData.suggestFileExtension();//获取图片的后缀名
byte[] data = picData.getData();
String fileName = UUID.randomUUID().toString();
//文件夹路径：temporarypath
String temporarypath = FtpPropertiesLoader.get("star");
FileOutputStream out = new FileOutputStream(star + fileName + ext);
out.write(data);
out.close();
|
自己开发时写的的代码
@Transactional
public ResultData databaseVendorInfoSizeInfo(MultipartFile file) {
try {
// System.out.println("开始时间" + new Date().toString());
Workbook book;
String originalFilename = file.getOriginalFilename();
String fileType = Objects.requireNonNull(originalFilename)
.substring(originalFilename.lastIndexOf(".") + 1);
Map<String, HSSFPictureData> HSSFPictureMap = new HashMap<String, HSSFPictureData>();
Map<String, XSSFPictureData> XSSFPictureMap = new HashMap<String, XSSFPictureData>();
if ("xls".equalsIgnoreCase(fileType)) {
book = new HSSFWorkbook(file.getInputStream());// 2003版本office
String fileName = originalFilename.substring(0, originalFilename.lastIndexOf("."));
int numberOfSheets = book.getNumberOfSheets();
if (numberOfSheets > 1) {
return ResultData.fail(fileName + "Sheet页大于1");
}
HSSFSheet sheet = (HSSFSheet) book.getSheetAt(0);
HSSFPictureMap = getHSSFSheetPictures(sheet);
} else {
book = new XSSFWorkbook(file.getInputStream());// 判断是否是2007版本office
String fileName = originalFilename.substring(0, originalFilename.lastIndexOf("."));
int numberOfSheets = book.getNumberOfSheets();
if (numberOfSheets > 1) {
return ResultData.fail(fileName + "Sheet页大于1");
}
XSSFSheet sheet = (XSSFSheet) book.getSheetAt(0);
XSSFPictureMap = getXSSFSheetPictures(sheet);
}
String userCode = UserUtils.getUserCode();
Date now = new Date();
VarConfig uploadFilePathConfig = varConfigMapper.getVarConfig("upload_file_path");
if (ObjectUtil.isNull(uploadFilePathConfig)) {
return ResultData.fail("NoUploadFilePath");
}
VarConfig urlConfig = varConfigMapper.getVarConfig("product_url");
if (ObjectUtil.isNull(urlConfig)) {
return ResultData.fail("NotPostTaskUrl");
}
String cp = "/";
String path = uploadFilePathConfig.getValue();
path = path.replace("\\", cp);
if (path.lastIndexOf(cp) != path.length() - 1) {
path = path + cp;
}
String filePath = urlConfig.getValue();
if (filePath.lastIndexOf(cp) != filePath.length() - 1) {
filePath = filePath + cp;
}
Sheet sheet = book.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum <= 3) {
return ResultData.fail("系统查看要导的数据为空,请导入数据");
}
for (int i = 3; i <= lastRowNum; i++) {
Row row = sheet.getRow(i);
if (null != row) {
boolean rowEmpty = isRowEmpty(row);
if (rowEmpty) {
continue;
}
//获取浮动图片
if ("xls".equalsIgnoreCase(fileType)) {
PictureData hssfPictureData = HSSFPictureMap.get(i + "-" + 1);
if (null != hssfPictureData) {
byte[] data = hssfPictureData.getData();
String end = hssfPictureData.suggestFileExtension();
String uuid = UUID.randomUUID().toString();
String pictureFileName = uuid + "." + end;
upload(data, pictureFileName, path + filePath + cp);
}
} else {
PictureData xssfPictureData = XSSFPictureMap.get(i + "-" + 1);
if (null != xssfPictureData) {
byte[] data = xssfPictureData.getData();
String end = xssfPictureData.suggestFileExtension();
String uuid = UUID.randomUUID().toString();
String pictureFileName = uuid + "." + end;
upload(data, pictureFileName, path + filePath + cp);
}
}
}
// System.out.println("结束时间" + new Date().toString());
} catch (Exception e) {
e.getMessage();
e.getStackTrace();
return ResultData.fail("请求异常" + e.getMessage());
}
return ResultData.succeed();
}
|
/**
* 获取图片和位置 (xls)
*
* @param sheet
* @return
* @throws IOException
*/
public static Map<String, HSSFPictureData> getHSSFSheetPictures(HSSFSheet sheet) throws IOException {
Map<String, HSSFPictureData> map = new HashMap<String, HSSFPictureData>();
List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
for (HSSFShape shape : list) {
if (shape instanceof HSSFPicture) {
HSSFPicture picture = (HSSFPicture) shape;
HSSFClientAnchor cAnchor = picture.getClientAnchor();
HSSFPictureData 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, XSSFPictureData> getXSSFSheetPictures(XSSFSheet sheet) throws IOException {
Map<String, XSSFPictureData> map = new HashMap<String, XSSFPictureData>();
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;
}
/**
* 上传文件
*/
public static String upload(byte[] fileByte, String fileName, String fileDir) throws IOException {
File file = new File(fileDir + fileName);
if (!file.exists()) { //判断要目标文件夹是否存在不存在则创建
File parentFile = file.getParentFile();
if (!parentFile.exists()) {
parentFile.mkdirs();
}
file.createNewFile();
} else {
file.delete();
file.createNewFile();
}
FileOutputStream out = new FileOutputStream(file);
out.write(fileByte);
out.close();
return fileName;
}
/**
* 判断是否空行
*/
public static boolean isRowEmpty(Row row) {
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell != null && cell.getCellType() != CellType.BLANK)
return false;
}
return true;
}
|
