1、引入依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--mysql连接-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--mybatis-plus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.2</version>
</dependency>
<!--excel导入工具类-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.2</version>
</dependency>
<!--druid连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.21</version>
</dependency>
相关jar包的源码地址:mirrors / alibaba / easyexcel · GitCode
2.定义接口 用于vo和dto实体类之间数据的处理及其他操作
public interface ExcelReport {
<T> List handleData(List<T> t);
}
3.添加execl对应实体类及数据库字段对应实体类
@TableName("excel_demo")
public class ExcelDemo implements Serializable , ExcelReport {
private static final long serialVersionUID = 1L;
private String stringTitle;
private String dateTitle;
private String numTitle;
public String getStringTitle() {
return stringTitle;
}
public void setStringTitle(String stringTitle) {
this.stringTitle = stringTitle;
}
public String getDateTitle() {
return dateTitle;
}
public void setDateTitle(String dateTitle) {
this.dateTitle = dateTitle;
}
public String getNumTitle() {
return numTitle;
}
public void setNumTitle(String numTitle) {
this.numTitle = numTitle;
}
@Override
public String toString() {
return "ExcelDemo{" +
"stringTitle=" + stringTitle +
", dateTitle=" + dateTitle +
", numTitle=" + numTitle +
"}";
}
@Override
public <T> List handleData(List<T> t) {
List<Object> result = new ArrayList<>();
//如果需要execl对应字段和数据库字段之间转换可以在这里处理
t.forEach((x)->{
ExcelDemoDto excelDemoDto = new ExcelDemoDto();
ExcelDemo excelDemo = (ExcelDemo)x;
//将里面的日期由字符串转为日期格式
//这里转换字符串只是举个例子 实际上日期格式可以用实体对象直接读取到不用转换
String dateTitle1 = excelDemo.getDateTitle();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
try {
Date parse = simpleDateFormat.parse(dateTitle1);
excelDemoDto.setDateTitle(parse);
} catch (ParseException e) {
throw new RuntimeException(e);
}
excelDemoDto.setStringTitle(excelDemo.getStringTitle());
excelDemoDto.setNumTitle(excelDemo.getNumTitle());
result.add(excelDemoDto);
});
return result;
}
}
public class ExcelDemoDto implements Serializable {
private static final long serialVersionUID = 1L;
private String stringTitle;
private Date dateTitle;
private String numTitle;
public String getStringTitle() {
return stringTitle;
}
public void setStringTitle(String stringTitle) {
this.stringTitle = stringTitle;
}
public Date getDateTitle() {
return dateTitle;
}
public void setDateTitle(Date dateTitle) {
this.dateTitle = dateTitle;
}
public String getNumTitle() {
return numTitle;
}
public void setNumTitle(String numTitle) {
this.numTitle = numTitle;
}
@Override
public String toString() {
return "ExcelDemo{" +
"stringTitle=" + stringTitle +
", dateTitle=" + dateTitle +
", numTitle=" + numTitle +
"}";
}
}
4.代码从excel导入到vo经过数据处理成dto最后存储到数据库中
@Service
@Slf4j
public class ExcelDemoServiceImpl extends ServiceImpl<ExcelDemoMapper, ExcelDemo> implements IExcelDemoService {
/**
* @param t excel对应的实体类
* @param p 保存excel数据库实体类对应的service(为了调用保存的方法保存数据)
* @param fileName excel文件存储路径
* @param <T>
* @param <P>
*/
@Override
public <T extends ExcelReport, P extends IService> void export(T t, P p, String fileName) {
// 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭2
EasyExcel.read(fileName, t.getClass(), new ReadListener<T>() {
//单次缓存的数据
public static final int BATCH_COUNT = 100;
private List<T> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
@Override
public void invoke(T data, AnalysisContext context) {
cachedDataList.add(data);
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
}
//加上存储数据库
private void saveData() {
log.info("{}条数据,开始存储数据库!", cachedDataList.size());
p.saveBatch(t.handleData(cachedDataList));
log.info("存储数据库成功!");
}
}).sheet().doRead();
}
}
5.调用接口导入数据
@RestController
@RequestMapping("/demo")
@AllArgsConstructor
public class ExcelDemoController {
private final IExcelDemoService excelDemoService;
@GetMapping("/export")
public String export(){
excelDemoService.export(new ExcelDemo(),excelDemoService,"D:\\myproject\\excelDemo\\src\\main\\resources\\demo.xlsx");
return "ok";
}
}
6.excel数据
字符串标题 | 日期标题 | 数字标题 |
字符串0 | 2020/1/1 1:01 | 1 |
字符串1 | 2020/1/2 1:01 | 2 |
字符串2 | 2020/1/3 1:01 | 3 |
字符串3 | 2020/1/4 1:01 | 4 |
字符串4 | 2020/1/5 1:01 | 5 |
字符串5 | 2020/1/6 1:01 | 6 |
字符串6 | 2020/1/7 1:01 | 7 |
字符串7 | 2020/1/8 1:01 | 8 |
字符串8 | 2020/1/9 1:01 | 9 |
字符串9 | 2020/1/10 1:01 | 10 |
7.最后成功导入数据库
发现个问题:数据库里保存的时间和excel里的实际时间不一致
通过排查发现数据库连接设置时区没有使用中国上海的时间。
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
# 之前的
# url: jdbc:mysql://localhost:3306/cisql?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&serverTimezone=UTC
# 改之后的
url: jdbc:mysql://localhost:3306/cisql?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&serverTimezone=Asia/Shanghai
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
改完之后发现时间和excel里的一致了