easyexcel一个单元格导出多张图片等

keep_walk

已于 2023-01-05 20:05:15 修改

阅读量3.1k

点赞数 1

CC 4.0 BY-SA版权

文章标签： java 开发语言

于 2023-01-05 20:02:34 首次发布

本文链接：https://blog.csdn.net/keep_walk/article/details/128569258

1.引入easyexcel依赖


<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel-core</artifactId>
    <version>3.1.2</version>
    <scope>compile</scope>
</dependency>

2.设置自动列宽，我是获取到导出类上注解设置的宽度

/**
 * 自动列宽
 */
@Component
public class AutoColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {

    private static final int MAX_COLUMN_WIDTH = 255;

    //自定义列的列宽
    private Map<String, Integer> zdyColumnWidth = MapUtils.newHashMapWithExpectedSize(20);

    private Map<String,Integer> headNameColumnWidthMap = MapUtils.newHashMapWithExpectedSize(20);

    private final Map<Integer, Map<Integer, Integer>> cache = MapUtils.newHashMapWithExpectedSize(20);

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell,
                                  Head head,
                                  Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (!needSetWidth) {
            return;
        }

        String cellString = cell.toString();
        if (zdyColumnWidth.containsKey(cellString)) {
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), zdyColumnWidth.get(cellString) * 256);
            return;
        }

        if (headNameColumnWidthMap.containsKey(cellString)){
            cell.getCellStyle().setWrapText(true);
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), headNameColumnWidthMap.get(cellString) * 256);
            return;
        }

        if (true) return ;
        Map<Integer, Integer> maxColumnWidthMap = cache.get(writeSheetHolder.getSheetNo());
        if (maxColumnWidthMap == null) {
            maxColumnWidthMap = new HashMap<Integer, Integer>(16);
            cache.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
        }
        Integer columnWidth = dataLength(cellDataList, cell, isHead);
        if (columnWidth < 0) {
            return;
        }
        if (columnWidth > MAX_COLUMN_WIDTH) {
            columnWidth = MAX_COLUMN_WIDTH;
        }
        Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
        if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
            maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
        }
    }

    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        }
        WriteCellData<?> cellData = cellDataList.get(0);
        CellDataTypeEnum type = cellData.getType();
        if (type == null) {
            return -1;
        }
        switch (type) {
            case STRING:
                return cellData.getStringValue().getBytes().length;
            case BOOLEAN:
                return cellData.getBooleanValue().toString().getBytes().length;
            case NUMBER:
                return cellData.getNumberValue().toString().getBytes().length;
            default:
                return -1;
        }
    }

    public void setZdyColumnWidth(Map<String, Integer> zdyColumnWidth,Map<String,Integer> headNameColumnWidthMap) {
        this.zdyColumnWidth = zdyColumnWidth;
        this.headNameColumnWidthMap = headNameColumnWidthMap;
    }
}

3.将图片导出成超链接的处理类

/**
 * 动态添加超链接
 */
@Component
public class ExcelHyperlinkHandler implements CellWriteHandler {

