readexcel.java

解释xls文件

package cn.com.bj95ol.wap.readexcel;

// 读取Excel的类    
import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import javax.servlet.http.HttpServletRequest;

import jxl.Cell;
import jxl.LabelCell;
import jxl.Sheet;
import jxl.Workbook;

public class ReadExcel {

	private Connection conn = null;

	private Statement stmt = null;

	// public boolean Insert() {
	//
	// try {
	// ArrayList list = this.parsePic();
	//
	// StringBuffer su = null;
	// DBManager db = new DBManager();
	// conn = db.getConnection();
	// stmt = conn.createStatement();
	// for (int i = 0; i < list.size(); i++) {
	// su = new StringBuffer();
	// Mobilemeter read = (Mobilemeter) list.get(i);
	// su.append("insert into DB_WAP.test values");
	// su.append("(");
	// su.append("'" + read.getMobileID() + "',");
	// su.append("'" + read.getModel() + "',");
	// su.append("'" + read.getShortUA() + "',");
	// su.append("'" + read.getFullUserAgent() + "',");
	// su.append("'" + read.getScreenSize() + "',");
	// su.append("'" + read.getColorDepth() + "',");
	// su.append("'" + read.getProFile() + "',");
	// su.append("'" + read.getWapVer() + "',");
	// su.append("'" + read.getVerdorID() + "',");
	// su.append("'" + read.getNetType() + "',");
	// su.append("'" + read.getJ2meMIDP() + "',");
	// su.append("'" + read.getMmsSupported() + "',");
	// su.append("'" + read.getCameraSupported() + "'");
	// su.append(")");
	// // System.out.println(su.toString());
	// stmt.executeUpdate(su.toString());
	// }
	// return true;
	// } catch (Exception e) {
	// e.printStackTrace();
	// return false;
	// }
	// }

	// public ArrayList interFormat() {
	//
	// try {
	// DBManager db = new DBManager();
	// conn = db.getConnection();
	// stmt = conn.createStatement();
	//
	// ArrayList list = new ArrayList();
	// StringBuffer su = null;
	// // Mobilemeter mobile = (Mobilemeter) list.get(0);
	// su = new StringBuffer();
	// su.append("select te.fld_MobileID from DB_WAP.test te");
	// ResultSet rs = stmt.executeQuery(su.toString());
	// while (rs.next()) {
	// Mobilemeter mobile = new Mobilemeter();
	// mobile.setMobileID(rs.getString("fld_MobileID"));
	// list.add(mobile);
	// // System.out.println(mobile.getMobileID());
	// }
	// // System.out.println(su.toString());
	// // if (mobile.getResourcePic().equals("") == false) {
	// // mobile.setResourceType("1");
	// // }
	// // su.append("insert into DB_WAP.T_Mobile_Format_test values");
	// // su.append("(");
	// // su.append("'" + mobile.getMobileID() + "',");
	// // su.append("'" + mobile.getResourceType() + "',");
	// // su.append("'" + mobile.getPicFormat() + "',");
	// // su.append("'" + mobile.getPriority() + "',");
	// // su.append("'" + mobile.getCaption() + "'");
	// // su.append(")");
	// // System.out.println(mobile.getMobileID() + " "
	// // + mobile.getResourceType() + " " + mobile.getFormatID()
	// // + " " + mobile.getPriority() + " " + mobile.getCaption());
	// // stmt.executeUpdate(su.toString());
	// return list;
	// } catch (Exception e) {
	// e.printStackTrace();
	// return null;
	// }
	// }

	// public ArrayList type() {
	// try {
	// StringBuffer su = new StringBuffer();
	// Mobilemeter mobile = new Mobilemeter();
	// ArrayList list = this.parsePic();
	// DBManager db = new DBManager();
	// ArrayList list2 = null;
	// // for(int i = 1;i < list.size();i++){
	// // mobile = (Mobilemeter)list.get(i);
	// // // if(mobile.get)
	// // su.append("select fld_FormatID from DB_WAP.t_adapt_format ");
	// // su.append("where fld_FormatSize = '"+mobile.getPicFormat()+"'");
	// // System.out.println(su.toString());
	// // conn = db.getConnection();
	// // stmt = conn.createStatement();
	// // ResultSet rs = stmt.executeQuery(su.toString());
	// // new ArrayList();
	// // while(rs.next()){
	// // mobile = (Mobilemeter)list.get(0);
	// //
	// // mobile.setFormatID(rs.getInt(1));
	// // list2.add(mobile);
	// // System.out.println(mobile.getFormatID());
	// // }
	// // }
	//
	// return list2;
	// } catch (Exception e) {
	// e.printStackTrace();
	// return null;
	// }
	// }

