EasyExcel导出数据,并将数据中的实体类url转为图片
在导出excel数据时,用户要求把存储二维码url转为图片保存,然后研究了一下具体实现。
代码展示:
public void exportData(String pointName, String districtName, String streetName, HttpServletResponse response){
//init data
List<GarbagePointExportTemplate> list = pointBSService.exportPointData(pointName, districtName, streetName);
//handler
WriteCellStyle writeCellStyle = new WriteCellStyle();
writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
HorizontalCellStyleStrategy cellStyleStrategy = new HorizontalCellStyleStrategy(writeCellStyle, writeCellStyle);
// Export data to Excel
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
EasyExcel.write(outputStream, GarbagePointExportTemplate.class)
.sheet("Sheet1")
.registerWriteHandler(cellStyleStrategy)
.doWrite(list);
// Insert images into exported Excel
try {
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"); // 获取 Sheet1
Drawing<?> 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 column
String 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(); // 重置 ByteArrayOutputStream
workbook.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;
}
}