SSM框架的Excel的导出导入读取数据

由于项目的原因需要用spring+springMVC+Mybatis框架来完成Excel文件的导入和导出功能，之前没有做过Excel的导入和导出功能，经过查资料等最终将功能完成，需要用的jar包除了基本的SSM的jar包外还需要用到的jar包，用到的是easypoi的jar包
jar包下载：https://download.csdn.net/download/weixin_42812037/10671588

excel文件的导出
对应的实体类

public class User{
	@Excel(name="姓名",height=20,width=30)
	private String name;
	@Excel(name="性别",height=20,width=30)
	private String sex;
	public String getName() {
		return name;
	}
	public void setKczw(String name) {
		this.name= name;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.kcyw = sex;
	}
	@Override
	public String toString() {
		return "User[name=" + name+ ", sex=" + sex + "]";
	}
}

controller层

//导出路径如果是浏览器自选
	@RequestMapping("downExcel")
	public void downExcel(HttpServletResponse response) throws UnsupportedEncodingException{
		Collection<User> list=new ArrayList<User>();
		User user=new User();
		user.setName("wang");
		user.setSex("女")；
		list.add(user);
		ExportParams params=new ExportParams();
		Workbook workbook=ExcelExportUtil.exportExcel(params,User.class,list);
		response.setHeader("content-Type", "application/vnd.ms-excel");
		String title="用户信息";//设置导出的文件名
		response.setHeader("Content-DisPosition", "attachment;filename="+URLEncoder.encode(title, "UTF-8")+".xls");
		response.setCharacterEncoding("UTF-8");
		try {
			workbook.write(response.getOutputStream());
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
//导出路径固定
@RequestMapping("downExcel")
	public void downExcel(HttpServletResponse response) throws UnsupportedEncodingException{
		Collection<User> list=new ArrayList<User>();
		User user=new User();
		user.setName("wang");
		user.setSex("女")；
		list.add(user);
		ExportParams params=new ExportParams();
		Workbook workbook=ExcelExportUtil.exportExcel(params,User.class,list);
		try {
			FileOutputStream fout = new FileOutputStream("E:\\123\\课程翻译.xls");  
			workbook.write(fout);  
            fout.close();  
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

启动项目后访问接口就可以导出excel文件


Excel文件的导入和读取数据，因为要多用到，所以就封装了一下

用于上传excel文件

public class ExcelUpload {
	 public File importExcel(MultipartFile  file,HttpServletRequest request) {
	        try {
	            ImportParams params = new ImportParams();
	            params.setTitleRows(1);
	            params.setHeadRows(2);
	            params.setNeedSave(true);
	            String path = request.getSession().getServletContext().getRealPath("");
	            File f = new File(path+"/excel/"+file.getOriginalFilename());
	            if(!f.exists()){
	                try {
	                    File dir = new File(path+"/excel/");
	                    dir.mkdirs();
	                    if(f.createNewFile()){
	                        System.out.println("创建文件成功");
	                    }else{
	                        System.out.println("创建文件失败");
	                    }
	                } catch (IOException e) {
	                    e.printStackTrace();
	                }
	            }
	            file.transferTo(f);
	            return f;
	        }catch (Exception e) {
	            e.printStackTrace();
	            return null;
	        }
			
	 }	 
}

用于读取excel文件的内容

public class ExcelFet {
	 public List readExcel(File file) {
	        try {
	            InputStream is = new FileInputStream(file.getAbsolutePath()); // 创建输入流，读取Excel
	            Workbook wb = Workbook.getWorkbook(is);// jxl提供的Workbook类
	            int sheet_size = wb.getNumberOfSheets(); // Excel的页签数量
	            for (int index = 0; index < sheet_size; index++) {
	                List<List> outerList=new ArrayList<List>();
	                Sheet sheet = wb.getSheet(index); // 每个页签创建一个Sheet对象 
	                for (int i = 0; i < sheet.getRows(); i++) {// sheet.getRows()返回该页的总行数
	                    List innerList=new ArrayList(); 
	                    for (int j = 0; j < sheet.getColumns(); j++) {// sheet.getColumns()返回该页的总列数
	                        String cellinfo = sheet.getCell(j, i).getContents();
	                        if(cellinfo.isEmpty()){
	                            continue;
	                        }
	                        innerList.add(cellinfo);
	                        System.out.print(cellinfo);
	                    }
	                    outerList.add(i, innerList);
	                    System.out.println();
	                }
	                is.close();
	                return outerList;
	            }
	        } catch (FileNotFoundException e) {
	            e.printStackTrace();
	        } catch (BiffException e) {
	            e.printStackTrace();
	        } catch (IOException e) {
	            e.printStackTrace();
	        }
	        return null;
	    }
	}

controller层调用

@RequestMapping(value = "/import", method = RequestMethod.POST)
	@ResponseBody
	public void importExcel(MultipartFile file, HttpServletRequest request) {
		ExcelUpload excelUpload = new ExcelUpload();
		File f = excelUpload.importExcel(file, request);
		ExcelFet excelUtil = new ExcelFet();
		List list = excelUtil.readExcel(f);
		System.out.println(list);
		if(f.exists()){
			f.delete();
			System.out.println("文件删除成功");
		}
	}

启动项目，访问import接口，因为需要上传文件，没有写对应的前台页面，所以用的postman来测试

控制台结果

就这么多，有什么错误请大佬多多指教