多场景easyExcel导出excel文件(一)

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

测试类

所有测试类都是这个格式,没有其他代码.

import com.minghe.jiaozhu.service.ExportService;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.web.bind.annotation.GetMapping;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

@SpringBootTest
public class ExportTest {

    @Resource
    private ExportService exportService;

    /**
     * 普通导出
     */
    @Test
    public void exportExcel1() {
        String path = "D:\\test\\test.xlsx";
        exportService.exportExcel1(path);
    }
}

代码导入包

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.converters.string.StringNumberConverter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.*;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.merge.LoopMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.fastjson.JSON;
import com.minghe.jiaozhu.handler.*;
import com.minghe.jiaozhu.model.dto.*;
import com.minghe.jiaozhu.service.ExportService;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Service
public class ExportServiceImpl implements ExportService {
}

数据源

    private List<ExportHeadersExcel> data() {
        List<ExportHeadersExcel> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            ExportHeadersExcel excel = new ExportHeadersExcel();
            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");
            list.add(excel);
        }
        return list;
    }

1.正常导出

实体类

@Data
@HeadRowHeight(30)
//属性注释在最下
@ContentStyle(wrapped = BooleanEnum.TRUE, verticalAlignment = VerticalAlignmentEnum.CENTER, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN)
public class ExportExcel {

    /**
     * 金
     */
    @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;
}

代码

public void exportExcel1(String path) {

        String sheetName = "sheetName";
        List<ExportExcel> exportExcelList = new ArrayList<>();
        ExportExcel excel = new ExportExcel();
        excel.setShui("aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa");
        exportExcelList.add(excel);

        ExcelWriter excelWriter = EasyExcel.write(path).build();
        //sheetName可以不写
        WriteSheet tableNameSheet = EasyExcel.writerSheet(sheetName).head(ExportExcel.class).build();
        excelWriter.write(exportExcelList, tableNameSheet);
        excelWriter.finish();
    }

效果

表头,和实体类对应,aaa是set的shui的值,sheetName是sheet的名字

另一种实现方式

        ExcelWriterSheetBuilder sheetBuilder = EasyExcel.write(path, head)
                .sheet(sheetName);
        sheetBuilder.doWrite(exportExcelList);

第三种实现方式

 EasyExcel.write(path, ExportHeadersExcel.class)
                .sheet(sheetName)
                .doWrite(excels);

我觉得第三种最简洁,文章中会使用第三种

从效果图可以看到,aaa太多,自动换行了,加上这行代码LongestMatchColumnWidthStyleStrategy,可以自动处理宽度,但是不太精确,效果也不太好,会把单元格变小

        WriteSheet tableNameSheet = EasyExcel.writerSheet(sheetName).head(ExportExcel.class)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .build();

效果

加上这行代码可以在表头上空行,参数写几就空几行.这是空5行的效果

2.添加密码

有时候文档需要加密,这是最简单的一种添加密码的方式

    public void exportExcel2(String path) {

        String sheetName = "sheetName";
        List<ExportHeadersExcel> exportExcelList = new ArrayList<>();
        ExportHeadersExcel excel = new ExportHeadersExcel();
        excel.setShui("aaaaaaaaaaaaaaaaaaaaa");
        exportExcelList.add(excel);

        ExcelWriterSheetBuilder sheetBuilder = EasyExcel.write(path, ExportExcel.class)
                .password("123456")
                .sheet(sheetName);
        sheetBuilder.doWrite(exportExcelList);

    }

打开文档时

输入密码 123456 后,可以正常编辑

3.导出多级表头和标题

实体类


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 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 ExportHeadersExcel {

    /**
     * 金
     */
    @ExcelProperty(value = {"七灵图","五行", "金"})
    //合并单元格
    //@ContentLoopMerge(eachRow = 2)
    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;
}

代码

    public void exportHeaders(String path) {

        String sheetName = "sheetName";
        List<ExportHeadersExcel> excels = new ArrayList<>();
        ExportHeadersExcel exportHeadersExcel = new ExportHeadersExcel();
        exportHeadersExcel.setShui("一元重水");
        excels.add(exportHeadersExcel);

        EasyExcel.write(path, ExportHeadersExcel.class)
                .sheet(sheetName)
                .doWrite(excels);
    }

效果图

可以看到,代码和正常导出一样,关键在于实体类的配置.在真正的表头金木水火土等字段上加了

