通用Excel数据导入功能模板

最近做了一个excel导入功能，思路很简单，给一个上传excel文件的入口，然后获取excel文件的sheet签和签中的数据，可以对数据对象进行业务处理，当然也可以数据存入数据库中
代码如下：jsp页面：一个上传excel附件表的入口，ajax方法请求后台
          controller：后台控制层，用于前端的调用
          service：业务处理，控制层传过来的参数用于处理业务，也可以调用多个工具类
          excel工具类：在这里获取excel中的数据，获取数据后做一系列的业务处理

jsp页面代码：

<div id="msgdiv" style="text-align: center;font-size: 16px;color: red;">${msg}</div>
<tr valign="middle" class="mytr">
	<td width="30%" height="25" class="tdtitbgcorl" align="left"  style="text-align:left;">团员关系数据导入</td>
	<td >
		<input type="file" name="sxfile" id="sxfile"  />
	</td>
	<td >
		<input type="button" name="stsx" id="stsx" value="导入数据"  onclick="execSx()" />&nbsp;&nbsp;
	</td>
</tr>
<script type="text/javascript">
function execSx(){
	if($("#sxfile").val()==""){
		alert("附件不可为空！");
		return;
	}
	var formData = new FormData();
	formData.append("file",$("#sxfile")[0].files[0]);
	
	$.ajax({
	    url:'xxxxxx.action?method=doDataImportTygxzcb',
	    type:'POST', 
	    async:false,
	    data:formData,
		// 告诉jQuery不要去处理发送的数据
	    processData : false,
	    // 告诉jQuery不要去设置Content-Type请求头
	    contentType : false,
	    timeout:40000,
	    dataType:'json',
	    beforeSend:function(){
	    	console.log("正在进行，请稍候");
	    	$("#msgdiv").text("正在导入数据,请稍候...");
	    },
	    success:function(data,textStatus,jqXHR){
	        console.log(data);
	        console.log(textStatus);
	        console.log(jqXHR);
	        if("1"==data.msgcode){
		        $("#msgdiv").text("导入数据成功！");
	        }else{
	        	$("#msgdiv").text("导入数据失败！");
	        }
	        alert(data.msg);
	    },
	    error:function(xhr,textStatus){
	        console.log('错误');
	        console.log(xhr);
	        console.log(textStatus);
	        $("#msgdiv").text("导入数据失败或任务执行超时...");
	    }
	});
}

controller层代码：

public ModelAndView doDataImportTygxzcb(HttpServletRequest req, HttpServletResponse res) throws Exception {
	HashMap resultMap = new HashMap();
	resultMap.put(super.ACTIONKEYSTR, this.ACTIONKEY);
	String msg = "导入失败！";
	String msgcode = "0";
	MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest)req;  
	MultipartFile mfile = multipartRequest.getFile("file");
	if(mfile==null || mfile.isEmpty() || mfile.getSize()==0L) {
		msg = "附件不可为空！";
		resultMap.put("msg", msg);
		return new ModelAndView("jsonView", resultMap);  //这里注意返回的是json，不是视图
	}
	try {
		xstjTqtjTyxxService.saveTyxxbyTygxzcbExcel(mfile);
		msg = "团员关系注册表数据导入成功！";
		msgcode = "1";
	} catch (Exception e) {
		e.printStackTrace();
	}
	resultMap.put("msg", msg);
	resultMap.put("msgcode", msgcode); 
	return new ModelAndView("jsonView",resultMap);		//这里注意返回的是json，不是视图
}

service层代码：

public void saveTyxxbyTygxzcbExcel(MultipartFile mfile) {
		// TODO Auto-generated method stub
		InputStream in = null;
		org.apache.poi.ss.usermodel.Workbook workbook=null;
		try {
			in = mfile.getInputStream();
			workbook = WorkbookFactory.create(in);
			//这里可以同时调用多个工具类中的方法
			XstjExcelToolApi.api.analyzeXstjTyxxbyTygxzcb(workbook);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(in!=null) {
				try {
					in.close();
				} catch (Exception e) {
				}
			}
		}
	}

excel工具类代码：

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.weboa.web.StaticLoad;

import org.weboa.dao.CommonDao;


public class XstjExcelToolApi {
	public static XstjExcelToolApi api = new XstjExcelToolApi();
	private CommonDao commonDao = (CommonDao) StaticLoad.getBean("commonDao");
	private FormulaEvaluator evaluator = null;
	
