前言:之前的博客介绍了Java实现Excel批量导入数据库,昨天二哈兄弟说为什么没有数据库导出到Excel的呢?所以此篇博客将主要针对数据库里面的数据导出到Excel
1.导出前分析
众所周知Alibaba提供了开源的EasyExcel技术,该技术是针对Apache POI技术的封装和优化,主要解决了POI技术的耗内存问题,并且提供了较好的API使用。不需要大量的代码就可以实现excel的操作功能
- 性能对比:POI对内存消耗很大,在处理大批量的数据时,容易造成内存溢出,比如比如处理一个 3M 的 Excel,poi 可能需要上百兆的内存,而 easyexcel 可能只需要几兆而已,所以在性能这一块, poi与EasyExcel是无法媲美的
- 复杂度对比:POI需要自己对数据进行处理,尤其是对表格样式设计起来稍微有点复杂,所以造成开发的时候代码量很大;但是easyExcel能够自己处理数据,表格格式设计也简单
不过现在使用POI进行数据操作的还是不占少数,所以此篇博客还是对于使用POI实现对数据库数据导出到Excel,关于使用easyExcel操作后序博客会有讲解
2.首先数据库表里存储一些测试数据
3.配置pom以及application.properties
这里我还是使用之前的Java实现Excel批量导入数据库的配置,就不做大的变动
pom.xml
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.1</version>
</dependency>
<!--java对象状态自动映射到关系数据库中数据上-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>5.0.2.RELEASE</version>
</dependency>
<!--实现类与xml之间的相互转换-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-oxm</artifactId>
<version>5.0.2.RELEASE</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>5.2.12.Final</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-jpamodelgen</artifactId>
<version>5.2.12.Final</version>
</dependency>
<dependency>
<groupId>org.springframework.data</groupId>
<artifactId>spring-data-jpa</artifactId>
<version>2.2.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--前端页面我这里使用了thymeleaf模板引擎-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.12</version>
</dependency>
<!--使用POI读取文件-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
application.properties
spring.datasource.username=123456
spring.datasource.password=123456
spring.datasource.url=jdbc:mysql://localhost:3306/excel?useUnicode=true&useSSL=false&characterEncoding=UTF-8&serverTimezone=UTC
4.创建实体类 Excel
package com.wxy.excel.entity;
import java.io.Serializable;
public class Excel implements Serializable {
private String id;
private String username;
private String email;
private String password;
private String role;
//此处省略getter,setter以及构造方法
5.创建Dao层接口
因为导出到Excel数据之前,需要将需要的数据查出来,所以此接口实现数据查询,这里为了方便直接通过@Select注解进行查询操作
package com.wxy.excel.mapper;
import com.wxy.excel.entity.Excel;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
@Mapper
public interface UserMapper {
@Select("select * from excel")
List<Excel> getAllUser();
}
6.service层处理
这里提供的接口,这里主要是通过HttpServletResponse 进行请求响应,由于HttpServletResponse是ServletResponse的子接口,功能和方法更加强大
package com.wxy.excel.service;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
public interface ExcelService {
/*数据库导出到Excel*/
void exportExcel(HttpServletResponse response) throws IOException;
}
对以上接口实现,进行导出Excel的逻辑处理
package com.wxy.excel.service;
import com.wxy.excel.mapper.ExcelRepository;
import com.wxy.excel.entity.Excel;
import com.wxy.excel.mapper.UserMapper;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
@Service
public class ExcelServiceImpl implements ExcelService{
@Autowired
private UserMapper userMapper;
@Override
public void exportExcel(HttpServletResponse response) throws IOException {
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("test");
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow(0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
// 创建一个居中格式
style.setAlignment(HorizontalAlignment.CENTER);
/*此处根据情况自己自定义样式*/
HSSFCell cell = row.createCell(0);
cell.setCellValue("ID");
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("姓名");
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue("邮箱");
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue("密码");
cell.setCellStyle(style);
cell = row.createCell(4);
cell.setCellValue("角色");
cell.setCellStyle(style);
// 第五步,写入实体数据 实际应用中这些数据从数据库得到,
List<Excel> list = userMapper.getAllUser();
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(i + 1);
Excel excel = list.get(i);
// 创建单元格,并设置值
row.createCell(0).setCellValue(excel.getId());
row.createCell(1).setCellValue(excel.getUsername());
row.createCell(2).setCellValue(excel.getEmail());
row.createCell(3).setCellValue(excel.getPassword());
row.createCell(4).setCellValue(excel.getRole());
}
//第六步,输出Excel文件
OutputStream output = response.getOutputStream();
response.reset();
//设置日期格式
SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
// 获取当前系统时间
String fileName = df.format(new Date());
//设置导出文件表头(即文件名)
response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");
//设置返回内容类型
response.setContentType("application/msexcel");
wb.write(output);
output.close();
}
}
7.controller层请求
package com.wxy.excel.controller;
import com.wxy.excel.service.ExcelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import javax.xml.soap.SAAJResult;
@Controller
public class ExcelController {
@Autowired
private ExcelService excelService;
@GetMapping("/export")
public void exportExcel(HttpServletResponse response) {
try {
excelService.exportExcel(response);
System.out.println("导出成功");
} catch (Exception e) {
e.printStackTrace();
}
}
}
8.浏览器请求进行数据导出
浏览器请求地址 localhost:8080/export
打开下载文件效果如下:
结语:博客整理时难免会有些遗漏,已经项目Demo上传到github,地址https://github.com/Wangxy9527/ExcelToDatabase 包括数据库导出到excel以及Excel导入到数据库,上述文章,如有疑问欢迎留言,Common progress