{"七灵图","五行",金},阴阳上加了{"七灵图","两仪",阴},最终的效果就是"七灵图"成了标题,金木水火土在五行标题下,阴阳在两仪标题下

实体类上的注解的用法


    /**
     * ContentStyle注解的属性的注释
     dataFormat    日期格式
     hidden    设置单元格使用此样式隐藏
     locked    设置单元格使用此样式锁定
     quotePrefix    在单元格前面增加`符号，数字或公式将以字符串形式展示
     horizontalAlignment    设置是否水平居中
     wrapped    设置文本是否应换行。将此标志设置为true通过在多行上显示使单元格中所有内容可见
     verticalAlignment    设置是否垂直居中
     rotation    设置单元格中文本旋转角度。03版本的Excel旋转角度区间为-90° ~ 90°，07版本的Excel旋转角度区间为0°~180°
     indent    设置单元格中缩进文本的空格数
     borderLeft    设置左边框的样式
     borderRight    设置右边框样式
     borderTop    设置上边框样式
     borderBottom    设置下边框样式
     leftBorderColor    设置左边框颜色
     rightBorderColor    设置右边框颜色
     topBorderColor    设置上边框颜色
     bottomBorderColor    设置下边框颜色
     fillPatternType    设置填充类型
     fillBackgroundColor    设置背景色
     fillForegroundColor    设置前景色
     shrinkToFit    设置自动单元格自动大小
     */
//边框设置
/*    border-width：
        （1）thin：定义细的边框
        （2）medium：默认，中等边框
        （3）thick：定义粗的边框
        （4）length：自定义边框的宽度
    border-style：
        （1）none 定义无边框
        （2）hidden 与none相同，不过应用于表时除外，对于表，hidden用于解决边框冲突。
        （3）dotted 定义点状边框。在大多数浏览器显示为实线。
         (4）dashed 定义虚线。在大多数浏览器显示为实线。
         (5）soild 定义实线
        （6）double 定义双线，双线的宽度等于border-width的值。
        （7）groove 定义3d凹槽边框，效果取决于border-color
        （8）ridge 定义3d垄状边框，其效果取决于border-color
        （9）inset 同上
        （10）outset 同上*/

4.导出多级表头和标题+没有表头的动态值

有时候,我们需要在导出的时候添加一个动态的值,比如导出的时候把时间作为一行展示.同时表头和标题都需要存在.

实体类


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 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 ExportHeadersValueExcel {
//cellWritervalue对应ValueCellWriteHandler里的cellWritervalue
    /**
     * 金
     */
    @ExcelProperty(value = {"七灵图","总纲","五行", "金"})
    private String jin;
    /**
     * 木
     */
    @ExcelProperty(value = {"七灵图","${cellWriterValue}","五行", "木"})
    private String mu;
    /**
     * 水
     */
    @ExcelProperty(value = {"七灵图","${cellWriterValue}","五行", "水"})
    private String shui;
    /**
     * 火
     */
    @ExcelProperty(value = {"七灵图","${cellWriterValue}","五行", "火"})
    private String huo;
    /**
     * 土
     */
    @ExcelProperty(value = {"七灵图","${cellWriterValue}","五行", "土"})
    private String tu;
    /**
     * 阴
     */
    @ExcelProperty(value = {"七灵图","${cellWriterValue}","两仪", "阴"})
    private String yin;
    /**
     * 阳
     */
    @ExcelProperty(value = {"七灵图","${cellWriterValue}","两仪", "阳"})
    private String yang;
}