    public ExcelHyperlinkHandler() {
    }
 

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                 Head head, Integer integer, Integer integer1, Boolean isHead) {

    }
 
    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
                                Head head, Integer integer, Boolean aBoolean) {
 
    }
 
    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {


    }
 
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

        if (isHead) return ;
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();

        CellStyle cellStyle = cell.getCellStyle();
        // 设置换行
        cellStyle.setWrapText(true);

        if (curColIndex == 6 || curColIndex == 7 || curColIndex == 8) {
            // 设置字体颜色
            XSSFFont xssfFont = new XSSFFont();
            xssfFont.setColor(IndexedColors.BLUE.index);
            cellStyle.setFont(xssfFont);
            String stringCellValue = cell.getCellType()==CellType.STRING ? cell.getStringCellValue():null;
            if (StringUtils.isNotBlank(stringCellValue)){
                //获得超链接，当前单元格的内容就是一个超链接
                CreationHelper creationHelper = writeSheetHolder.getSheet().getWorkbook().getCreationHelper();
                Hyperlink hyperlink = creationHelper.createHyperlink(HyperlinkType.URL);
                String[] picUrlArr = stringCellValue.split(",");
                // 处理图片超链接
                if (curColIndex == 6){
                    hyperlink.setAddress(picUrlArr[0]);
                    cell.setHyperlink(hyperlink);//添加超链接
                    cell.setCellValue("图片1");
                } else if (curColIndex == 7){
                    if (picUrlArr.length > 1){
                        hyperlink.setAddress(picUrlArr[1]);
                        cell.setHyperlink(hyperlink);//添加超链接
                        cell.setCellValue("图片2");
                    }else{
                        cell.setCellValue("");
                    }
                } else if(curColIndex == 8){
                    if (picUrlArr.length > 2){
                        hyperlink.setAddress(picUrlArr[2]);
                        cell.setHyperlink(hyperlink);//添加超链接
                        cell.setCellValue("图片3");
                    }else{
                        cell.setCellValue("");
                    }
                }
            }
        }
    }

4.设置表格头和内容样式

/*
  EasyExcel 表格样式工具类
*/
@Component
public class SetCellStyle extends AbstractVerticalCellStyleStrategy {

    //表格头样式
    @Override
    protected WriteCellStyle headCellStyle(Head head) {

        WriteCellStyle writeCellStyle = new WriteCellStyle();
        writeCellStyle.setWrapped(true);
        WriteFont font = new WriteFont();
        
        //自定义字体颜色, 默认黑色
        font.setColor(IndexedColors.BLACK.index);

        writeCellStyle.setWriteFont(font);
        return writeCellStyle;
    }


    //单元格格内样式 写法与上面类似
    @Override
    protected WriteCellStyle contentCellStyle(Head head) {

        WriteCellStyle writeCellStyle = new WriteCellStyle();
        WriteFont font = new WriteFont();

        Integer columnIndex = head.getColumnIndex();
        //自定义字体颜色, 默认黑色
        if (columnIndex == 6 || columnIndex == 7 || columnIndex == 8){
            font.setColor(IndexedColors.BLUE.index);
        }else{
            font.setColor(IndexedColors.BLACK.index);
        }
        writeCellStyle.setWriteFont(font);
        writeCellStyle.setWrapped(true);
        return writeCellStyle;
    }

