java easyexcel导入

1.引入pom.xml依赖

    <dependency>
    		<groupId>com.alibaba</groupId>
    		<artifactId>easyexcel</artifactId>
    		<version>1.1.2-beta5</version>
    </dependency>

2.前端

jsp中：

<a type="button" class="btn btn-primary mc-btn-f-s" onclick="installer_fn.importPanel();">Import Installer</a>

installer.js

var installer_fn = {
    importPanel:function(){
		new $.Zebra_Dialog('<form id="installerFilesForm" class="form-horizontal">' +
	        	'<div class="form-group">' + 
	            '<label class="col-sm-4 control-label"><font class="control-label-start">*</font>Installer Excel</label>' +
	            '<div class="col-sm-7 control-layout-right">' +
	            '<input type="file" name="installer_file" style="display:none;" onchange="$(this).next().children(\'input\').val(this.value);"/>' +
	            '<div class="input-group" onclick="$(this).prev().click();">' +
	            '<input type="text" class="form-control" aria-describedby="basic-addon2" readonly />' +
	            '<a class="input-group-addon btn btn-primary" id="basic-addon2">Brows</a>' +
	            '</div> ' +
	            '</div>' +
	            '</div>' +
	            '<div class="form-group">',
				{
					title:"Please choose the upload file",
					type:false,
					buttons:[
                                            {caption: 'Upload', callback: function(){installer_fn.uploadInstallersFile();return true;}},
                                            {caption: 'Cancel',callback:function(){ return true;}},
					         ]
				});
	},
    uploadInstallersFile:function(){
		var formData = new FormData();
		var file = $("#installerFilesForm").find("input[name=installer_file]");
		formData.append("installer_file",file.get(0).files[0]);
		$.ajax({ 
			url : "/installer/import?r="+Math.random(), 
			type : 'POST', 
			data : formData, 
			processData : false, 
			contentType : false,
                        beforeSend:function(){
                            //渲染弹出的框
                            showBeforeSendDialog();
			},
			success : function(result) { 
				if(!$.isPlainObject(result)){
					result = $.parseJSON(result);
				}
                            alert(result.message);
                            //关闭弹出的框
                            closeBeforeSendDialog();
			}, 
			error : function(data) { 
				alert("Unknown error!");
				closeBeforeSendDialog();
			} 
		});
	},
}

3.Controller层

@Controller
@Scope("prototype")
@RequestMapping("/installer")
public class InstallerController {
	
@Autowired
private InstallerService installerService;

protected final Logger log = LoggerFactory.getLogger(this.getClass());

@ResponseBody
@RequestMapping(path="/import", method = RequestMethod.POST)
public Map<String,Object> readExcel(@RequestParam("installer_file")MultipartFile excel,HttpServletRequest request,HttpServletResponse response) {
		Map<String, Object> result =null;
		try {
			result = installerService.importInstallerList(excel.getInputStream());
		} catch (Exception e) {
			e.printStackTrace();
			 //打印到日志文件中
			log.error("Internal error:"+e);
		}
	   
		return result;
}
}

4.Service层

Service接口：

public interface InstallerService {
	public Map<String,Object> importInstallerList(InputStream io);
}

Service实现类：

@Service
@Transactional
public class InstallerServiceImp implements InstallerService {
	private static final Logger logger=Logger.getLogger(InstallerServiceImp.class);

        @Override
	public Map<String, Object> importInstallerList(InputStream io) {
		Map<String,Object> resultMap = new HashMap<String,Object>();
		try {
			//第一种有表头获取方式Sheet(1,1)，无表头为Sheet(0,1)
			EasyExcelFactory.readBySax(io, new com.alibaba.excel.metadata.Sheet(1,1), new AnalysisEventListener() {
				@Override
				public void invoke(Object object, AnalysisContext context) {
					// TODO Auto-generated method stub
					System.out.println("当前sheet"+context.getCurrentSheet().getSheetNo()+ " 当前行：" + context.getCurrentRowNum()
	                + " data:" + object);
				}
				@Override
				public void doAfterAllAnalysed(AnalysisContext context) {
					// TODO Auto-generated method stub
				}
			});
			//第二种使用InstallerExcelBean有表头获取方式，无表头为Sheet(0,1)
			com.alibaba.excel.metadata.Sheet sheet = new com.alibaba.excel.metadata.Sheet(1,1,InstallerExcelBean.class);
			List<Object> data = EasyExcelFactory.read(io, sheet);
			for (Object o : data) {
		            if (o instanceof InstallerExcelBean) {
		            	InstallerExcelBean importEntity = (InstallerExcelBean) o;
		                try {
		                	//此处可以处理需要处理的业务
		                	
		                } catch (Exception e) {
		                	e.printStackTrace();
		                    logger.error("Internal error:"+e);
		                    continue;
		                }
		            }
		        }
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			logger.error("Internal error:"+e);
		}
		return resultMap;
	}


}

5.导入的列与实体对应

@ExcelProperty(value="Installer Code",index=1)中value表示列头，与列名相等，index表示第几列；

import java.math.BigDecimal;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;

public class InstallerExcelBean extends BaseRowModel {
	
	@ExcelProperty(value="Installer Code",index=1)
	private String installerCode;
	
	@ExcelProperty(value="Installer Name",index=2)
	private String installerName;
	 
	@ExcelProperty(value="Phone1",index=3)
	private String Phone1;
	
	@ExcelProperty(value="Phone2",index=4)
	private String Phone2;
	
	@ExcelProperty(value="Country",index=5)
	private String country;
	
	@ExcelProperty(value="Province",index=6)
	private String province;
	
	@ExcelProperty(value="District",index=7)
	private String district;
	
	@ExcelProperty(value="Commune",index=8)
	private String commune;
	
	@ExcelProperty(value="Village",index=9)
	private String village;
	
	@ExcelProperty(value="Register Date",index=10)
	private String registerDate;
	
	@ExcelProperty(value="RegisterBy",index=11)
	private String registerBy;

	public String getInstallerCode() {
		return installerCode;
	}

	public void setInstallerCode(String installerCode) {
		this.installerCode = installerCode;
	}

	public String getInstallerName() {
		return installerName;
	}

	public void setInstallerName(String installerName) {
		this.installerName = installerName;
	}

	public String getPhone1() {
		return Phone1;
	}

	public void setPhone1(String phone1) {
		Phone1 = phone1;
	}

	public String getPhone2() {
		return Phone2;
	}

	public void setPhone2(String phone2) {
		Phone2 = phone2;
	}

	public String getCountry() {
		return country;
	}

	public void setCountry(String country) {
		this.country = country;
	}

	public String getProvince() {
		return province;
	}

	public void setProvince(String province) {
		this.province = province;
	}

	public String getDistrict() {
		return district;
	}

	public void setDistrict(String district) {
		this.district = district;
	}

	public String getCommune() {
		return commune;
	}

	public void setCommune(String commune) {
		this.commune = commune;
	}

	public String getVillage() {
		return village;
	}

	public void setVillage(String village) {
		this.village = village;
	}

	public String getRegisterDate() {
		return registerDate;
	}

	public void setRegisterDate(String registerDate) {
		this.registerDate = registerDate;
	}

	public String getRegisterBy() {
		return registerBy;
	}

	public void setRegisterBy(String registerBy) {
		this.registerBy = registerBy;
	}
	
	
	
}