exceltodb.java

java swing源码 欢迎下载 有问题请联系 我一定负责到底

package com.NCL;

import java.io.*;

import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.sinosoft.common.*;

import java.util.*;

public class ExcelToDB {
	
	String info = "22206415,22208460,22207360,08242853,01256729,01256781,04218685,08262419,01255100,08251335,01256066,01256257,08260981,01203045,01203188,01203226,01203966,01204667,01204906,01206054,01206943,01206957,01208596,01209210,01200201,01200381,01201115,01270409,01201528,01201812,01202031,01202114,01202377,01202688,08214859,08220758,08221335,08241377,08235020,36102238,31200315,08235164,36270156,08220279,36219815,08216773,08242711,312A0625,01279553,01287465,01289888,01288574,01288661,01228219,01228547,01228620,01241953,01250901,01250993,01251184,36014819,36007017,01212861,01214195,01214364,01215766,01215783,01212975,01218504,01218716,01216064,01216137,01217473,01217722,36009606,01219649,01235963,01236154,01236309,36180027,08270828,01211925,312A1411,01252398,01252524,01213680,01253920,01226011,01215086,01215201,01215255,36251098,01236906,01237100,01237755,01232537,01233206,01242187,01251380,01246419,01246636,01253713,01251647,01226786,01238069,01238098,01238911,01238917,08277339,01229627,01229985,01230062,01233431,01233523,01233881,01243033,01243601,01246838,01247153,01254828,01226877,01221720,01247263,08270659,36278193,01244398,01252706,01252711,01254042,01254147,01227729,01222487,01225141,01225570,312B0262,08270039,36219563,36076070,312D0002,36266980,36217324,36219568,36217299,08286085,312D0107,312D0061,36274950,36217257,312D0151,36217479,36217227,36130275,312D0264,312E0127,312E0704,36191395,312E0175,36236763,312E0083,08217580,23203092,08228260,nclwys,08242026,08228271,08280799,08252058,08270122,08261934,08277001,08277316,08261930,36118127,36305388,36291747,17204979,172N1256,172J4069,172J4640,172F3364,23233277,17200030,17200108,172J3174,17209747,172F3159,17200988,17201065,172F0937,172F6772,172F1419,172J4656,17209280,172P2003,172K2111,21255248,36258121,21235803,21210931,09203175,21210688,09202891,18202309,21270301,18209009,18218701,18217070,21204638,21209039,21235939,11206617,312E0533,21255380,31201184,31200084,11200029,21241075,21245699,36169187,36174857,36245918,36209023,312C0529,36233019,31200229,36070794,312A1931,36113954,312D0131,36217280,312E3210,36062615,312E0916,312F0474,312D0311,312E0316,36258656,36076320,312B0207,36244066,312C0075,312F0018,25201099,25202852,25200167,24253334,22208106,05202269,05221463,27200225,27260976,05230008,25202228,25200008,09203239,09203479,08214125,09202619,09202668,09203429,09202140,09202490,09202801,09202247,09203363,05221000,22299862,04205704,04213921,04208262,04208982,04203033,04203083,04214454,04203479,04203516,04200237,04203651,29250001,04211442,29202399,29200137,19203819,19204848,19201806,15210714,19201636,15210317,19204157,16272469,13240399,13240049,18218855,18208288,302g1225,18201318,18201719,18207005,18206310,18217033,18206850,032A6967,36002670,032D2676,032D4168,032D5618,032I6200,152B2594,032G2453,04214483,04214991,36020195,032C0721,032D8600,36004495,032A8944,36027761,15210276,06212818,30260134,30260140,302a0640,302d1085,04211964,04221228,04210255,262A1113,26208877,26206922,26206703,28216807,28220109,28228871,28216839,28232704,28215355,26220090,22292889,22276242,04221545,04222296,04214867,28216803,28220191,28220198,28219842,28228880,28231312,28231348,28204798,28206427,04201918,04222252,342A1533,28200053,04221404,27200349,28201340,04209490,28202698,26206153,27202916,28202524,28232351,06223056,28205361,04222109,04222125,04200181,04200225,04200426,04222798,04221667,03205585,03203015,04221223,04222629,28204800,03201013,04206940,04207148,04210347,04204747,04204860,03247205,04210915,04215881,04204953,04207643,06290900,04212527,06217158,04218119,04201750,04201920,04202378,04202485,04202755,32230365,04203156,04200989,04203879,22200954,22233707,22237359,22201485,22239727,262U0291,20251829,11202853,11240373,20200124,04212576,04214163,04219726,04221920,07224066,07201888,07224120,07228131,07228136,07228186,07210780,07228158,07205228,07212424,07228241";
	String excelPath = "E:/work/feizhigao/test/ldoccupation.xls";
	String sql = "insert into agent values(?,?,?,?,?,?,?,to_date(?,'yyyy-MM-dd'),?,?,?,?,'',?,?,?,?,?,?,?,?,?,?)";
	boolean isDebug = true;
	int startrow = 0;
	int startcol = 0;
	int endrow = 100000;
	int endcol = 100000;
	Md5 m = new Md5();
	String tmp = "";
	
	//分析Excel文件并入库 从startrow行startcol列开始
	public boolean FromExcelToOracle(){
		try{
			File file = new File(excelPath);
			Workbook workbook = Workbook.getWorkbook(file);
		    Sheet sheet = workbook.getSheet(0);
		    int rows=sheet.getRows();		//得到总行数
		    int cols = sheet.getColumns();	//得到总列数
		    if(endrow<rows)rows = endrow;
		    if(endcol<cols)cols = endcol;
		    List l = new ArrayList();
		    for(int i=startrow;i<rows;i++){
		    	String[] colValue = new String[cols-startcol];
		    	for(int j=startcol;j<cols;j++){
		    		colValue[j-startcol] = sheet.getCell(j,i).getContents().trim();
		    	}
		    	//TODO  对数据作特殊处理
		    	if("".equals(colValue[0]))colValue[0] = info;
		    	if("".equals(colValue[1]))colValue[1] = tmp;
		    	info = colValue[0];
		    	tmp = colValue[1];
		    	
		    	l.add(colValue);
		    }
		    file = null;
		    workbook.close();
		    workbook = null;
		    
		  	DBAccess d = new DBAccess();
		  	if(isDebug){
		  		for(int i=0;i<l.size();i++){
		  			String[] tmpStr = (String[])l.get(i);
		  			for(int j=0;j<tmpStr.length;j++){
		  				System.out.print(startrow+i+"行");
		  				System.out.print(startcol+j+"列="+tmpStr[j]+"  ");
		  			}
		  			System.out.print("\n");
		  		}
		  		System.out.println("共"+l.size()+"条数据！");
//		  		System.out.println("优秀代理人"+tmp);
		  		return true;
		  	}else{
		  		return d.mulExecute(sql,l);
		  	}
		}catch (Exception ex){
	    	ex.printStackTrace();
	      	return false;
		}
	}
	
	//分割excel文件
	public boolean formatExcel(){
		int pageRows = 10000;
		if(excelPath.length()<5)return false;
		String url1 = excelPath.substring(0,excelPath.length()-4);
		try{
			Workbook workbook = Workbook.getWorkbook(new File(excelPath));
			int nSheet = workbook.getNumberOfSheets();
			Sheet sheet = null;
			int num = 1;
			for(int i=0;i<nSheet;i++){
				List l = new ArrayList();
				sheet = workbook.getSheet(i);
				int rows = sheet.getRows();//得到总行数