由于毕业设计需要用到SSM框架下使用Ajax上传xls文件,注意文件必须是xls,现在实现了这个,如果想要能上传xlsx,可以自己实现。
数据库在此就不贴出,自己可以根据自己需要设置数据库
使用Maven管理jar包之间的依赖关系,所需jar包如下:
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3</version>
</dependency>
现将工具类代码贴出
Msg.java
package com.dy.bean;
import java.util.HashMap;
import java.util.Map;
/**
* 通用返回信息类
*
* @author dingye
*
*/
public class Msg {
private int code;// 状态码100-成功,200-失败
private String msg;// 状态信息
private Map<String, Object> extend = new HashMap<String, Object>();// 返回对象信息
public static Msg success() {
Msg result = new Msg();
result.setCode(100);
result.setMsg("处理成功");
return result;
}
public static Msg fail() {
Msg result = new Msg();
result.setCode(200);
result.setMsg("处理失败");
return result;
}
public Msg add(String key, Object value) {
this.getExtend().put(key, value);
return this;
}
public int getCode() {
return code;
}
public void setCode(int code) {
this.code = code;
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
public Map<String, Object> getExtend() {
return extend;
}
public void setExtend(Map<String, Object> extend) {
this.extend = extend;
}
}
第一步:我是使用模态框作为页面,下面是代码其中需要注意的是form表单中必须写enctype="multipart/form-data"这句:
<!-- 文件上传-->
<div class="modal fade" id="stuImportModal" tabindex="-1" role="dialog"
aria-labelledby="myModalLabel">
<div class="modal-dialog" role="document">
<div class="modal-content">
<form enctype="multipart/form-data" id="uploadform">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal"
aria-label="Close">
<span aria-hidden="true">×</span>
</button>
<h4 class="modal-title" id="myModalLabel">请选择Execl文件</h4>
</div>
<div class="modal-body">
<input type="file" id="filename" name="filename"
class="file-loading" />
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal">关闭</button>
<button type="button" class="btn btn-primary"
οnclick="importxls()">导入</button>
</div>
</form>
</div>
</div>
</div>
第二步:在js中,代码如下:
//导入xls文件
function importxls() {
var file = new FormData($("#uploadform")[0]);//获取文件名
$.ajax({
url : "${basePath}/import",//请求地址
type : "POST",
data : file,
async : false,
cache : false,
contentType : false, //不设置内容类型
processData : false, //不处理数据
success : function(result) {
console.log(result);
if (result.code == 100) {
$('#stuImportModal').modal('hide');
} else {
alert(result.extend.errorfilname);
}
}
})
}
第三步:在@Controller类中,响应对应的url,在控制台可以自己看一下文件路径,我都已在代码中输出
@Controller
public class StudentController {
@Autowired
private StudentService studentservice;
@RequestMapping(value = "/import", method = RequestMethod.POST)
@ResponseBody
public Msg importstu(HttpServletRequest request, HttpServletResponse rep,
@RequestParam(value = "filename") MultipartFile file) throws Exception {
if (!file.isEmpty()) {// 判断文件是否为空
String filepath = request.getSession().getServletContext().getRealPath("/") + "upload/"
+ file.getOriginalFilename();// 文件保存路径
System.out.println(filepath);
try {
file.transferTo(new File(filepath));// 转存文件
} catch (IllegalStateException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
String filename = file.getOriginalFilename();// 获得文件名
String regx = filename.substring(filename.lastIndexOf("."));
// System.out.println("后缀"+regx);
if (regx.equals(".xls")) {
String name = "upload\\" + filename;
String realpath = request.getSession().getServletContext().getRealPath("/") + name;
System.out.println("name" + realpath);
studentservice.importstu(realpath);
return Msg.success();
} else {
return Msg.fail().add("errorfilname", "只能上传xls文件");
}
}
}
第四步:在@Service中,实现控制器中的方法
@Service
public class StudentService {
@Autowired
private StudentMapper studentmapper;
public void importstu(String filename) throws Exception {
List<Student> list = Import.readExcel(filename);//读出Excel文件中的学生集合
for (Student student : list) {
Student stu = studentmapper.selectByPrimaryKey(student.getStuId());// 判断是否已经添加
if (stu != null) {
continue;
} else {
studentmapper.insert(student);//添加到数据库中
}
}
}
}
第五步:Excel转工具类,其中学生对象属性可以根据自己的实际需要自己设置
public class Import {
/**
* 读出Excel表中的值,并赋值给对象
* @param filename
* @return
* @throws Exception
*/
public static List<Student> readExcel(String filename) throws Exception {
System.out.println("filename"+filename);
InputStream is = new FileInputStream(filename);
HSSFWorkbook workbook = new HSSFWorkbook(is);
Student s = null;
List<Student> list = new ArrayList<Student>();
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {// 遍历数据表
HSSFSheet hs = workbook.getSheetAt(i);
if (hs == null) {
continue;
}
for (int j = 1; j <= hs.getLastRowNum(); j++) {// 遍历表中每行是否有数据
HSSFRow hassrow = hs.getRow(j);
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
if (hassrow != null) {
s = new Student();
HSSFCell id = hassrow.getCell(0);// 遍历行中每个单元格
HSSFCell name = hassrow.getCell(1);
HSSFCell psw = hassrow.getCell(2);
HSSFCell classname = hassrow.getCell(3);
HSSFCell date = hassrow.getCell(4);
s.setStuId(getValue(id));
s.setStuName(getValue(name));
s.setStuPassword(getValue(psw));
s.setStuClassname(getValue(classname));
s.setStuRegisterdate(getValue(date));
list.add(s);
}
}
}
return list;
}
/**
* 获取单元格的值
*
* @param hc
* @return 单元格中的值
*/
public static String getValue(HSSFCell hc) {
if (hc.getCellType() == hc.CELL_TYPE_BOOLEAN) {
return String.valueOf(hc.getBooleanCellValue());
} else if (hc.getCellType() == hc.CELL_TYPE_NUMERIC) {
return String.valueOf(hc.getNumericCellValue());
} else {
return String.valueOf(hc.getStringCellValue());
}
}
}
好了,这是我在毕设所用方法,如有问题可以私信共同探讨。