readexcel.java

解释xls文件

			String caption = "";
			Mobilemeter mobi = null;
			StringBuffer suf = null;
			Mobilemeter mo = null;
			while (rs.next()) {

				Mobilemeter mobile = new Mobilemeter();
				mobile.setMobileID(rs.getString(1));
				list.add(mobile);
				picformat = mo.getPicFormat().replaceAll("\\u002A", "x").trim(); // 128x128
				// System.out.println(rs.getString(1));
				for (int i = 0; i < listparse.size(); i++) {
					mo = (Mobilemeter) listparse.get(i);
					resourcepic = mo.getResourcePic(); // jpg

					System.out.println("x" + i + " " + picformat);
					priority = mo.getPriority();
					caption = mo.getCaption();
					if (resourcepic.equals("jpg")) {
						resourcetype = "1";
					}
					// System.out.println("formatid"+i+": "+formatid.size());
					suf = new StringBuffer();
					suf
							.append("select fld_FormatID from DB_WAP.T_Adapt_Format ");
					suf.append("where fld_FormatSize = '" + picformat + "'");
					// System.out.println(i+ " " + picformat);
					// System.out.println("SQL2___: "+suf.toString());
					conn = db.getConnection();
					stmt = conn.createStatement();
					ResultSet rst = stmt.executeQuery(suf.toString());
					while (rst.next()) {
						mobi = new Mobilemeter();
						mobi.setFormatID(rst.getInt("fld_FormatID"));
						// formatid = rst.getString(1);
						// System.out.println(mobi.getFormatID());
						formatid.add(mobi);
					}
				}

				// System.out.println(mobi.getFormatID());

				StringBuffer str = new StringBuffer();
				str.append("insert into DB_WAP.T_Mobile_Format_test values");
				str.append("(");
				str.append("'" + mobile.getMobileID() + "',");
				str.append("'" + resourcetype + "',");
				str.append("'" + mobi.getFormatID() + "',");
				str.append("'" + priority + "',");
				str.append("'" + caption + "'");
				str.append(")");
				// DBManager dba = new DBManager();
				// conn = dba.getConnection();
				// stmt = conn.createStatement();
				// stmt.executeUpdate(str.toString());
				// System.out.println("SQL3__: " + str.toString());

			}

			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	public ArrayList parseRing() {
		try {
			Workbook book = Workbook.getWorkbook(new File("ua.xls"));
			Sheet se = book.getSheet(1);
			int rownum = se.getRows();
			ArrayList list = new ArrayList();
			DBManager db = new DBManager();
			conn = db.getConnection();
			stmt = conn.createStatement();
			for (int i = 1; i < rownum; i++) {
				Mobilemeter rea = new Mobilemeter();
				rea.setMobileID(se.getCell(0, i).getContents());
				rea.setModel(se.getCell(3, i).getContents());
				rea.setShortUA(se.getCell(11, i).getContents());
				rea.setWapVer("1.2");
				rea.setVerdorID("1");
				rea.setNetType("GSM");
				rea.setJ2meMIDP("1.2");
				rea.setMmsSupported("0");
				rea.setCameraSupported("0");
				rea.setPriority("1");
				rea.setResourceRing(se.getCell(9, i).getContents());
				rea.setRingFormat(se.getCell(10, i).getContents());
				list.add(rea);

			}
			book.close();
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

	}

	public ArrayList parseVideo() {

		try {
			Workbook book = Workbook.getWorkbook(new File("ua.xls"));
			Sheet se = book.getSheet(1);
			int rownum = se.getRows();
			// Cell ce = null;
			// StringBuffer su = new StringBuffer();
			ArrayList list = new ArrayList();
			DBManager db = new DBManager();
			conn = db.getConnection();
			stmt = conn.createStatement();
			for (int i = 1; i < rownum; i++) {
				Mobilemeter rea = new Mobilemeter();
				rea.setMobileID(se.getCell(0, i).getContents());
				rea.setModel(se.getCell(3, i).getContents());
				rea.setShortUA(se.getCell(11, i).getContents());
				String arr = se.getCell(8, i).getContents();
				String ayy = arr.replaceAll("\\u002A", "x");
				// System.out.println("ayy"+i+": "+ayy);
				rea.setScreenSize(ayy);
				rea.setWapVer("1.2");
				rea.setVerdorID("1");
				rea.setNetType("GSM");
				rea.setJ2meMIDP("1.2");
				rea.setMmsSupported("0");
				rea.setCameraSupported("0");
				rea.setResourcePic("jpg");
				// rea.setResourcePic(se.getCell(7,i).getContents());
				rea.setPriority("1");
				rea.setPicFormat(se.getCell(8, i).getContents());
				rea.setResourceRing(se.getCell(9, i).getContents());
				rea.setRingFormat(se.getCell(10, i).getContents());
				rea.setResourcevideo("3gp");
				rea.setVideoFormat("3gp");
				// System.out.print(rea.getMobileID() + " ");
				// System.out.print(rea.getModel() + " ");
				// System.out.print(rea.getShortUA() + " ");
				// System.out.print(rea.getScreenSize() + " ");
				// System.out.print(rea.getWapVer() + " ");
				// System.out.print(rea.getVerdorID() + " ");
				// System.out.print(rea.getNetType() + " ");
				// System.out.print(rea.getJ2meMIDP() + " ");
				// System.out.print(rea.getMmsSupported() + " ");
				// System.out.print(rea.getCameraSupported() + " ");
				// System.out.print(rea.getResourcePic() + " ");
				// System.out.print(rea.getResourceRing() + " ");
				// System.out.print(rea.getRingFormat() + " ");
				// System.out.println(rea.getResourcevideo());
				// int j;
				// for(j = 0;j < list.size();j++){
				// if(rea.getResourcePic() == "jpg" || rea.getResourcePic() ==
				// "jpg/gif"){
				// rea.setResourceType("1");
				// }
				// if(rea.getResourceRing() == "mid"){
				// rea.setResourceType("2");
				// }
				// if(rea.getResourcevideo() == "3gp"){
				// rea.setResourceType("4");
				// }
				//					
				// }
				// System.out.print("J: "+j+" ");
				// System.out.println(rea.getResourceType());
				list.add(rea);

				// su = new StringBuffer();
				// // Mobilemeter read = (Mobilemeter) list.get(i);
				// su.append("insert into DB_WAP.test values");
				// su.append("(");
				// su.append("'" + rea.getMobileID() + "',");
				// su.append("'" + rea.getModel() + "',");
				// su.append("'" + rea.getShortUA() + "',");
				// su.append("'" + rea.getFullUserAgent() + "',");
				// su.append("'" + rea.getScreenSize() + "',");
				// su.append("'" + rea.getColorDepth() + "',");
				// su.append("'" + rea.getProFile() + "',");
				// su.append("'" + rea.getWapVer() + "',");
				// su.append("'" + rea.getVerdorID() + "',");
				// su.append("'" + rea.getNetType() + "',");
				// su.append("'" + rea.getJ2meMIDP() + "',");
				// su.append("'" + rea.getMmsSupported() + "',");
				// su.append("'" + rea.getCameraSupported() + "'");
				// su.append(")");
				// System.out.println(su.toString());
				// stmt.executeUpdate(su.toString());
			}
			book.close();
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

	}

	public ArrayList parsePic() {

		try {
			Workbook book = Workbook.getWorkbook(new File("ua.xls"));
			Sheet se = book.getSheet(1);
			int rownum = se.getRows();
			Cell ce = null;
			StringBuffer su = new StringBuffer();
			ArrayList list = new ArrayList();
			DBManager db = new DBManager();
			conn = db.getConnection();
			stmt = conn.createStatement();
			for (int i = 1; i < rownum; i++) {
				Mobilemeter rea = new Mobilemeter();
				rea.setMobileID(se.getCell(0, i).getContents());
				rea.setModel(se.getCell(3, i).getContents());
				rea.setShortUA(se.getCell(11, i).getContents());
				String arr = se.getCell(8, i).getContents();
				String ayy = arr.replaceAll("\\u002A", "x");
				// System.out.println("ayy"+i+": "+ayy);
				rea.setScreenSize(ayy);
				rea.setWapVer("1.2");
				rea.setVerdorID("1");
				rea.setNetType("GSM");
				rea.setJ2meMIDP("1.2");
				rea.setMmsSupported("0");
				rea.setCameraSupported("0");
				rea.setResourcePic("jpg");
				// rea.setResourcePic(se.getCell(7,i).getContents());
				rea.setPriority("1");
				rea.setPicFormat(se.getCell(8, i).getContents());
				rea.setResourceRing(se.getCell(9, i).getContents());
				rea.setRingFormat(se.getCell(10, i).getContents());
				rea.setResourcevideo("3gp");
				// System.out.print(rea.getMobileID() + " ");
				// System.out.print(rea.getModel() + " ");
				// System.out.print(rea.getShortUA() + " ");
				// System.out.print(rea.getScreenSize() + " ");
				// System.out.print(rea.getWapVer() + " ");
				// System.out.print(rea.getVerdorID() + " ");
				// System.out.print(rea.getNetType() + " ");
				// System.out.print(rea.getJ2meMIDP() + " ");
				// System.out.print(rea.getMmsSupported() + " ");
				// System.out.print(rea.getCameraSupported() + " ");
				// System.out.print(rea.getResourcePic() + " ");
				// System.out.print(rea.getResourceRing() + " ");
				// System.out.print(rea.getRingFormat() + " ");
				// System.out.println(rea.getResourcevideo());
				// int j;
				// for(j = 0;j < list.size();j++){
				// if(rea.getResourcePic() == "jpg" || rea.getResourcePic() ==
				// "jpg/gif"){
				// rea.setResourceType("1");
				// }
				// if(rea.getResourceRing() == "mid"){
				// rea.setResourceType("2");
				// }
				// if(rea.getResourcevideo() == "3gp"){
				// rea.setResourceType("4");
				// }
				//					
				// }
				// System.out.print("J: "+j+" ");
				// System.out.println(rea.getResourceType());
				list.add(rea);

				// su = new StringBuffer();
				// // Mobilemeter read = (Mobilemeter) list.get(i);
				// su.append("insert into DB_WAP.test values");
				// su.append("(");
				// su.append("'" + rea.getMobileID() + "',");
				// su.append("'" + rea.getModel() + "',");
				// su.append("'" + rea.getShortUA() + "',");
				// su.append("'" + rea.getFullUserAgent() + "',");
				// su.append("'" + rea.getScreenSize() + "',");
				// su.append("'" + rea.getColorDepth() + "',");
				// su.append("'" + rea.getProFile() + "',");
				// su.append("'" + rea.getWapVer() + "',");
				// su.append("'" + rea.getVerdorID() + "',");
				// su.append("'" + rea.getNetType() + "',");
				// su.append("'" + rea.getJ2meMIDP() + "',");
				// su.append("'" + rea.getMmsSupported() + "',");
				// su.append("'" + rea.getCameraSupported() + "'");
				// su.append(")");
				// System.out.println(su.toString());
				// stmt.executeUpdate(su.toString());
			}
			book.close();
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	public ArrayList parse() {

		try {
			Workbook book = Workbook.getWorkbook(new File("ua.xls"));
			Sheet se = book.getSheet(1);
			int rownum = se.getRows();
			Cell ce = null;
			StringBuffer su = new StringBuffer();
			ArrayList list = new ArrayList();
			DBManager db = new DBManager();
			conn = db.getConnection();
			stmt = conn.createStatement();
			for (int i = 1; i < rownum; i++) {
				Mobilemeter rea = new Mobilemeter();
				rea.setMobileID(se.getCell(0, i).getContents());
				rea.setModel(se.getCell(3, i).getContents());
				rea.setShortUA(se.getCell(11, i).getContents());
				String arr = se.getCell(8, i).getContents();
				String ayy = arr.replaceAll("\\u002A", "x");
				// System.out.println("ayy"+i+": "+ayy);
				rea.setScreenSize(ayy);
				rea.setWapVer("1.2");
				rea.setVerdorID("1");
				rea.setNetType("GSM");
				rea.setJ2meMIDP("1.2");
				rea.setMmsSupported("0");
				rea.setCameraSupported("0");
				rea.setResourcePic("jpg");
				// rea.setResourcePic(se.getCell(7,i).getContents());
				rea.setPriority("1");
				rea.setPicFormat(se.getCell(8, i).getContents());
				rea.setResourceRing(se.getCell(9, i).getContents());
				rea.setRingFormat(se.getCell(10, i).getContents());
				rea.setResourcevideo("3gp");
				rea.setVideoFormat("3gp");
				list.add(rea);

				su = new StringBuffer();
				su.append("insert into DB_WAP.T_Mobile_test values");
				su.append("(");
				su.append("'" + rea.getMobileID() + "',");
				su.append("'" + rea.getModel() + "',");
				su.append("'" + rea.getShortUA() + "',");
				su.append("'" + rea.getFullUserAgent() + "',");
				su.append("'" + rea.getScreenSize() + "',");
				su.append("'" + rea.getColorDepth() + "',");
				su.append("'" + rea.getProFile() + "',");
				su.append("'" + rea.getWapVer() + "',");
				su.append("'" + rea.getVerdorID() + "',");
				su.append("'" + rea.getNetType() + "',");
				su.append("'" + rea.getJ2meMIDP() + "',");
				su.append("'" + rea.getMmsSupported() + "',");
				su.append("'" + rea.getCameraSupported() + "'");
				su.append(")");
//				stmt.executeUpdate(su.toString());
				System.out.println(su.toString());
			}
			book.close();
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

	}

	public static void main(String args[]) {
		ReadExcel read = new ReadExcel();
//		 read.parse();
//		 read.byallPic();
		read.byallRing();
//		 read.byallVideo();
	}
}