概述
业务中经常需要按照一个特定的模板导出特定内容,有些单元格还要求特殊的格式。
这里以一个单元格填充多张图片为例。
模板导出时一个单元格填充多张图片
springboot版本
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.12-SNAPSHOT</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
easyExcel版本
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
用到了hutool工具类
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.0</version>
</dependency>
实现概述:
通过writeHandler监听对应的单元格渲染(将指定单元格先默认为空,然后在渲染完之后自己填充图片内容),按照自己的需求填充图片
handler
package com.fchan.excelexport.handler;
import cn.hutool.core.io.IoUtil;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.Units;
import org.springframework.core.io.ClassPathResource;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Objects;
@Slf4j
public class ImageModifyHandler implements CellWriteHandler {
/**
* 数据转换完成后
* @param writeSheetHolder
* @param writeTableHolder
* @param cellData
* @param cell
* @param head
* @param relativeRowIndex
* @param isHead
*/
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 此处不处理表头,不处理不包含图像的
boolean noImageValue = Objects.isNull(cellData) ||
CollectionUtils.isEmpty(cellData.getImageDataList());
if(Objects.equals(Boolean.TRUE, isHead) || noImageValue){
return;
}
cellData.setType(CellDataTypeEnum.EMPTY);
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 在 单元格写入完毕后 ,自己填充图片
int rowIndex = cell.getRowIndex();
int columnIndex = cell.getColumnIndex();
//指定单元格自己渲染图片
if(rowIndex == 3 && columnIndex == 3){
log.info("第{}行,第{}列数据转换后", cell.getRowIndex(), cell.getColumnIndex());
Sheet sheet = cell.getSheet();
//图片的数据可以从db中获取
ClassPathResource imgClassPathResource = new ClassPathResource("imgs/my_huckleberry_friends.jpg");
InputStream imgInputStream = null;
try {
imgInputStream = imgClassPathResource.getInputStream();
} catch (IOException e) {
throw new RuntimeException(e);
}
byte[] imgBytes = IoUtil.readBytes(imgInputStream);
//每张图片的宽度
int picWidth = Units.pixelToEMU(60);
//列宽设置为图片的总宽度---不设置宽度,当图片的坐标超过单元格宽度时图片显示失败
//EMU转字符宽度:characterWidth = emu / 256
sheet.setColumnWidth(columnIndex, picWidth * 10 / 256);
//假设图片张数10张
for (int i = 0 ; i < 10; i ++) {
int index = sheet.getWorkbook().addPicture(imgBytes, HSSFWorkbook.PICTURE_TYPE_JPEG);
Drawing drawing = sheet.getDrawingPatriarch();
if (drawing == null) {
drawing = sheet.createDrawingPatriarch();
}
CreationHelper helper = sheet.getWorkbook().getCreationHelper();
ClientAnchor anchor = helper.createClientAnchor();
// 设置图片坐标
anchor.setDx1(picWidth * i);
anchor.setDx2(picWidth * (1 + i));
anchor.setDy1(0);
anchor.setDy2(0);
//设置图片位置
anchor.setCol1(cell.getColumnIndex());
anchor.setCol2(cell.getColumnIndex());
anchor.setRow1(cell.getRowIndex());
anchor.setRow2(cell.getRowIndex() + 1);
// 设置图片可以随着单元格移动
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
drawing.createPicture(anchor, index);
}
}
}
}
在使用模板填充时注册handler
public static void complexFillMutilFile(HttpServletRequest request, HttpServletResponse response) throws IOException {
// 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"{","}"代替
// {} 代表普通变量 {.} 代表是list的变量
// 填充数据
byte[] imgBytes = new byte[]{};
Map<String, Object> valMap = new HashMap<>();
valMap.put("name", "123");
valMap.put("address", "333");
//图片单元格需要一个空值,为null时会跳过该单元格,导致无法自定义渲染
valMap.put("imgs", imgBytes);
valMap.put("name2", "123");
valMap.put("address2", "333");
//图片单元格需要一个空值,为null时会跳过该单元格,导致无法自定义渲染
valMap.put("imgs2", imgBytes);
// 处理数据 (此处是业务需要,可以自行更改)
//获取模板 (我是将模板放到 resource文件下面了)
ClassPathResource classPathResource = new ClassPathResource("excelTemplate/templateExport.xlsx");
InputStream inputStream = classPathResource.getInputStream();
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = "templateExport" + File.separator + System.currentTimeMillis() + ".xlsx";
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
// 生成sheet
WriteSheet sheet1 = EasyExcel.writerSheet(0, "oppm").build();
ExcelWriter write = EasyExcel.write(response.getOutputStream())
.registerWriteHandler(new ImageModifyHandler())
.withTemplate(inputStream)
.build();
// 填充数据
write.fill(valMap, sheet1);
write.close();
}