poixlsdb.java

poixls.从xls文件中读出文件

			rs = ps.executeQuery();
			while (rs.next()) {
				py1 = rs.getString("py_code");// .substring(0,1);
			}
		} catch (SQLException ex) {
			// logger.error(ex.getMessage());
			throw new Exception("添加入库错误");
		} finally {
			if (ps != null) {
				ps.close();
			}
			if (rs != null)
				rs.close();
			if (conn != null)
				conn.close();
		}
		return py1;
	}
	
	
	/**
	 * 判断是否是城市
	 * @param conn
	 * @param city
	 * @return
	 * @throws Exception
	 */
	private boolean isCity(Connection conn, String city) throws Exception {

		// String sql="select bran_name from EMAP_MGR_BRANCH ";
		String sql = "select dist_code  from EMAP_MGR_DIST where dist_name like '"+city+"%";
		PreparedStatement ps = null;
		ArrayList alist = new ArrayList();
		long code = 0;
		boolean isCity=false;
		ResultSet rs = null;
		try {
			// ps=dataCon.getcon().prepareStatement(sql);
			ps = conn.prepareStatement(sql);

			rs = ps.executeQuery();
			while (rs.next()) {
				code = rs.getLong("dist_code");// .substring(0,1);
			}
		} catch (SQLException ex) {
			// logger.error(ex.getMessage());
			throw new Exception("isCity查询库错误");
		} finally {
			if (ps != null) {
				ps.close();
			}
			if (rs != null)
				rs.close();
			if (conn != null)
				conn.close();
		}
		if (code>0){
			isCity=true;
		}
		return isCity;
	}
	
	private long getBranch(Connection conn, String branchname) throws Exception {

			// String sql="select bran_name from EMAP_MGR_BRANCH ";
			String sql = "select bran_code  from BRAN_CODE where BRAN_name like '"+branchname+"%";
			PreparedStatement ps = null;
			ArrayList alist = new ArrayList();
			long code = 0;
			
			ResultSet rs = null;
			try {
				// ps=dataCon.getcon().prepareStatement(sql);
				ps = conn.prepareStatement(sql);

				rs = ps.executeQuery();
				while (rs.next()) {
					code = rs.getLong("bran_code");// .substring(0,1);
				}
			} catch (SQLException ex) {
				// logger.error(ex.getMessage());
				throw new Exception("getBranch库错误");
			} finally {
				if (ps != null) {
					ps.close();
				}
				if (rs != null)
					rs.close();
				if (conn != null)
					conn.close();
			}

			return code;
		}
	
	public static boolean iscity2(String city){
		boolean iscity=false;
		for(int i=0;i<staticcitys.length;i++){ 
			if (city.indexOf("市")>-1){
				if (staticcitys[i].equals(city)){
					iscity=true;
				}
			}else{
				city=city+"市";
				if (staticcitys[i].equals(city)){
					iscity=true;
				}
			}
			
		} 
		return iscity;
	}
	
	public static String  getbrancode(String branname){
		String brancode="";
		for(int i=0;i<staticbranchs.length;i++){ 

				
				if (staticbranchs[i].equals(branname)){
					
					brancode=staticbranchs[i-1].trim();
				}
			
			
		} 
		return brancode;
	}
	/**
	 * 城市的静态列表,上海交行
	 */
	static String[] staticcitys = {
		"北京市",
		"天津市",
		"石家庄市",
		"唐山市",
		"秦皇岛市",
		"太原市",
		"晋城市",
		"呼和浩特市",
		"包头市",
		"沈阳市",
		"鞍山市",
		"抚顺市",
		"丹东市",
		"锦州市",
		"营口市",
		"辽阳市",
		"大连市",
		"长春市",
		"吉林市",
		"哈尔滨市",
		"齐齐哈尔市",
		"大庆市",
		"上海市",
		"南京市",
		"徐州市",
		"连云港市",
		"扬州市",
		"泰州市",
		"南通市",
		"镇江市",
		"常州市",
		"盐城市",
		"苏州市",
		"无锡市",
		"杭州市",
		"温州市",
		"嘉兴市",
		"湖州市",
		"绍兴市",
		"台州市",
		"金华市",
		"宁波市",
		"合肥市",
		"芜湖市",
		"蚌埠市",
		"淮南市",
		"安庆市",
		"马鞍山市",
		"福州市",
		"泉州市",
		"厦门市",
		"南昌市",
		"景德镇市",
		"新余市",
		"九江市",
		"济南市",
		"淄博市",
		"潍坊市",
		"通化市",
		"烟台市",
		"威海市",
		"济宁市",
		"泰安市",
		"青岛市",
		"郑州市",
		"洛阳市",
		"武汉市",
		"黄石市",
		"宜昌市",
		"襄樊市",
		"长沙市",
		"岳阳市",
		"广州市",
		"珠海市",
		"汕头市",
		"东莞市",
		"中山市",
		"佛山市",
		"揭阳市",
		"惠州市",
		"江门市",
		"深圳市",
		"南宁市",
		"柳州市",
		"桂林市",
		"梧州市",
		"北海市",
		"海南省",
		"重庆市",
		"成都市",
		"自贡市",
		"攀枝花市",
		"贵阳市",
		"遵义市",
		"昆明市",
		"曲靖市",
		"玉溪市",
		"西安市",
		"咸阳市",
		"兰州市",
		"银川市",
		"乌鲁木齐市",
		"延边市",
		"楚雄市"
};
	/**
	 * 上海分行列表信息
	 */
	static String[] staticbranchs = {
		"	0516	","宁夏分行"	,
		"	1	","淮南分行"	,
		"	103	","江西省分行"	,
		"	109	","重庆市分行"	,
		"	112	","常州分行"	,
		"	1138	","华南审计部"	,
		"	116	","河北省分行"	,
		"	118	","梧州分行"	,
		"	12	","山西省分行"	,
		"	128	","河南省分行"	,
		"	1304	","华西授信中心"	,
		"	131	","自贡分行"	,
		"	133	","泰安分行"	,
		"	136	","黑龙江省分行"	,
		"	1371	","华西审计部"	,
		"	138	","湖北省分行"	,
		"	14	","丹东分行"	,
		"	141	","天津市分行"	,
		"	144	","吉林省分行"	,
		"	148	","无锡分行"	,
		"	150	","苏州分行"	,
		"	153	","秦皇岛分行"	,
		"	16	","洛阳分行"	,
		"	163	","海南省分行"	,
		"	169	","潍坊分行"	,
		"	1716	","惠州分行"	,
		"	172	","安庆分行"	,
		"	176	","桂林分行"	,
		"	18	","新余分行"	,
		"	183	","鞍山分行"	,
		"	1865	","华中授信中心"	,
		"	187	","连云港分行"	,
		"	193	","大庆分行"	,
		"	195	","宜昌分行"	,
		"	20	","陕西省分行"	,
		"	202	","九江分行"	,
		"	208	","东北审计部"	,
		"	210	","泰州分行"	,
		"	22	","江苏省分行"	,
		"	222	","扬州分行"	,
		"	24	","汕头分行"	,
		"	242	","遵义分行"	,
		"	245	","镇江分行"	,
		"	256	","黄石分行"	,
		"	258	","新疆分行"	,
		"	260	","吉林分行"	,
		"	2697	","华东授信中心"	,
		"	271	","华北审计部"	,
		"	274	","贵州省分行"	,
		"	28	","锦州分行"	,
		"	284	","烟台分行"	,
		"	287	","太平洋卡中心"	,
		"	296	","延边分行"	,
		"	298	","中山分行"	,
		"	3	","岳阳分行"	,
		"	30	","云南省分行"	,
		"	3022	","盐城分行"	,
		"	3027	","华北授信中心"	,
		"	307	","柳州分行"	,
		"	318	","芜湖分行"	,
		"	32	","嘉兴分行"	,
		"	321	","华东审计部"	,
		"	323	","台州分行"	,
		"	342	","厦门分行"	,
		"	350	","华中审计部"	,
		"	36	","济宁分行"	,
		"	361	","楚雄分行"	,
		"	369	","曲靖分行"	,
		"	3719	","东京分行"	,
		"	38	","营口分行"	,
		"	388	","威海分行"	,
		"	3915	","新加坡分行"	,
		"	40	","香港分行"	,
		"	404	","淄博分行"	,
		"	4146	","马鞍山分行"	,
		"	42	","深圳分行"	,
		"	4223	","伦敦分行"	,
		"	423	","徐州分行"	,
		"	4261	","纽约分行"	,
		"	427	","泉州分行"	,
		"	44	","东莞分行"	,
		"	46	","珠海分行"	,
		"	477	","景德镇分行"	,
		"	49	","北京市分行"	,
		"	5	","福建省分行"	,
		"	51	","广西分行"	,
		"	525	","抚顺分行"	,
		"	54	","南通分行"	,
		"	550	","唐山分行"	,
		"	556	","攀枝花分行"	,
		"	56	","湖南省分行"	,
		"	58	","安徽省分行"	,
		"	591	","北海分行"	,
		"	60	","四川省分行"	,
		"	63	","青岛分行"	,
		"	649	","华南授信中心"	,
		"	65	","浙江省分行"	,
		"	675	","晋城分行"	,
		"	678	","辽阳支行"	,
		"	69	","上海市分行"	,
		"	7	","山东省分行"	,
		"	71	","绍兴分行"	,
		"	73	","甘肃省分行"	,
		"	75	","广东省分行"	,
		"	77	","包头分行"	,
		"	772	","法兰克福分行"	,
		"	791	","揭阳支行"	,
		"	793	","玉溪分行"	,
		"	866	","蚌埠分行"	,
		"	89	","佛山分行"	,
		"	898	","金华分行"	,
		"	9	","大连分行"	,
		"	9001	","江门分行"	,
		"	91	","温州分行"	,
		"	93	","齐齐哈尔分行"	,
		"	95	","湖州分行"	,
		"	951	","内蒙古分行"	,
		"	97	","辽宁省分行"	,
		"	977	","咸阳分行"	,
		"	99	","宁波分行"	
		
	};
	
	

}