    public static HorizontalCellStyleStrategy getHorizontalStyleStrategy(){
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//        // 背景设置为灰色
//        headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
//        WriteFont headWriteFont = new WriteFont();
//        headWriteFont.setFontHeightInPoints((short)10);
//        // 字体样式
//        headWriteFont.setFontName("Frozen");
//        headWriteFont.setColor(IndexedColors.BLUE.getIndex());
//        headWriteCellStyle.setWriteFont(headWriteFont);
//        //自动换行
//        headWriteCellStyle.setWrapped(true);
//        // 水平对齐方式
//        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//        // 垂直对齐方式
//        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setWrapped(true);
//        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
//        //        contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
//        // 背景白色
//        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
//        // 字体样式
//        WriteFont contentWriteFont = new WriteFont();
//        // 字体大小
//        contentWriteFont.setFontHeightInPoints((short)10);
//        // 字体样式
//        contentWriteFont.setFontName("Calibri");
//        contentWriteFont.setColor(IndexedColors.BLACK.getIndex());
//        contentWriteCellStyle.setWriteFont(contentWriteFont);
//        // 设置边框
//        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
//        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
//        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
//        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

}

5.将图片导出在单元格

/**
 * 生成有图片的excel
 * @param dataList 数据列表
 */
public void generateImageExcel(HttpServletResponse response,List<ReportQuestionReplyExportDTO> dataList) throws Exception {

	if (CollectionUtils.isEmpty(dataList)) return ;
	String fileName = "数据列表";
	String sheetName = "数据列表";

	String picHeadName = "截图";

	//图片列最大图片数
	AtomicReference<Integer> maxImageSize = new AtomicReference<>(0);
	dataList.forEach(item -> {
		String picUrls = item.getReportPicUrl();
		if (StringUtils.isNotEmpty(picUrls)){
			if (picUrls.split(",").length > maxImageSize.get()) {
				maxImageSize.set(picUrls.split(",").length);
			}
		}
	});

	// 表头名称和列宽的映射
	Map<String, Integer> stringIntegerMap = HandleObjDataUtil.headNameColumnWidthMap(ReportQuestionReplyExportDTO.class);
	//设置列长度所用类
	AutoColumnWidthStyleStrategy longWidth = new AutoColumnWidthStyleStrategy();

	Integer partitionSize = dataList.size()/10;
	// 对集合切割分区并发处理
	List<List<ReportQuestionReplyExportDTO>> dataPartition = Lists.partition(dataList, partitionSize > 20? 20 : partitionSize);

	dataPartition.parallelStream().forEach(objList -> {
		objList.forEach(item -> {
			WriteCellData<Void> writeCellData = new WriteCellData<>();
			if (StringUtils.isNotEmpty(item.getReportPicUrl())) {
				//每张图片间距
				Integer splitWidth = -2;
				//每张图片的长度
				Integer imageWidth = 60;
				//图片列的最大长度
				Integer sumWidth = maxImageSize.get() * (imageWidth + splitWidth);

				List<ImageData> imageDataList = new ArrayList<>();
				List<String> imagePathList = Lists.newArrayList(item.getReportPicUrl().split(","));
				for (int i = 1; i <= imagePathList.size(); i++) {
					String path = imagePathList.get(i - 1);
					Integer left = imageWidth * (i - 1) + i * splitWidth;
					Integer right = sumWidth - imageWidth - left;
					ImageData imageData = new ImageData();

					byte[] bytes = null;
					InputStream inputStream = null;
					ByteArrayOutputStream outputStream = null;
					try {
						inputStream = new URL(path).openStream();
						outputStream = new ByteArrayOutputStream();
						// 对图片进行压缩
						Thumbnails.of(inputStream).scale(0.9f).outputQuality(0.3f).toOutputStream(outputStream);
						bytes = outputStream.toByteArray();
					} catch (IOException e) {
						e.printStackTrace();
						continue;
					} finally {
						if (inputStream != null){
							try {
								inputStream.close();
							} catch (IOException e) {
								e.printStackTrace();
							}
						}
					}
					if (bytes.length < 1) continue;

					imageData.setImage(bytes);
					imageData.setImageType(ImageData.ImageType.PICTURE_TYPE_PNG);
					//距离单元格顶部距离
					imageData.setTop(1);
					//距离单元格底部距离
					imageData.setBottom(1);
					//距离单元格左边距离
					imageData.setLeft(left);
					//距离单元格右边距离
					imageData.setRight(right);
					imageData.setAnchorType(ClientAnchorData.AnchorType.MOVE_AND_RESIZE);
					imageDataList.add(imageData);
				}
				writeCellData.setImageDataList(imageDataList);
				

				Map<String, Integer> zdyColumnWidth = new HashMap<>();
				//图片列名称，对应导出对象的列名称，图片列长度
				zdyColumnWidth.put(picHeadName, sumWidth / 6);
				//设置列长度所用类
				longWidth.setZdyColumnWidth(zdyColumnWidth,stringIntegerMap);
			}
			item.setWriteCellDataFile(writeCellData);
		});
	});

	ResponseDataUtils.setExcelRespProp(response,fileName);
	EasyExcel.write(response.getOutputStream())
			.registerWriteHandler(longWidth)
			.head(ReportQuestionReplyExportDTO.class)
			.excelType(ExcelTypeEnum.XLSX)
			.sheet(sheetName)
			.doWrite(dataList);
}

最后由于导出图片太多的话，很慢，导致导出超时，并且excel很大，最后方案改成将图片链接导成excel超链接