maven 依赖jar
代码语言：javascript复制<!--导入表格需要的包-->
<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>实体类
代码语言：javascript复制@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自动选择合适的生成策略
@Column(name="id") //列名,默认为属性名,可通过name属性指定列名
@Id
private Integer id;
//所属课程
@Column(name = "curriculum")
private String curriculum;
//题目内容
@Column(name = "content")
private String content;
//题目类型
@Column(name = "type")
private String type;
//难易程度
@Column(name = "degree")
private String degree;
//知识点名称
@Column(name = "name")
private String name;
// 解析
@Column(name = "analysis")
private String analysis;
//公式题参数
@Column(name = "parameter")
private String parameter;
//正确答案
@Column(name = "correct")
private String correct;
//错误答案
@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层
代码语言：javascript复制@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层
代码语言：javascript复制 @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 "上传文件格式不正确";
}
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("导入失败(第" (r 1) "行,姓名请设为文本格式)");
}*/
String curriculum = row.getCell(0).getStringCellValue();
if(curriculum == null || curriculum.isEmpty()){
return "导入失败(第" (r 1) "列,所属课程没填写)";
}
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
String content = row.getCell(1).getStringCellValue();
if(content==null || content.isEmpty()){
return "导入失败(第" (r 1) "列,题目未填写)";
}
String type = row.getCell(2).getStringCellValue();
if(type==null|| type.isEmpty()){
return "导入失败(第" (r 1) "列,题目类型)";
}
String degree = row.getCell(3).getStringCellValue();
if(degree==null|| degree.isEmpty()){
return "导入失败(第" (r 1) "列,难易程度没填写)";
}
String name = row.getCell(4).getStringCellValue();
if(name==null|| name.isEmpty()){
return "导入失败(第" (r 1) "列,知识点名称没填写)";
}
String analysis = row.getCell(5).getStringCellValue();
if(analysis==null|| analysis.isEmpty()){
return "导入失败(第" (r 1) "列,解析没填写)";
}
String parameter = row.getCell(6).getStringCellValue();
if(parameter==null|| parameter.isEmpty()){
return "导入失败(第" (r 1) "列,公式题参数没填写)";
}
String correct = row.getCell(7).getStringCellValue();
if(correct==null|| correct.isEmpty()){
return "导入失败(第" (r 1) "列,正确答案没填写)";
}
String error = row.getCell(8).getStringCellValue();
if(error==null|| error.isEmpty()){
return "导入失败(第" (r 1) "列,错误答案没填写)";
}
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层
代码语言：javascript复制 @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;
}