	private String getCellValue(Cell cell){
		String cellValue = "" ;
		if(cell!=null){
			switch(cell.getCellType()){
			case  Cell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {  
					double d = cell.getNumericCellValue();  
					Date date = HSSFDateUtil.getJavaDate(d); 
					SimpleDateFormat dformat=new SimpleDateFormat("yyyy-MM-dd");
					cellValue=dformat.format(date);
				}else{
					NumberFormat nf = NumberFormat.getInstance();
					nf.setGroupingUsed(false);//true时的格式：1,234,567,890
					nf.setMaximumFractionDigits(9); //设置数值的小数部分允许的最大位数。 
					nf.setMinimumFractionDigits(0);
					cellValue= nf.format(cell.getNumericCellValue());//数值类型的数据为double，所以需要转换一下
				}
				break;
			case Cell.CELL_TYPE_STRING:
				cellValue=StringUtils.trim(cell.getStringCellValue());
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				cellValue=String.valueOf(cell.getBooleanCellValue());
				break;
			case Cell.CELL_TYPE_FORMULA:
				cellValue=getCellValue(evaluator.evaluate(cell));
				break;
			default:
				cellValue="";
				break;
			}
		}
		return cellValue ;
	}
	
    private static String getCellValue(CellValue cell) {
        String cellValue = null;
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            System.out.print("String :");
            cellValue=cell.getStringValue();
            break;
        case Cell.CELL_TYPE_BOOLEAN:
			cellValue=String.valueOf(cell.getBooleanValue());
			break;
        case Cell.CELL_TYPE_NUMERIC:
            System.out.print("NUMERIC:");
            cellValue=String.valueOf(cell.getNumberValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
        	cellValue="";
            System.out.print("FORMULA:");
            break;
        default:
            break;
        }
        
        return cellValue;
    }
    
	private String getDateCellValue(Cell cell){
		String cellValue = "" ;
		if(cell!=null){
			switch(cell.getCellType()){
			case  Cell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {  
					double d = cell.getNumericCellValue();  
					Date date = HSSFDateUtil.getJavaDate(d); 
					SimpleDateFormat dformat=new SimpleDateFormat("yyyy-MM-dd");
					cellValue=dformat.format(date);
				}else{
					NumberFormat nf = NumberFormat.getInstance();
					nf.setGroupingUsed(false);//true时的格式：1,234,567,890
					nf.setMaximumFractionDigits(9); //设置数值的小数部分允许的最大位数。 
					cellValue= nf.format(cell.getNumericCellValue());//数值类型的数据为double，所以需要转换一下
				}
				break;
			case Cell.CELL_TYPE_STRING:
				cellValue=StringUtils.trim(cell.getStringCellValue());
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				cellValue=String.valueOf(cell.getBooleanCellValue());
				break;
			case Cell.CELL_TYPE_FORMULA:
				cellValue=getDateCellValue(evaluator.evaluate(cell));
				break;
			default:
				cellValue="";
				break;
			}
		}
		return cellValue ;
	}
	
