EasyExcel简单使用学习

竹下烟雨客

已于 2022-06-07 15:04:37 修改

阅读量2.2k

点赞数 6

分类专栏：实用工具文章标签： java

于 2022-06-01 14:39:35 首次发布

本文链接：https://blog.csdn.net/weixin_58973530/article/details/125082949

版权

实用工具专栏收录该内容

6 篇文章 0 订阅

订阅专栏

在做业务时难免会遇到导出导入的需求：

有一说一，easyexcel确实是很easy

pom.xml 导入依赖

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

数据模型：

public class User {
    private String name;
    private Integer age;
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
}

简单导出：

方法1：

    public String simpleExport() throws IOException {
        //文件地址名字
        String fileName = "D:\\study\\excel\\userExport.xlsx";
        //模拟数据
        List<User> userList = new ArrayList<>();
        for (int i = 0;i<=3;i++){
            User user = new User();
            user.setAge(i);
            user.setName(i+"号用户");
            userList.add(user);
        }
        File file = new File("D:\\study\\excel");
        if (!file.exists()){
            file.mkdirs();
        }
        //写出数据    指定数据模板 可以 .head(Class<?>)
        EasyExcel.write(fileName,User.class).sheet("用户信息").doWrite(userList);
        //随便写的返回值，根据实际情况来
        return "导出成功";
    }

输出效果：

方法2：

    public String simpleExport() throws IOException {
        //文件地址名字
        String fileName = "D:\\study\\excel\\userExport2.xlsx";
        //模拟数据
        List<User> userList = new ArrayList<>();
        for (int i = 0;i<=3;i++){
            User user = new User();
            user.setAge(i);
            user.setName(i+"号用户");
            userList.add(user);
        }
        //创建excelWriter对象、
        ExcelWriter excelWriter = EasyExcel.write(fileName, User.class).build();
        WriteSheet writeSheet = EasyExcel.writerSheet("用户信息").build();
        excelWriter.write(userList,writeSheet);
        //关流
        excelWriter.finish();
        return "导出成功";
    }

排除模型中的属性字段

指定字段不导出到excel中

其实就是在write之后加上excludeColumnFiledNames(...)

方法有多种：

    public String simpleExport() throws IOException {
        //文件地址名字
        String fileName = "D:\\study\\excel\\userExport2.xlsx";
        //模拟数据
        List<User> userList = new ArrayList<>();
        for (int i = 0;i<=3;i++){
            User user = new User();
            user.setAge(i);
            user.setName(i+"号用户");
            userList.add(user);
        }


        //使用一个set集合指定不导出的字段 也可以在模型上使用注解 @ExcelIgnore
        Set<String> excludeField = new HashSet<>();
        excludeField.add("age");

        //创建excelWriter对象
        //两个位置加 .excludeColumnFiledName 都可以实现
        //ExcelWriter excelWriter = EasyExcel.write(fileName, User.class)
                //.excludeColumnFiledNames(excludeField).build();
        ExcelWriter excelWriter = EasyExcel.write(fileName, User.class).build();
   
        WriteSheet writeSheet = 
            EasyExcel.writerSheet("用户信息").excludeColumnFiledNames(excludeField).build();
        excelWriter.write(userList,writeSheet);

        //关流
        excelWriter.finish();
        return "导出成功";
    }

这是较为简单的方法:

    public String simpleExport() throws IOException {
        //文件地址名字
        String fileName = "D:\\study\\excel\\userExport2.xlsx";
        //模拟数据
        List<User> userList = new ArrayList<>();
        for (int i = 0;i<=3;i++){
            User user = new User();
            user.setAge(i);
            user.setName(i+"号用户");
            userList.add(user);
        }

        //使用一个set集合指定不导出的字段 也可以在模型上使用注解 @ExcelIgnore
        Set<String> excludeField = new HashSet<>();
        excludeField.add("age");

        EasyExcel.write(fileName,User.class).sheet("用户信息").head(User.class)
                .excludeColumnFiledNames(excludeField).doWrite(userList);
        return "导出成功";
    }

效果：

向表格中导出指定属性

学会前面那个这个简直就不要太简单了，直接exclude改为include就可以了

 public String simpleExport() throws IOException {
        //文件地址名字
        String fileName = "D:\\study\\excel\\userExport2.xlsx";
        //模拟数据
        List<User> userList = new ArrayList<>();
        for (int i = 0;i<=3;i++){
            User user = new User();
            user.setAge(i);
            user.setName(i+"号用户");
            userList.add(user);
        }

        Set<String> includeFiled = new HashSet<>();
        includeFiled.add("age");

        EasyExcel.write(fileName,User.class).sheet("用户信息").head(User.class)
                .includeColumnFiledNames(includeFiled).doWrite(userList);
        return "导出成功";
    }

这就只有age了

指定列名称和顺序

用@ExcelProperty注解对模型的字段进行操作

public class User {
    @ExcelProperty(value = "姓名",index = 0)
    private String name;
    @ExcelProperty(value = "年龄",index = 1)
    private Integer age;
    @ExcelProperty(value = "家庭地址",index = 2)
    private String address;
    @ExcelProperty(value = "联系电话",index = 3)
    private String phone;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }
}

效果：

复杂头数据导出

注解@ExcelProperty 的value属性是一个数组类型，遇到相同的值会自动合并

例如：

    @ExcelProperty(value = {"必填信息","姓名"},index = 0)
    private String name;
    @ExcelProperty(value = {"必填信息","年龄"},index = 1)
    private Integer age;
    @ExcelProperty(value = {"选填信息","家庭地址"},index = 2)
    private String address;
    @ExcelProperty(value = {"必填信息","联系电话"},index = 3)
    private String phone;

继续进行导出

        //文件地址名字
        String fileName = "D:\\study\\excel\\userExport2.xlsx";
        //模拟数据
        List<User> userList = new ArrayList<>();
        for (int i = 0;i<=3;i++){
            User user = new User();
            user.setAge(i);
            user.setName(i+"号用户");
            user.setAddress(i+"街"+i+"号楼");
            user.setPhone(i+i+"123132131");
            userList.add(user);
        }
        EasyExcel.write(fileName,User.class).sheet("用户信息").doWrite(userList);

结果：

也就是head合并单元格

重复向同一个sheet写入数据

        //文件地址名字
        String fileName = "D:\\study\\excel\\userExport4.xlsx";
        //模拟数据
        List<User> userList = new ArrayList<>();
        for (int i = 0;i<=3;i++){
            User user = new User();
            user.setAge(i);
            user.setName(i+"号用户");
            user.setAddress(i+"街"+i+"号楼");
            user.setPhone(i+i+"123132131");
            userList.add(user);
        }
        //创建ExcelWriter对象
        ExcelWriter excelWriter = EasyExcel.write(fileName, User.class).build();
        //创建writeSheet对象
        WriteSheet writeSheet = EasyExcel.writerSheet("用户信息").build();
        //向同一个sheet重复写入数据
        for (int i = 0;i<=2;i++){
            excelWriter.write(userList,writeSheet);
        }
        //关流
        excelWriter.finish();

结果

导出到不同的sheet中

冗余的代码就不多展示了

        //创建ExcelWriter对象
        ExcelWriter excelWriter = EasyExcel.write(fileName, User.class).build();
        for (int i = 0;i<=2;i++){
            //创建writeSheet对象
            WriteSheet writeSheet = EasyExcel.writerSheet("用户信息"+i).build();
            excelWriter.write(userList,writeSheet);
        }
        //关流
        excelWriter.finish();

日期/数字类型格式化

因为在excel表中经常会遇到数据格式的问题,所以需要对特定类型的数据进行格式化处理

基础两个注解

@DateTimeFormat 日期格式化

@NumberFormat 数字格式化(小数或百分数)