代码

    public void exportHeadersValue(String path) {

        String sheetName = "sheetName";
        List<ExportHeadersValueExcel> excels = new ArrayList<>();
        ExportHeadersValueExcel exportHeadersValueExcel = new ExportHeadersValueExcel();
        exportHeadersValueExcel.setShui("一元重水");
        excels.add(exportHeadersValueExcel);
        String value = "阴阳包括五行,五行含有阴阳";
        EasyExcel.write(path, ExportHeadersValueExcel.class)
                .registerWriteHandler(new ValueCellWriteHandler(value))
                .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 org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.util.PropertyPlaceholderHelper;

import java.util.List;
import java.util.Properties;

/**
 * @author 冥河教主
 * @Date 2021/02/08 16:00
 */
public class ValueCellWriteHandler implements CellWriteHandler {
    private String cellWritervalue;
    PropertyPlaceholderHelper propertyPlaceholderHelper = new PropertyPlaceholderHelper("${","}");

    public ValueCellWriteHandler(String cellvalue) {
        this.cellWritervalue = cellvalue;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
        if (head != null) {
            List<String> headNameList = head.getHeadNameList();
            if (CollectionUtils.isNotEmpty(headNameList)) {
                Properties properties = new Properties();
                properties.setProperty("cellWriterValue", cellWritervalue);
                for (int i = 0; i < headNameList.size(); i++) {
                    headNameList.set(i, propertyPlaceholderHelper.replacePlaceholders(headNameList.get(i), properties));
                }
            }
        }
    }

}

效果

实体类中的"${cellWriterValue}",取的是处理器中的cellWriterValue,在写入流的时候, .registerWriteHandler(new ValueCellWriteHandler(value)),注册处理器就行了

5.处理数值型单元格的角标

有时候数据中会存在小数,为了防止科学计数法等会将小数转成string存储,导出的时候单元格的格式就是string,(因为字段类型就是string),但是值是数值型,此时单元格上就会出现角标

在数字的左上方出现了角标.这种情况有两种解决办法,一种是将wps或者office设置为不显示角标[😀]..另一种是通过转换器转换成数值型.

代码

 public void exportHeadersValueText1(String path) {

        String sheetName = "sheetName";
        List<ExportHeadersValueTextExcel> excels = new ArrayList<>();
        ExportHeadersValueTextExcel excel = new ExportHeadersValueTextExcel();
        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");
        excels.add(excel);
        EasyExcel.write(path, ExportHeadersValueTextExcel.class)
                //.registerWriteHandler(new ValueCellWriteNumberHandler()) 如果需要对数值型做单独处理的可以用这个
//                .registerConverter(new StringNumberConverter())
                .sheet(sheetName)
                .doWrite(excels);
    }

StringNumberConverter是easyExcel内部的数值转换器.但是使用了数值型后导出的小数将会自动去0.

如果能接受小数格式不统一可以用这种方法,另外如果在实体类中将字段类型改为bigdeciml类型也是一样的效果.

如果需要单独对单元格类型做处理,可以用ValueCellWriteNumberHandler单独处理


import cn.hutool.core.util.NumberUtil;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;

/**
 * @author 冥河教主
 * @Date 2021/02/08 16:00
 */
public class ValueCellWriteNumberHandler implements CellWriteHandler {

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                       WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        String stringValue = cellData.getStringValue();
        if (NumberUtil.isNumber(stringValue)){
            cellData.setType(CellDataTypeEnum.NUMBER);
            cellData.setNumberValue(NumberUtil.toBigDecimal(stringValue));
        }
    }

}

6.导出的excel中有图片

实体类

package com.minghe.jiaozhu.model.dto;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.converters.string.StringImageConverter;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import lombok.Data;

@Data
@HeadRowHeight(30)//设置表头的行高度
@ContentRowHeight(60)//设置单元格的高度
@ColumnWidth(100 / 8)
//属性注释在最下
@ContentStyle(wrapped = BooleanEnum.TRUE, verticalAlignment = VerticalAlignmentEnum.CENTER, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN)
public class ExportHeadersValueImageExcel {
//cellWritervalue对应ValueCellWriteHandler里的cellWritervalue
    /**
     * 金
     */
    @ExcelProperty(value = {"七灵图","总纲","五行", "金"})
    private String jin;
    /**
     * 木
     */
    @ExcelProperty(value = {"七灵图","${cellWriterValue}","五行", "木"})
    private String mu;
    /**
     * 水
     */
    @ExcelProperty(value = {"七灵图","${cellWriterValue}","五行", "水"})
    private String shui;
    /**
     * 火
     */
    @ExcelProperty(value = {"七灵图","${cellWriterValue}","五行", "火"})
    private String huo;
    /**
     * 土
     */
    @ExcelProperty(value = {"七灵图","${cellWriterValue}","五行", "土"})
    private String tu;
    /**
     * 阴
     */
    @ExcelProperty(value = {"七灵图","${cellWriterValue}","两仪", "阴"})
    private String yin;
    /**
     * 阳
     */
    @ExcelProperty(value = {"七灵图","${cellWriterValue}","两仪", "阳"})
    private String yang;
    /**
     * 如果图片用string类型 必须指定转换器，string默认转换成string
     */
    @ExcelProperty(value = {"七灵图","${cellWriterValue}", "两仪","灵图"},converter = StringImageConverter.class)
    @ColumnWidth(30)//图片的列宽单独设置
    private String imagePath;
    //有五种写入图片的方式,url是写图片的地址.测试中发现InputStream转换有问题 实际应用中可以看看具体什么问题
    /*    private File file;
    private InputStream inputStream;
    private String string;
    private byte[] byteArray;
    private URL url;*/

}

