临时从excel插入数据库数据的方法

package com.cn.zl.controller;

import java.io.File;
import java.io.FileOutputStream;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

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

public class ImportExcel {

	public static void main(String[] args) {
		handle();
	}

	public static List<String> handle() {
		List<String> list = new ArrayList<String>();
		try {
			Workbook book = Workbook.getWorkbook(new File("E:\\export\\d.xls"));
			Sheet sheet = book.getSheet(0);

			for (int j = 1; j < sheet.getRows(); j++) {
				StringBuffer sql = new StringBuffer();
				Cell[] cells = sheet.getRow(j);
				sql.append(
						"insert into [sales].[T_SM_REPORT_AREA_DAY] (STATDAY,GROUP_ID,GROUP_NAME,AREA_ID,AREA_NAME,WZ_ID,WZ_NAME,BIGAREA_ID,BIGAREA_NAME,HLOOKPHONE,HSALESNEW,HRENTNEW,HROPPNEW,HKEYNEW,HTRACK,HTRACK_INVALID,HTRACK_VALID,HPRICE_ADJUST,HEXCL,HREG,CUSTNEW,CUSTACTIVE,CPOOL_RCV,CTRACK,HTRACK_LOOK,HTRACK_PHONE,HTRACK_INFO,HTRACK_PRICE,HEXP_PIC,HEXP_HOUTYPE,HEXP_HOUSE,HEXP_BEDROOM,HEXP_PARLOR,HEXP_TOILET,HEXP_BALCONY,CLOOKHOUSE,HOUDELALL,HOUDELVALID,HOUDELQF,SWZ_ID,SWZ_NAME,CLOOKHOUSECOUNT,CLOOKHOUSECOUNTSALE,CLOOKHOUSECOUNTRENT,CLOOKHOUSECOUNFHAND,CTRACKPRIVATE,CTRACKPOOL,CLOOKHOUSESALES,CLOOKHOUSERENT,LOOKPLANCT,CLOOKHOUSEFHAND,Custvcount,Custqcount,HSALESACTIVE,HRENTACTIVE,PERSONALFAVORCT,PERSONALFAVORTOTAL,GROUPFAVORCT,GROUPFAVORTOTAL,ACCOMPANYLOOK,ACCOMPANYLOOKSALES,ACCOMPANYLOOKRENT,CustvcountSale,CustvcountRent,HOUSOLE,hounormal,HTRACK_YSTP,HACTIVE_PERSON,HPOOL_RECEIVE,HPOOL_ASSIGN,HRES_TRANSFER,CUSTNEWA_PERSON,CPOOL_RECEIVE,CPOOL_ASSIGN,CRES_TRANSFER,CIMPORT_RECEIVE,CIMPORT_ASSIGN,housoleRent,housoleSale,hounormalRent,hounormalSale,HREGRent,HREGSale,USERNAME,EMPLOYEE_CODE,ONLINE_OUTERLOOK_HOU,ONLINE_OUTERLOOK_CUST,DEP_HIERARCHY,TOTALCALLCOUNT,SUCCESSCALLCOUNT,NOANSWERCALLCOUNT,ASuccessCallCount,QSuccessCallCount,PICKUPRAITO,TOTALDURATION,TotalCallCountCUST,SUCCESSCALLCOUNTCUST,NOANSWERCALLCOUNTCUST,PICKUPRAITOCUST,TOTALDURATIONCUST,videoNum\r\n"
								+ ") values (");
				for (int k = 1; k < cells.length; k++) {
					if (isNumeric(cells[k].getContents()) || "NULL".equals(cells[k].getContents())) {
						sql.append(cells[k].getContents());
						sql.append(",");
					} else {
						sql.append("'");
						sql.append(cells[k].getContents());
						sql.append("',");
					}
				}
				sql.deleteCharAt(sql.length() - 1).append(");");
				list.add(sql.toString());
			}

			book.close();
			insert(list);
		} catch (Exception e) {

			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 利用正则表达式判断字符串是否是数字
	 * 
	 * @param str
	 * @return
	 */
	public static boolean isNumeric(String str) {
		Pattern pattern = Pattern.compile("[0-9]*");
		Matcher isNum = pattern.matcher(str);
		if (!isNum.matches()) {
			return false;
		}
		return true;
	}

	public static void insert(List<String> list) throws ClassNotFoundException, SQLException {
		int res = 0;
		// 注册
		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		// 连接
		Connection connention = DriverManager
				.getConnection("jdbc:sqlserver://10.28.19.215:1433;databaseName=gsp_sales_dev", "dev", "dev.good");
		// 准备sql语句
		// 获取Statement对象（简称：特）
		Statement statement = connention.createStatement();
		// 执行
		for(String sql : list) {
			System.out.println(sql);
			int resultSet = statement.executeUpdate(sql);
			if(resultSet==0) {
				res+=1;
			}
		}
		System.out.println(list.size());
		System.out.println(res);
	
	
		// 关闭（倒关）
		statement.close();
		connention.close();
	}

	/**
	 * 写sql写入文件
	 * 
	 * @param buffer
	 * @throws Exception
	 */
	private static void writer(StringBuffer buffer) throws Exception {
		PrintStream out = null;
		try {
			out = new PrintStream(new FileOutputStream(new File("E://export//T_SM_REPORT_AREA_DAY.sql"), true));
			System.err.println(buffer.toString());
			out.print(buffer.toString());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (out != null) {
				out.close();
			}
		}
	}
}