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);}}); 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:'失败'}"); } } }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");}}