POI 写的excel导入通用方法(附带jquery的ajaxfileupload上传excel文件)

1.jsp使用jquery的ajaxfileupload插件

function importExcel(){
		if(CheckForTestFile()){
			var nwOfficeId = jQuery('#nwOfficeId')[0].value;
			$.ajaxFileUpload({
                url:'${pageContext.request.contextPath}/juasum.do?method=importExcel',             //需要链接到服务器地址
                secureuri:false,
                fileElementId:'fileUpload',                   //文件选择框的id属性
                dataType: 'text',                             //服务器返回的格式，可以是json
                data:{nwOfficeId:nwOfficeId},
                success: function (data, status) {   
                	 $('#result').html(data).attr("class",'error');
                },
                error: function (data, status, e){
               		 $('#result').html("上传失败!").attr("class",'error');
                }
              }
            );

		}
	}

只有部分代码 详细的请baidu jquery ajaxfileupload

2.action struts1的做法

response.setContentType("text/html;charset=UTF-8");
		response.setCharacterEncoding("UTF-8");
		PrintWriter out = response.getWriter();
		FormFile[] f = (FormFile[])dynaMap.get("fileUpload");
		try {
			POIFSFileSystem in = new POIFSFileSystem(f[0].getInputStream());
			//有效数据总共9列
			Map<Integer, Map<Integer, List<String>>> map = ExcelMap.getExcel(in, 9);
			//导入数据从第3行开始
			String res = ssoUserService.importExcel(map.get(0),3,Integer.parseInt(dynaMap.get("nwOfficeId")+""));
			if("success".equals(res)){
				out.print("导入成功!");
			}else{
				out.print(res);
			}
			out.flush();
			out.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} 
		return null;

