maven depends on jar
<!--Import the package needed for the form-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>Entity class
@Entity
@Table(name = "subject")
public class Subject implements Serializable {
public Subject() {
}
public Subject(String curriculum, String content, String type, String degree, String name, String analysis, String parameter, String correct, String error) {
this.curriculum = curriculum;
this.content = content;
this.type = type;
this.degree = degree;
this.name = name;
this.analysis = analysis;
this.parameter = parameter;
this.correct = correct;
this.error = error;
}
@GeneratedValue(strategy = GenerationType.IDENTITY)//JPA automatically selects the appropriate generation strategy
@Column(name="id")//Column name, the default is the attribute name, you can specify the column name through the name attribute
@Id
private Integer id;
//Belonging to the course
@Column(name = "curriculum")
private String curriculum;
//Subject content
@Column(name = "content")
private String content;
//Question type
@Column(name = "type")
private String type;
//Degree of difficulty
@Column(name = "degree")
private String degree;
//Knowledge point name
@Column(name = "name")
private String name;
//parse
@Column(name = "analysis")
private String analysis;
//Formula question parameters
@Column(name = "parameter")
private String parameter;
//correct answer
@Column(name = "correct")
private String correct;
//Wrong answer
@Column(name = "error")
private String error;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getCurriculum() {
return curriculum;
}
public void setCurriculum(String curriculum) {
this.curriculum = curriculum;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getDegree() {
return degree;
}
public void setDegree(String degree) {
this.degree = degree;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAnalysis() {
return analysis;
}
public void setAnalysis(String analysis) {
this.analysis = analysis;
}
public String getParameter() {
return parameter;
}
public void setParameter(String parameter) {
this.parameter = parameter;
}
public String getCorrect() {
return correct;
}
public void setCorrect(String correct) {
this.correct = correct;
}
public String getError() {
return error;
}
public void setError(String error) {
this.error = error;
}
@Override
public boolean equals(Object that) {
if (this == that) {
return true;
}
if (that == null) {
return false;
}
if (getClass() != that.getClass()) {
return false;
}
Subject other = (Subject) that;
return (this.getId() == null? other.getId() == null: this.getId().equals(other.getId()))
&& (this.getName() == null? other.getName() == null: this.getName().equals(other.getName()))
&& (this.getAnalysis() == null? other.getAnalysis() == null: this.getAnalysis().equals(other.getAnalysis()))
&& (this.getContent() == null? other.getContent() == null: this.getContent().equals(other.getContent()))
&& (this.getCorrect() == null? other.getCorrect() == null: this.getCorrect().equals(other.getCorrect()))
&& (this.getCurriculum() == null? other.getCurriculum() == null: this.getCurriculum().equals(other.getCurriculum()))
&& (this.getDegree()== null? other.getDegree() == null: this.getDegree().equals(other.getDegree()))
&& (this.getError() == null? other.getError() == null: this.getError().equals(other.getError()))
&& (this.getParameter() == null? other.getParameter() == null: this.getParameter().equals(other.getParameter()))
&& (this.getType() == null? other.getType() == null: this.getType().equals(other.getType()));
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((getId() == null)? 0: getId().hashCode());
result = prime * result + ((getName() == null)? 0: getName().hashCode());
result = prime * result + ((getAnalysis() == null)? 0: getAnalysis().hashCode());
result = prime * result + ((getContent() == null)? 0: getContent().hashCode());
result = prime * result + ((getCorrect() == null)? 0: getCorrect().hashCode());
result = prime * result + ((getDegree() == null)? 0: getDegree().hashCode());
result = prime * result + ((getError() == null)? 0: getError().hashCode());
result = prime * result + ((getParameter() == null)? 0: getParameter().hashCode());
result = prime * result + ((getType() == null)? 0: getType().hashCode());
result = prime * result + ((getCurriculum() == null)? 0: getCurriculum().hashCode());
return result;
}
}DAO layer
@Repository
public interface SubjectDao {
@Insert("insert into subject(name,content,curriculum,type,degree,analysis,parameter,correct,error) values(#{name},#{content},#{curriculum},#{type},#{ degree},#{analysis},#{parameter},#{correct},#{error})")
void addUser(Subject subject);
@Update("update subject set name=#{name},content=#{content},curriculum=#{curriculum},type=#{type},degree=#{degree},analysis=#{analysis},parameter =#{parameter},correct=#{correct},error=#{error} where id=#{id}")
int updateUserByName(Subject subject);
@Select("select * from subject where content=#{content}")
Subject selectByName(@Param("content") String content);
}service layer
@Override
public String batchImport(String fileName, MultipartFile file) throws Exception {
String notNull = "0";
List<Subject> userList = new ArrayList<Subject>();
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
return "The format of the uploaded file is incorrect";
}
boolean isExcel2003 = true;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
InputStream is = file.getInputStream();
Workbook wb = null;
if (isExcel2003) {
wb = new HSSFWorkbook(is);
} else {
wb = new XSSFWorkbook(is);
}
Sheet sheet = wb.getSheetAt(0);
if(sheet!=null){
notNull = "1";
}
Subject subject;
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
if (row == null){
continue;
}
/* if( row.getCell(0).getCellType() !=1){
throw new Exception("Import failed (line "+(r+1)+", please set the name in text format)");
}*/
String curriculum = row.getCell(0).getStringCellValue();
if(curriculum == null || curriculum.isEmpty()){
return "Import failed (the "+(r+1)+" column, the course did not fill in)";
}
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
String content = row.getCell(1).getStringCellValue();
if(content==null || content.isEmpty()){
return "Import failed (the "+(r+1)+" column, the title is not filled in)";
}
String type = row.getCell(2).getStringCellValue();
if(type==null|| type.isEmpty()){
return "Import failed (Column "+(r+1)+", title type)";
}
String degree = row.getCell(3).getStringCellValue();
if(degree==null|| degree.isEmpty()){
return "Import failed (the "+(r+1)+" column, the degree of difficulty is not filled in)";
}
String name = row.getCell(4).getStringCellValue();
if(name==null|| name.isEmpty()){
return "Import failed (the "+(r+1)+" column, the knowledge point name is not filled in)";
}
String analysis = row.getCell(5).getStringCellValue();
if(analysis==null|| analysis.isEmpty()){
return "Import failed (the "+(r+1)+" column, the analysis did not fill in)";
}
String parameter = row.getCell(6).getStringCellValue();
if(parameter==null|| parameter.isEmpty()){
return "Import failed (the "+(r+1)+" column, the formula question parameter is not filled in)";
}
String correct = row.getCell(7).getStringCellValue();
if(correct==null|| correct.isEmpty()){
return "Import failed (the "+(r+1)+" column, the correct answer is not filled in)";
}
String error = row.getCell(8).getStringCellValue();
if(error==null|| error.isEmpty()){
return "Import failed (the "+(r+1)+" column, the wrong answer is not filled in)";
}
subject=new Subject();
subject.setAnalysis(analysis);
subject.setDegree(degree);
subject.setContent(content);
subject.setCorrect(correct);
subject.setCurriculum(curriculum);
subject.setError(error);
subject.setName(name);
subject.setParameter(parameter);
subject.setType(type);
userList.add(subject);
}
for (Subject subject1: userList) {
String content = subject1.getContent();
Subject cnt = subjectDao.selectByName(content);
if (cnt == null ||null == cnt.getContent()) {
subjectDao.addUser(subject1);
} else {
subject1.setId(cnt.getId());
subjectDao.updateUserByName(subject1);
}
}
return notNull;
}controller layer
@RequestMapping(value = "import")
@ResponseBody
public String addUser(@RequestParam(value = "file",required=false) MultipartFile file) {
if(file.isEmpty()){
return null;
}
String result = null;
String fileName = file.getOriginalFilename();
try {
result = subjectService.batchImport(fileName, file);
} catch (Exception e) {
e.printStackTrace();
}
return result;
}