1.导入Excel一般都会给一个Excel数据模板,模板下载请看本博客另一篇模板下载文章
2.数据库数据导入效果图
3.前台简陋图
4,需要引入mybatis、mysql、poi、io等依赖
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.21</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.13</version>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.6</version>
</dependency>
5.Excel工具类
工具类上传csdn了,有需要的同学们可以自行下载哦,关注博主公众号可以解答问题哦,公众号二维码在最下方
下载地址:https://download.csdn.net/download/royal1235/13985138
6.功能结构图:
7.controller代码
package com.ansheng.controller;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.annotation.MultipartConfig;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import com.ansheng.entity.Notice;
import com.ansheng.service.QueryTestService;
import com.ansheng.util.ExcelUtils;
@RestController
public class UploadFile {
@Autowired
private QueryTestService queryTestService;
@RequestMapping("/uploadFile")
public Map<String,Object> bathAddData(@RequestParam("file") MultipartFile file){
InputStream is = null;
Map<String,Object> map=new HashMap<String,Object>();
try {
is = file.getInputStream();
List<Object> entityList = ExcelUtils.importDataFromExcel(new Notice(),is,file.getOriginalFilename());
System.out.println(entityList.size()+"---------");
if (entityList.size() == 0){
map.put("status", "0");
map.put("msg", "导入数据不能为空");
}else if (entityList.size() >10){
map.put("status", "1");
map.put("msg", "数据条数不准大于10条");
} else {
Map<String,Object> Resmap= queryTestService.batchAddData(entityList);
// System.out.println(Resmap.get("success")+"---"+Resmap.get("data"));
int totalNum = entityList.size();
int failed = totalNum -Integer.parseInt(Resmap.get("success").toString());
map.put("status", "2");
map.put("msg", "导入成功");
map.put("success",Resmap.get("success"));
map.put("totalNum",totalNum);
map.put("failed",failed);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
is = null;
}
System.out.println(map.get("msg")+"--"+map.get("status"));
return map;
}
}
8.service代码
Map<String,Object> batchAddData(List<Object> entityList);
9.serviceImpl代码
@Override
public Map<String, Object> batchAddData(List<Object> entityList) {
// TODO Auto-generated method stub
int sum=entityList.size();
int success=0;
StringBuilder successIds = new StringBuilder("");
StringBuilder failedIds = new StringBuilder("");
List<Notice> lis=new ArrayList<Notice>();
Map<String,Object> map=new HashMap<String,Object>();
for (Object object : entityList) {
Notice notice=(Notice) object;
int i=queryTestMapper.insertDataToTable(notice);
if(i>0) {
success++;
}else {
//失败
lis.add(notice);
}
}
map.put("success",success);
map.put("data",lis);
return map;
}
10.mapper接口
int insertDataToTable(Notice notice);
11.xml
<insert id="insertDataToTable" parameterType="com.ansheng.entity.Notice">
insert into notice (id,descr,createtime,name,updatetime,delstatus) values (#{id},#{descr},#{createtime},#{name},#{updatetime},#{delstatus})
</insert>
注意:sql可以使用动态sql进行导入数据
需要工具类的先扫码关注微信公众号哦: