easyExcel导出超链接和批注和公式

有时候,需要导出带有额外信息的excel,比如超链接,批注,公式,该怎么实现呢?话不多说,上代码

实体类

 
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import com.alibaba.excel.metadata.data.WriteCellData;
import lombok.Data;
 
@Data
@HeadRowHeight(30)
//属性注释在最下
@ContentStyle(wrapped = BooleanEnum.TRUE, verticalAlignment = VerticalAlignmentEnum.CENTER, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN)
public class ExportHeadersLinkExcel {
 
    /**
     * 金
     */
    @ExcelProperty(value = {"七灵图","五行", "金"})
    private String jin;
    /**
     * 木
     */
    @ExcelProperty(value = {"七灵图","五行", "木"})
    private String mu;
    /**
     * 水
     */
    @ExcelProperty(value = {"七灵图","五行", "水"})
    private String shui;
    /**
     * 火
     */
    @ExcelProperty(value = {"七灵图","五行", "火"})
    private String huo;
    /**
     * 土
     */
    @ExcelProperty(value = {"七灵图","五行", "土"})
    private String tu;
    /**
     * 阴
     */
    @ExcelProperty(value = {"七灵图","两仪", "阴"})
    private String yin;
    /**
     * 阳
     */
    @ExcelProperty(value = {"七灵图","两仪", "阳"})
    private String yang;
    /**
     * 超链接
     *
     */
    private WriteCellData<String> hyperlink;
 
    /**
     * 备注
     *
     */
    private WriteCellData<String> commentData;
 
    /**
     * 公式
     *
     */
    private WriteCellData<String> formulaData;
 
    /**
     * 指定单元格的样式。当然样式 也可以用注解等方式。
     *
     */
    private WriteCellData<String> writeCellStyle;
 
    /**
     * 指定一个单元格有多个样式
     *
     */
    private WriteCellData<String> richText;
}

代码

public void exportHeadersValueLink(String path) {
 
        String sheetName = "sheetName";
        ExportHeadersLinkExcel excel = new ExportHeadersLinkExcel();
        excel.setJin("10");
        excel.setMu("2.00");
        excel.setShui("3.00");
        excel.setHuo("4.00");
        excel.setTu("5.00");
        excel.setYin("6.00");
        excel.setYang("7.01");
        // 设置超链接
        WriteCellData<String> hyperlink = new WriteCellData<>("java生成pdf图表链接");
        HyperlinkData hyperlinkData = new HyperlinkData();
        hyperlinkData.setAddress("https://blog.youkuaiyun.com/lol19950605/article/details/128929870");
        hyperlinkData.setHyperlinkType(HyperlinkData.HyperlinkType.URL);
        hyperlink.setHyperlinkData(hyperlinkData);
        excel.setHyperlink(hyperlink);
 
        // 设置备注
        WriteCellData<String> comment = new WriteCellData<>("批注的单元格信息");
        CommentData commentData = new CommentData();
        commentData.setAuthor("冥河教主");
        commentData.setRichTextStringData(new RichTextStringData("这是一个批注"));
        // 备注的默认大小是按照单元格的大小 这里想调整到4个单元格那么大 所以向后 向下 各额外占用了一个单元格 但是没生效
        //commentData.setRelativeLastColumnIndex(1);
        //commentData.setRelativeLastRowIndex(1);
        comment.setCommentData(commentData);
        excel.setCommentData(comment);
 
        // 设置公式
        WriteCellData<String> formula = new WriteCellData<>();
        FormulaData formulaData = new FormulaData();
        // 将 123456789 中的第一个数字替换成 2
        // 这里只是例子 如果真的涉及到公式 能内存算好尽量内存算好 公式能不用尽量不用
        //REPLACE(123456789,1,1,2) REPLACE:公式名 123456789:原字符串 第一个1:起始位置  第二个1:字符数 2:要替换的字符串
        formulaData.setFormulaValue("REPLACE(123456789,1,1,2)");
        formula.setFormulaData(formulaData);
        excel.setFormulaData(formula);
 
        List<ExportHeadersLinkExcel> excels = new ArrayList<>();
        excels.add(excel);
        EasyExcel.write(path, ExportHeadersLinkExcel.class)
                .sheet(sheetName)
                .doWrite(excels);
    }

效果

超链接

批注

公式

如果想要用处理器来实现的话,也可以

EasyExcel.write(path, ExportHeadersLinkExcel.class)
        .registerWriteHandler(new WriteRowHandler())
        .sheet(sheetName)
        .doWrite(excels);

处理器代码

 
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
 
 
/**
 * @author 冥河教主
 * @Date 2021/02/08 16:00
 */
@Slf4j
public class WriteRowHandler implements CellWriteHandler {
    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
                                Head head, Integer relativeRowIndex, Boolean isHead) {
        if (isHead) {
            Drawing<?> drawingPatriarch = writeSheetHolder.getSheet().createDrawingPatriarch();
            // 在第一行 第二列创建一个批注
            Comment comment;
            int columnIndex = cell.getColumnIndex();
            switch (columnIndex){
                case 1:
                    comment =drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0,
                            cell.getColumnIndex(), cell.getRowIndex(),cell.getColumnIndex()+1,cell.getRowIndex()+1));
                    // 输入批注信息
                    comment.setString(new XSSFRichTextString("创建批注!"));
                    break;
                default:
                    break;
            }
 
        }
    }
}

 git仓库:导入导出: 导入导出的实例

更多导出场景:多场景easyExcel导出excel文件(一)-优快云博客

读取excel场景:多场景easyExcel读取excel文件(二)-优快云博客

根据模板填充excel:多场景easyExcel根据模板填充excel文件(三)-优快云博客