代码

    public void exportHeadersValueImage(String path) {

        String sheetName = "sheetName";
        String imagePath = "D:\\test\\miao.jpg";
        List<ExportHeadersValueImageExcel> excels = new ArrayList<>();
        ExportHeadersValueImageExcel imageExcel = new ExportHeadersValueImageExcel();
        imageExcel.setJin("10");
        imageExcel.setMu("2.00");
        imageExcel.setShui("3.00");
        imageExcel.setHuo("4.00");
        imageExcel.setTu("5.00");
        imageExcel.setYin("6.00");
        imageExcel.setYang("7.01");
        imageExcel.setImagePath(imagePath);
        excels.add(imageExcel);
        EasyExcel.write(path, ExportHeadersValueImageExcel.class)
                .sheet(sheetName).doWrite(excels);
    }

效果

实体类中,字段上可以加注解@ColumnWidth(30)来单独设置该字段的列宽,@ExcelProperty注解上要指定converter @ExcelProperty(value = {"七灵图", "两仪","灵图"},converter = StringImageConverter.class)

图片的方式有五种,文件,文件流,string(我理解为path),字节流,url(适用于图片在云端存储的)

/*    private File file;
private InputStream inputStream;
private String string;
private byte[] byteArray;
private URL url;*/

7.导出的文件中有超链接和批注和公式

7.1 代码实现

实体类


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

效果

超链接

批注

公式

7.2 处理器实现

代码

.registerWriteHandler(new WriteRowHandler())
处理器


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

        }
    }
}

必须使用xlsx格式,不能用xls格式,涉及到XSSF和HSSF解析

8.导出文件中设置单元格样式和字体样式

实体类上同

代码

public void exportHeadersValueStyle(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> writeCellStyle = new WriteCellData<>("单元格样式");
        WriteCellStyle writeCellStyleData = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.
        writeCellStyleData.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
        writeCellStyleData.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        writeCellStyle.setWriteCellStyle(writeCellStyleData);
        writeCellStyle.setType(CellDataTypeEnum.STRING);
        excel.setWriteCellStyle(writeCellStyle);

        // 设置单个单元格多种样式
        // 这里需要设置 inMomery=true 不然会导致无法展示单个单元格多种样式，所以慎用
        WriteCellData<String> richTest = new WriteCellData<>();
        RichTextStringData richTextStringData = new RichTextStringData();
        richTextStringData.setTextString("红色绿色默认");
        // 前2个字红色
        WriteFont writeFont = new WriteFont();
        writeFont.setColor(IndexedColors.RED.getIndex());
        richTextStringData.applyFont(0, 2, writeFont);
        // 接下来2个字绿色
        writeFont = new WriteFont();
        writeFont.setColor(IndexedColors.GREEN.getIndex());
        richTextStringData.applyFont(2, 4, writeFont);
        richTest.setRichTextStringDataValue(richTextStringData);
        richTest.setType(CellDataTypeEnum.RICH_TEXT_STRING);

        excel.setRichText(richTest);

        List<ExportHeadersLinkExcel> excels = new ArrayList<>();
        excels.add(excel);
        EasyExcel.write(path, ExportHeadersLinkExcel.class)
                .inMemory(true)
                .sheet(sheetName)
                .doWrite(excels);
    }

一个单元格多种样式需要启用内存.

在设置单元格背景或字体的颜色的时候，颜色会对应一个short类型的数据，下面是具体对应关系

9.导出多sheet页

9.1导出多sheet页同一个对象

代码

    public void exportHeadersSheets(String path) {
        String sheetName = "sheetName";
        ExcelWriter excelWriter = EasyExcel.write(path, ExportHeadersExcel.class).build();
        List<ExportHeadersExcel> data = this.data();
        for (int i = 0; i < data.size(); i++) {
            WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName + i).build();
            excelWriter.write(data,writeSheet);
        }
        excelWriter.finish();
    }

this.data() 数据来自1正常导出前的多数据

效果

9.2导出多sheet页不同对象

代码

    public void exportHeadersClassSheets(String path) {

        ExcelWriter excelWriter = EasyExcel.write(path, ExportHeadersExcel.class).build();
        List<ExcelSheets> sheets = new ArrayList<>();
        List<ExportHeadersExcel> data = this.data();
        //添加不同的对象  sheet名一定不能一样
        sheets.add(new ExcelSheets("ExportHeadersExcel1",ExportExcel.class,data) );
        sheets.add(new ExcelSheets("ExportHeadersExcel2",ExportHeadersExcel.class,data) );
        sheets.add(new ExcelSheets("ExportHeadersExcel3",ExportHeadersValueExcel.class,data) );
        //3个sheet,不同对象
        for (ExcelSheets sheet : sheets) {
            WriteSheet writeSheet = EasyExcel.writerSheet(sheet.getSheetName()).head(sheet.getClazz()).build();
            excelWriter.write(sheet.getList(),writeSheet);
        }
        excelWriter.finish();
    }

效果

10.注解形式自定义设置样式

10.1 注解形式设置样式

实体类


import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import lombok.Data;

@Data
@HeadRowHeight(30)
//属性注释在最下 // 内容的背景设置成绿色 IndexedColors.GREEN.getIndex()
@ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 17,wrapped = BooleanEnum.TRUE, verticalAlignment = VerticalAlignmentEnum.CENTER, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN)
// 头背景设置成红色 IndexedColors.RED.getIndex()
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 10)
// 头字体设置成20
@HeadFontStyle(fontHeightInPoints = 20)
// 内容字体设置成20
@ContentFontStyle(fontHeightInPoints = 20)
public class ExportHeadersStyleExcel {

    /**
     * 金
     */
    @ExcelProperty(value = {"七灵图","五行", "金"})
    // 字符串的头背景设置成粉红 IndexedColors.PINK.getIndex()
    @HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 14)
    // 字符串的头字体设置成20
    @HeadFontStyle(fontHeightInPoints = 30)
    // 字符串的内容的背景设置成天蓝 IndexedColors.SKY_BLUE.getIndex()
    @ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)
    // 字符串的内容字体设置成20
    @ContentFontStyle(fontHeightInPoints = 30)
    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;
}

代码同普通导出

效果

10.2 使用已有策略自定义样式

代码

public void exportHeadersStyle2(String path) {

        String sheetName = "sheetName";
        List<ExportHeadersExcel> excels = new ArrayList<>();
        ExportHeadersExcel exportHeadersExcel = new ExportHeadersExcel();
        exportHeadersExcel.setShui("一元重水");
        excels.add(exportHeadersExcel);
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为红色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)20);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short)20);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(path, ExportHeadersExcel.class)
                .registerWriteHandler(horizontalCellStyleStrategy)
                .sheet(sheetName)
                .doWrite(excels);
    }

效果

9.3 使用easyexcel的方式完全自己定义样式

代码

    public void exportHeadersStyle3(String path) {

        String sheetName = "sheetName";
        List<ExportHeadersExcel> excels = new ArrayList<>();
        ExportHeadersExcel exportHeadersExcel = new ExportHeadersExcel();
        exportHeadersExcel.setShui("一元重水");
        excels.add(exportHeadersExcel);

        EasyExcel.write(path, ExportHeadersExcel.class)
                .registerWriteHandler(new EasyexcelCellWriteHandler())
                .sheet(sheetName)
                .doWrite(excels);
    }

处理器


import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.util.BooleanUtils;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;

/**
 * @author 冥河教主
 * @Date 2021/02/08 09:09
 */
public class EasyexcelCellWriteHandler implements CellWriteHandler {

    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        // 当前事件会在 数据设置到poi的cell里面才会回调
        // 判断不是头的情况 如果是fill 的情况 这里会==null 所以用not true
        if (BooleanUtils.isNotTrue(context.getHead())) {
            // 第一个单元格
            // 只要不是头 一定会有数据 当然fill的情况 可能要context.getCellDataList() ,这个需要看模板，因为一个单元格会有多个 WriteCellData
            WriteCellData<?> cellData = context.getFirstCellData();
            // 这里需要去cellData 获取样式
            // 很重要的一个原因是 WriteCellStyle 和 dataFormatData绑定的 简单的说 比如你加了 DateTimeFormat
            // ，已经将writeCellStyle里面的dataFormatData 改了 如果你自己new了一个WriteCellStyle，可能注解的样式就失效了
            // 然后 getOrCreateStyle 用于返回一个样式，如果为空，则创建一个后返回
            WriteCellStyle writeCellStyle = cellData.getOrCreateStyle();
            writeCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
            // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND
            writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);

