工具: idea
数据库: mysql
框架:Springboot
准备工作:
1.导入主要依赖 (poi)
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.1.2</version>
</dependency>
2.配置yml
server:
port: 8081
#管理数据源
spring:
datasource:
#高版本驱动使用
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/news?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true
#设定用户名和密码
username: root
password: root
mvc:
pathmatch:
matching-strategy: ant_path_matcher
thymeleaf:
prefix: classpath:/templates/ #th模板运行路径
suffix: .html
mode: HTML5
encoding: UTF-8
cache: false
resources:
chain:
strategy:
content:
enabled: true
paths: /**
static-locations: classpath:/templates/ #页面运行路径
mybatis:
mapper-locations: classpath:/mapper/*.xml
type-aliases-package: com.ketai.springbootpoiechart.pojo
一、数据库导出Excle表格
1.0 实体类
package com.ketai.springbootpoiechart.pojo;
import lombok.*;
import javax.persistence.Column;
@Data
public class Type{
@Column(name = "id")
private String id;
private String tyname;
public Type(String id, String tyname) {
this.id = id;
this.tyname = tyname;
}
}
1.1 工具类
package com.ketai.springbootpoiechart.util;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
public class ExportExcel {
// 导出表的标题
private String title;
// 导出表的列名
private String[] rowName;
// 导出表的数据
private List<Object[]> dataList = new ArrayList<Object[]>();
// 构造函数,传入要导出的数据
public ExportExcel(String title, String[] rowName, List<Object[]> dataList) {
this.dataList = dataList;
this.rowName = rowName;
this.title = title;
}
// 导出数据
public void export(OutputStream out) throws Exception {
try {
//1.创建一个workbook,一个workbook 对应一个Excel文件
HSSFWorkbook workbook = new HSSFWorkbook();
//2.创建 sheet excel中多个sheet组成一个excel文件 至少有一个sheet
HSSFSheet sheet = workbook.createSheet(title);
//3.在sheet中添加表头第0行
HSSFRow rowm = sheet.createRow(0);
//4.创建单元格
HSSFCell cellTitle = rowm.createCell(0);
//5.定义标题样式 和 数据样式
HSSFCellStyle columnTopStyle = this.getCol