3.service

	public String importExcel(Map<Integer, List<String>> map, int i, int nwOfficeId) {
		String res = "";
		Map<String, Integer> map1 = new HashMap<String, Integer>();
		for (Entry<Integer, List<String>> ent : map.entrySet()) {
			String login_name = ent.getValue().get(0);
			if(map1.containsKey(login_name)){
				res = "第"+map1.get(login_name)+"行第1列与第"+(ent.getKey()+1)+"行第1列重复";
				return res;
			}else{
				map1.put(login_name, ent.getKey()+1);
			}
		}
		List<SSOUser> ssoUserlist = new ArrayList<SSOUser>();
		SSOUser ssoUser = null;
		List<String> list = null;
		for (Entry<Integer, List<String>> ent : map.entrySet()) {
			if(ent.getKey()>=i-1){
				ssoUser = new SSOUser();
				list = ent.getValue();
				String username = list.get(0);      //用户名
				String nickname = list.get(1);      //昵称
				String email = list.get(2);         //邮箱
				String sex = list.get(3);           //性别
				String regDate = list.get(4);       //注册时间
				String phone = list.get(5);         //电话
				String age = list.get(6);           //年龄
				String address = list.get(7);       //地址
				String postcode = list.get(8);      //邮编
				if("".equals(username)){
					res = "第"+(ent.getKey()+1)+"行,第1列格式错误";
					return res;
				}else{
					username = nwOfficeId+"-"+username;
					int count = ssoUserDao.countSSOUserByLoginName(username);
					if (count > 0) {
						res = "第"+(ent.getKey()+1)+"行,第1列用户名重复";
						return res;
					} else {
						ssoUser.setLoginName(username);
					}
				}
				if("".equals(nickname)){
					res = "第"+(ent.getKey()+1)+"行,第2列格式错误";
					return res;
				}else{
					ssoUser.setNickname(nickname);
				}
				//判断邮箱格式
				if(!email.matches("^([\\.a-zA-Z0-9_-])+@([a-zA-Z0-9_-])+(\\.[a-zA-Z0-9_-]+)+{1}quot;)){
					res = "第"+(ent.getKey()+1)+"行,第3列格式错误";
					return res;
				}else{
					ssoUser.setEmail(email);
				}
				//判断性别
				if("男".equals(sex)){
					ssoUser.setSex(0);
				}else if("女".equals(sex)){
					ssoUser.setSex(1);
				}else{
					res = "第"+(ent.getKey()+1)+"行,第4列格式错误";
					return res;
				}
				//判断注册时间
				if("".equals(regDate)){
					res = "第"+(ent.getKey()+1)+"行,第5列格式错误";
					return res;
				}else{
					if(!regDate.matches("^\\w{3}\\s\\w{3}\\s\\d{2}\\s(\\d{2}\\:){2}\\d{2}\\sCST\\s\\d{4}{1}quot;)){
						res = "第"+(ent.getKey()+1)+"行,第5列格式错误";
						return res;
					}else{
						SimpleDateFormat sdf = new SimpleDateFormat("EEE MMM dd HH:mm:ss 'CST' yyyy", Locale.US); 
						try {
							ssoUser.setRegDate(sdf.parse(regDate));
						} catch (ParseException e) {
							e.printStackTrace();
						}
					}
				}
				Map<String, String> metadata = new HashMap<String,String>();
				//判断电话格式
				String phone_res = getFormat(phone,"^([0-9]{3,4}\\-)?[0-9]{7,8}$|^[0-9]{11}{1}quot;);
				if("error".equals(phone_res)){
					res = "第"+(ent.getKey()+1)+"行,第6列格式错误";
					return res;
				}else{
					metadata.put("phone", phone_res);
				}
				//判断年龄格式
				String age_res = getFormat(age,"^\\d{1,3}{1}quot;);
				if("error".equals(age_res)){
					res = "第"+(ent.getKey()+1)+"行,第7列格式错误";
					return res;
				}else{
					metadata.put("age", age_res);
				}
				metadata.put("address", address);
				//判断邮编格式
				String postcode_res = getFormat(postcode,"^[1-9]\\d{5}{1}quot;);
				if("error".equals(postcode_res)){
					res = "第"+(ent.getKey()+1)+"行,第9列格式错误";
					return res;
				}else{
					metadata.put("yb", postcode_res);
				}
				ssoUser.setMetadata(metadata);
				ssoUser.setNwOfficeId(nwOfficeId);
				ssoUserlist.add(ssoUser);
			}
		}
		int m = ssoUserDao.save(ssoUserlist);
		System.out.println(m);
		return "success";
	}

4.将上传的excel转换成map（公用部分，适用于所有模板）

/***
	 * 
	 * @param in   POIFSFileSystem
	 * @param max  所导入excel的列数
	 * @return map
	 * @throws IOException
	 */
	public static Map<Integer, Map<Integer, List<String>>> getExcel(POIFSFileSystem in,int max)
			throws IOException {
		Map<Integer, Map<Integer, List<String>>> map = new HashMap<Integer, Map<Integer, List<String>>>();// 总map
		Map<Integer, List<String>> sheetMap = null;// 每个sheet的map
		List<String> list = null;// 每行一个list
		HSSFWorkbook workBook = null;
		try {
			workBook = new HSSFWorkbook(in);
		} catch (final Exception e) {
			throw new IOException("读取上传文件失败");
		}
		/**
		 * 获得Excel中工作表个数
		 */
		// sheet.autoSizeColumn(( short ) 0 );//导出自动适应宽度
		int sheetSize = workBook.getNumberOfSheets();
//		System.out.println("工作表个数 :" + sheetSize);
		for (int i = 0; i < sheetSize; i++) {
			sheetMap = new HashMap<Integer, List<String>>();
//			System.out.println("工作表名称:" + workBook.getSheetName(i));
			HSSFSheet sheet = workBook.getSheetAt(i);
			int rows = sheet.getPhysicalNumberOfRows(); // 获得行数
			if (rows > 0) {
				for (int j = 0; j < rows; j++) { // 行循环
					list = new ArrayList<String>();
					HSSFRow row = sheet.getRow(j);
					if (row != null) {
						int cells = row.getLastCellNum();// 获得列数
						if(cells<max){
							cells = max;
						}
						for (short k = 0; k < cells; k++) { // 列循环
							HSSFCell cell = row.getCell(k);
							String value = "";
							if(cell != null){
								switch (cell.getCellType()) {
								case HSSFCell.CELL_TYPE_NUMERIC: // 数值型
									if (HSSFDateUtil.isCellDateFormatted(cell)) {
										// 如果是date类型则 ，获取该cell的date值
										value = HSSFDateUtil.getJavaDate(
												cell.getNumericCellValue())
												.toString();
									} else {// 纯数字
										value = String.valueOf(cell
												.getNumericCellValue());
									}
									if(value.matches("^((\\d+\\.?\\d+)[Ee]{1}(\\d+)){1}quot;)){
										DecimalFormat df = new DecimalFormat("#.##");
										value = df.format(Double.parseDouble(value));
									}
									break;
								case HSSFCell.CELL_TYPE_STRING: // 字符串型
									value = cell.getRichStringCellValue()
											.toString().trim();
									break;
								case HSSFCell.CELL_TYPE_FORMULA:// 公式型
									// 读公式计算值
									value = String.valueOf(cell
											.getNumericCellValue());
									if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
										value = cell.getRichStringCellValue()
												.toString();
									}
									break;
								case HSSFCell.CELL_TYPE_BOOLEAN:// 布尔
									value = "" + cell.getBooleanCellValue();
									break;
								/* 此行表示该单元格值为空 */
								case HSSFCell.CELL_TYPE_BLANK: // 空值
									value = "";
									break;
								case HSSFCell.CELL_TYPE_ERROR: // 故障
									value = "";
									break;
								default:
									value = cell.getRichStringCellValue()
											.toString().trim();
								}
							}
							list.add(value);
						}
						if(!isAllNull(list)){
							sheetMap.put(j, list);
						}
					}
				}
			}
			map.put(i, sheetMap);
		}
		return map;
	}
	/**
	 * 如果list里面的值全为空 则范围true 反之则为false
	 * @param l list
	 * @return
	 */
	private static boolean isAllNull(List<String> l){
		int i=0;
		for(String s : l){
			if(!"".equals(s)){
				i++;
			}
		}
		if(i>0){
			return false;
		}
		return true;
	}

	public static void main(String[] args) {
		String filePath = "d:/template.xls";
		try {
			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filePath)); 
			Map<Integer, Map<Integer, List<String>>> map = getExcel(fs,9);
			for(Entry<Integer, List<String>> ent : map.get(0).entrySet()){
				System.out.println(ent);
			}
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}

本版本采用poi-3.6-20091214.jar jar包
excel2003 2007均适用

转自: http://blog.csdn.net/cbxjj/article/details/6890351