            // 这样样式就设置好了 后面有个FillStyleCellWriteHandler 默认会将 WriteCellStyle 设置到 cell里面去 所以可以不用管了
        }
    }
}

效果

11.合并单元格

11.1 策略形式

代码

    public void exportHeadersMerge(String path) {

        String sheetName = "sheetName";
        List<ExportHeadersExcel> excels = new ArrayList<>();
        ExportHeadersExcel exportHeadersExcel = new ExportHeadersExcel();
        exportHeadersExcel.setShui("一元重水");
        excels.add(exportHeadersExcel);
        ExportHeadersExcel exportHeadersExcel1 = new ExportHeadersExcel();
        exportHeadersExcel1.setShui("玄冥真水");
        excels.add(exportHeadersExcel1);
        ExportHeadersExcel exportHeadersExcel2 = new ExportHeadersExcel();
        exportHeadersExcel2.setShui("天一真水");
        excels.add(exportHeadersExcel2);
        ExportHeadersExcel exportHeadersExcel3 = new ExportHeadersExcel();
        exportHeadersExcel3.setShui("无形真水");
        excels.add(exportHeadersExcel3);
        //自定义策略
        //参数  int eachRow, int columnExtend, int columnIndex
        //eachRow 2 代表每两行就合并,每一行都执行,如果不够两行,要合并的这一列会没有下方的边框
        //columnExtend 合并扩展列， 向右合并列
        // columnIndex 列索引,从零开始，第columnIndex列开始执行合并，往右合并columnExtend列
        LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 0);
        //注解形式  @ContentLoopMerge(eachRow = 2)
        EasyExcel.write(path, ExportHeadersExcel.class)
                .registerWriteHandler(loopMergeStrategy)
                .sheet(sheetName)
                .doWrite(excels);
    }

效果

LoopMergeStrategy的参数释义:
//eachRow 2 代表每两行就合并,每一行都执行,如果不够两行,要合并的这一列会没有下方的边框
//columnExtend 合并扩展列， 向右合并列
// columnIndex 列索引,从零开始，第columnIndex列开始执行合并，往右合并columnExtend列

11.2 注解形式

实体类


import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ContentLoopMerge;
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 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 ExportHeadersExcel {

    /**
     * 金
     */
    @ExcelProperty(value = {"七灵图","五行", "金"})
    //合并单元格
    @ContentLoopMerge(eachRow = 2)
    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;
    /**
     * 阳
     */
    @ContentLoopMerge(eachRow = 2)
    @ExcelProperty(value = {"七灵图","两仪", "阳"})
    private String yang;

}

代码

    public void exportHeadersMerge(String path) {

        String sheetName = "sheetName";
        List<ExportHeadersExcel> excels = new ArrayList<>();
        ExportHeadersExcel exportHeadersExcel = new ExportHeadersExcel();
        exportHeadersExcel.setShui("一元重水");
        excels.add(exportHeadersExcel);
        ExportHeadersExcel exportHeadersExcel1 = new ExportHeadersExcel();
        exportHeadersExcel1.setShui("玄冥真水");
        excels.add(exportHeadersExcel1);
        ExportHeadersExcel exportHeadersExcel2 = new ExportHeadersExcel();
        exportHeadersExcel2.setShui("天一真水");
        excels.add(exportHeadersExcel2);
        ExportHeadersExcel exportHeadersExcel3 = new ExportHeadersExcel();
        exportHeadersExcel3.setShui("无形真水");
        excels.add(exportHeadersExcel3);

        EasyExcel.write(path, ExportHeadersExcel.class)
                .sheet(sheetName)
                .doWrite(excels);
    }

效果

注解形式可以设定某一列进行合并,实体类上对金和阳两个字段加了注解,所以这两列进行了合并.策略形式不知道怎么设置某一列,知道的大佬在评论回复一下具体操作,万分感谢!

12.使用table,达成两层表头效果

