1、功能介绍
spring boot项目 定时扫描指定路径下的excel文件,把excel文件里的数据导入数据库中。
该功能不需要前端页面,不用controller
一、实现
1、先看需求excel文件字段类型(我挑了个简单的)
三个都是文本格式
2、开始建项目(spring boot)
依赖(pom.xml)
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.21</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.21</version>
</dependency>
yml加(就是excel文件的地址)
excel:
tongji: C:/Excel/数据动态导入.xlsx
service
public interface BizExcellService {
public ReturnInfoUtil getExcelInfo(String fileName, MultipartFile file) throws Exception;
}
serviceipml
package com.puboot.module.admin.service.impl;
import com.puboot.common.util.ReturnInfoUtil;
import com.puboot.common.util.ReturnUtil;
import com.puboot.module.admin.mapper.BizImportMapper;
import com.puboot.module.admin.mapper.BizStatisticalMapper;
import com.puboot.module.admin.model.BizImportant;
import com.puboot.module.admin.model.BizStatistical;
import com.puboot.module.admin.service.BizExcellService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
@Service
public class BizExcellServiceImpl implements BizExcellService {
@Autowired
BizStatisticalMapper bizStatisticalMapper;
@Override
@Transactional
public ReturnInfoUtil getExcelInfo