前言
本文主要记录如何使用excel批量录入,并且实现录入时选取其他表字段。
一、步骤
1.依赖
//导入excel需要的依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
//mybatis依赖
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.1</version>
</dependency>
2.实体类PiL
代码如下(示例):
@Data
@NoArgsConstructor
@AllArgsConstructor
public class PiL {
private String stuid;
private String projecttype;
private String match;
private String grade;
private String num;
private String applypeople;
}
3.Mapper-sql语句:PiLMapper
<?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.zhao.secondclass.mapper.PiLMapper">
<insert id="addPiL" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
insert into score (
stuid,
projecttype,
`match`,
grade,
num,
applypeople,
clas,
college,
createtime,
flag
)
(
select
id,
<if test="item.projecttype != null">
#{item.projecttype}
</if>,
<if test="item.match != null">
#{item.match}
</if>,
<if test="item.grade != null">
#{item.grade}
</if>,
<if test="item.num != null">
#{item.num}
</if>,
<if test="item.applypeople != null">
#{item.applypeople}
</if>,
clas,
college,
NOW(),
'0'
from student where
<if test="item.stuid != null">
id = #{item.stuid}
</if>
)
</foreach>
</insert>
</mapper>
mapper接口:
@Mapper
public interface PiLMapper {
// 批量添加用户数据
public int addPiL(List<PiL> PiLList);
}
4.工具类:ImportExcelUtils
@Component
public class ImportExcelUtils {
// 将表格中的数据添加到List集合中
public static List<PiL> upload(String fileName, InputStream is) throws Exception{
Workbook workbook = getWorkbook(fileName,is);
List<PiL> piLList = new ArrayList<>();
int number = workbook.getNumberOfSheets(); // 获取sheet值
for (int i = 0; i < number; i++) {
Sheet sheet = workbook.getSheetAt(i); // 获取表格页码
if (sheet != null){
int rowNum = sheet.getLastRowNum(); // 获取该页表共有多少行
for (int j = 1; j <= rowNum; j++) { // 一般来说第一行是标题,所以第二行开始读取
Row row = sheet.getRow(j); // 获取表格行
PiL piL = new PiL();
row.getCell(0).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
piL.setStuid(row.getCell(0).getStringCellValue());
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
piL.setProjecttype(row.getCell(1).getStringCellValue());
row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
piL.setMatch(row.getCell(2).getStringCellValue());
row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
piL.setGrade(row.getCell(3).getStringCellValue());
row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
piL.setNum(row.getCell(4).getStringCellValue());
row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
piL.setApplypeople(row.getCell(5).getStringCellValue());
piLList.add(piL);
}
}
}
System.out.println(piLList.toString());
return piLList;
}
// 判断传入的文件是哪种类型的excel文件
public static Workbook getWorkbook(String fileName,InputStream is) throws Exception{
Workbook workbook = null;
String name = fileName.substring(fileName.lastIndexOf("."));
System.out.println(name);
if (".xls".equals(name)){
workbook = new HSSFWorkbook(is);
}else if (".xlsx".equals(name)){
workbook = new XSSFWorkbook(is);
}else {
throw new Exception(" 请上传.xls/.xlsx格式文件!");
}
return workbook;
}
}
5.sercive层:
service接口:PiLService
public interface PiLService {
// 批量添加用户数据
public boolean addPiL(String fileName, InputStream is);
}
实现类:PiLServiceImpl
@Service
public class PiLServiceImpl implements PiLService {
@Autowired
private PiLMapper piLMapper;
@Override
public boolean addPiL(String fileName, InputStream is) {
try {
List<PiL> PiLList = ImportExcelUtils.upload(fileName, is);
int i = piLMapper.addPiL(PiLList);
if (i>1){
return true;
}
return false;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
}
6.controller
@PostMapping("/piLiang")
public String piLiang( MultipartFile excelFile) throws IOException {
String str="";
Map<String,String> map = new HashMap<>();
if (excelFile.isEmpty()){
str="文件夹为空,重新上传";
return str;
}else {
String fileName = excelFile.getOriginalFilename();
InputStream is = excelFile.getInputStream();
if (piLService.addPiL(fileName,is)){
return "数据添加成功";
}else {
return "数据添加失败,请重新添加";
}
}
}
7.前端页面
<button style="float: right" onclick="upload()">录入</button>
<input style="float: right" multiple="multiple" type="file" id="uploadFile">
<script>
function upload(){
var formData = new FormData(); //创建一个formData对象实例
var excelFile = $("#uploadFile").get(0).files[0];
formData.append("excelFile",excelFile)
$.ajax({
url: 'teacher/piLiang',
data: formData,
type: 'post',
contentType:false, // 必须false才会自动加上正确的Content-Type
processData: false, // 必须false才会避开jQuery对 formdata 的默认处理,XMLHttpRequest会对 formdata 进行正确的处理
success: function (resp){
console.log(resp);
// 后续的操作
}
})
}
</script>
三:遇到的问题
1、sql语句
一种是我上面写的语句,用foreach将整个语句包括,separator使用“;”分割,会大大降低速率,这种方式中间一定不能加values,加上values就会爆错。
另一种是foreach包括数据部分,只循环数据,这种批量插入方式是推荐的,但选取其他表的数据不能使用这种方式来进行批量插入。
2、静态代码块问题,需要将工具类所有方法设为静态的。