EasyExcel简单使用

官方文档

代码地址：https://gitee.com/suisui9857/easyExcleCode
导入依赖

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

1.写入Excel

1.创建实体类模板对象

/**
 * @Description: 用户
 * 创建user类模板，通过User类模板，想Excel表格中写数据
 * @ExcelProperty("用户编号")  描述excel的表格头
 **/
public class User {
    @ExcelProperty(value = "用户编号")
    private Integer userId;
    @ExcelProperty(value = "用户名称")
    private String userName;
    @ExcelProperty(value = "用户性别")
    private String gender;
    @ExcelProperty(value = "用户薪资")
    private Double salary;
    @ExcelProperty(value = "入职时间")
    private Date hireDate;
    }

1.简单写入-方式一

  @Test
    public void test1(){
        //创建一个excel文档，默认当前项目的目录下，文件自己创建
        String fileName = "user1.xlsx";
        //根据User模板构建数据
        ArrayList<User> list = new ArrayList<>();
        User user1 = new User(1001,"李磊","男",10000.0,new Date());
        User user2 = new User(1002,"韩梅梅","女",10000.0,new Date());
        User user3 = new User(1003,"汤姆","男",10000.0,new Date());
        User user4 = new User(1004,"猫","女",10000.0,new Date());
        User user5 = new User(1005,"狗","男",10000.0,new Date());
        list.add(user1);
        list.add(user2);
        list.add(user3);
        list.add(user4);
        list.add(user5);
        //向excel中写数据，文件名，类型，标签名
        EasyExcel.write(fileName,User.class).sheet("用户信息").doWrite(list);
    }

2.简单写入-方式二

    @Test
    public void test2(){
        //创建一个excel文档，默认当前项目的目录下，文件自己创建
        String fileName = "user2.xlsx";
        //根据User模板构建数据
        ArrayList<User> list = new ArrayList<>();
        User user1 = new User(1001,"李磊","男",10000.0,new Date());
        User user2 = new User(1002,"韩梅梅","女",10000.0,new Date());
        User user3 = new User(1003,"汤姆","男",10000.0,new Date());
        User user4 = new User(1004,"猫","女",10000.0,new Date());
        User user5 = new User(1005,"狗","男",10000.0,new Date());
        list.add(user1);
        list.add(user2);
        list.add(user3);
        list.add(user4);
        list.add(user5);
        //构建ExcelWriter对象
        ExcelWriter excelWriter = EasyExcel.write(fileName, User.class).build();
        //创建sheet对象
        WriteSheet writerSheet = EasyExcel.writerSheet("用户信息").build();
        //信息写入
        excelWriter.write(list,writerSheet);
        //关闭
        excelWriter.finish();
    }

3.排除模板对象中的某些列

    /**
     * 排除模板中的列
     */
    @Test
    public void test3(){
        //创建一个excel文档，默认当前项目的目录下，文件自己创建
        String fileName = "user3.xlsx";
        //根据User模板构建数据
        ArrayList<User> list = new ArrayList<>();
        User user1 = new User(1001,"李磊","男",10000.0,new Date());
        User user2 = new User(1002,"韩梅梅","女",10000.0,new Date());
        User user3 = new User(1003,"汤姆","男",10000.0,new Date());
        User user4 = new User(1004,"猫","女",10000.0,new Date());
        User user5 = new User(1005,"狗","男",10000.0,new Date());
        list.add(user1);
        list.add(user2);
        list.add(user3);
        list.add(user4);
        list.add(user5);

        //设置排除的属性
        HashSet<String> set = new HashSet<>();
        set.add("hireDate");
        set.add("salary");
        //写入
        EasyExcel.write(fileName, User.class).excludeColumnFiledNames(set).sheet("用户信息").doWrite(list);
    }

4.按照顺序排列

/**
 * @Description: 用户
 * 创建user类模板，通过User类模板，想Excel表格中写数据
 * @ExcelProperty("用户编号")  描述excel的表格头,index = 0定义顺序，或者改变属性上下顺序
 **/
public class User {

    @ExcelProperty(value = "用户编号",index = 0)
    private Integer userId;
    @ExcelProperty(value = "用户名称",index = 1)
    private String userName;
    @ExcelProperty(value = "用户性别",index = 3)
    private String gender;
    @ExcelProperty(value = "用户薪资",index = 2)
    private Double salary;
    @ExcelProperty(value = "入职时间",index = 4)
    private Date hireDate;

5.复杂头

public class ComplexHeadUser {
    @ExcelProperty({"用户主题1","用户编号"})
    private Integer userId;
    @ExcelProperty({"用户主题1","用户名"})
    private String userName;
    @ExcelProperty({"用户主题1","用户性别"})
    private String gender;

示例：

6.重复写到同一个excel的sheet标签中

