easyExcel把Excel文件里的数据导入数据库。从数据库导出到Excel，或浏览器直接下载为TXT格式 （非EasyExcel）

导入数据库

导入依赖

<!-- 使用 EasyExcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.2</version>
        </dependency>
        <!-- 需要使用 JPA -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-jpa</artifactId>
        </dependency>

编辑实体类

实体类的字段需要加上@ExcelProperty注解标注字段。

使用JPA，需要给实体类添加@Entity注解，

数据库必须指定唯一主键，并需要给主键添加两个注解

@Id
//主键自增策略
@GeneratedValue(strategy = GenerationType.IDENTITY)

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student4 {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @ExcelProperty(value = "主键ID", index = 0)
    private Integer id;
    @ExcelProperty(value = "姓名", index = 1)
    private String name;
    @ExcelProperty(value = "生日", index = 2)
    private String birthday;
    @ExcelProperty(value = "性别", index = 3)
    private String sex;
    @ExcelProperty(value = "学生住址", index = 4)
    private String address;
}

 创建一个接口，继承JpaRepository，并添加泛型。

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface Student4Reposotory extends JpaRepository<Student4,Long> {
}

在controller层中，直接使用

@RestController
public class EasyExcelToDb {
    @Autowired
    Student4Reposotory student4Reposotory;

    @PostMapping("/excelToDb")
    public String excelToDb(@RequestParam("file")MultipartFile file) throws IOException {
        //使用输入流获取文件
        InputStream inputStream = file.getInputStream();
        //调用easyExcel 方法，直接上传
        List<Student4> student4List = EasyExcel.read(inputStream).head(Student4.class).sheet().doReadSync();
        //调用repository的方法。
        student4Reposotory.saveAll(student4List);
        return "上传成功";
    }
}

 postman,测试完全OK

从数据库导出指定位置Excel

 @Autowired
    JdbcTemplate jdbcTemplate;

    @GetMapping("/DbToExcel")
    public String DbToExcel(){
        List<Student4> studentList = jdbcTemplate.query("SELECT * FROM student4", new RowMapper<Student4>() {
            @Override
            public Student4 mapRow(ResultSet resultSet, int i) throws SQLException {
                Student4 student = new Student4();
                student.setId(resultSet.getInt("id"));
                student.setName(resultSet.getString("name"));
                student.setBirthday(resultSet.getString("birthday"));
                student.setSex(resultSet.getString("sex"));
                student.setAddress(resultSet.getString("address"));
                return student;
            }
        });
        System.out.println(studentList);
        ExcelWriter excelWriter = EasyExcel.write("D:\\files/student4EasyExcel导出测试.xlsx", Student4.class).build();
        WriteSheet writeSheet = EasyExcel.writerSheet("学生信息表").build();
        excelWriter.write(studentList, writeSheet);
        excelWriter.finish();
        System.out.println("导出成功");
        return "导出成功";
    }

浏览器直接下载Excel

@SuppressWarnings("SpringJavaInjectionPointsAutowiringInspection")
    @Autowired
    JdbcTemplate jdbcTemplate;

    @GetMapping("/EasyDbToFile")
    public void DbToExcel(HttpServletResponse response) throws IOException {
        // 查询数据库，获取学生信息列表
        List<Student4> studentList = jdbcTemplate.query("SELECT * FROM student4", new RowMapper<Student4>() {
            @Override
            public Student4 mapRow(ResultSet resultSet, int i) throws SQLException {
                Student4 student = new Student4();
                student.setId(resultSet.getInt("id"));
                student.setName(resultSet.getString("name"));
                student.setBirthday(resultSet.getString("birthday"));
                student.setSex(resultSet.getString("sex"));
                student.setAddress(resultSet.getString("address"));
                return student;
            }
        });

        // 将学生信息导出到 ByteArrayOutputStream 对象中
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        EasyExcel.write(byteArrayOutputStream, Student4.class).sheet("学生信息表").doWrite(studentList);

        // 设置响应头信息，将 Excel 文件发送给浏览器
        response.reset();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("学生信息表.xlsx", "UTF-8"));
        response.setContentLength(byteArrayOutputStream.size());
        ServletOutputStream outputStream = response.getOutputStream();
        byteArrayOutputStream.writeTo(outputStream);
        outputStream.flush();
        outputStream.close();

        System.out.println("导出成功");
}

浏览器直接下载为TXT格式 （非EasyExcel）

@SuppressWarnings("SpringJavaInjectionPointsAutowiringInspection")
    @Autowired
    JdbcTemplate jdbcTemplate;

    @GetMapping("/DbToTxt")
    public void DbToTxt(HttpServletResponse response) throws IOException {
        // 查询数据库，获取学生信息列表
        List<Student4> studentList = jdbcTemplate.query("SELECT * FROM student4", new RowMapper<Student4>() {
            @Override
            public Student4 mapRow(ResultSet resultSet, int i) throws SQLException {
                Student4 student = new Student4();
                student.setId(resultSet.getInt("id"));
                student.setName(resultSet.getString("name"));
                student.setBirthday(resultSet.getString("birthday"));
                student.setSex(resultSet.getString("sex"));
                student.setAddress(resultSet.getString("address"));
                return student;
            }
        });

        // 将学生信息导出到文本文件中
        File file = new File("学生信息表.txt");
        FileWriter fileWriter = new FileWriter(file);

        for (Student4 student : studentList) {
            String line = String.format("%d\t%s\t%s\t%s\t%s%n",
                    student.getId(), student.getName(), student.getSex(),
                    student.getBirthday(), student.getAddress());
            fileWriter.write(line);
        }

        fileWriter.close();

        // 设置响应头信息，将文本文件发送给浏览器
        response.reset();
        response.setContentType("text/plain");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("学生信息表导出文本测试.txt", "UTF-8"));
        response.setContentLength((int) file.length());
        InputStream inputStream = new FileInputStream(file);
        OutputStream outputStream = response.getOutputStream();
        byte[] buffer = new byte[1024];
        int bytesRead = 0;
        while ((bytesRead = inputStream.read(buffer)) != -1) {
            outputStream.write(buffer, 0, bytesRead);
        }
        inputStream.close();
        outputStream.flush();
        outputStream.close();

        System.out.println("导出成功");
    }