EasyExcel导出excel且Cell超链接至其他附件

OkidoGreen

于 2024-07-16 16:11:35 发布

阅读量374

点赞数 4

分类专栏： Excel处理-导出文章标签： excel java 服务器

本文链接：https://blog.csdn.net/z69183787/article/details/140469343

版权

Excel处理-导出专栏收录该内容

45 篇文章 2 订阅

订阅专栏

写Excel | Easy Excel 官网 (alibaba.com)

easyexcel 添加附件链接报address of hyperlink must be a valid URI问题-CSDN博客

easyexcel 设置超链接或附件地址_easyexcle支持单元格内容为链接格式-CSDN博客

使用poi生成excel并设置单元格超链接链接到本地文件，因为文件名里有[]导致报错，有遇到过同样情况的吗?poi版本3.17_email, path to file and url must be valid uris-CSDN博客

 <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.2.0</version>
        </dependency>

package com.dfham.cas.utils;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.net.URI;
import java.util.ArrayList;
import java.util.List;

public class HyperLinkUtils {

    public static void main(String[] args) {
        String fileName = "D:\\output.xlsx";
        List<FileData> data = new ArrayList<>();
        data.add(new FileData("文件1", "D:\\11111.txt"));
        data.add(new FileData("文件2", "D:\\22222.txt"));

        EasyExcel.write(fileName, FileData.class)
                .registerWriteHandler(new HyperlinkWriteHandler())
                .sheet("Sheet1")
                .doWrite(data);
    }


    public static class HyperlinkWriteHandler implements CellWriteHandler {

        @Override
        public void afterCellDispose(CellWriteHandlerContext context) {
            if (!context.getHead() && context.getCell().getColumnIndex() == 1) { // 假设超链接在第2列
                String filePath = context.getCell().getStringCellValue();
                Workbook workbook = context.getWriteSheetHolder().getSheet().getWorkbook();
                CreationHelper createHelper = workbook.getCreationHelper();
                File file = new File(filePath);
                URI uri = file.toURI();
                filePath = uri.toString().split("file:")[1];
                // 创建超链接
                Hyperlink link = createHelper.createHyperlink(HyperlinkType.FILE);
                link.setAddress(filePath);

                // 设置单元格样式
                CellStyle style = workbook.createCellStyle();
                style.setFillForegroundColor(IndexedColors.RED.getIndex());
                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

                // 设置字体样式
                Font font = workbook.createFont();
                font.setColor(IndexedColors.BLUE.getIndex());
                font.setUnderline(Font.U_SINGLE);
                style.setFont(font);

                // 应用超链接和样式
                context.getCell().setHyperlink(link);
                context.getCell().setCellStyle(style);

                // 这里要把 WriteCellData的样式清空， 不然后面还有一个拦截器 FillStyleCellWriteHandler 默认会将 WriteCellStyle 设置到
                // cell里面去 会导致自己设置的不一样
                context.getFirstCellData().setWriteCellStyle(null);
            }
        }


    }

    public static class FileData {
        private String text;
        private String filePath;

        public FileData(String text, String filePath) {
            this.text = text;
            this.filePath = filePath;
        }

        public String getText() {
            return text;
        }

        public String getFilePath() {
            return filePath;
        }
    }
}