- 功能点说明
- 作用：将图片写入到指定的excel文件（或output流）
- 依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.6.3</version>
</dependency>
注意：这两个版本不一样的话可能会有版本对应的问题
- 用法代码解析
/**
row 行
col 列
data 图片数据byte数组
workbook 目标
patriarch 画图对象
**/
public static void writeImg(Integer row, Integer col, byte[] data, Workbook workbook, Drawing<?> patriarch) {
//构建定位器（即要写入的图片写哪个格子、占多少格之类的）
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 1023, col.shortValue(), row.shortValue(), col.shortValue() + 1, row.shortValue() + 1);
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
patriarch.createPicture(anchor, workbook.addPicture(data, XSSFWorkbook.PICTURE_TYPE_JPEG));
}
- 实战
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.ExcelWriter;
import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.StyleSet;
import lombok.Builder;
import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Test {
public static void main(String[] args) {
List<Person> objects = CollUtil.newArrayList();
Person person = null;
for (int i = 0; i < 10; i++) {
File file = new File("/12.jpg");
File file1 = new File("/房子.jpeg");
person = Person.builder()
.age(10+i)
.name("张三" + i)
.url(i % 2 == 0 ? FileUtil.readBytes(file) : FileUtil.readBytes(file1))
.build();
objects.add(person);
}
getExcelByBean(objects);
}
private static void getExcelByBean(List<Person> list) {
//excel大数据生成
File file = new File("/目标.xlsx");
BigExcelWriter writer = ExcelUtil.getBigWriter(file);
Workbook workbook = writer.getWorkbook();
Sheet sheet = writer.getSheet();
//设置默认高度
sheet.setDefaultRowHeight((short) 1000);
writer.addHeaderAlias("name", "姓名");
writer.addHeaderAlias("age", "年龄");
writer.addHeaderAlias("url", "图片");
//构建画图对象
Drawing<?> patriarch = sheet.createDrawingPatriarch();
int j = 1;
for (int i = 0; i < list.size(); i++) {
sheet.setColumnWidth(i, 20 * 256);
Person person = list.get(i);
byte[] url = person.getUrl();
//这个要注意，2指的图片位置，从0开始
writeImg(j++, 2, url, workbook, patriarch);
person.setUrl(null);
}
StyleSet style = writer.getStyleSet();
CellStyle cellStyle = style.getCellStyleForDate();
cellStyle.setDataFormat(writer.getWorkbook().createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
writer.write(list);
writer.flush();
writer.close();
}
public static void writeImg(Integer row, Integer col, byte[] data, Workbook workbook, Drawing<?> patriarch) {
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 1023, col.shortValue(), row.shortValue(), col.shortValue() + 1, row.shortValue() + 1);
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
patriarch.createPicture(anchor, workbook.addPicture(data, XSSFWorkbook.PICTURE_TYPE_JPEG));
}
@Builder
@Data
public static class Person {
private String name;
private int age;
private byte[] url;
}
}
- 应用实战
private static void getExcelByBean(List<Person> list, HttpServletResponse response) {
//excel大数据生成
File file = new File("/目标.xlsx");
BigExcelWriter writer = ExcelUtil.getBigWriter(file);
Workbook workbook = writer.getWorkbook();
Sheet sheet = writer.getSheet();
//设置默认高度
sheet.setDefaultRowHeight((short) 1000);
writer.addHeaderAlias("name", "姓名");
writer.addHeaderAlias("age", "年龄");
writer.addHeaderAlias("url", "图片");
//构建画图对象
Drawing<?> patriarch = sheet.createDrawingPatriarch();
int j = 1;
for (int i = 0; i < list.size(); i++) {
sheet.setColumnWidth(i, 20 * 256);
Person person = list.get(i);
byte[] url = person.getUrl();
//这个要注意，2指的图片位置，从0开始
writeImg(j++, 2, url, workbook, patriarch);
person.setUrl(null);
}
StyleSet style = writer.getStyleSet();
CellStyle cellStyle = style.getCellStyleForDate();
cellStyle.setDataFormat(writer.getWorkbook().createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Disposition","attachment;filename="+URLEncoder.encode("文件123.xlsx"),"utf-8"));
ServletOutPutStream outputStream = response.getOutputSteam();
writer.write(list);
writer.flush(outputStream);
writer.close();
}
public static void writeImg(Integer row, Integer col, byte[] data, Workbook workbook, Drawing<?> patriarch) {
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 1023, col.shortValue(), row.shortValue(), col.shortValue() + 1, row.shortValue() + 1);
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
patriarch.createPicture(anchor, workbook.addPicture(data, XSSFWorkbook.PICTURE_TYPE_JPEG));
}
public static class Person {
private String name;
private int age;
private byte[] url;
public Person() {
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public byte[] getUrl() {
return url;
}
public void setUrl(byte[] url) {
this.url = url;
}
}
图片并排填充可以参考文章（点此跳转）
单元格合并参考文章（点此跳转）
单元格合并代码样例
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class MergeRowsExample {
public static void main(String[] args) {
// 创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Sheet1");
// 创建行和单元格
for (int i = 0; i < 10; i++) {
Row row = sheet.createRow(i);
Cell cell = row.createCell(0);
cell.setCellValue("Value " + i);
}
// 合并行，从第 2 行到第 4 行
sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(2, 4, 0, 0));
try (FileOutputStream outputStream = new FileOutputStream("merged_rows.xlsx")) {
workbook.write(outputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
}