Java实现图片粘贴到excel

package com.utils;

import java.awt.image.BufferedImage;

import java.io.ByteArrayOutputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.util.ArrayList;

import javax.imageio.ImageIO;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFClientAnchor;

import org.apache.poi.xssf.usermodel.XSSFDrawing;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Screen_write_to_Excel {

public static void main(String[] args) {

String path="D:/Project/e-Statement/estatement_pdf/2021_06_08_estatement/";

String filename="Result_fail_Refs.xlsx";

Screen_write_to_Excel s = new Screen_write_to_Excel();

s.get_Customer_ID(path,filename);

System.out.println("Runfinish");

}

public void get_Customer_ID(String path,String filename) {

ArrayList<String> fail_Customer_id_list = new ArrayList<>();

FileInputStream fileInputStream = null;

FileOutputStream out;

try {

String source_file= path+"/"+filename;

fileInputStream = new FileInputStream(source_file);

XSSFWorkbook wk = new XSSFWorkbook(fileInputStream);

XSSFSheet sheet = wk.getSheetAt(0);

int num=sheet.getLastRowNum();

for(int i=1;i<num+1;i++) {

//System.out.println(sheet.getRow(i).getCell(0).toString());

fail_Customer_id_list.add(sheet.getRow(i).getCell(0).toString());

}

for(int i =0;i<fail_Customer_id_list.size();i++) {

wk.createSheet();

wk.setSheetName(i+1,fail_Customer_id_list.get(i));

}

get_Refs_Write_to_Excel(fail_Customer_id_list, wk, path);

out = new FileOutputStream(source_file);

wk.write(out);

out.close();

} catch (Exception e) {

e.printStackTrace();

}

}

public XSSFWorkbook get_Refs_Write_to_Excel(ArrayList<String> fail_Customer_id_list,XSSFWorkbook wk,String folder_path) {

String picture_path = folder_path+"//Image";

for(int i=0;i<fail_Customer_id_list.size();i++) {

String ID = fail_Customer_id_list.get(i);

File file = new File(picture_path);

if(!file.exists()) {

return null;

}

File[] fs = file.listFiles();

int count=0;

for(File f:fs) {

String file_name = f.getName();

if(file_name.startsWith(ID)&&f.getName().contains(".png")) {

PictureWriteToExcel(f.getAbsolutePath().trim(),wk,wk.getSheet(ID),count);

count++;

}

}

}

return wk;

}

public void PictureWriteToExcel(String picture_path,XSSFWorkbook wb,XSSFSheet sheet,int count) {

FileOutputStream fileOut = null;

BufferedImage bufferImg = null;//图片

try {

// 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray

ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();

//将图片读到BufferedImage

bufferImg = ImageIO.read(new File(picture_path));

// 将图片写入流中

ImageIO.write(bufferImg, "png", byteArrayOut);

// 创建一个工作薄

//XSSFWorkbook wb = new XSSFWorkbook();

//创建一个sheet

//XSSFSheet sheet = wb.createSheet("out put excel");

// 利用HSSFPatriarch将图片写入EXCEL

//HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

XSSFDrawing patriarch = sheet.createDrawingPatriarch();

/**

* 该构造函数有8个参数

* 前四个参数是控制图片在单元格的位置，分别是图片距离单元格left，top，right，bottom的像素距离

* 后四个参数，前两个表示图片左上角所在的cellNum和 rowNum，后两个参数对应的表示图片右下角所在的cellNum和 rowNum，

* excel中的cellNum和rowNum的index都是从0开始的

*

*/

//图片一导出到单元格中

//XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0,

//(short) 0, 0, (short) 6, 12);

XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0,

(short) 0, count*7, (short) 18, count*7+6);

// 插入图片

patriarch.createPicture(anchor, wb.addPicture(byteArrayOut

.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));

//生成的excel文件地址

//fileOut = new FileOutputStream("D:\\123.xlsx");

// 写入excel文件

//wb.write(fileOut);

} catch (IOException io) {

io.printStackTrace();

System.out.println("io erorr : " + io.getMessage());

} finally {

if (fileOut != null) {

try {

fileOut.close();

} catch (IOException e) {

e.printStackTrace();

}

}

}

}

}