poi 将图片写入到excel文件中
- 功能点说明
- 作用：将图片写入到指定的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@Datapublic 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();}}
}