    private static String getDateCellValue(CellValue cell) {
        String cellValue = null;
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            System.out.print("String :");
            cellValue=cell.getStringValue();
            break;
        case Cell.CELL_TYPE_BOOLEAN:
			cellValue=String.valueOf(cell.getBooleanValue());
			break;
        case Cell.CELL_TYPE_NUMERIC:
            System.out.print("NUMERIC:");
            double d = cell.getNumberValue();  
			Date date = HSSFDateUtil.getJavaDate(d); 
			SimpleDateFormat dformat=new SimpleDateFormat("yyyy-MM-dd");
			cellValue=dformat.format(date);
            break;
        case Cell.CELL_TYPE_FORMULA:
        	cellValue="";
            System.out.print("FORMULA:");
            break;
        default:
            break;
        }
        return cellValue;
    }
    //以上几个方法是为了解析数据类型，下面开始解析数据，团员注册登记表
    void analyzeXstjTyxxbyTygxzcb(Workbook workbook){
    	this.evaluator = workbook.getCreationHelper().createFormulaEvaluator();	
		System.out.println("开始解析 Sheet 团员注册登记表");
		Sheet sheet;
		sheet = workbook.getSheet("团员注册登记表");
		//用户提供的数据必须验证按照模版的列顺序，不可调整列顺序
		int startRowNum = 1; //开始读取数据的行数
		int lastRowNum = sheet.getLastRowNum();//获取配置的读取结束的行数
		
		//遍历row
		for(;startRowNum<=lastRowNum;startRowNum++){
			System.out.println("---------start---row"+startRowNum+"-------------");
			Row hssfrow = sheet.getRow(startRowNum);
			if(startRowNum%100==0){
				Timestamp ts1 = new Timestamp(System.currentTimeMillis());
				System.out.println(ts1+" --- "+startRowNum);
			}
			
			Cell cellXH = hssfrow.getCell(5);//学号
			String xh = getCellValue(cellXH);
			DetachedCriteria tyxxdc = DetachedCriteria.forClass(XstjTqtjTyxx.class);
			tyxxdc.add(Restrictions.eq("xh", xh));
			List<XstjTqtjTyxx> tyxxdcList = commonDao.findListByCriteria(tyxxdc, -1, -1);
			XstjTqtjTyxx tyxx;
			if(tyxxdcList.size()>0){
				tyxx=tyxxdcList.get(0);
			}else{
				tyxx = new XstjTqtjTyxx();
			}
			tyxx.setXh(xh);
			tyxx.setZt("0");
			tyxx.setRtdw("XX大学");
			Cell cellEJZZ = hssfrow.getCell(1);//二级组织名称
			String ejtzzmc = getCellValue(cellEJZZ);
			tyxx.setEjtzzmc(ejtzzmc);
			
			
			DetachedCriteria dcbm = DetachedCriteria.forClass(XstjTqtjTzz.class);
			dcbm.add(Restrictions.eq("zzmc", ejtzzmc));
			List<XstjTqtjTzz> bmList = commonDao.findListByCriteria(dcbm, -1, -1);
			XstjTqtjTzz tzz = bmList.get(0);
			if(tzz==null) {
				System.out.println("二级组织名称不存在 =="+ejtzzmc);
			}
			tyxx.setEjtzzid(tzz.getUnid());
			
			Cell cellTZBMC = hssfrow.getCell(2);//团支部名称
			String tzbmc = getCellValue(cellTZBMC);
			tyxx.setTzbmc(tzbmc);
			
			DetachedCriteria tzbmcdc = DetachedCriteria.forClass(XstjTqtjTzz.class);
			tzbmcdc.add(Restrictions.eq("zzmc", tzbmc));
			List<XstjTqtjTzz> tzbmcList = commonDao.findListByCriteria(tzbmcdc, -1, -1);
			XstjTqtjTzz tzzt = tzbmcList.get(0);
			if(tzzt==null) {
				System.out.println("团支部名称不存在 =="+tzbmc);
			}
			tyxx.setTzzbh(tzzt.getUnid());
			
			
			Cell cellTYBH = hssfrow.getCell(3);//团员编号
			String tybh = getCellValue(cellTYBH);
			tyxx.setTybh(tybh);
			
			Cell cellXM = hssfrow.getCell(4);//姓名
			String xm = getCellValue(cellXM);
			tyxx.setXm(xm);
			
			
			//根据学号获得学生信息
			DetachedCriteria xsxxdc = DetachedCriteria.forClass(XsxxJbxx.class);
			xsxxdc.add(Restrictions.eq("xsxh", xh));
			List<XsxxJbxx> xsxxdcList = commonDao.findListByCriteria(xsxxdc, -1, -1);
			if(xsxxdcList.size()>0){
				XsxxJbxx xsxx=xsxxdcList.get(0);
				tyxx.setSfzh(xsxx.getZjhm());
				tyxx.setNj(xsxx.getNj());
				tyxx.setXb(xsxx.getXsxb());
				tyxx.setYxdm(xsxx.getYx());
				tyxx.setZydm(xsxx.getZy());
				tyxx.setBjdm(xsxx.getBj());
				DetachedCriteria yxmcdc = DetachedCriteria.forClass(JcsjYxxx.class);
				yxmcdc.add(Restrictions.eq("yxdm", xsxx.getYx()));
				List<JcsjYxxx> yxmcdcList = commonDao.findListByCriteria(yxmcdc, -1, -1);
				if(yxmcdcList.size()>0){
					JcsjYxxx yxxx=yxmcdcList.get(0);
					tyxx.setYxmc(yxxx.getYxmc());
				}
				DetachedCriteria zymcdc = DetachedCriteria.forClass(JcsjZyxx.class);
				zymcdc.add(Restrictions.eq("zydm", xsxx.getZy()));
				List<JcsjZyxx> zymcdcList = commonDao.findListByCriteria(zymcdc, -1, -1);
				if(zymcdcList.size()>0){
					JcsjZyxx zyxx=zymcdcList.get(0);
					tyxx.setZymc(zyxx.getZymc());
				}
				DetachedCriteria bjmcdc = DetachedCriteria.forClass(JcsjBjxx.class);
				bjmcdc.add(Restrictions.eq("bjdm", xsxx.getBj()));
				List<JcsjBjxx> bjmcdcList = commonDao.findListByCriteria(bjmcdc, -1, -1);
				if(bjmcdcList.size()>0){
					JcsjBjxx bjxx=bjmcdcList.get(0);
					tyxx.setBjmc(bjxx.getBjmc());
				}
			}
			
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			Cell cellks = hssfrow.getCell(6);//入团日期
			String kssj = getCellValue(cellks);
			try {
				if(StringUtils.isNotBlank(kssj)) {
					Date dateks =sdf.parse(kssj);
					tyxx.setRtsj(new Timestamp(dateks.getTime()));
				}
				commonDao.saveOrUpdate(tyxx);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			System.out.println("---------end---row"+startRowNum+"-------------");
		}
    }
    
    
}