    /**
     * 重复写到同一个excel的sheet标签中
     */
    @Test
    public void test6() {
        //创建一个excel文档，默认当前项目的目录下，文件自己创建
        String fileName = "user6.xlsx";
        //根据User模板构建数据
        ArrayList<User> list = new ArrayList<>();
        User user1 = new User(1001, "李磊", "男", 10000.0, new Date());
        User user2 = new User(1002, "韩梅梅", "女", 10000.0, new Date());
        User user3 = new User(1003, "汤姆", "男", 10000.0, new Date());
        User user4 = new User(1004, "猫", "女", 10000.0, new Date());
        User user5 = new User(1005, "狗", "男", 10000.0, new Date());
        list.add(user1);
        list.add(user2);
        list.add(user3);
        list.add(user4);
        list.add(user5);

        //构建ExcelWriter对象
        ExcelWriter excelWriter = EasyExcel.write(fileName, User.class).build();
        //创建sheet对象
        WriteSheet writerSheet = EasyExcel.writerSheet("用户信息").build();
        //信息重复写入
        for (int i = 0; i < 5; i++) {
            excelWriter.write(list, writerSheet);
        }
        //关闭
        excelWriter.finish();
    }

7.重复写到同一个excel的不同sheet标签中

       /**
        *重复写到同一个excel的不同sheet标签中
        */
        @Test
        public void test7(){
            //创建一个excel文档，默认当前项目的目录下，文件自己创建
            String fileName = "user7.xlsx";
            //根据User模板构建数据
            ArrayList<User> list = new ArrayList<>();
            User user1 = new User(1001,"李磊","男",10000.0,new Date());
            User user2 = new User(1002,"韩梅梅","女",10000.0,new Date());
            User user3 = new User(1003,"汤姆","男",10000.0,new Date());
            User user4 = new User(1004,"猫","女",10000.0,new Date());
            User user5 = new User(1005,"狗","男",10000.0,new Date());
            list.add(user1);
            list.add(user2);
            list.add(user3);
            list.add(user4);
            list.add(user5);

            //构建ExcelWriter对象
            ExcelWriter excelWriter = EasyExcel.write(fileName, User.class).build();
            for (int i = 0; i < 5; i++) {
                //创建sheet对象
                WriteSheet writerSheet = EasyExcel.writerSheet("用户列表"+i).build();
                //写数据
                excelWriter.write(list,writerSheet);
            }
            //关闭
            excelWriter.finish();
        }

7.数字日期格式化

/**
 * @Description: 用户
 * 创建user类模板，通过User类模板，想Excel表格中写数据
 * @ExcelProperty("用户编号")  描述excel的表格头
 **/
public class User {
    @ExcelProperty(value = "用户编号",index = 0)
    private Integer userId;
    @ExcelProperty(value = "用户名称",index = 1)
    private String userName;
    @ExcelProperty(value = "用户性别",index = 3)
    private String gender;
    @ExcelProperty(value = "用户薪资",index = 2)
    //数字保留的位数
    @NumberFormat("#.##")
    private Double salary;
    //表格中日期显示格式
    @DateTimeFormat("yyyy年MM月dd日 HH:mm:ss")
    @ExcelProperty(value = "入职时间",index = 4)
    private Date hireDate;

8.插入图片-4种方式

创建模板对象

/**
 * @Description: 图片数据格式
 **/
public class ImageDate {
    //使用抽象文件表示一个图片
    private File file;

    //使用输入流保存一个图片
    private InputStream inputStream;

    //使用String类型保存图片，需使用StringImageConverter转换器
    @ExcelProperty(converter = StringImageConverter.class)
    private String string;

    //使用二进制保存一个图片
    private byte[] byteArrays;
    }

插入图片操作