	public ArrayList byallPic() {
		try {
			ArrayList listparse = this.parsePic();
			Mobilemeter mobile = null;
			String picformat = "";
			String resourcetype = "";
			StringBuffer su = null;
			String formatid = "";
			String priority = "";
			String caption = "";
			Mobilemeter mo = null;
			for (int i = 0; i < listparse.size(); i++) {
				mobile = (Mobilemeter) listparse.get(i);
				String mobileid = mobile.getMobileID();
				priority = mobile.getPriority();
				caption = mobile.getCaption();
				String resourcepic = mobile.getResourcePic(); // jpg
				String[] arry = resourcepic.split("/");
				// String resource = arry[resourcepic.length()];
				// System.out.print(arry.length+" ");
				// System.out.println("resourcepic: "+resourcepic);

				if (resourcepic.equals("jpg")) {
					resourcetype = "1";
				}
				picformat = mobile.getPicFormat().replaceAll("\\u002A", "x");
				// System.out.println(i + " " + picformat);
				DBManager db = new DBManager();
				conn = db.getConnection();
				stmt = conn.createStatement();
				su = new StringBuffer();
				su.append("select fld_FormatID from DB_WAP.T_Adapt_Format ");
				su.append("where fld_FormatSize = '" + picformat + "'");
				ResultSet rs = stmt.executeQuery(su.toString());
				ArrayList list2 = new ArrayList();
				while (rs.next()) {
					mo = new Mobilemeter();
					mo.setFormatID(rs.getInt(1));
					// System.out.println("x: "+mo.getFormatID());
					list2.add(mo);
					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("'" + mo.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 listparse;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	public ArrayList byallRing() {

		try {
			ArrayList listparse = this.parseRing();
			Mobilemeter mobile = null;
			String ringformat = "";
			String resourcetype = "3";
			StringBuffer su = null;
			String priority = "";
			String caption = "";
			Mobilemeter mo = null;
			for (int i = 0; i < listparse.size(); i++) {
				mobile = (Mobilemeter) listparse.get(i);
				priority = mobile.getPriority();
				caption = mobile.getCaption();
				String resourcering = mobile.getResourceRing();
				int mobilegetformat = mobile.getFormatID();
				String[] ayy = new String[20];
				ayy = resourcering.split("\\/");
				String ring = "";
				DBManager db = new DBManager();
				conn = db.getConnection();
				stmt = conn.createStatement();
				String ringgetformat = mobile.getRingFormat();
				for (int j = 0; j < ayy.length; j++) {
					su = new StringBuffer();
					ring = ayy[j].toUpperCase();

					if (ring.equals("MP3")) {
						ringformat = ring + "-1";
					}else if(ring.equals("MIDI")){
						ring = "MID";
						if(Integer.parseInt(String.valueOf(mobile.getRingFormat())) == 24
								||Integer.parseInt(String.valueOf(mobile.getRingFormat())) == 32
								||Integer.parseInt(String.valueOf(mobile.getRingFormat())) == 64
								){
							ringgetformat = "16";
						}
						ringformat = ringgetformat + ring;
					}else{
						ringformat = ringgetformat + ring;
					}
					su
							.append("select fld_FormatID from DB_WAP.T_Adapt_Format ");
					su.append("where fld_FormatSize = '" + ringformat + "'");
					System.out.println(i + " " + "SQLRing" + j + ": "
							+ su.toString());
					ResultSet rs = stmt.executeQuery(su.toString());
					ArrayList list2 = new ArrayList();
					while (rs.next()) {
						mo = new Mobilemeter();
						mo.setFormatID(rs.getInt(1));
						System.out.println(i + " " + "x: " + mo.getFormatID());
//						System.out.println(i + " " + mobile.getFormatID());
						list2.add(mo);
						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("'" + mo.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(i + " " + "SQL3__: "
								+ str.toString());
					}

				}

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

	public ArrayList byallVideo() {

		try {
			ArrayList listparse = this.parseVideo();
			Mobilemeter mobile = null;
			String videoformat = "";
			String resourcetype = "4";
			StringBuffer su = null;
			String priority = "";
			String caption = "";
			String formatid = "33";
			Mobilemeter mo = null;
			for (int i = 0; i < listparse.size(); i++) {
				mobile = (Mobilemeter) listparse.get(i);
				priority = mobile.getPriority();
				caption = mobile.getCaption();
				String resourcevideo = mobile.getResourcevideo();
				// System.out.println(i+" "+mobile.getResourcevideo());
				// if (resourcevideo.equals("3gp")) {
				// resourcetype = "4";
				// }
				videoformat = mobile.getVideoFormat();
				// videoformat = mobile.getPicFormat().replaceAll("\\u002A",
				// "x");
				// System.out.println(i + " " + videoformat);
				// DBManager db = new DBManager();
				// conn = db.getConnection();
				// stmt = conn.createStatement();
				// su = new StringBuffer();
				// su.append("select fld_FormatID from DB_WAP.T_Adapt_Format ");
				// su.append("where fld_FileExtension = '" + videoformat + "'");
				// System.out.println("SQLVideo: "+su.toString());
				// ResultSet rs = stmt.executeQuery(su.toString());
				ArrayList list2 = new ArrayList();
				// while (rs.next()) {
				mo = new Mobilemeter();
				// mo.setFormatID(rs.getInt(1));
				// System.out.println(i+" "+"x: "+mo.getFormatID());
				// System.out.println(i+" "+"x: "+formatid);
				// list2.add(mo);
				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("'" + formatid + "',");
				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 listparse;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

	}

	public ArrayList byall() {
		try {
			DBManager db = new DBManager();
			ResultSet rs = null;
			conn = db.getConnection();
			StringBuffer su = new StringBuffer();
			stmt = conn.createStatement();
			su.append("select fld_MobileID from DB_WAP.T_Mobile_test");
			System.out.println("SQL1___: " + su.toString());
			rs = stmt.executeQuery(su.toString());
			ArrayList listparse = this.parsePic();
			ArrayList list = new ArrayList();
			String resourcepic;
			String picformat = "";
			String resourcetype = "";
			Vector formatid = new Vector();
			// String formatid = "";
			String priority = "";