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

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;
    }
}

  • 4
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
在Java项目使用EasyExcel导出数据时,如果数据量超过了Excel 2003(即xls格式)的最大行数65536,可以采用以下两种方式来解决: 1. 使用Excel 2007或以上版本的xlsx格式,该格式最大支持1048576行,可以满足大部分需求。只需要在EasyExcel的写操作将文件格式设置为xlsx即可: ```java // 导出Excel,文件格式为xlsx EasyExcel.write(fileName, dataClass).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).doWrite(dataList); ``` 2. 将数据分批次导出,每次导出数据量不超过65536行,最后再将多个Excel文件合并成一个。可以使用Apache POI的Workbook类来实现Excel文件的合并,具体实现方法可以参考以下代码: ```java // 初始化Workbook对象 Workbook wb = new HSSFWorkbook(); // 遍历多个Excel文件,将每个Excel文件的数据读入到Workbook对象 for (int i = 0; i < fileCount; i++) { InputStream inputStream = new FileInputStream("文件路径"); Workbook tmpWb = WorkbookFactory.create(inputStream); int sheetCount = tmpWb.getNumberOfSheets(); for (int j = 0; j < sheetCount; j++) { Sheet tmpSheet = tmpWb.getSheetAt(j); Sheet sheet = wb.createSheet(tmpSheet.getSheetName()); int rowCount = tmpSheet.getLastRowNum(); for (int k = 0; k <= rowCount; k++) { Row tmpRow = tmpSheet.getRow(k); Row row = sheet.createRow(k); int cellCount = tmpRow.getLastCellNum(); for (int l = 0; l < cellCount; l++) { Cell tmpCell = tmpRow.getCell(l); Cell cell = row.createCell(l); if (tmpCell != null) { cell.setCellStyle(tmpCell.getCellStyle()); int cellType = tmpCell.getCellType(); switch (cellType) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: cell.setCellValue(tmpCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: cell.setCellValue(tmpCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: cell.setCellFormula(tmpCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: cell.setCellValue(tmpCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: cell.setCellValue(tmpCell.getStringCellValue()); break; } } } } } } // 将合并后的数据写入到一个Excel文件 FileOutputStream fileOut = new FileOutputStream("输出文件路径"); wb.write(fileOut); fileOut.close(); ```
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值