SpringMVC读取Excel,mongodb存储

最新推荐文章于 2021-02-03 19:13:58 发布

mmm2v

最新推荐文章于 2021-02-03 19:13:58 发布

阅读量561

点赞数

分类专栏： SpringMVC 文章标签： excel springMVC JAVA

本文链接：https://blog.csdn.net/mengweiweisama/article/details/80264046

版权

SpringMVC 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

前台页面：

<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数据：

数据库结果：

mmm2v

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
2
评论
SpringMVC读取Excel,mongodb存储

package com.demo;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.List;imp...
复制链接

扫一扫