Springboot整合Swagger,easyExcel的表格导入
这只是简单的入门阶段,我会在后续发布跟更难的表格导入
后期迭代更新:
- 一个表的多个sheet的导入
- 多个表的导入
- 表中多种数据类型的导入
- 我遇到的表格导入时的各种问题
准备阶段:
建表:
建表格:
创建一个Springboot项目:(自行解决)
干货开始:
1. 导依赖
<!-- easyexcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.0-beta4</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.2</version>
</dependency>
<!--fastjson-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.58</version>
</dependency>
<!-- MYSQL包 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--mybatis-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<!--使用durid连接池的依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>
2.创建实体类
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
//使用此注解,不用get,set
@Data
public class Book {
@ExcelProperty(index = 0)
private Integer id;
@ExcelProperty(index = 1)
private String name;
@ExcelProperty(index = 2)
private String price;
@ExcelProperty(index = 3)
private String author;
}
3.创建Swagger配置类
@Configuration
@EnableSwagger2
public class SwaggerConfiguration {
/**
* swagger2的配置文件,这里可以配置swagger2的一些基本的内容,比如扫描的包等等
*/
@Bean
public Docket createRestfulApi(){
return new Docket(DocumentationType.SWAGGER_2)
.pathMapping("/")
.select()
.apis(RequestHandlerSelectors.basePackage("com.lance.learn.springbootswagger.controller")) //暴露接口地址的包路径
.paths(PathSelectors.any())
.build();
}
/**
* 构建 api文档的详细信息函数,注意这里的注解引用的是哪个
*/
private ApiInfo apiInfo(){
return new ApiInfoBuilder()
//页面标题
.title("Spring Boot 测试使用 Swagger2 构建RESTful API")
.description("简单优雅的restful风格")
.termsOfServiceUrl("")
.version("1.0")
.build();
}
}
4.创建监听器
// 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener<Object> {
private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 5;
List<Object> list = new ArrayList<Object>();
private DemoService demoService;
public DemoDataListener(DemoService demoService) {
this.demoService = demoService;
}
/**
* 这个每一条数据解析都会来调用
* @param data
*/
@Override
public void invoke(Object data, AnalysisContext context) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
list.add(data);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
LOGGER.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
LOGGER.info("{}条数据,开始存储数据库!", list.size());
demoService.saveBook(list);
LOGGER.info("存储数据库成功!");
}
}
5.配置文件:application.yml
server:
port: 8765
servlet:
context-path: /
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/ehr?useUnicode=truecte&chararEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&autoReconnect=true&allowMultiQueries=true
username: root
password: root
# 使用druid数据源
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
mybatis:
mapper-locations: classpath:mapper/*.xml
configuration:
map-underscore-to-camel-case: true
6.创建controller层
@RestController
@RequestMapping(value = "api/import")
@Api(value = "导入Excel类", tags = {"导入Excel类"})
public class BookController {
@Autowired
private DemoService demoService;
@RequestMapping(value = "/importExcel", method = RequestMethod.POST)
@ApiOperation(value = "导入信息表", notes = "导入信息表")
@ResponseBody
public String getBook(MultipartFile file) throws IOException {
EasyExcel.read(file.getInputStream(), Book.class,new DemoDataListener(demoService)).sheet().headRowNumber(1).doRead();
return "success";
}
}
7.创建service层
接口:
public interface DemoService {
void saveBook(List<Object> list);
}
实现类:
@Service
public class DemoServiceImpl implements DemoService {
@Resource
private DemoBookDao demoBookDao;
@Override
public void saveBook(List<Object> list) {
for (Object o : list) {
if (o instanceof Book) {
Book e = (Book) o;
this.demoBookDao.saveBook(e);
}
}
}
}
8.创建DAO层
接口:
@Repository
public interface DemoBookDao {
void saveBook(Book e);
}
mapper映射文件:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.lance.learn.springbootswagger.dao.DemoBookDao">
<insert id="saveBook" >
insert into book(id,name,price,author) values(
#{id},#{name},#{price},#{author}
)
</insert>
</mapper>
8.访问路径:
http://localhost:8765/swagger-ui.html
易出现的问题:
1.没有扫描DAO包(在启动类上添加注解)@MapperScan(“com.lance.learn.springbootswagger.dao”)
@SpringBootApplication
@EnableSwagger2
@MapperScan("com.lance.learn.springbootswagger.dao")
public class SpringbootSwaggerApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootSwaggerApplication.class, args);
}
}
2.三层是否添加了注解
@RestController
@Service
@Repository
3.自己本地的数据库名和表名有没有改
4.实体类中的索引从0开始,实体类与表格的顺序是否保持一致
5.controller层(sheet()和 headRowNumber()的使用 )
EasyExcel.read(file.getInputStream(), Book.class,newDemoDataListener(demoService)).sheet().headRowNumber(1).doRead();