Spring Data配合easyexcel的使用

最新推荐文章于 2024-05-19 17:18:53 发布

QH丶

最新推荐文章于 2024-05-19 17:18:53 发布

阅读量193

点赞数 2

本文链接：https://blog.csdn.net/weixin_45012050/article/details/117672129

版权

一.导入相关springdata与easyexcel的依赖。

二.springdata的配置和相关操作。

三.springdata在easyexcel的简单运用。

一.导入相关springdata与easyexcel的依赖。其他的springboot、mysql驱动等依赖这里不再赘述。

<dependency>
    <groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>easyexcel</artifactId>
	<version>2.2.4</version>
</dependency>

二.springdata的配置和相关操作

1.springdata在application.yml中的配置。

spring:
  devtools:
    restart:
      enabled: false
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test
    hikari: # springboot 2.0 整合了hikari ,据说这是目前性能最好的java数据库连接池
      username: root
      password: root
  jpa:
    hibernate:
      ddl-auto: update  # 第一次建表create  后面用update，要不然每次重启工程会删除表并新建
    show-sql: true

2.建立如下图所示的目录结构

3.编写实体类、控制层类、持久层类、服务层类，相关类的代码如下。

实体User类。

package com.space.jpa.bean;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;


@Data
@Entity  // 该注解声明一个实体类，与数据库中的表对应
public class User extends BaseRowModel implements Serializable {

    @Id   // 表明id
    @GeneratedValue   //  自动生成
    @ExcelProperty(value = "id",index = 0)
    private long id ;

    @ExcelProperty(value = "name",index = 1)
    private String name ;

}

持久层UserDAO类。

package com.space.jpa.dao;

import com.space.jpa.bean.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Component;

@Component
public interface UserDAO extends JpaRepository<User,Long> {
    /*
    * 我们在这里直接继承 JpaRepository
    * 这里面已经有很多现成的方法了
    * 这也是JPA的一大优点
    *
    * */
}

服务层UserService接口以及它的实现类。

package com.space.jpa.service;

import com.space.jpa.bean.User;

import java.util.List;

public interface UserService {
    List<User> findAll();
}

package com.space.jpa.service.impl;

import com.space.jpa.bean.User;
import com.space.jpa.dao.UserDAO;
import com.space.jpa.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;


@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserDAO userDAO;

    @Override
    public List<User> findAll() {
        return userDAO.findAll();
    }
}

控制层UserController类。

package com.space.jpa.controller;

import com.alibaba.excel.EasyExcel;
import com.space.jpa.Listener.ModelExcelListener;
import com.space.jpa.bean.User;
import com.space.jpa.service.UserService;
import lombok.Data;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;
    
    /*
    springdata使用测试
     */
    @GetMapping("/list")
    public String findAll() {
        return userService.findAll().toString();
    }

        /**
         * 下载模板
         * 加载资源->读取资源->写入响应流
         */
        @GetMapping("/downloadTemplate")
        public void downloadTemplate(HttpServletResponse response) throws Exception {
            ClassPathResource classPathResource = new ClassPathResource("excelTemplate/easyexcel.xlsx");
            InputStream inputStream = classPathResource.getInputStream();
            Workbook workbook = new XSSFWorkbook(inputStream);
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("content-Disposition", "attachment;filename=" + URLEncoder.encode("easyexcel.xlsx", "utf-8"));
            response.setHeader("Access-Control-Expose-Headers", "content-Disposition");
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        }

    /**
     * 导出数据
     * 定义列标题->创建sheet->自定义字体和风格->构造数据->写入数据->写入到浏览器响应流
     */
    @GetMapping("/exportData")
    public void exportData(HttpServletResponse response) throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();

        String []columnNames = {"id","name"};

        Sheet sheet = workbook.createSheet();
        Font titleFont = workbook.createFont();
        titleFont.setFontName("simsun");
        titleFont.setBold(true);
        titleFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
        titleStyle.setFont(titleFont);

        Row titleRow = sheet.createRow(0);

        for (int i = 0; i < columnNames.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(titleStyle);
        }
        //模拟构造数据
        List<User> dataList = new ArrayList<>();
        List<User> allusers = userService.findAll();
        dataList = allusers;

        //创建数据行并写入值
        for (int j = 0; j < dataList.size(); j++) {
            User user = dataList.get(j);
            int lastRowNum = sheet.getLastRowNum();
            Row dataRow = sheet.createRow(lastRowNum + 1);
            dataRow.createCell(0).setCellValue(user.getId());
            dataRow.createCell(1).setCellValue(user.getName());
        }
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("content-Disposition", "attachment;filename=" + URLEncoder.encode("users.xls", "utf-8"));
        response.setHeader("Access-Control-Expose-Headers", "content-Disposition");
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }

    /*
    读取数据
     */
    @PostMapping("/readExcel")
    public List<User> readExcel(@RequestParam("file") MultipartFile file){
        List<User> list = new ArrayList<>();
        try {
            list = EasyExcel.read(file.getInputStream(),User.class,new ModelExcelListener()).sheet().doReadSync();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }



}

三.springdata在easyexcel的简单运用。搭配实体类上的相关注解和控制层中导入的easyexcel相关包编写逻辑代码实现。

 /**
         * 下载模板
         * 加载资源->读取资源->写入响应流
         */
        @GetMapping("/downloadTemplate")
        public void downloadTemplate(HttpServletResponse response) throws Exception {
            ClassPathResource classPathResource = new ClassPathResource("excelTemplate/easyexcel.xlsx");
            InputStream inputStream = classPathResource.getInputStream();
            Workbook workbook = new XSSFWorkbook(inputStream);
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("content-Disposition", "attachment;filename=" + URLEncoder.encode("easyexcel.xlsx", "utf-8"));
            response.setHeader("Access-Control-Expose-Headers", "content-Disposition");
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        }

    /**
     * 导出数据
     * 定义列标题->创建sheet->自定义字体和风格->构造数据->写入数据->写入到浏览器响应流
     */
    @GetMapping("/exportData")
    public void exportData(HttpServletResponse response) throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();

        String []columnNames = {"id","name"};

        Sheet sheet = workbook.createSheet();
        Font titleFont = workbook.createFont();
        titleFont.setFontName("simsun");
        titleFont.setBold(true);
        titleFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
        titleStyle.setFont(titleFont);

        Row titleRow = sheet.createRow(0);

        for (int i = 0; i < columnNames.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(titleStyle);
        }
        //模拟构造数据
        List<User> dataList = new ArrayList<>();
        List<User> allusers = userService.findAll();
        dataList = allusers;

        //创建数据行并写入值
        for (int j = 0; j < dataList.size(); j++) {
            User user = dataList.get(j);
            int lastRowNum = sheet.getLastRowNum();
            Row dataRow = sheet.createRow(lastRowNum + 1);
            dataRow.createCell(0).setCellValue(user.getId());
            dataRow.createCell(1).setCellValue(user.getName());
        }
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("content-Disposition", "attachment;filename=" + URLEncoder.encode("users.xls", "utf-8"));
        response.setHeader("Access-Control-Expose-Headers", "content-Disposition");
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }

    /*
    读取数据
     */
    @PostMapping("/readExcel")
    public List<User> readExcel(@RequestParam("file") MultipartFile file){
        List<User> list = new ArrayList<>();
        try {
            list = EasyExcel.read(file.getInputStream(),User.class,new ModelExcelListener()).sheet().doReadSync();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

声明:第一次运行项目时，如果对应数据库中没有相关表，则会自动生成与实体类对应的表(表名为实体类名首字母小写)。使用lombok时，如果发现getter和setter获取不到，检查是否安装了lombok插件和lombok是否为最新版本。easyexcel操作流程固定，具体使用时可根据业务需要进行进一步封装和代码优化。springdata相当于mybatis来说，简化了持久层，也就是dao层或时mapper层的代码量，不用写繁琐的xml映射而实现增删改查。特别强调注意JpaRepository<User,Long>等相关类的dao层继承使用和方法的命名规范以及相关参数的含义。(命名的具体规范大家可以搜一下，网上一大片，JpaRepository<User,Long>中泛型中第一个参数为实体类型，第二个参数为实体主键类型，这里时User类主键id类型为long)。具体的其他的springdata的相关用法，大家可以参考网上其他的资料。