jar依赖:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
HSSF代码具体实现:
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
public class Test {
public static void main(String[] args) {
HSSFWorkbook book=new HSSFWorkbook();
HSSFSheet sheet=book.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
BufferedImage bufferedImage = null;
try {
String[] imgs = {"D:\\photos\\792b6604637e40de2f0c800db6648122.jpeg",
"D:\\photos\\d701e5d7f2f32d27ff3cb8e919e56b61.jpeg",
"D:\\photos\\e1885b439f188ef9ed1ab209af35b3ee.jpeg" };
// 计算边距
int mar = 10 + 10 + (imgs.length - 1) * 10;
// 大致平均值,每个图片宽度(1023为每个单元格总比,)
int ave = (1023 - mar) / imgs.length;
//设置图片位于表格的单元格下标
int hang=0;
int lie=0;
HSSFRow row = sheet.createRow(hang);
//设置单元个宽高,单元格宽高限制了图片的宽高
row.setHeight((short)(100*10));
sheet.setColumnWidth(lie,4800*imgs.length);
for (int i = 0; i < imgs.length; i++) {
File file = new File(imgs[i]);
bufferedImage = ImageIO.read(file);
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
ImageIO.write(bufferedImage, "jpg", byteArrayOutputStream);
HSSFClientAnchor hSSFClientAnchor=null;
//dx1:图片左上角x坐标, dy1:图片左上角y坐标范, dx2:图片右下角x坐标, dy2:图片右下角y坐标。坐标x轴总比1023,坐标y轴总比255,图片排列样式根据左上角与右下角坐标比例排列
hSSFClientAnchor = new HSSFClientAnchor(10 * (i + 1) + ave * i, 10,(10 + ave) * (i + 1), 245, (short) lie, hang, (short) lie, hang);
patriarch.createPicture(hSSFClientAnchor,book.addPicture(byteArrayOutputStream.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
}
FileOutputStream outputStream = new FileOutputStream("C:\\Users\\LENOVO\\Desktop\\photo\\template\\test.xls");
book.write(outputStream);
outputStream.close();
} catch (Exception io) {
io.printStackTrace();
}
}
}
具体效果:
XSSF代码具体实现:
@Test
public void exportExcelImg(){
XSSFWorkbook wb = new XSSFWorkbook();
try {
XSSFSheet sheet = wb.createSheet();
XSSFDrawing patriarch = sheet.createDrawingPatriarch();
BufferedImage bufferedImage = null;
//遍历每行, 遍历单元格
String[] imgs = {
"D:\\photos\\792b6604637e40de2f0c800db6648122.jpeg",
"D:\\photos\\d701e5d7f2f32d27ff3cb8e919e56b61.jpeg",
"D:\\photos\\e1885b439f188ef9ed1ab209af35b3ee.jpeg",
"D:\\photos\\d701e5d7f2f32d27ff3cb8e919e56b61.jpeg",
"D:\\photos\\e1885b439f188ef9ed1ab209af35b3ee.jpeg" };
int hang=2;
int lie=0;
//设置单元个宽高,单元格宽高限制了图片的宽高
Row dataRow = sheet.createRow(hang);
dataRow.setHeight((short)(100*10));
sheet.setColumnWidth(lie,5000*imgs.length);
XSSFClientAnchor anchor=null;
for (int i = 0; i < imgs.length; i++) {
File file = new File(imgs[i]);
bufferedImage = ImageIO.read(file);
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
ImageIO.write(bufferedImage, "jpg", byteArrayOutputStream);
/* dx1:图片左边界距离单元格左边框像素值,
* dy1:图片上边界距离单元格上边框像素值,
* dx2:图片右边界距离单元格右边框像素值(负数),
* dy2:图片下边界距离单元格下边框像素值(负数),
* col1:列下标(0开始),
* row1:行下标(0开始),
* col2:列下标(1开始),
* row2:行下标(1开始)。*/
anchor = new XSSFClientAnchor(1500000*i, 200000,-((1500000*imgs.length)-(1500000*(i+1))), -100000, (short) lie, hang, (short) lie+1, hang+1);
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
patriarch.createPicture(anchor,wb.addPicture(byteArrayOutputStream.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
}
//输出目的地
FileOutputStream outputStream = new FileOutputStream("C:\\Users\\LENOVO\\Desktop\\photo\\template\\test.xlsx");
wb.write(outputStream);
outputStream.close();
} catch (Exception e){
e.printStackTrace();
}
}
具体效果: