首页 > 资讯 > 后端开发 > JAVA >EasyExcel实现Excel文件导入导出功能

934

分享到

EasyExcel实现Excel文件导入导出功能

excel java Powered by 金山文档 2023-09-15 10:09:04 934人浏览独家记忆

摘要

一、Easyexcel简介 Java领域解析、生成Excel比较有名的框架有Apache poi、jxl等。但他们都存在一个严重的问题就是非常的耗内存。如果你的系统并发量不大的话可能还行，但是一旦并发上来后一定会OOM或者JVM频繁的fu

一、Easyexcel简介

Java领域解析、生成Excel比较有名的框架有Apache poi、jxl等。但他们都存在一个严重的问题就是非常的耗内存。如果你的系统并发量不大的话可能还行，但是一旦并发上来后一定会OOM或者JVM频繁的full GC。

EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单、节省内存著称。EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。

EasyExcel采用一行一行的解析模式，并将一行的解析结果以观察者的模式通知处理（AnalysisEventListener）

二、EasyExcel的具体使用

1、EasyExcel相关依赖

           com.alibaba        easyexcel        3.1.1

2、写Excel

2.1、最简单的写（方式一）

创建实体类

@NoArgsConstructor@AllArgsConstructor@Data@Builderpublic class User {    @ExcelProperty(value = "用户编号")    private Integer userId;    @ExcelProperty(value = "姓名")    private String userName;    @ExcelProperty(value = "性别")    private String gender;    @ExcelProperty(value = "工资")    private Double salary;    @ExcelProperty(value = "入职时间")    @DateTimeFORMat(value = "yyyy年MM月dd日 HH时mm分ss秒")    private Date hireDate;}

