1.我的poi版本
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
2.获取sheet中图片的信息
这两个函数分别获取03与07excel中的图片的信息(Anchor,图片流,图片类型)存入map中,用于在sheet中插入图片。
/**
* 获取图片和位置 (xls)
*/
public static Map<String, Object> getPicturesFromHSSFSheet (HSSFSheet sheet) throws IOException {
Map<String, Object> map = new HashMap<String, Object>();
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();
map.put("pictureAnchor", cAnchor);
map.put("pictureByteArray", pdata.getData());
map.put("pictureType", pdata.getPictureType());
}
}
return map;
}
/**
* 获取图片和位置 (xlsx)
*/
public static Map<String, Object> getPicturesFromXSSFSheet (XSSFSheet sheet) throws IOException {
Map<String, Object> map = new HashMap<String, Object>();
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();
map.put("pictureAnchor", anchor);
map.put("pictureByteArray", picture.getPictureData().getData());
map.put("pictureType", picture.getPictureData().getPictureType());
}
}
}
return map;
}
3.在sheet中插入图片
此部分参考我的另外一篇博客Java操作POI4.1.0向Excel添加图片兼容03(.xls)及07后(xlsx)两个版本
public static void copyPicture(HSSFWorkbook workbook,String sourceSheetName,String targetSheetName) throws IOException {
HSSFSheet sourceSheet=workbook.getSheet(sourceSheetName);
HSSFSheet targetSheet=workbook.getSheet(targetSheetName);
Map<String, Object> sourceSheetPicture=getPicturesFromHSSFSheet(sourceSheet);
HSSFPatriarch drawing=targetSheet.createDrawingPatriarch();
drawing.createPicture((HSSFClientAnchor) sourceSheetPicture.get("pictureAnchor"),
workbook.addPicture((byte[])sourceSheetPicture.get("pictureByteArray"),
Integer.parseInt(sourceSheetPicture.get("pictureType").toString())));
}
public static void copyPicture(XSSFWorkbook workbook,String sourceSheetName,String targetSheetName) throws IOException {
XSSFSheet sourceSheet=workbook.getSheet(sourceSheetName);
XSSFSheet targetSheet=workbook.getSheet(targetSheetName);
XSSFDrawing drawing=targetSheet.createDrawingPatriarch();
Map<String, Object> sourceSheetPicture=getPicturesFromXSSFSheet(sourceSheet);
drawing.createPicture((XSSFClientAnchor) sourceSheetPicture.get("pictureAnchor"),
workbook.addPicture((byte[])sourceSheetPicture.get("pictureByteArray"),
Integer.parseInt(sourceSheetPicture.get("pictureType").toString())));
}
4.测试主程序
public static void main(String[] args) {
String filePath="D:\\test.xlsx";
FileInputStream fileInputStream=null;
FileOutputStream fileOut=null;
try {
// fileInputStream=new FileInputStream(filePath);
// HSSFWorkbook workbook=new HSSFWorkbook(fileInputStream);
// copyPicture(workbook,"Sheet1","Sheet2");
// fileOut=new FileOutputStream(filePath);
// workbook.write(fileOut);
fileInputStream=new FileInputStream(filePath);
XSSFWorkbook workbook=new XSSFWorkbook(fileInputStream);
copyPicture(workbook,"Sheet1","Sheet2");
fileOut=new FileOutputStream(filePath);
workbook.write(fileOut);
System.out.print(" Success!");
}catch (Exception e) {
e.printStackTrace();
}finally {
try {
fileInputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
注意:此程序只支持sheet只有一个图片的情况,如果有两个以上的图片可有参考进行相应的修改!