代码

    public void exportHeadersTable(String path) {

        String sheetName = "sheetName";
        List<ExportHeadersExcel> excels = new ArrayList<>();
        ExportHeadersExcel exportHeadersExcel = new ExportHeadersExcel();
        exportHeadersExcel.setShui("一元重水");
        excels.add(exportHeadersExcel);
        ExportHeadersExcel exportHeadersExcel1 = new ExportHeadersExcel();
        exportHeadersExcel1.setShui("玄冥真水");
        excels.add(exportHeadersExcel1);
        List<ExportHeadersExcel> excels1 = new ArrayList<>();
        ExportHeadersExcel exportHeadersExcel2 = new ExportHeadersExcel();
        exportHeadersExcel2.setShui("天一真水");
        excels1.add(exportHeadersExcel2);
        ExportHeadersExcel exportHeadersExcel3 = new ExportHeadersExcel();
        exportHeadersExcel3.setShui("无形真水");
        excels1.add(exportHeadersExcel3);

        // 把sheet设置为不需要头 不然会输出sheet的头 这样看起来第一个table 就有2个头了
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).needHead(Boolean.FALSE).build();
        // 这里必须指定需要头，table 会继承sheet的配置，sheet配置了不需要，table 默认也是不需要
        WriteTable writeTable0 = EasyExcel.writerTable(0).needHead(Boolean.TRUE).build();
        WriteTable writeTable1 = EasyExcel.writerTable(1).needHead(Boolean.TRUE).build();
        // 第一次写入会创建头
        ExcelWriter excelWriter = EasyExcel.write(path, ExportHeadersExcel.class).build();
        excelWriter.write(excels, writeSheet, writeTable0);
        // 第二次写入也会创建头，然后在第一次的后面写入数据
        excelWriter.write(excels1, writeSheet, writeTable1);
        excelWriter.finish();
    }

效果

13.动态表头

表头信息

private List<List<String>> head() {
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> head0 = new ArrayList<String>();
        head0.add("地" );
        List<String> head1 = new ArrayList<String>();
        head1.add("水");
        List<String> head2 = new ArrayList<String>();
        head2.add("火");
        List<String> head3 = new ArrayList<String>();
        head3.add("风");
        list.add(head0);
        list.add(head1);
        list.add(head2);
        list.add(head3);
        return list;
    }

代码

    public void exportDynamicHeaders(String path) {
        //因为水在ExportHeadersExcel类中是第三个表头,对应head()方法中地水火风的火,所以"一元重水"在表头火下
        String sheetName = "sheetName";
        List<ExportHeadersExcel> excels = new ArrayList<>();
        ExportHeadersExcel exportHeadersExcel = new ExportHeadersExcel();
        exportHeadersExcel.setShui("一元重水");
        excels.add(exportHeadersExcel);
        List<List<String>> head = this.head();
        EasyExcel.write(path)
                .head(head)
                .sheet(sheetName)
                .doWrite(excels);
    }

效果

exportHeadersExcel.setShui("一元重水");shui这个字段在exportHeadersExcel对象中是第三个字段,所以导出的excel中,表头火下是一元重水.也可以在exportHeadersExcel对象中用@ExcelProperty(index = 0)来指定列.还可以直接用list<string>等形式

14 .操作单元格(第四行第一列添加超链接)

从14开始,后面的案例都是告诉大家,代码还可以这么实现

代码

    public void exportHeadersRow(String path) {
        //
        String sheetName = "sheetName";
        List<ExportHeadersExcel> data = data();
        EasyExcel.write(path, ExportHeadersExcel.class)
                .registerWriteHandler(new WriteRowHandler())
                .sheet(sheetName)
                .doWrite(data);
    }

处理器


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

        }
    }
}

效果

第四行,第a列,值为10的单元格,有超链接,图片上显示不了

15.操作多sheet(第一列第一行和第二行添加下拉框)

代码

.registerWriteHandler(new WriteSheetHandler())

处理器

package com.minghe.jiaozhu.handler;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.context.SheetWriteHandlerContext;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

/**
 * @author 冥河教主
 * @Date 2021/02/08 16:00
 */
@Slf4j
public class WriteSheetHandler implements SheetWriteHandler {
    @Override
    public void afterSheetCreate(SheetWriteHandlerContext context) {
        log.info("第{}个Sheet写入成功。", context.getWriteSheetHolder().getSheetNo());

        // 区间设置 第一列第一行和第二行的数据。由于第一行是头，所以第一、二行的数据实际上是第二三行
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 2, 0, 0);
        DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[] {"测试1", "测试2"});
        DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
        context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);
    }
}

效果