这是数据模型（实体类）的形式

    @ExcelProperty(value = {"必填信息","姓名"},index = 0)
    private String name;
    @ExcelProperty(value = {"必填信息","年龄"},index = 1)
    private Integer age;
    @ExcelProperty(value = {"选填信息","家庭地址"},index = 2)
    private String address;
    @ExcelProperty(value = {"选填信息","联系电话"},index = 3)
    private String phone;
    
    @NumberFormat(value = "###.#")//数字格式化,保留一位小数
    @ExcelProperty(value = "薪水",index = 4)
    private Double salary;
    
    @DateTimeFormat(value = "yyyy年MM月dd日 HH时mm分ss秒")//日期格式化
    @ExcelProperty(value = "入职日期",index = 5)
    private Date hiredate;

        //文件地址名字
        String fileName = "D:\\study\\excel\\userExport6.xlsx";
        //模拟数据
        List<User> userList = new ArrayList<>();
        for (int i = 0;i<=3;i++){
            User user = new User();
            user.setAge(i);
            user.setName(i+"号用户");
            user.setAddress(i+"街"+i+"号楼");
            user.setPhone(i+i+"123132131");
            user.setSalary(12121.12112);
            user.setHiredate(new Date());
            userList.add(user);
        }
        //创建ExcelWriter对象
        EasyExcel.write(fileName, User.class).sheet("用户信息").doWrite(userList);

图片导出,写入excel

数据模板:

@ContentRowHeight(value = 80)//内容行高
@ColumnWidth(value = 30)//列宽
public class ImageData {
    //使用抽象文件表示一个图片
    @ExcelProperty(value = "file类型")
    private File file;
    //使用输入流保存一个图片
    @ExcelProperty(value = "InputStream类型")
    private InputStream inputStream;
    //当使用String类型保存图片时需要使用一个StringImageConverter转换器
    @ExcelProperty(value = "str类型",converter = StringImageConverter.class)
    private String str;
    //使用二进制保存为一个图片
    @ExcelProperty(value = "二进制数据（字节）")
    private byte[] byteArr;
    @ExcelProperty(value = "网络图片")
    private URL url;

    public File getFile() {
        return file;
    }

    public void setFile(File file) {
        this.file = file;
    }

    public InputStream getInputStream() {
        return inputStream;
    }

    public void setInputStream(InputStream inputStream) {
        this.inputStream = inputStream;
    }

    public String getStr() {
        return str;
    }

    public void setStr(String str) {
        this.str = str;
    }

    public byte[] getByteArr() {
        return byteArr;
    }

    public void setByteArr(byte[] byteArr) {
        this.byteArr = byteArr;
    }

    public URL getUrl() {
        return url;
    }

    public void setUrl(URL url) {
        this.url = url;
    }
}

导出代码：

    @Test
    public void testFileExport() throws Exception{
        String fileName = "D:\\study\\excel\\testExcel6.xlsx";
        //图片位置 这里是自己本地的路径
        String imagePath = "C:\\Users\\admin\\Desktop\\products\\017beb7fcbd34b3fd374b138cbb9b9b9.gif";
        URL url = new URL("https://gimg2.baidu.com/image_search/src=http%3A%2F%2Fwww.isanxia.com%2Fzb_users%2Fupload%2F2021%2F01%2F202101211611208459430553.jpg&refer=http%3A%2F%2Fwww.isanxia.com&app=2002&size=f9999,10000&q=a80&n=0&g=0n&fmt=auto?sec=1657088291&t=648a0eca6c05232d947ff20cfcb76e4b");
        //将图片读取到二进制数据中
        byte[] bytes = new byte[(int)new File(imagePath).length()];
        InputStream inputStream = new FileInputStream(imagePath);
        inputStream.read(bytes,0,bytes.length);

        List<ImageData> imageDataList = new ArrayList<>();

        //创建数据模板
        ImageData imageData = new ImageData();
        imageData.setFile(new File(imagePath));
        //不能直接用上面的inputStream
        imageData.setInputStream(new FileInputStream(imagePath));
        imageData.setStr(imagePath);
        imageData.setByteArr(bytes);
        imageData.setUrl(url);

        imageDataList.add(imageData);

        //写数据
        EasyExcel.write(fileName,ImageData.class).sheet("测试图片").doWrite(imageDataList);
    }

实现效果：

设置表格的行高列宽

@HeadRowHeight(value = 30) // 头部行高
@ContentRowHeight(value = 25) // 内容行高
@ColumnWidth(value = 20) // 列宽, 可以作用在类或字段上

这里太简单，省略了，直接把注解套在数据模板类上面就行了

使用注解控制表格样式

例如颜色,字体

@HeadRowHeight(value = 30)//头部行高
@ContentRowHeight(value = 25)//内容行高
@ColumnWidth(value = 20)//列宽
//头背景设置成红色 IndexedColors.RED.getIndex()
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND,fillForegroundColor = 10 )
@HeadFontStyle(fontName = "宋体",fontHeightInPoints = 20)
//内容的背景颜色设置成绿色IndexedColors.GREEN.getIndex()
@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND,fillForegroundColor = 17 )
// 内容字体设置成20, 字体默认宋体
@ContentFontStyle(fontName = "宋体", fontHeightInPoints = 20)
public class User {

    // 字符串的头背景设置成粉红 IndexedColors.PINK.getIndex()
    @HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 14)
    // 字符串的头字体设置成20
    @HeadFontStyle(fontHeightInPoints = 30)
    // 字符串的内容背景设置成天蓝 IndexedColors.SKY_BLUE.getIndex()
    @ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 40)
    // 字符串的内容字体设置成20,默认宋体
    @ContentFontStyle(fontName = "宋体", fontHeightInPoints = 20)
    @ExcelProperty(value = {"必填信息","姓名"},index = 0)
    private String name;
    @ExcelProperty(value = {"必填信息","年龄"},index = 1)
    private Integer age;
    @ExcelProperty(value = {"选填信息","家庭地址"},index = 2)
    private String address;
    @ExcelProperty(value = {"选填信息","联系电话"},index = 3)
    private String phone;

    @NumberFormat(value = "###.#")//数字格式化,保留一位小数
    @ExcelProperty(value = "薪水",index = 4)
    private Double salary;

    @ColumnWidth(value = 50)//列宽
    @DateTimeFormat(value = "yyyy年MM月dd日 HH时mm分ss秒")//日期格式化
    @ExcelProperty(value = "入职日期",index = 5)
    private Date hiredate;

    public Double getSalary() {
        return salary;
    }

    public void setSalary(Double salary) {
        this.salary = salary;
    }

    public Date getHiredate() {
        return hiredate;
    }

    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }
}

        //文件地址名字
        String fileName = "D:\\study\\excel\\userExport7.xlsx";
        //模拟数据
        List<User> userList = new ArrayList<>();
        for (int i = 0;i<=3;i++){
            User user = new User();
            user.setAge(i);
            user.setName(i+"号用户");
            user.setAddress(i+"街"+i+"号楼");
            user.setPhone(i+i+"123132131");
            user.setSalary(12121.12112);
            user.setHiredate(new Date());
            userList.add(user);
        }
        //创建ExcelWriter对象
        EasyExcel.write(fileName, User.class).sheet("用户信息").doWrite(userList);

实现效果：

合并单元格

数据模板

@HeadRowHeight(value = 25) // 头部行高
@ContentRowHeight(value = 20) // 内容行高
@ColumnWidth(value = 20) // 列宽
/**
 * @OnceAbsoluteMerge 指定从哪一行/列开始,哪一行/列结束 进行单元格合并
 * firstRowIndex 起始行索引,从0开始
 * lastRowIndex 结束行索引
 * firstColumnIndex 起始列索引,从0开始
 * lastColumnIndex 结束列索引
 */
@OnceAbsoluteMerge(firstRowIndex = 1,lastRowIndex = 2,firstColumnIndex = 0,lastColumnIndex = 0)
public class TestData {

    //每隔两行进行一次合并
    //@ContentLoopMerge(eachRow = 2)
    @ExcelProperty(value = "字符串标题")
    private String str;
    @ExcelProperty(value = "日期标题")
    private Date date;
    @ExcelProperty(value = "数字标题")
    private Double doubleData;

    public String getStr() {
        return str;
    }

    public void setStr(String str) {
        this.str = str;
    }

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    public Double getDoubleData() {
        return doubleData;
    }

    public void setDoubleData(Double doubleData) {
        this.doubleData = doubleData;
    }
}

    @Test
    public void testExport2(){
        String filename = "D:\\study\\excel\\单元格合并2.xlsx";
        // 构建数据
        List<TestData> dataList = new ArrayList<>();
        TestData testData = new TestData();
        testData.setDate(new Date());
        testData.setDoubleData(222.2222);
        testData.setStr("测试字符串");
        dataList.add(testData);
        // 向Excel中写入数据
        EasyExcel.write(filename, TestData.class)
                .sheet("单元格合并测试")
                .doWrite(dataList);
    }

实现效果：