    /**
     * 图片存入
     */
    @Test
    public void test9() throws Exception {
        //创建一个excel文档，默认当前项目的目录下，文件自己创建
        String fileName = "user9.xlsx";
        ArrayList<ImageDate> list = new ArrayList<>();
        //根据模板构建数据
        ImageDate imageDate = new ImageDate();
        imageDate.setFile(new File("fm.jpg"));
        imageDate.setInputStream(new FileInputStream(new File("fm.jpg")));
        imageDate.setString("fm.jpg");
        //创建一个指定大小的二进制数组
        byte[] bytes = new byte[(int) new File("fm.jpg").length()];
        FileInputStream stream = new FileInputStream("fm.jpg");
        //将文件流写入到二进制数组中
        stream.read(bytes,0,(int) new File("fm.jpg").length());
        imageDate.setByteArrays(bytes);

        imageDate.setUrl(new URL("https://gimg2.baidu.com/image_search/src=http%3A%2F%2Fimg.jj20.com%2Fup%2Fallimg%2F1114%2F113020142315%2F201130142315-1-1200.jpg&refer=http%3A%2F%2Fimg.jj20.com&app=2002&size=f9999,10000&q=a80&n=0&g=0n&fmt=auto?sec=1655946837&t=83859771f6a4e369535370da854e1b86"));
        //向excel中写数据，文件名，类型，标签名
        list.add(imageDate);
        EasyExcel.write(fileName,ImageDate.class).sheet("图片信息").doWrite(list);

    }

9.设置表格列宽和行高

创建模板对象

/**
 * @Description: 列宽和行高
 **/
@ContentRowHeight(30)//设置内容高度
@HeadRowHeight(40)//设置标题高度
@ColumnWidth(25)//设置列宽
public class WidthAndHeightData {

    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    //设置列宽(可以修饰类，也可修饰具体属性)
    @ColumnWidth(50)
    @ExcelProperty("数字标题")
    private Double aDouble;
    }

示例：

10.设置表格样式

创建模板对象

/**
 * @Description: 属性设置模板
 **/
//头背景设置成红色
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND,fillForegroundColor = 10)
//头字体设置成20
@HeadFontStyle(fontHeightInPoints = 10)
//字符串内容的背景设置为绿色
@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND,fillForegroundColor = 17)
//字符串的内容字体设置成20
@ContentFontStyle(fontHeightInPoints = 20)
public class StyleDate {

    //字符串的头背景设置成粉红色
    @HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND,fillForegroundColor = 14)
    //字符串的头字体设置成20
    @HeadFontStyle(fontHeightInPoints = 20)
    //字符串内容的背景设置为天蓝
    @ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND,fillForegroundColor = 40)
    //字符串的内容字体设置成30
    @ContentFontStyle(fontHeightInPoints = 30)
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double aDouble;
    }

示例：

11.合并单元格

创建模板对象

/**
 * firstRowIndex：起始行索引
 * lastRowIndex：结束行索引
 * firstColumnIndex：起始列索引
 * lastColumnIndex：结束列索引
 */
@OnceAbsoluteMerge(firstRowIndex = 1,lastRowIndex = 2,firstColumnIndex = 1,lastColumnIndex = 2)
public class MergeData {

    //每隔两行合并一次(竖着合并单元格)
    @ContentLoopMerge(eachRow = 2)
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double aDouble;
}

示例：

3.读文件

1.简单读文件-方式一

创建模板对象

/**
 * @Description: excel读到的数据
 **/
public class DemoData {
    //根据标题匹配
    //@ExcelProperty("日期标题")
    //通过下标获取
    @ExcelProperty(index = 1)
    //excel日期格式转换
    @DateTimeFormat("yyyy年MM月dd日 HH时mm分ss秒")
    private Date date;
    //@ExcelProperty("字符串标题")
    @ExcelProperty(index = 0)
    private String string;
    //@ExcelProperty("数字标题")
    @ExcelProperty(index = 2)
    //建议String类型@NumberFormat("#.##")
    private Double aDouble;

读文件

/**
     * 默认读取方式---excel数据表头和对象的属性字段顺序一致
     * * 通过名称读取： 实体类加@ExcelProperty("日期标题")
     * * 根据下标读取：实体类加@ExcelProperty(index = 1)
     */
    @Test
    public void test1(){
        //读取文件路径
        String fileName = "user10.xlsx";
        //读取excel
        EasyExcel.read(fileName, DemoData.class, new AnalysisEventListener<DemoData>(){
            /**
             * 每解析一行excel数据，就会被调用一次
             * @param demoData
             * @param analysisContext
             */
            @Override
            public void invoke(DemoData demoData, AnalysisContext analysisContext) {
                System.out.println("解析数据为：" + demoData.toString());
                //调用数据库将解析到的数据保存到数据库里
            }

            /**
             * 全部解析完被调用
             * @param analysisContext
             */
            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                System.out.println("全部解析完成");
            }
        }).sheet().doRead();

2.简单读文件-方式二

   @Test
   public void test1(){
        //读取excel--方式2
        ExcelReader build = EasyExcel.read(fileName, DemoData.class, new AnalysisEventListener<DemoData>() {
            /**
             * 每解析一行excel数据，就会被调用一次
             * @param demoData
             * @param analysisContext
             */
            @Override
            public void invoke(DemoData demoData, AnalysisContext analysisContext) {
                System.out.println("解析数据为：" + demoData.toString());
                //调用数据库将解析到的数据保存到数据库里
            }

            /**
             * 全部解析完被调用
             * @param analysisContext
             */
            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                System.out.println("全部解析完成");
            }
        }).build();
        //创建sheet对象，读取excel的第一个sheet(下级从0开始)
        ReadSheet sheet = EasyExcel.readSheet(0).build();
        build.read(sheet);
        //关闭流操作
        build.finish();
    }

