easyexcel_语雀easyexcel

作者：我家小花儿 | 2024-06-14 20:29:53

踩

语雀easyexcel

目录:

1.easyexcel

2.easyexcel的写操作

3.easyexcel写操作--web模式

4.easyexcel完成读操作

5.easyexcel读操作--web模式

1.easyexcel

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。github地址:https://github.com-alibaba/easyexcel

我们现在用这个网站写excel（文档已经迁移） · 语雀EasyExcel写Excel的示例https://www.yuque.com/easyexcel/doc/write通过java代码完成对Excel的读写操作。所谓的读写理解为上传和下载

2.easyexcel写操作

所谓的写操作就是把java中的类对象写入到excel表格中

实现步骤：

1.引入相关依赖 2.配置相应的实体类 3.通过excel进行写入操作

1.引入相关依赖


<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.12.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.hql</groupId>
    <artifactId>spring-easyexcel</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>spring-easyexcel</name>
    <description>spring-easyexcel</description>
    <properties>
        <java.version>8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
 
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
 
 
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.2</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.62</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
    </dependencies>
 
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>
 
</project>

2.配置相应的实体类


package com.hql.excel;
 
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
 
/**
 * @program: spring-easyexcel
 * @description:
 * @author:
 * @create: 2023-04-28 14:51
 **/
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName
public class ExcelDemo {
    @ExcelProperty("ID")//标记excel标题内容
    @TableId(type = IdType.AUTO)
    private int id;
    @ExcelProperty("姓名")
    private String ename;
    @ExcelProperty("年龄")
    private int age;
//    @ExcelIgnore  该属性不写入excel表格里
 
}

3.通过excel进行写入操作

这里的data,以后我们可以通过查询数据库


package com.hql.excel;
 
import com.alibaba.excel.EasyExcel;
 
import java.util.ArrayList;
import java.util.List;
 
/**
 * @program: spring-easyexcel
 * @description:
 * @author: 
 * @create: 2023-04-28 14:54
 **/
public class TestWrite {
    public static void main(String[] args) {
        String fileName =  "D:\\maven\\apache-maven-3.8.6\\conf\\spring-easyexcel\\mingren.xls";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        List<ExcelDemo> data=new ArrayList<>();
        data.add(new ExcelDemo(1,"李白",18));
        data.add(new ExcelDemo(2,"杜甫",18));
        data.add(new ExcelDemo(3,"辛弃疾",18));
        data.add(new ExcelDemo(4,"苏轼",18));
        EasyExcel.write(fileName, ExcelDemo.class).sheet("ok").doWrite(data);
    }
}

运行后

3.easyexcel写操作web模式


package com.ykq.qy163easyexcel.controller;
 
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.fastjson.JSON;
import com.ykq.qy163easyexcel.dao.UserDao;
import com.ykq.qy163easyexcel.excel.ExcelDemo;
import com.ykq.qy163easyexcel.listener.UserDataListener;
import com.ykq.qy163easyexcel.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
 
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;
 
/**
 * @program: qy163-easyexcel
 * @description:
 * @author: 
 * @create: 2023-04-28 15:06
 **/
@Controller
public class UploadController {
 
 
 
    @GetMapping("/upload")
    public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
        // 这里需要设置不关闭流
        List<ExcelDemo> data=new ArrayList<>();
        data.add(new ExcelDemo(1,"李白",18));
        data.add(new ExcelDemo(2,"杜甫",18));
        data.add(new ExcelDemo(3,"辛弃疾",18));
        data.add(new ExcelDemo(4,"苏轼",18));
        upload(response,"今天放假",data);
    }
    public void upload(HttpServletResponse response,String title,List<?> data) throws IOException{
        // 这里注意 使用swagger 会导致各种问题，请直接用浏览器或者用postman
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("qy163下载学员信息", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
 
            EasyExcel.write(response.getOutputStream(), ExcelDemo.class).autoCloseStream(Boolean.FALSE).sheet("模板")
                    .doWrite(data);
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = MapUtils.newHashMap();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }
}

4.easyexcel完成读操作

1.监听器


package com.hql.listener;
 
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import com.hql.dao.ExcelDao;
import com.hql.excel.ExcelDemo;
import lombok.extern.slf4j.Slf4j;
 
import java.util.List;
 
/**
 * @program: spring-easyexcel
 * @description:
 * @author: 
 * @create: 2023-04-28 16:30
 **/
@Slf4j
public class ExcelListener implements ReadListener<ExcelDemo> {
 
    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;
    /**
     * 缓存的数据
     */
    private List<ExcelDemo> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private ExcelDao excelDao;
 
    public ExcelListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
//        excelDao = new ExcelDao();
    }
 
    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param excelDao
     */
    public ExcelListener(ExcelDao excelDao) {
        this.excelDao = excelDao;
    }
 
    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(ExcelDemo data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        cachedDataList.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }
 
    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }
 
    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        excelDao.batchSave(cachedDataList);
        log.info("存储数据库成功！");
    }
}

2.dao层


package com.hql.dao;
 
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hql.excel.ExcelDemo;
import org.apache.ibatis.annotations.Mapper;
 
import java.util.List;
 
/**
 * @program: spring-easyexcel
 * @description:
 * @author: 
 * @create: 2023-04-28 16:36
 **/
@Mapper
public interface ExcelDao extends BaseMapper<ExcelDemo>{
 
   void batchSave(List<ExcelDemo> cachedDataList);
 
 
}

3.测试层


package com.hql.excel;
 
import com.alibaba.excel.EasyExcel;
import com.hql.dao.ExcelDao;
import com.hql.listener.ExcelListener;
import org.springframework.beans.factory.annotation.Autowired;
 
import java.io.File;
 
/**
 * @program: spring-easyexcel
 * @description:
 * @author: 
 * @create: 2023-04-28 16:18
 **/
public class TestRead {
//    @Autowired
//    private ExcelDao excelDao;
    public static void main(String[] args) {
       String fileName = "D:\\maven\\apache-maven-3.8.6\\conf\\spring-easyexcel\\mingre.xls";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, ExcelDemo.class, new ExcelListener()).sheet().doRead();
    }
 
}

easyexce读操作web模式


package com.hql.controller;
 
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.fastjson.JSON;
import com.hql.dao.ExcelDao;
import com.hql.excel.ExcelDemo;
import com.hql.listener.ExcelListener;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
 
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;
 
/**
 * @program: spring-easyexcel
 * @description:
 * @author: 
 * @create: 2023-04-28 15:15
 **/
@RestController
public class ExcelController {
    /**
     * 文件上传
     */
    @Autowired
    private ExcelDao excelDao;
    @PostMapping("upload2")
    public String upload2(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), ExcelDemo.class, new ExcelListener(excelDao)).sheet().doRead();
        return "success";
    }
 
}

通过postman进行测试

查看数据库

本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】