easypoi Excel导入导出 （工具类）

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前端代码

<form action="importExcel" method="post" enctype="multipart/form-data">
 	<input type="file" name="textFile"/>
 	<input type="submit" value="进来"/>
 </form>

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

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

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