excel导入实例---servlet+ext

1.页面

UpForm = Ext.extend(Ext.form.FormPanel, {
	id: 'up_form_id',
	constructor: function(config) {
		var newConfig = {};
		Ext.apply(newConfig, config || {}, {
			idProperty: 'up_form',
			labelWidth: 80,
			border: false,
			frame: true,
			autoScroll: true, 
			fileUpload : true, 
			buttonPosition : 'bottom',
			bodyStyle: "padding: 8px",
			trackResetOnLoad: false,
			defaults: {
				border: false,
				bodyStyle: 'padding: 5px 25px 5px 5px;',
				layout: 'form',
				labelWidth: 120
			},   
			items: [{   
		            xtype: 'textfield',   
		            fieldLabel: '文件名称',   
		            id: 'form_file', 
		            inputType: 'file',
		            allowBlank: false,   
		            blankText: '文件名称不能为空.',   
		            anchor: '90%'  // anchor width by percentage   
		        },{   
		            xtype: 'hidden',   
		            name: 'fileName'
		   }] ,
		   buttonAlign:'center',
		   buttons : [{
				   text : '上传',//提交按钮
				   iconCls: 'icon_disk',
				   handler : function(){
				   	   if(upForm.getForm().isValid()){
				   	   	   var fileName = upForm.form.findField("form_file").getValue();
				   	   	//   upForm.form.findField("fileName").setValue(fileName);
				   	   	//   alert(fileName);
					       upForm.form.submit({
					     	   waitMsg : '正在上传文件,请稍候......',//提示信息
					   	 	   waitTitle : '请稍候',//标题
					   		   url : jutil.getRootPath()+'/nurseInfoImport.do',
					           method : 'post',
					           success : function(form, action) {  
				                      Ext.MessageBox.alert('提交失败', action.result.msg);  
				                      Ext.getCmp("form_file").reset();
				               }, 
					           failure:function(form,action){//加载失败的处理函数
					     			 Ext.Msg.alert('提示','导入失败！');
					    	   }
				    		});
				   	   }
				   }
			   },{
				text: '取消',
				iconCls: 'icon_cancel',
				handler: function() {
					var grid=Ext.getCmp(config.title_id);
					grid.hide(); 
				}.createDelegate(this)
			}]
		});

		UpForm.superclass.constructor.call(this, newConfig);
	}
}); 

2.控制层

package com.alensic.nursing.sysmgr;

import java.io.IOException;
import java.util.Iterator;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.tomcat.util.http.fileupload.DiskFileUpload;
import org.apache.tomcat.util.http.fileupload.FileItem;

/**
 * 人员信息导入
 * @author 
 *
 */

public class NurseInfoImportServlet extends HttpServlet {

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
		      throws ServletException, IOException {
		doPost(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response)  
            throws IOException, ServletException {  
        try {  
        	String tempPath = "";
            System.out.println("开始进行文件上传");  
            request.setCharacterEncoding("UTF-8");
            DiskFileUpload fu = new DiskFileUpload();  
            fu.setSizeMax(100*1024*1024); // 设置最大文件尺寸，这里是4MB  
            fu.setSizeThreshold(4096); // 设置缓冲区大小，这里是4kb  
            fu.setRepositoryPath(tempPath); // 设置临时目录  
            List fileItems = fu.parseRequest(request); // 得到所有的文件：  
            Iterator i = fileItems.iterator();  
            // 依次处理每一个文件：  
            while (i.hasNext()) {  
                FileItem fi = (FileItem) i.next();  
                String fileName = fi.getName();// 获得文件名，这个文件名包括路径：  
                if (fileName != null) {  
                	ImportExcel importNurse = new ImportExcel();
                    importNurse.importExcel(fi.getInputStream());  
                }
            }
        } catch (Exception e) {  
            e.printStackTrace();  
            // 可以跳转出错页面  
            response.getWriter().print("{success:flase,message:'失败'}");  
        }  

    }  
}

3.导入

package com.alensic.nursing.sysmgr;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.helix.core.context.MyApplicationContext;

import com.alensic.nursing.hrmgr.NurseInfoDAO;

/**
 * 人员信息导入excel
 * @author zhchen
 *
 */
public class ImportExcel {

	private Map map = new HashMap();

	//错误信息
	private List<Map> errorList = new ArrayList<Map>();

	//excel中字段与数据库中的字段对应
	private String excelRelationDataBase[][]={{"姓名","name"},{"性别","gender"},{"出生日期","birthday"}};

	/**
	 * 根据传入的文件创建工作簿
	 * 
	 * @author zhchen
	 * @param filename
	 * @return
	 */
	public List importExcel(InputStream filename){	
		try {
			HSSFWorkbook wb = new HSSFWorkbook(filename);
			return importExcel(wb);
		}catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 导入excel
	 * 
	 * @author zhchen
	 * @param wb
	 * @param user
	 * @return
	 */
    public List importExcel(HSSFWorkbook wb) {
		map.clear();
		errorList.clear();
		List addList = new ArrayList();
		HSSFSheet sheet = wb.getSheetAt(0);
		int firstRowNum = sheet.getFirstRowNum();
	    int lastRowNum = sheet.getLastRowNum();
	    //数组转换成集合
	    Map excelMap = this.ArrayToMap(excelRelationDataBase);
	    //取得excel表头对应的序列号和表对应的字段
	    Map excelToData = this.excelHeaderToMap(sheet, excelMap, 0);
		for(int h = firstRowNum +1; h <= lastRowNum ;h++){
				Map dataMap = new HashMap();
		    	HSSFRow dataRow = sheet.getRow(h);
		    	//遍历需要保存到数据库的字段
		    	Iterator it = excelToData.keySet().iterator();
		    	for(;it.hasNext();){
		    		int index = Integer.valueOf(it.next().toString());
		    		dataMap.put(excelToData.get(index), dataRow.getCell(index));
		    	}
		    	if(!dataMap.isEmpty())
		    		addList.add(dataMap);
	    }
	    //保存
		NurseInfoDAO nurseInfoDAO = dogetNurseDAO();
		nurseInfoDAO.createBatch(addList);
		return null;
	}



	/**
	 * 数组转换成集合
	 * 
	 * @author zhchen
	 */
	private Map ArrayToMap(String array[][]){
		Map map = new HashMap();
		int length = array.length;
		for(int i=0;i<length;i++){
			map.put(array[i][0], array[i][1]);
		}
		return map;
	}

	/**
	 * 取得excel表头对应的序列号和表对应的字段
	 * 
	 * @author zhchen
	 * @param sheet 工作表
	 * @param excelmap 需要导入字段的集合
	 * @param rowNum 取excel第几行的表头
	 * 
	 * @return
	 */
	private Map excelHeaderToMap(HSSFSheet sheet,Map excelmap,int rowNum){
		Map map = new HashMap();
		HSSFRow dataRow = sheet.getRow(rowNum);
		int firstCellNum = dataRow.getFirstCellNum();
    	int lastCellNum = dataRow.getLastCellNum();
    	for(int i=firstCellNum;i<lastCellNum;i++){
    		String headerName = dataRow.getCell(i).toString();
    		Object tableHeaderName = excelmap.get(headerName);
    		if(tableHeaderName != null){
    			map.put(i, tableHeaderName);
    		}
    	}
		return map;
	}

	/**
	 * 取得DAO
	 * 
	 * @author zhchen
	 */
	private NurseInfoDAO dogetNurseDAO(){
		return (NurseInfoDAO)MyApplicationContext.getContext().getBean("nurseInfoDAO");
	}
}