Maven
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.11</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.11</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.11</version>
</dependency>
代码
package com.xinyunfu.service.impl;
import com.xinyunfu.aop.CustomException;
import com.xinyunfu.aop.ExecptionEnum;
import com.xinyunfu.model.Subject;
import com.xinyunfu.mapper.SubjectMapper;
import com.xinyunfu.service.ISubjectService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
@Service
public class SubjectServiceImpl extends ServiceImpl<SubjectMapper, Subject> implements ISubjectService {
@Autowired
private SubjectMapper subjectMapper;
@Override
public int[] getSubject() {
int count = subjectMapper.count();
int[] array = new int[6];
for(int i = 0; i < 6; i++){
array[i] = (int)(Math.random() * (count - 1) + 1);
for(int j = 0; j < i;j++){
if(array[i] == array[j]){
i -= 1;
break;
}
}
}
return array;
}
@Override
public boolean inputQuestionBank(String currentUserId, Integer type, MultipartFile file, String fileName) throws IOException {
List<Subject> list = new ArrayList<>();
InputStream is = file.getInputStream();
Workbook wb = null;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
wb = new XSSFWorkbook(is);
}else{
wb = new HSSFWorkbook(is);
}
Sheet sheet = wb.getSheetAt(0);
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
Subject sub = new Subject();
for (int c = 1; c < 7; c++) {
Cell cell = row.getCell(c);
if(null != cell){
cell.setCellType(Cell.CELL_TYPE_STRING);
String v = cell.getStringCellValue();
if(StringUtils.isNotEmpty(v)){
if(c == 1){
sub.setSubjectTitle(v);
}else if( c == 2){
sub.setSubjectOption(v);
}else if( c == 3){
sub.setSubjectOption(sub.getSubjectOption()+"#"+v);
}else if( c == 4){
sub.setSubjectOption(sub.getSubjectOption()+"#"+v);
}else if( c == 5){
sub.setSubjectOption(sub.getSubjectOption()+"#"+v);
}else if( c == 6){
sub.setSubjectAnswer(v);
}
}
}
}
sub.setSubjectType(type+"");
sub.setCreatedBy(currentUserId);
sub.setUpdatedBy(currentUserId);
list.add(sub);
}
if(!super.saveBatch(list))
throw new CustomException(ExecptionEnum.ERROR_SAVE_FAIL);
return true;
}
}