一.导入相关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: true2.建立如下图所示的目录结构
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的相关用法，大家可以参考网上其他的资料。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
