前台页面:
<body>
<form id="myForm" action="ec/re" method="post" enctype="multipart/form-data">
<input type="file" name="filename" value="选择jar包">
<input type="submit" value="导入"/>
</form>
</body>
<script type="text/javascript">
function uploadFile(){
console.info("file");
$("#myForm").form("submit",{
success:function(result){
var result=eval('('+result+')');
if(result.errorMsg){
$.messager.alert("系统提示",result.errorMsg);
}else{
$.messager.alert("系统提示","上传成功");
}
}
});
}
</script>
读取Excel的工具类:
package com.demo;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
public class ExcelRead {
public List<User> readExcel(MultipartFile uploadFile,HttpServletRequest request){
List<User> users = new ArrayList<User>();
HSSFWorkbook wb = null;
InputStream ins = null;
try {
ins = uploadFile.getInputStream();
wb = new HSSFWorkbook(ins);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
try {
ins.close();
wb.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
HSSFSheet sheetAt = wb.getSheetAt(0);
if(sheetAt!=null){
for(int rowNum=1;rowNum<=sheetAt.getLastRowNum();rowNum++){
HSSFRow xRow = sheetAt.getRow(rowNum);
if(xRow==null){
continue;
}
User su = new User();
su.setName(xRow.getCell(0).toString());
su.setAge(Double.valueOf(xRow.getCell(1).toString()).intValue());
su.setBirth(xRow.getCell(3).getDateCellValue());
users.add(su);
}
}
return users;
}
public List<User> readExcel2(MultipartFile uploadFile,HttpServletRequest request){
List<User> users = new ArrayList<User>();
XSSFWorkbook wb = null;
InputStream ins = null;
try {
//此时的Workbook应该是从 客户端浏览器上传过来的 uploadFile了,其实跟读取本地磁盘的一个样
ins = uploadFile.getInputStream();
wb = new XSSFWorkbook(ins);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
try {
ins.close();
wb.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
XSSFSheet sheetAt = wb.getSheetAt(0);
if(sheetAt!=null){
//遍历excel,从第二行开始 即 rowNum=1,逐个获取单元格的内容,然后进行格式处理,最后插入数据库
for(int rowNum=1;rowNum<=sheetAt.getLastRowNum();rowNum++){
XSSFRow xRow = sheetAt.getRow(rowNum);
if(xRow==null){
continue;
}
User su = new User();
su.setName(xRow.getCell(0).toString());
su.setAge(Double.valueOf(xRow.getCell(1).toString()).intValue());
su.setBirth(xRow.getCell(3).getDateCellValue());
users.add(su);
}
}
return users;
}
}
主要jar包(下载http://mvnrepository.com/):
bean类:
package com.demo;
import java.util.Date;
import org.springframework.data.annotation.Id;
import org.springframework.data.mongodb.core.mapping.Document;
import com.alibaba.fastjson.annotation.JSONField;
@Document(collection="testUser")
public class User {
@JSONField(name="_id")
@Id
private String id;
private String name;
private long age;
private Date birth;
public User(String id, String name, long age,Date birth) {
super();
this.id = id;
this.name = name;
this.age = age;
this.birth = birth;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public long getAge() {
return age;
}
public void setAge(long age) {
this.age = age;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
public User() {
super();
}
}
controller:
package com.demo;
import java.util.List;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import com.demo.User;
@Controller
@RequestMapping(value="ec")
public class ExcelController {
@Resource(name = "es")
private ExcelService es;
@RequestMapping(value="tu")
public String toUpload(){
return "demo/excelUpload";
}
@RequestMapping(value="re")
public String readExcel(@RequestParam(value="filename") MultipartFile file,HttpServletRequest request){
String fileName = file.getOriginalFilename();
String suffix = fileName.substring(fileName.lastIndexOf(".")+1, fileName.length());
List<User> list = null;
if ("xls".equals(suffix) || "XLS".equals(suffix)) {
list = new ExcelRead().readExcel(file,request);
}else if ("xlsx".equals(suffix) || "XLSX".equals(suffix)) {
list = new ExcelRead().readExcel2(file,request);
}
/**
* TODO
* 将读取到的结果写入到数据库
*/
for (User user2 : list) {
es.insert(user2);
}
return "ajaxResult";
}
}
service接口:
package com.demo;
public interface ExcelService {
public String insert(User user);
}
service实现:
package com.demo;
import javax.annotation.Resource;
import org.springframework.data.mongodb.core.MongoOperations;
import org.springframework.stereotype.Service;
@Service("es")
public class ExcelServiceImp implements ExcelService{
@Resource(name = "mongoTemplate")
private MongoOperations mongoTemplate;
public String insert(User user) {
mongoTemplate.insert(user);
return null;
}
}
配置文件需要添加,id不能修改:
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
</bean>
页面效果:
Excel数据:
数据库结果: