easypoi的多sheet页导入与导出

maven:

<dependency>
				<groupId>cn.afterturn</groupId>
				<artifactId>easypoi-base</artifactId>
				<version>3.0.3</version>
			</dependency>
			<dependency>
				<groupId>cn.afterturn</groupId>
				<artifactId>easypoi-web</artifactId>
				<version>3.0.3</version>
			</dependency>
			<dependency>
				<groupId>cn.afterturn</groupId>
				<artifactId>easypoi-annotation</artifactId>
				<version>3.0.3</version>
			</dependency>

 

环境:SSM，jdk1.8

 

一、多sheet页导入，看注释

@RequestMapping(value = "/excelImport", method = RequestMethod.POST)  
    @ResponseBody  
    public Object excelImport(MultipartFile file) throws IOException {  
        //根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页  
        Workbook hssfWorkbook = ExcelUtil.getWorkBook(file);  
        StringBuilder sb=new StringBuilder();  
        try {  
            ImportParams params = new ImportParams();  
            // 循环工作表Sheet  
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {  
                //表头在第几行 
				params.setTitleRows(0); 
				//距离表头中间有几行不要的数据  
				//params.setStartRows(1);  
                //第几个sheet页  
                params.setStartSheetIndex(numSheet);  
                //验证数据  
                params.setNeedVerfiy(true);  
                  
                ExcelImportResult<Object> result=null;  
                if(numSheet==0){  
                     result = ExcelImportUtil.importExcelMore(file.getInputStream(),  
                            Entity1.class, params);  
                     //插入验证合格的数据    
                     //CallServiceUtil.callDataService("", "", new Object[] { result.getList() },  
                     //new Class[] { List.class });  
                }else if(numSheet==1){  
                     result = ExcelImportUtil.importExcelMore(file.getInputStream(),  
                                Entity2.class, params);  
                    //插入验证合格的数据   
                }//............  
                  
                List list=null;  
                //如果有些数据验证出来有误   为true  
                if(result.isVerfiyFail()){  
                    //不合规定的数据  
                    list=result.getFailList();  
                    //拼凑错误信息,自定义  
                    for(int i=0;i<list.size();i++){  
                        if(list.get(i) instanceof Entity1)  
                            ExcelUtil.getWrongInfo(sb, list, i, list.get(i), "realName", "Entity1信息不符");  
                        else if(list.get(i) instanceof Entity2)  
                            ExcelUtil.getWrongInfo(sb, list, i, list.get(i), "age", "Entity2信息不符");  
                        //....  
                    }  
                }  
            }  
            if(sb.length()!=0){  
                return renderError(sb.toString());  
            }  
        } catch (Exception e) {  
            e.printStackTrace();  
            return renderError("导入失败！请检查导入文档的格式是否正确");  
        }  
        return renderSuccess("导入成功！");  
    }

 

ExcelUtil工具类

 

import java.beans.PropertyDescriptor;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import cn.zj.pubinfo.comm.core.exception.BaseException;

public class ExcelUtil {
	/**
	 * 得到Workbook对象
	 * @param file
	 * @return
	 * @throws IOException
	 */
	public static Workbook getWorkBook(MultipartFile file) throws IOException{
		//这样写  excel 能兼容03和07
		InputStream is = file.getInputStream();
		Workbook hssfWorkbook = null; 
		try { 
		    hssfWorkbook = new HSSFWorkbook(is); 
		} catch (Exception ex) {
		    is =file.getInputStream();
		    hssfWorkbook = new XSSFWorkbook(is); 
		}
		return hssfWorkbook;
	}
	
	/**
	 * 得到错误信息
	 * @param sb
	 * @param list
	 * @param i
	 * @param obj
	 * @param name  用哪个属性名去表明不和规定的数据
	 * @param msg
	 * @throws Exception
	 */
	public static void getWrongInfo(StringBuilder sb,List list,int i,Object obj,String name,String msg) throws Exception{
		Class clazz=obj.getClass();
		Object str=null;
		//得到属性名数组 
		Field[] fields = clazz.getDeclaredFields();
		 for(Field f : fields){
			 if(f.getName().equals(name)){
				 //用来得到属性的get和set方法
				 PropertyDescriptor pd = new PropertyDescriptor(f.getName(), clazz);
				 //得到get方法
				 Method getMethod=pd.getReadMethod();
				 str = getMethod.invoke(obj);
			 }
		 }
		 if(i==0)
				sb.append(msg+str+";");
		 else if(i==(list.size()-1))
				sb.append(str+"</br>");
		 else
				sb.append(str+";");
	}
	/**
	 * 
	 * @param response
	 * @param wb
	 * @param showFileName
	 * @throws IOException
	 */
	public static void downloadExcel(HttpServletResponse response, Workbook wb, String showFileName) throws IOException {
		 // 判断数据
        if(wb == null) {
        	throw new BaseException(50001000);
        }
        // 设置excel的文件名称
        String excelName = "人事信息" ;
        // 重置响应对象
        response.reset();
        // 当前日期，用于导出文件名称
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        String dateStr = excelName+sdf.format(new Date())+".xls";
        // 指定下载的文件名--设置响应头
        response.addHeader("Content-Disposition", "attachment;filename=" + new String(dateStr.getBytes("gb2312"), "ISO8859-1"));
        response.setContentType("application/octet-stream;charset=UTF-8");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        // 写出数据输出流到页面
        try {
            OutputStream output = response.getOutputStream();
            BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
            wb.write(bufferedOutPut);
            bufferedOutPut.flush();
            bufferedOutPut.close();
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        
	}
}

Entity1实体类： 利用Hibernate Validate  来验证导入字段的信息是否符合   @Zone是自定义的标签

public class Entity1 {
	
	 /**
     * 姓名
     */
	@Excel(name = "姓名")
	@Zone(zone={"葛","卢"})
    private String realName;

    /**
     * 身份证号
     */
	@Excel(name = "身份证号")
	@NotBlank(message = "不能为空")
    private String idCard;

	public Entity1() {
		super();
	}

	public Entity1(String realName, String idCard) {
		super();
		this.realName = realName;
		this.idCard = idCard;
	}

	public String getRealName() {
		return realName;
	}

	public void setRealName(String realName) {
		this.realName = realName;
	}

	public String getIdCard() {
		return idCard;
	}

	public void setIdCard(String idCard) {
		this.idCard = idCard;
	}

	@Override
	public String toString() {
		return "Entity1 [realName=" + realName + ", idCard=" + idCard + "]";
	}
	
	
}

 

二、多sheet页的导出，相较于导入  简单多了

//	导出
	@RequestMapping(value = "/excelExport", method = RequestMethod.POST)
	public void excelExport(HttpServletResponse response, @RequestParam Map<String, Object> params) throws IOException {
		
		//把要导出的信息放在map里面
		Map<String, Object> map = new HashMap<String, Object>();
		//获取信息
			// 员工信息
			List<PersonVO> personList = CallServiceUtil.callDataService("personService", "excelExport",
					new Object[] { params }, new Class[] { Map.class });
			// 教育经历
			List<EducationVO> educationList = CallServiceUtil.callDataService("educationService", "excelExport",
					new Object[] { params }, new Class[] { Map.class });
			//合同
			List<ContractVO> contract = CallServiceUtil.callDataService("contractService", "excelExport",
					new Object[] { params }, new Class[] { Map.class });
		//往map里面存放信息
		map.put("person", personList);
		map.put("education", educationList);
		map.put("contract", contract);
		
		// 获取导出excel指定模版
		File fs = new File(this.getClass().getResource("/").getPath());
		// 文件存放目录   自定义
		String path = fs.getAbsolutePath().substring(0, fs.getAbsolutePath().indexOf("wanbao-console") + "wanbao-console".length() +
				 1) .concat("src\\main\\webapp\\static\\").concat(BaseController.
						 MUDULE_RSXX_EXPORT_FILE_PATH);
		TemplateExportParams tep = new TemplateExportParams(path, true);
		//把map里面的信息放入模板
		Workbook workbook = ExcelExportUtil.exportExcel(tep, map);
		//下载
		ExcelUtil.downloadExcel(response, workbook, "人员信息");

	}

模板：

第一个：{{$fe: person t.realName 

最后一个：t.usedName}} 

日期格式化：fd:(t.partyDate;yyyy-MM-dd)

第一个和最后一个的大括号把中间的都包起来了。

 

官方API：http://easypoi.mydoc.io/#text_225967