【问题解决】EasyExcel导出数据，并将数据中的实体类url转为图片

EasyExcel导出数据，并将数据中的实体类url转为图片

在导出excel数据时，用户要求把存储二维码url转为图片保存，然后研究了一下具体实现。

代码展示：

public void exportData(String pointName, String districtName, String streetName, HttpServletResponse response){//init dataList<GarbagePointExportTemplate> list = pointBSService.exportPointData(pointName, districtName, streetName);//handlerWriteCellStyle writeCellStyle = new WriteCellStyle();writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);HorizontalCellStyleStrategy cellStyleStrategy = new HorizontalCellStyleStrategy(writeCellStyle, writeCellStyle);// Export data to ExcelByteArrayOutputStream outputStream = new ByteArrayOutputStream();EasyExcel.write(outputStream, GarbagePointExportTemplate.class).sheet("Sheet1").registerWriteHandler(cellStyleStrategy).doWrite(list);// Insert images into exported Exceltry {ExcelUtils.insertImages(outputStream, list);// 设置响应头response.setContentType("application/vnd.ms-excel");response.setHeader("Content-Disposition", "attachment; filename=点位集合.xlsx");// 将文件数据写入响应的输出流response.getOutputStream().write(outputStream.toByteArray());response.getOutputStream().flush();response.getOutputStream().close();} catch (IOException e) {throw new RuntimeException(e);}}

ExcelUtils

package com.jeesite.modules.utils.excel;import com.jeesite.modules.backstage.entity.GarbagePointExportTemplate;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;import java.io.*;
import java.net.URL;
import java.util.List;/*** @author fwh* @date 2024/5/9/009* @remark:*/
public class ExcelUtils {public static void insertImages(ByteArrayOutputStream outputStream, List<GarbagePointExportTemplate> exportData) throws IOException {try (Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(outputStream.toByteArray()))) {Sheet sheet = workbook.getSheet("Sheet1"); // 获取 Sheet1Drawing<?> drawing = sheet.createDrawingPatriarch();CreationHelper creationHelper = workbook.getCreationHelper();int rowIndex = 1;for (GarbagePointExportTemplate item : exportData) {Row row = sheet.getRow(rowIndex);Cell cell = row.getCell(9); // Assuming the URL is in the tenth columnString codeUrl = item.getCheckCode();if (codeUrl != null && !codeUrl.isEmpty()) {// 清空 URL 数据(cell.setCellValue("") 设置单元格的值为空字符串并不会清除单元格中的链接或图片。这是因为链接和图片是以不同的方式存储在单元格中的，仅设置值为空字符串并不会触发删除操作。)cell.setCellType(CellType.BLANK);byte[] imageBytes = getImageBytes(codeUrl);if (imageBytes != null) {int pictureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG);ClientAnchor anchor = creationHelper.createClientAnchor();anchor.setCol1(cell.getColumnIndex());anchor.setRow1(cell.getRowIndex());anchor.setCol2(cell.getColumnIndex() + 1);anchor.setRow2(cell.getRowIndex() + 1);Picture picture = drawing.createPicture(anchor, pictureIdx);double desiredWidth = 0.8; // Set the width of the picture to 80%double desiredHeight = 1; // Set the height of the picture to 80%picture.resize(desiredWidth, desiredHeight);}}rowIndex++;}outputStream.reset(); // 重置 ByteArrayOutputStreamworkbook.write(outputStream);}}private static byte[] getImageBytes(String codeUrl) throws IOException {try (InputStream inputStream = new URL(codeUrl).openStream()) {return IOUtils.toByteArray(inputStream);}}
}

实体类

package com.jeesite.modules.backstage.entity;import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.jeesite.common.utils.excel.annotation.ExcelField;
import com.jeesite.common.utils.excel.annotation.ExcelFields;/*** @author fwh* @date 2024/5/8/008* @remark:*/@ContentRowHeight(60)
@ColumnWidth(20)
public class GarbagePointExportTemplate {@ColumnWidth(30)@ExcelProperty("点位名称")private String pointName;@ColumnWidth(30)@ExcelProperty("区县名称")private String districtsName;@ColumnWidth(30)@ExcelProperty("街道名称")private String streetName;@ColumnWidth(30)@ExcelProperty("社区名称")private String communityName;@ColumnWidth(15)@ExcelProperty("责任人")private String dutyName;@ColumnWidth(15)@ExcelProperty("责任人电话")private String mobile;@ColumnWidth(10)@ExcelProperty("经度")private String lon;@ColumnWidth(10)@ExcelProperty("维度")private String lat;@ColumnWidth(30)@ExcelProperty("详情地址")private String addr;@ColumnWidth(20)@ExcelProperty("二维码")private String checkCode;@ExcelFields({@ExcelField(title="点位名称", attrName="pointName",width = 25*256, align= ExcelField.Align.CENTER, sort=1)})public String getPointName() {return pointName;}public void setPointName(String pointName) {this.pointName = pointName;}@ExcelFields({@ExcelField(title="点位区县", attrName="districtsName",width = 25*256, align= ExcelField.Align.CENTER, sort=2)})public String getDistrictsName() {return districtsName;}public void setDistrictsName(String districtsName) {this.districtsName = districtsName;}@ExcelFields({@ExcelField(title="街道名称", attrName="streetName",width = 25*256, align= ExcelField.Align.CENTER, sort=3)})public String getStreetName() {return streetName;}public void setStreetName(String streetName) {this.streetName = streetName;}@ExcelFields({@ExcelField(title="社区名称", attrName="communityName",width = 25*256, align= ExcelField.Align.CENTER, sort=4)})public String getCommunityName() {return communityName;}public void setCommunityName(String communityName) {this.communityName = communityName;}@ExcelFields({@ExcelField(title="责任人", attrName="dutyName",width = 25*256, align= ExcelField.Align.CENTER, sort=5)})public String getDutyName() {return dutyName;}public void setDutyName(String dutyName) {this.dutyName = dutyName;}@ExcelFields({@ExcelField(title="责任人电话", attrName="mobile",width = 25*256, align= ExcelField.Align.CENTER, sort=6)})public String getMobile() {return mobile;}public void setMobile(String mobile) {this.mobile = mobile;}@ExcelFields({@ExcelField(title="经度", attrName="lon",width = 25*256, align= ExcelField.Align.CENTER, sort=7)})public String getLon() {return lon;}public void setLon(String lon) {this.lon = lon;}@ExcelFields({@ExcelField(title="维度", attrName="lat",width = 25*256, align= ExcelField.Align.CENTER, sort=8)})public String getLat() {return lat;}public void setLat(String lat) {this.lat = lat;}@ExcelFields({@ExcelField(title="具体地址", attrName="addr",width = 25*256, align= ExcelField.Align.CENTER, sort=9)})public String getAddr() {return addr;}public void setAddr(String addr) {this.addr = addr;}@ExcelFields({@ExcelField(title="点位二维码", attrName="checkCode",width = 25*256, align= ExcelField.Align.CENTER, sort=10)})public String getCheckCode() {return checkCode;}public void setCheckCode(String checkCode) {this.checkCode = checkCode;}
}