写入

  @PostMapping("/WriteExcel1")    @apiOperation(value="写excel")    public void WriteExcel() {        String filename = "D:\\excel\\write\\user.xlsx";        // 向Excel中写入数据 也可以通过 head(Class) 指定数据模板        EasyExcel.write(filename, User.class)                .sheet("用户信息")                .doWrite(getUserData());   }

当然我们需要创建数据

private List getUserData() {        List users = new ArrayList<>();        for (int i = 1; i <= 10; i++) {            User user = User.builder()                    .userId(i)                    .userName("admin" + i)                    .gender(i % 2 == 0 ? "男" : "女")                    .salary(i * 1000.00)                    .hireDate(new Date())                    .build();            users.add(user);        }        return users;    }

效果：

2.2、最简单的写（方式二）

    @PostMapping("/WriteExcel2")    @ApiOperation(value="写excel")    public void WriteExcel2() {        String filename = "D:\\excel\\write\\user2.xlsx";        // 创建ExcelWriter对象        ExcelWriter excelWriter = EasyExcel.write(filename, User.class).build();        // 创建Sheet对象        WriteSheet writeSheet = EasyExcel.writerSheet("用户信息").build();        // 向Excel中写入数据        excelWriter.write(getUserData(), writeSheet);        // 关闭流        excelWriter.finish();    }

效果：

2.3、排除模型中的属性字段

@PostMapping("/WriteExcel3")    @ApiOperation(value="写excel")    public void WriteExcel3() {        String filename = "D:\\excel\\write\\user3.xlsx";        // 设置排除的属性 也可以在数据模型的字段上加@ExcelIgnore注解排除        Set excludeField = new HashSet<>();        excludeField.add("hireDate");        excludeField.add("salary");        // 写Excel        EasyExcel.write(filename, User.class)                .excludeColumnFiledNames(excludeField)                .sheet("用户信息")                .doWrite(getUserData());    }

效果：

2.4、向表格中导出指定属性

@PostMapping("/WriteExcel4")    @ApiOperation(value="写excel")    public void WriteExcel4() {        String filename = "D:\\excel\\write\\user4.xlsx";        // 设置要导出的字段        Set includeFields = new HashSet<>();        includeFields.add("userName");        includeFields.add("hireDate");        // 写Excel        EasyExcel.write(filename, User.class)                .includeColumnFiledNames(includeFields)                .sheet("用户信息")                .doWrite(getUserData());    }

效果：

2.5、插入指定的列

将Java对象中指定的属性, 插入到Eexcel表格中的指定列(在Excel表格中进行列排序), 使用index属性指定列顺序

@NoArgsConstructor@AllArgsConstructor@Data@Builderpublic class User {    @ExcelProperty(value = "用户编号",index = 0)    private Integer userId;    @ExcelProperty(value = "姓名",index = 1)    private String userName;    @ExcelProperty(value = "性别",index = 2)    private String gender;    @ExcelProperty(value = "工资",index = 4)    private Double salary;    @ExcelProperty(value = "入职时间",index = 3)    @DateTimeFormat(value = "yyyy年MM月dd日 HH时mm分ss秒")    private Date hireDate;}

@PostMapping("/WriteExcel5")    @ApiOperation(value="写excel")    public void WriteExcel5() {        String filename = "D:\\excel\\write\\user5.xlsx";        // 向Excel中写入数据        EasyExcel.write(filename, User.class)                .sheet("用户信息")                .doWrite(getUserData());    }

效果：

2.6、复杂头数据写入

@ExcelProperty注解的value属性是一个数组类型, 设置多个head时会自动合并

@NoArgsConstructor@AllArgsConstructor@Data@Builderpublic class ComplexHeadUser {    @ExcelProperty(value = {"group1", "用户编号"}, index = 0)    private Integer userId;    @ExcelProperty(value = {"group1", "姓名"}, index = 1)    private String userName;    @ExcelProperty(value = {"group2", "入职时间"}, index = 2)    private Date hireDate;}

   @PostMapping("/WriteExcel6")    @ApiOperation(value="写excel")    public void WriteExcel6() {        String filename = "D:\\excel\\write\\user6.xlsx";        List users = new ArrayList<>();        for (int i = 1; i <= 10; i++) {            ComplexHeadUser user = ComplexHeadUser.builder()                    .userId(i)                    .userName("大哥" + i)                    .hireDate(new Date())                    .build();            users.add(user);        }        // 向Excel中写入数据        EasyExcel.write(filename, ComplexHeadUser.class)                .sheet("用户信息")                .doWrite(users);    }

效果：

2.7、重复写到Excel的同一个Sheet中

@PostMapping("/WriteExcel7")    @ApiOperation(value="写excel")    public void WriteExcel7() {        String filename = "D:\\excel\\write\\user7.xlsx";        // 创建ExcelWriter对象        ExcelWriter excelWriter = EasyExcel.write(filename, User.class).build();        // 创建Sheet对象        WriteSheet writeSheet = EasyExcel.writerSheet("用户信息").build();        // 向Excel的同一个Sheet重复写入数据        for (int i = 0; i < 2; i++) {            excelWriter.write(getUserData(), writeSheet);        }        // 关闭流        excelWriter.finish();    }

效果：

2.8 写到Excel的不同Sheet中

 @PostMapping("/WriteExcel8")    @ApiOperation(value="写excel")    public void WriteExcel8() {        String filename = "D:\\excel\\write\\user8.xlsx";        // 创建ExcelWriter对象        ExcelWriter excelWriter = EasyExcel.write(filename, User.class).build();        // 向Excel的同一个Sheet重复写入数据        for (int i = 0; i < 2; i++) {            // 创建Sheet对象            WriteSheet writeSheet = EasyExcel.writerSheet("用户信息" + i).build();            excelWriter.write(getUserData(), writeSheet);        }        // 关闭流        excelWriter.finish();    }

效果：

2.9、日期/数字类型格式化

在实体类加上这两个注解即可

@NumberFormat(value = "###.#") // 数字格式化,保留1位小数@DateTimeFormat(value = "yyyy年MM月dd日 HH时mm分ss秒") // 日期格式化

2.10、写入图片到Excel

@NoArgsConstructor@AllArgsConstructor@Data@Builder@ContentRowHeight(value = 100) // 内容行高@ColumnWidth(value = 20) // 列宽public class ImageData {    //使用抽象文件表示一个图片    @ExcelProperty(value = "File类型")    private File file;    // 使用输入流保存一个图片    @ExcelProperty(value = "InputStream类型")    private InputStream inputStream;    // 当使用String类型保存一个图片的时候需要使用StringImageConverter转换器    @ExcelProperty(value = "String类型", converter = StringImageConverter.class)    private String str;    // 使用二进制数据保存为一个图片    @ExcelProperty(value = "二进制数据(字节)")    private byte[] byteArr;    // 使用网络链接保存为一个图片    @ExcelProperty(value = "网络图片")    private URL url;    // lombok 会生成getter/setter方法}

@PostMapping("/WriteExcel9")    @ApiOperation(value="写excel")    public void WriteImageToExcel() throws IOException {        String filename = "D:\\excel\\write\\user9.xlsx";        // 图片位置        String imagePath = "D:\\excel\\me.jpg";        // 网络图片        URL url = new URL("https://cn.bing.com/th?id=OHR.TanzaniaBeeEater_ZH-CN3246625733_1920x1080.jpg&rf=LaDigue_1920x1080.jpg&pid=hp");        // 将图片读取到二进制数据中        byte[] bytes = new byte[(int) new File(imagePath).length()];        InputStream inputStream = new FileInputStream(imagePath);        inputStream.read(bytes, 0, bytes.length);        List imageDataList = new ArrayList<>();        // 创建数据模板        ImageData imageData = ImageData.builder()                .file(new File(imagePath))                .inputStream(new FileInputStream(imagePath))                .str(imagePath)                .byteArr(bytes)                .url(url)                .build();        // 添加要写入的图片模型        imageDataList.add(imageData);        // 写数据        EasyExcel.write(filename, ImageData.class)                .sheet("帅哥")                .doWrite(imageDataList);    }

效果：

2.11 设置写入Excel的列宽和行高

@NoArgsConstructor@AllArgsConstructor@Data@Builder@HeadRowHeight(value = 30) // 头部行高@ContentRowHeight(value = 25) // 内容行高@ColumnWidth(value = 20) // 列宽public class WidthAndHeightData {    @ExcelProperty(value = "字符串标题")    private String string;    @ExcelProperty(value = "日期标题")    private Date date;    @ExcelProperty(value = "数字标题")    @ColumnWidth(value = 25)    private Double doubleData;}

 @PostMapping("/WriteExcel10")    @ApiOperation(value="写excel")    public void Write10() {        String filename = "D:\\excel\\write\\user10.xlsx";        // 构建数据        List dataList = new ArrayList<>();        WidthAndHeightData data = WidthAndHeightData.builder()                .string("字符串")                .date(new Date())                .doubleData(888.88)                .build();        dataList.add(data);        // 向Excel中写入数据        EasyExcel.write(filename, WidthAndHeightData.class)                .sheet("行高和列宽测试")                .doWrite(dataList);    }

效果：

2.12、通过注解形式设置写入Excel样式

@NoArgsConstructor@AllArgsConstructor@Data@Builder@HeadRowHeight(value = 30) // 头部行高@ContentRowHeight(value = 25) // 内容行高@ColumnWidth(value = 20) // 列宽// 头背景设置成红色 IndexedColors.RED.getIndex()@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 10)// 头字体设置成20, 字体默认宋体@HeadFontStyle(fontName = "宋体", fontHeightInPoints = 20)// 内容的背景设置成绿色  IndexedColors.GREEN.getIndex()@ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 17)// 内容字体设置成20, 字体默认宋体@ContentFontStyle(fontName = "宋体", fontHeightInPoints = 20)public class DemoStyleData {    // 字符串的头背景设置成粉红 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(fontName = "宋体", fontHeightInPoints = 20)    @ExcelProperty(value = "字符串标题")    private String string;    @ExcelProperty(value = "日期标题")    private Date date;    @ExcelProperty(value = "数字标题")    private Double doubleData;}

 @PostMapping("/WriteExcel11")    @ApiOperation(value="写excel")    public void Write11() {        String filename = "D:\\excel\\write\\user11.xlsx";        // 构建数据        List dataList = new ArrayList<>();        DemoStyleData data = DemoStyleData.builder()                .string("字符串")                .date(new Date())                .doubleData(888.88)                .build();        dataList.add(data);        // 向Excel中写入数据        EasyExcel.write(filename, DemoStyleData.class)                .sheet("样式设置测试")                .doWrite(dataList);    }

效果：

2.13、合并单元格

@NoArgsConstructor@AllArgsConstructor@Data@Builder@HeadRowHeight(value = 25) // 头部行高@ContentRowHeight(value = 20) // 内容行高@ColumnWidth(value = 20) // 列宽// 例如: 第2-3行,2-3列进行合并@OnceAbsoluteMerge(firstRowIndex = 1, lastRowIndex = 2, firstColumnIndex = 1, lastColumnIndex = 2)public class DemoMergeData {    // 每隔两行合并一次(竖着合并单元格)//    @ContentLoopMerge(eachRow = 2)    @ExcelProperty(value = "字符串标题")    private String string;    @ExcelProperty(value = "日期标题")    private Date date;    @ExcelProperty(value = "数字标题")    private Double doubleData;}

@PostMapping("/WriteExcel12")    @ApiOperation(value="写excel")    public void Write12() {        String filename = "D:\\excel\\write\\user12.xlsx";        // 构建数据        List dataList = new ArrayList<>();        DemoMergeData data = DemoMergeData.builder()                .string("字符串")                .date(new Date())                .doubleData(888.88)                .build();        dataList.add(data);        // 向Excel中写入数据        EasyExcel.write(filename, DemoMergeData.class)                .sheet("单元格合并测试")                .doWrite(dataList);    }

效果：

@ContentLoopMerge

@OnceAbsoluteMerge

3、读Excel

3.1、读API的拆分

在读取Excel表格数据时, 将读取的每行记录映射成一条LinkedHashMap记录, 而没有映射成实体类

@PostMapping("/ReadExcel1")    @ApiOperation(value="读excel")    public void Read() {        String filename = "D:\\excel\\read\\read.xlsx";        // 创建ExcelReaderBuilder对象        ExcelReaderBuilder readerBuilder = EasyExcel.read();        // 获取文件对象        readerBuilder.file(filename);        // 指定映射的数据模板//  readerBuilder.head(DemoData.class);        // 指定sheet        readerBuilder.sheet(0);        // 自动关闭输入流        readerBuilder.autoCloseStream(true);        // 设置Excel文件格式        readerBuilder.excelType(ExcelTypeEnum.XLSX);        // 注册监听器进行数据的解析        readerBuilder.reGISterReadListener(new AnalysisEventListener() {            // 每解析一行数据,该方法会被调用一次            @Override            public void invoke(Object demoData, AnalysisContext analysisContext) {                // 如果没有指定数据模板, 解析的数据会封装成 LinkedHashMap返回                // demoData instanceof LinkedHashMap 返回 true                System.out.println("解析数据为:" + demoData.toString());            }            // 全部解析完成被调用            @Override            public void doAfterAllAnalysed(AnalysisContext analysisContext) {                System.out.println("解析完成...");                // 可以将解析的数据保存到数据库            }        });        readerBuilder.doReadAll();

3.2、最简单的读(方式一)

@NoArgsConstructor@AllArgsConstructor@Data@Builderpublic class DemoData {    // 根据Excel中指定列名或列的索引读取    @ExcelProperty(value = "字符串标题", index = 0)    private String name;    @ExcelProperty(value = "日期标题", index = 1)    private Date hireDate;    @ExcelProperty(value = "数字标题", index = 2)    private Double salary;}

    @PostMapping("/ReadExcel2")    @ApiOperation(value="读excel")    public void testReadExcel() {        // 读取的excel文件路径        String filename = "D:\\excel\\read\\read.xlsx";        // 读取excel        EasyExcel.read(filename, DemoData.class, new AnalysisEventListener() {            // 每解析一行数据,该方法会被调用一次            @Override            public void invoke(DemoData demoData, AnalysisContext analysisContext) {                System.out.println("解析数据为:" + demoData.toString());            }            // 全部解析完成被调用            @Override            public void doAfterAllAnalysed(AnalysisContext analysisContext) {                System.out.println("解析完成...");                // 可以将解析的数据保存到数据库            }        }).sheet().doRead();    }

效果：

3.3、最简单的读(方式二)

@PostMapping("/ReadExcel3")    @ApiOperation(value="读excel")    public void testReadExcel2() {        // 读取的excel文件路径        String filename = "D:\\excel\\read\\read.xlsx";        // 创建一个数据格式来装读取到的数据        Class head = DemoData.class;        // 创建ExcelReader对象        ExcelReader excelReader = EasyExcel.read(filename, head, new AnalysisEventListener() {            // 每解析一行数据,该方法会被调用一次            @Override            public void invoke(DemoData demoData, AnalysisContext analysisContext) {                System.out.println("解析数据为:" + demoData.toString());            }            // 全部解析完成被调用            @Override            public void doAfterAllAnalysed(AnalysisContext analysisContext) {                System.out.println("解析完成...");                // 可以将解析的数据保存到数据库            }        }).build();        // 创建sheet对象,并读取Excel的第一个sheet(下标从0开始), 也可以根据sheet名称获取        ReadSheet sheet = EasyExcel.readSheet(0).build();        // 读取sheet表格数据, 参数是可变参数,可以读取多个sheet        excelReader.read(sheet);        // 需要自己关闭流操作,在读取文件时会创建临时文件,如果不关闭,会损耗磁盘,严重的磁盘爆掉        excelReader.finish();    }

效果:

3.4、格式化Excel中的数据格式

要读取的源数据, 日期格式是yyyy年MM月dd日 HH时mm分ss秒, 数字带小数点

// 格式化日期类型数据    @DateTimeFormat(value = "yyyy年MM月dd日 HH时mm分ss秒") // 格式化数字类型数据,保留一位小数    @NumberFormat(value = "###.#")

3.5、读取多个sheet表格

3.5.1 读所有sheet

方式一, 使用ExcelReaderBuilder#doReadAll方法

public void readExcel() {        // 读取的excel文件路径        String filename = "D:\\excel\\read.xlsx";        // 读取excel        EasyExcel.read(filename, DemoData.class, new AnalysisEventListener() {            // 每解析一行数据,该方法会被调用一次            @Override            public void invoke(DemoData demoData, AnalysisContext analysisContext) {                System.out.println("解析数据为:" + demoData.toString());            }            // 全部解析完成被调用            @Override            public void doAfterAllAnalysed(AnalysisContext analysisContext) {                System.out.println("解析完成...");                // 可以将解析的数据保存到数据库            }        })           .doReadAll(); // 读取全部sheet    }

方式二, 使用ExcelReader#readAll方法

public void readExcel2() {        // 读取的excel文件路径        String filename = "D:\\excel\\read.xlsx";        // 创建一个数据格式来装读取到的数据        Class head = DemoData.class;        // 创建ExcelReader对象        ExcelReader excelReader = EasyExcel.read(filename, head,         new AnalysisEventListener() {            // 每解析一行数据,该方法会被调用一次            @Override            public void invoke(DemoData demoData, AnalysisContext analysisContext) {                System.out.println("解析数据为:" + demoData.toString());            }            // 全部解析完成被调用            @Override            public void doAfterAllAnalysed(AnalysisContext analysisContext) {                System.out.println("解析完成...");                // 可以将解析的数据保存到数据库            }        }).build();        // 创建sheet对象,并读取Excel的第一个sheet(下标从0开始), 也可以根据sheet名称获取        ReadSheet sheet = EasyExcel.readSheet(0).build();        // 读取sheet表格数据 , 参数是可变参数,可以读取多个sheet//        excelReader.read(sheet);        excelReader.readAll(); // 读所有sheet        // 需要自己关闭流操作,在读取文件时会创建临时文件,如果不关闭,会损耗磁盘,严重的磁盘爆掉        excelReader.finish();    }

3.5.2 读指定的多个sheet

不同sheet表格的数据模板可能不一样,这时候就需要分别构建不同的sheet对象,分别为其指定对于的数据模板

public void readExcel3() {    // 读取的excel文件路径    String filename = "D:\\study\\excel\\read.xlsx";    // 构建ExcelReader对象    ExcelReader excelReader = EasyExcel.read(filename).build();    // 构建sheet对象    ReadSheet sheet0 = EasyExcel.readSheet(0)            .head(DemoData.class) // 指定sheet0的数据模板            .registerReadListener(new AnalysisEventListener() {                // 每解析一行数据,该方法会被调用一次              @Override              public void invoke(DemoData demoData, AnalysisContext analysisContext) {                    System.out.println("解析数据为:" + demoData.toString());              }                // 全部解析完成被调用                @Override                public void doAfterAllAnalysed(AnalysisContext analysisContext) {                    System.out.println("解析完成...");                    // 可以将解析的数据保存到数据库                }            }).build();    // 读取sheet,有几个就构建几个sheet进行读取    excelReader.read(sheet0);    // 需要自己关闭流操作,在读取文件时会创建临时文件,如果不关闭,会损耗磁盘,严重的磁盘爆掉    excelReader.finish();}

4、填充Excel

4.1、简单填充

@NoArgsConstructor@AllArgsConstructor@Data@Builderpublic class FillData {    private String name;    private double number;}

    @PostMapping("/FillExcel1")    @ApiOperation(value="excel简单填充")    public void testFillExcel() {        // 根据哪个模板进行填充        String template = "D:\\excel\\fill\\template1.xlsx";        // 填充完成之后的excel        String fillname = "D:\\excel\\fill\\fill1.xlsx";        // 构建数据        FillData fillData = FillData.builder()                .name("张三")                .number(666.888)                .build();        // 填充excel 单组数据填充        EasyExcel.write(fillname).withTemplate(template).sheet(0).doFill(fillData);    }

模板：

效果：

4.2、列表填充

@PostMapping("/FillExcel2")    @ApiOperation(value="excel列表填充")    public void testFillExcel2() {        // 根据哪个模板进行填充        String template = "D:\\excel\\fill\\template2.xlsx";        // 填充完成之后的excel        String fillname = "D:\\excel\\fill\\fill2.xlsx";        // 填充excel 多组数据重复填充        EasyExcel.write(fillname)                .withTemplate(template)                .sheet(0)                .doFill(getFillData());    }

模板：

效果：

4.3 水平填充

@PostMapping("/FillExcel3")    @ApiOperation(value="excel水平填充")    public void testFillExcel4() {        // 根据哪个模板进行填充        String template = "D:\\excel\\fill\\template3.xlsx";        // 填充完成之后的excel        String fillname = "D:\\excel\\fill\\fill3.xlsx";        // 创建填充配置 水平填充        FillConfig fillConfig = FillConfig.builder()//                .forceNewRow(true)                .direction(WriteDirectionEnum.HORIZONTAL).build();        // 创建写对象        ExcelWriter excelWriter = EasyExcel.write(fillname,                FillData.class).withTemplate(template).build();        // 创建Sheet对象        WriteSheet sheet = EasyExcel.writerSheet(0).build();        // 多组填充excel        excelWriter.fill(getFillData(), fillConfig, sheet);        // 关闭流        excelWriter.finish();    }

模板：