SpringBoot+Mybatis+FastExcel查询数据并且导出

1。将本地的Excel文件数据上传到数据库当中

2。将查询结果下载到本地文件当中

使用FastExcel坐标

<dependency>
    <groupId>cn.idev.excel</groupId>
    <artifactId>fastexcel</artifactId>
    <version>1.0.0</version>
</dependency>

实体类，也就是查询结果的类

import cn.idev.excel.annotation.ExcelProperty;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;

@Getter
@EqualsAndHashCode
@Setter
public class ExcelUser {
    @ExcelProperty("id")
private  Integer id;
    @ExcelProperty("姓名")
private String Name;
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", Name='" + Name + '\'' +
                '}';
    }
}

先是上传到数据库的操作，实现监听器

import cn.idev.excel.context.AnalysisContext;
import cn.idev.excel.event.AnalysisEventListener;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class DemoDataListener<SqlUser> extends AnalysisEventListener<SqlUser> {

    private List<SqlUser> dataList=new ArrayList<>();

    @Override
    public void invoke(SqlUser t, AnalysisContext analysisContext) {
        dataList.add(t);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("读取完成。共读取了"+dataList.size()+"条数据");
    }
    public List<SqlUser>getDataList(){
        return dataList;
    }
}

编写Mapper中的插入语句

 @Insert("<script>" +
            "INSERT INTO procyell (id, name) VALUES " +
            "<foreach collection='users' item='user' separator=','>" +
            "(#{user.id}, #{user.name})" +
            "</foreach>" +
            "</script>")
    public  void insertUsers2(List<ExcelUser> users);

最后是Controller

@RestController

public class FileUploadController {
    @Autowired
    private Mapperuser mapper;

    @RequestMapping("/upload1")
    public String uploadFile(@RequestParam("file") MultipartFile file) throws IOException {
        if (file.isEmpty()) {
            return "路径不能为空";
        }
        DemoDataListener<ExcelUser> dataListener = new DemoDataListener<>();
        FastExcel.read(file.getInputStream(), ExcelUser.class, dataListener).sheet().doRead();
        //这里怎么遍历getDataList方法中的数据。然后添加到数据库当中
        List<ExcelUser> data = dataListener.getDataList();
        mapper.insertUsers(data);

        return "文件上传成功";
    }

接下来是导出下载的代码

封装一个FileDownloadUtil类用于解析转换数据格式的工具

import jakarta.servlet.http.HttpServletResponse;

import java.io.IOException;
import java.net.URLEncoder;

public class FileDownloadUtil {

    public static void setFileDownloadHeader(HttpServletResponse response, String fileName) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment; filename=\"" + fileName + "\"");
    }
}

Mapper查询需要导出的数据

    @Select("select a.id,a.name from procyell as a join procone as b on a.id=b.id ")
    List<ExcelUser> getResult();

Controller控制下载导出数据

 @RequestMapping("/download")
        public void sim(HttpServletResponse response) throws IOException {
            List<ExcelUser> userList = mapper.getResult();
            String fileName =  ".xlsx";
            // 设置文件下载头
            FileDownloadUtil.setFileDownloadHeader(response, fileName);
            // 使用FastExcel写入数据到Excel文件
            FastExcel.write(response.getOutputStream(), ExcelUser.class)
                    .sheet("用户信息")
                    .doWrite(userList);
        }