easypoi Excel导入导出（工具类）

谁践踏了优雅 2021-09-16 14:46 1099阅读 0赞

1.用到的jar

红色的必须的。

下面那些是运行起来，缺哪个就导哪个。

如果报错提示没有这个方法的话，重启Tomcat，还不好使就是jar包版本不对，找个高点的版本。

easypoi-annotation-3.1.0.jar  
easypoi-base-3.1.0.jar  
easypoi-web-3.1.0.jar  
poi-3.15.jar  
log4j-to-slf4j-2.10.0.jar  
slf4j-api-1.7.12.jar  
slf4j-log4j12-1.7.25.jar  
poi-ooxml-3.15.jar  
poi-ooxml-schemas-3.15.jar  
commons-lang3-3.4.jar  
commons-fileupload-1.2.1.jar  
 commons-io-1.3.jar

2.实体类

package com.ssm.entity;
    
    import cn.afterturn.easypoi.excel.annotation.Excel;
    
    public class FileEntity {
        //注解，easypoi教程里有，时间转换、数据字典转换等
    	 @Excel(name = "姓名", orderNum = "0")
    	 public String fileId;
    	 @Excel(name = "性别", replace = {"男_1", "女_2"}, orderNum = "1")
    	 public String fileName;
    	 @Excel(name = "孙权", orderNum = "2")
    	 public String tags;
    	/*如果不注释，抛异常，java.lang.InstantiationException（对象创建失败）
            public FileEntity(String fileId, String fileName, String tags) {        
    		this.fileId = fileId;       
    		this.fileName = fileName;        
    		this.tags = tags;   
    		}
        */
    	public String getFileId() {
    		return fileId;
    	}
    	public void setFileId(String fileId) {
    		this.fileId = fileId;
    	}
    	public String getFileName() {
    		return fileName;
    	}
    	public void setFileName(String fileName) {
    		this.fileName = fileName;
    	}
    	public String getTags() {
    		return tags;
    	}
    	public void setTags(String tags) {
    		this.tags = tags;
    	}
    
    }

3.导入导出工具类

package com.ssm.until;
    
    import java.io.File;
    import java.io.IOException;
    import java.net.URLEncoder;
    import java.util.List;
    import java.util.Map;
    import java.util.NoSuchElementException;
    
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.poi.ss.usermodel.Workbook;
    import org.springframework.web.multipart.MultipartFile;
    
    import cn.afterturn.easypoi.excel.ExcelExportUtil;
    import cn.afterturn.easypoi.excel.ExcelImportUtil;
    import cn.afterturn.easypoi.excel.entity.ExportParams;
    import cn.afterturn.easypoi.excel.entity.ImportParams;
    import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
    
    public class FileUtil {
    	public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
    			boolean isCreateHeader, HttpServletResponse response) throws Exception {
    		ExportParams exportParams = new ExportParams(title, sheetName);
    		exportParams.setCreateHeadRows(isCreateHeader);
    		defaultExport(list, pojoClass, fileName, response, exportParams);
    
    	}
    
    	public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
    			HttpServletResponse response) {
    		defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    	}
    
    	public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response)
    			throws Exception {
    		defaultExport(list, fileName, response);
    	}
    
    	private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response,
    			ExportParams exportParams) {
    		Workbook workbook = null;
    		try {
    			workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    		if (workbook != null)
    		try {
    			downLoadExcel(fileName, response, workbook);
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    	}
    
    	private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook)
    			throws Exception {
    		try {
    			response.setCharacterEncoding("UTF-8");
    			response.setHeader("content-Type", "application/vnd.ms-excel");
    			response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
    			workbook.write(response.getOutputStream());
    		} catch (IOException e) {
    			throw new Exception(e.getMessage());
    		}
    	}
    
    	private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response)
    			throws Exception {
    		Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
    		if (workbook != null)
    			;
    		downLoadExcel(fileName, response, workbook);
    	}
    
    	public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass)
    			throws Exception {
    		if (org.apache.commons.lang3.StringUtils.isBlank(filePath)) {
    			return null;
    		}
    		ImportParams params = new ImportParams();
    		params.setTitleRows(titleRows);
    		params.setHeadRows(headerRows);
    		List<T> list = null;
    		try {
    			list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
    		} catch (NoSuchElementException e) {
    			throw new Exception("模板不能为空");
    		} catch (Exception e) {
    			e.printStackTrace();
    			throw new Exception(e.getMessage());
    		}
    		return list;
    	}
    
    	public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)
    			throws Exception {
    		if (file == null) {
    			return null;
    		}
    		ImportParams params = new ImportParams();
    		params.setTitleRows(titleRows);
    		params.setHeadRows(headerRows);
    		List<T> list = null;
    		try {
    			list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
    		} catch (NoSuchElementException e) {
    			throw new Exception("excel文件不能为空");
    		} catch (Exception e) {
    			throw new Exception(e.getMessage());
    		}
    		return list;
    	}
    
    }

3.导入导出controller

package com.ssm.controller;
    
    import java.util.ArrayList;
    import java.util.List;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.RequestBody;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.multipart.MultipartFile;
    
    import com.ssm.entity.FileEntity;
    import com.ssm.until.FileUtil;
    
    @Controller
    public class FileController {
            //导出Excel
    	  @RequestMapping("/export")
    	    public void export(HttpServletResponse response) throws Exception{
    	 
    	        //模拟从数据库获取需要导出的数据
    	        List<FileEntity> personList = new ArrayList<FileEntity>();
    	        FileEntity person1 = new FileEntity();
    	        FileEntity person2 = new FileEntity();
    	        FileEntity person3 = new FileEntity();
    	        FileEntity person4 = new FileEntity();
    	        person1.setFileId("1");
    	        person2.setFileId("xm2");
    	        person1.setFileName("daiang");
    	        person1.setTags("谷向南是大老娘们");
    	        person2.setTags("asdsadg");
    	        personList.add(person1);
    	        personList.add(person2);
    	        personList.add(person3);
    	        personList.add(person4);
    	 
    	        //导出操作
    	        FileUtil.exportExcel(personList,"花名册","422",FileEntity.class,"虾米.xls",response);
    	    }
    	     //Excel导入
    	    @RequestMapping("importExcel")
    	    public void importExcel(@RequestParam("textFile") MultipartFile file,HttpServletRequest request) throws Exception{
    	        //String filePath = "F:\\故乡南.xls";
    	    	System.out.println(file);//用来检查前端是否把文件传过来
                //解析excel，
    	        List<FileEntity> personList = FileUtil.importExcel(file, 1, 1,FileEntity.class);
    	        //也可以使用FileUtil.importExcel(filePath,1,1,FileEntity.class) 导入
    	        System.out.println("导入数据一共【"+personList.size()+"】行");
    	        //TODO 保存数据库
    	    }
    
    }

4.导入Excel前端代码

5.用的是springmvc，需要改一下配置文件springmvc-servlet.xml，因为Excel导入功能

<bean name="multipartResolver"
            class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
            <property name="defaultEncoding" value="UTF-8"></property>
        </bean>

最后感谢陈锐、司马缸对本文大力支持(\#^.^\#)！！