【Springboot+mybatis】 解析Excel并批量导入到数据库
置顶 2018年01月16日 20:05:52 冉野丶 阅读数:4060 标签: excel导入数据库 文件上传 excel解析 更多
个人分类: POI 工作问题归纳
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/hanerer1314/article/details/79077663
一个很简单的需求,从后台中选取一个的Excel文件,并直接导入到数据库,然后根据导入的表查询个人信息,由于表的数据内容还挺多,有好几万条,所幸是单表。
一个很愚蠢的做法是每解析一个Row,然后构建一个新的对象,再把每个cell的值赋给该对象,直到循环结束,再通过mabatis的insert语句入库,这样很low,有没有???但是数据也能跑的过去,能完成入库,不过两万条数据,连解析带入库一共花了将近20分钟,天啦噜,这样客户会炸毛的。没办法,还得修改,看了很多办法,一种最直接的就是把解析和入库分开,然后重新构建sql批量插入,因为mybatis用的是generator插件自动生成的代码,想把自己构建的mapper接口内容放在mybatis自己的目录下,但是每次重新执行插件时候,自己新写的接口文件就会被清除掉,无奈之举,只能重新构建一个文件夹(excelmapper)来存储我的查询接口,这才逃过一劫,当我进行测试时候又报错了,无法扫描到excelmapper下面的接口内容,尝试在单元测试类上面配置@componentScan("com.net.excelmapper")注解,如果启动类在根包下面,则你可以在该类上添加@ComponentScan注解而不需要添加任何参数,Spring Boot会在根包下面搜索注有@Component, @Service, @Repository, @Controller注解的所有类,并将他们注册为Spring Beans,否则,你需要在@ComponentScan注解上定义basePackages或者其他属性。最后的解决办法是在启动类上添加了对应excelmapper文件夹下的扫描路径,@MapperScan({"com.neo.mapper","com.neo.excelmapper"}),合法化测试终于通过了,可是还有一个问题,上传超过了限制的默认值,只好在配置文件添加了配置项:
spring.http.multipart.max-file-size = 2048KB //支持文件上传最大的限制
spring.http.multipart.max-request-size = 10240kB //最大支持请求
spring.http.multipart.enabled = true#默认支持文件上传。
spring.http.multipart.file-size-threshold = 0#支持文件写入磁盘。
spring.http.multipart.location =#上传文件的临时目录
因为用的是表单进行提交,<input type =“file”>这种情况下在form标签下需要特殊的指定属性文件为formdata文件,例如:<form role =“form”action =“/ importExcel”method =“post” enctype =“multipart / form-data”>,这下终于可以完成数据导入了,不料,两万条数据果然还是花了20分钟,修改意见也就上面提到的,分开操作,先把解析的数据封装到一个对象上,并且存储到列表集合中,在从几何中批量插入。
excelmapper接口:
公共接口ExcelMapper {
void batchInsert(List <Tbagent> tbagentList);
}
excelmapper.xml中主要的SQL语句,也就是执行批量操作的SQL,
<insert id =“batchInsert”parameterType =“java.util.List”>
INSERT INTO代理(job_number,部门,地区,用户名,idcard,company_rankings,department_rankings,region_rankings,
distance_first_company,distance_first_department,distance_first_region)
VALUES
<foreach collection =“list”item =“item”index =“index”separator =“,”>
(#{item.job_number},{#} item.department,#{item.region},{#} item.username,#{item.idcard},{#} item.company_rankings,
#{item.department_rankings},{#} item.region_rankings,#{item.distance_first_company},{#} item.distance_first_department,#{item.distance_first_region})
</的foreach>
</插入>
主要的服务层:
/ **
*由管理员于2018/1/11创建。
* /
@服务
公共类IimportServiceImpl实现IimportService {
private final static String XLS =“xls”;
public static final String XLSX =“xlsx”;
私人最终静态记录器记录器= LoggerFactory.getLogger(IimportServiceImpl.class);
@Autowired私人TbagentMapper tbagentMapper;
@Autowired私人SqlSessionTemplate sqlSessionTemplate;
@Autowired私有ExcelMapper excelMapper;
@覆盖
public Integer importExcel(MultipartFile myFile){
// 1。使用HSSFWorkbook打开或者创建“Excel对象”
// 2。用HSSFWorkbook返回对象或者创建片对象
// 3。用片材返回行对象,用行对象得到细胞对象
// 4。对细胞对象进行读写
List <Tbagent> tbagents = new ArrayList <>();
工作簿工作簿=空;
String fileName = myFile.getOriginalFilename(); //获取文件名
logger.info( “【文件名】{}”,文件名);
if(fileName.endsWith(XLS))
{
尝试{
workbook = new HSSFWorkbook(myFile.getInputStream()); // 2003版本
} catch(IOException e){
e.printStackTrace();
}
} else if(fileName.endsWith(XLSX)){
尝试{
workbook = new XSSFWorkbook(myFile.getInputStream()); // 2007版本
} catch(IOException e){
e.printStackTrace();
}
}其他{
抛出新的LianjiaException(ResultEnum.FILE_IS_NOT_EXCEL); //文件不是Excel文件
}
工作表=工作簿.getSheet(“sheet1”);
int rows = sheet.getLastRowNum();
logger.info( “【行】{}”,行);
if(rows == 0){
抛出new LianjiaException(ResultEnum.DATA_IS_NULL); //数据为空请填写数据
}
long startTime = System.currentTimeMillis();
for(int i = 1; i <= rows + 1; i ++){
行排= sheet.getRow(i);
if(row!= null){
Tbagent tbagent = new Tbagent();
//部门
String department = getCellValue(row.getCell(0));
tbagent.setDepartment(部门);
//用户姓名
String username = getCellValue(row.getCell(1));
tbagent.setUsername(用户名);
//工号
String jobNumer = getCellValue(row.getCell(2));
如果(!StringUtils.isEmpty(jobNumer)){
Integer job_number = Integer.parseInt(jobNumer);
tbagent.setJob_number(Integer.valueOf(jobNumer));
}
//身份证后六位
String idcard = getCellValue(row.getCell(3));
tbagent.setIdcard(idcard);
//公司排名
String companyRankings = getCellValue(row.getCell(4));
如果(!StringUtils.isEmpty(companyRankings)){
整数new_companyRankings = Integer.parseInt(companyRankings);
tbagent.setCompany_rankings(new_companyRankings);
}
//事业部排名
String departmenRanks = getCellValue(row.getCell(5));
如果(!StringUtils.isEmpty(departmenRanks)){
整数new_departmentRanks = Integer.parseInt(departmenRanks);
tbagent.setDepartment_rankings(new_departmentRanks);
}
//大区排名
String region_Ranks = getCellValue(row.getCell(6));
如果(!StringUtils.isEmpty(region_Ranks)){
整数new_region_Rankings = Integer.parseInt(region_Ranks);
//logger.info( “【大区排名】{}”,new_region_Rankings);
tbagent.setRegion_rankings(new_region_Rankings);
} //距离公司第一名差距
String distance_first_company = getCellValue(row.getCell(7));
如果(!StringUtils.isEmpty(distance_first_company)){
long new_distance_first_company = Long.parseLong(distance_first_company);
tbagent.setDistance_first_company(new_distance_first_company);
}
//距离事业部第一名差距
String distance_first_deparment = getCellValue(row.getCell(8));
如果(!StringUtils.isEmpty(distance_first_deparment)){
Long new_distance_first_deparment = Long.parseLong(distance_first_deparment);
tbagent.setDistance_first_department(new_distance_first_deparment);
}
//距离大区第一名差距
String distance_first_region = getCellValue(row.getCell(9));
如果(!StringUtils.isEmpty(distance_first_region)){
Long new_distance_first_region = Long.parseLong(distance_first_region);
tbagent.setDistance_first_region(new_distance_first_region);
}
//System.out.println(JSON.toJSON(tbagent));
//tbagentMapper.insert(tbagent);
tbagents.add(tbagent);
//logger.info( “插入数据完成”);
}
}
excelMapper.batchInsert(tbagents); //批量插入五秒完成
long endTime = System.currentTimeMillis();
long totaltime = endTime - startTime;
logger.info( “【消耗时间为】{}”,TOTALTIME); //将近两万条数据3秒解析完成
logger.info( “【第一条数据为】{}”,JSON.toJSON(tbagents.get(0)));
返回行;
}
public String getCellValue(Cell cell){
String value =“”;
if(cell!= null){
开关(cell.getCellType()){
case HSSFCell.CELL_TYPE_NUMERIC://数字
value = cell.getNumericCellValue()+“”;
如果(HSSFDateUtil.isCellDateFormatted(小区)){
Date date = cell.getDateCellValue();
if(date!= null){
value = new SimpleDateFormat(“yyyy-MM-dd”)。format(date); //日期格式化
}其他{
value =“”;
}
} else {
//解析cell时候数字类型默认是double类型的但是想要获取整数类型需要格式化很重要很重要
value = new DecimalFormat(“0”)。format(cell.getNumericCellValue());
}
打破;
大小写HSSFCell.CELL_TYPE_STRING://字符串
value = cell.getStringCellValue();
打破;
大小写HSSFCell.CELL_TYPE_BOOLEAN://布尔类型
value = cell.getBooleanCellValue()+“”;
打破;
大小写HSSFCell.CELL_TYPE_BLANK://空值
value =“”;
打破;
case HSSFCell.CELL_TYPE_ERROR://错误类型
value =“非法字符”;
打破;
默认:
value =“未知类型”;
打破;
}
}
返回value.trim();
}
}
部分效果图:
登录效果图如上。
不太完美的地方就是没有一个进度条给客户看,后期需要做处理。清空数据用的是MySQL的的截断语句,直接清空所有的数据。然后让用户在导入到数据库。
源码地址 importexcel 分支