2.读一个excel的多个sheet–数据模板都一样

@Test
    public void test2() {
        //读取文件路径
        String fileName = "user10.xlsx";
        //读取excel
        EasyExcel.read(fileName, DemoData.class, new AnalysisEventListener<DemoData>() {
            /**
             * 每解析一行excel数据，就会被调用一次
             *
             * @param demoData
             * @param analysisContext
             */
            @Override
            public void invoke(DemoData demoData, AnalysisContext analysisContext) {
                System.out.println("解析数据为：" + demoData.toString());
                //调用数据库将解析到的数据保存到数据库里
            }

            /**
             * 全部解析完被调用
             *
             * @param analysisContext
             */
            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                System.out.println("全部解析完成");
            }
        }).doReadAll();//----保证所以sheet都使用DemoData.class模板
    }

3.读一个excel的多个sheet–数据模板不一样

@Test
    public void test3() {
        //读取文件路径
        String fileName = "user10.xlsx";
        //构建excelReader对象
        ExcelReader excelReader = EasyExcel.read(fileName).build();
        //构建sheet标签对象
        ReadSheet sheet1 = EasyExcel.readSheet(0).head(DemoData.class).registerReadListener(new AnalysisEventListener<DemoData>() {

            //每解析一行被调用
            @Override
            public void invoke(DemoData demoData, AnalysisContext analysisContext) {
                System.out.println("sheet1解析数据：" + demoData);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {

            }
        }).build();
        ReadSheet sheet2 = EasyExcel.readSheet(1).head(DemoData.class).registerReadListener(new AnalysisEventListener<DemoData>() {

            //每解析一行被调用
            @Override
            public void invoke(DemoData demoData, AnalysisContext analysisContext) {
                System.out.println("sheet2解析数据：" + demoData);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {

            }
        }).build();
        excelReader.read(sheet1,sheet2);
        excelReader.finish();
    }

web上传/下载

下载：在web中写并且失败的时候返回json

1.WebUtils设置下载文件的请求头

public class WebUtils
{
    /**
     * 将字符串渲染到客户端
     * 
     * @param response 渲染对象
     * @param string 待渲染的字符串
     * @return null
     */
    public static void renderString(HttpServletResponse response, String string) {
        try
        {
            response.setStatus(200);
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            response.getWriter().print(string);
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
    }

    /**
     * 上传文件时的请求头 
     */
    public static void setDownLoadHeader(String filename, HttpServletResponse response) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fname= URLEncoder.encode(filename,"UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition","attachment; filename="+fname);
    }
}

2.写入方法内容

 @GetMapping("/export")
    public void export(HttpServletResponse response){
        try {
            //设置下载文件的请求头
            WebUtils.setDownLoadHeader("分类.xlsx",response);
            //获取需要导出的数据
            List<Category> categoryVos = categoryService.list();
            List<ExcelCategoryVo> excelCategoryVos = BeanCopyUtils.copyBeanList(categoryVos, ExcelCategoryVo.class);

            //把数据写入到Excel中
            EasyExcel.write(response.getOutputStream(), ExcelCategoryVo.class).autoCloseStream(Boolean.FALSE).sheet("分类导出")
                    .doWrite(excelCategoryVos);

        } catch (Exception e) {
            //如果出现异常也要响应json
            ResponseResult result = ResponseResult.errorResult(AppHttpCodeEnum.SYSTEM_ERROR);
            WebUtils.renderString(response, JSON.toJSONString(result));
        }
    }

3.实体类标注

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelCategoryVo {
    @ExcelProperty("分类名")
    private String name;
    //描述
    @ExcelProperty("描述")
    private String description;

    //状态0:正常,1禁用
    @ExcelProperty("状态0:正常,1禁用")
    private String status;
}

上传

参考监听器地址

  @Override
    public void uploadExcel(MultipartFile file, HttpServletRequest req, HttpServletResponse resp) throws IOException {

        EasyExcel.read(file.getInputStream(), ExcelCategoryVo.class,new ReadListener<ExcelCategoryVo>() {
            @Override
            public void invoke(ExcelCategoryVo o, AnalysisContext analysisContext) {
                log.info("解析到一条数据:{}", JSON.toJSONString(o));
                cachedDataList.add(o);
                // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
                if (cachedDataList.size() >= BATCH_COUNT) {
                    log.info("存储到数据库里");
                    // 存储完成清理 list
                    cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
                }
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                // 这里也要保存数据，确保最后遗留的数据也存储到数据库
                log.info("存储到数据库里");
                log.info("所有数据解析完成！");
            }
        }).sheet().doRead();
    }
}

文件内容

日志打印