java读写 excel-方法二

package com.cpic.excelTesty;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

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

import org.apache.log4j.Logger;

public class ExcelTesty {
protected static Logger log = Logger.getLogger(ExcelTesty.class);
public String more_EXCELPATH= “file/码上保-合作方生产信息表11.xls”;
public void getmoreSqlList() {
// if (channel == null || “”.equals(channel) || corecode == null || “”.equals(corecode) || ePolicy == null || “”.equals(ePolicy) || plancode1 == null || “”.equals(plancode1)) {
//
// }
// SimpleDateFormat sdf = new SimpleDateFormat();
List insertXmList = new ArrayList();
List rollbackXmList = new ArrayList();
// List xmList = new ArrayList();
// StringBuffer sb = new StringBuffer();
try {
InputStream is = new FileInputStream(more_EXCELPATH); // savePath是文件的绝对路径如c:/aa.xls
Workbook wb = Workbook.getWorkbook(is); // 得到工作薄
Sheet[] sts = wb.getSheets(); // 获得所有的工作表
Sheet st = sts[0];// 得到工作薄中的第一个工作表 （有多个表的时候遍历sts）
int rsRows = st.getRows(); // 得到excel的总行数
int rsColumns = st.getColumns();
for (int i = 1; i < rsRows; i++) {
List xlsList = new ArrayList();
for (int j = 0; j < rsColumns; j++) {
Cell cell1 = st.getCell(j, i);// 得到工作表的单元格
String str = cell1.getContents(); // 获得单元格内的内容
String strxml = str;
if (str != null && !"".equals(str)) {
strxml = str.trim();
}
// if (j==11) {
// xmList.add(strxml);
// }
xlsList.add(strxml);
}

			//拼接sql
			//渠道				
			String sqlchannel = "insert into TP_CHANNELSETTING (CHANNELSETTINGID, CHANNELCODE, PRODUCTCODE, UNIQUECODE, SALECHANNEL, BUSINESSVARIETY, UNIFORMINSURANCECODE, THE4SCODE, AGENCYCODE, AGENCYNAME, INPUTORCODE, INPUTORNAME, MAKERCODE, MAKERNAME, UWCODE, UWNAME, CDDCODE, CDDNAME, PAYWAY, PAYCHANNEL, UNITCODE, VALIDFLAG, CREATEUSERID, CREATEDATE, UPDATEUSERID, UPDATEDATE, PRODUCTCATALOG1, PRODUCTCATALOG2, UNIQUENAME, SALESPERSON, SALESNAME,PAYBEFOREFLAG) values (seq_TP_CHANNELSETTING.Nextval, '"+xlsList.get(16)+"', '"+xlsList.get(12)+"', '"+xlsList.get(11)+"', '"+xlsList.get(17)+"', '"+xlsList.get(20)+"', '00000', null, '', '', '"+xlsList.get(2)+"', '"+xlsList.get(3)+"', '"+xlsList.get(4)+"', '"+xlsList.get(5)+"', '"+xlsList.get(6)+"', '"+xlsList.get(7)+"', '"+xlsList.get(8)+"', null, '4', null, '"+xlsList.get(1)+"', 'Y', 1, to_date('14-10-2015 02:47:45', 'dd-mm-yyyy hh24:mi:ss'), 1, to_date('14-10-2015 02:47:45', 'dd-mm-yyyy hh24:mi:ss'), '"+xlsList.get(18)+"', '"+xlsList.get(19)+"', '"+xlsList.get(0)+"分公司', null, null, '2');";
			//经办人				
			String sqlhander= "insert into TP_CHANNELHANDLER (CHANNELHANDLERID, CHANNELSETTINGID, HANDLERCODE, HANDLERNAME, PECENT, MAINFLAG, CREATEUSERID, CREATEDATE, UPDATEUSERID, UPDATEDATE) values (seq_TP_CHANNELHANDLER.Nextval, (select channelsettingid from tp_channelsetting t where channelcode = '"+xlsList.get(16)+"' and uniquecode='"+xlsList.get(11)+"' and productcode='"+xlsList.get(12)+"'), '"+xlsList.get(9)+"', '"+xlsList.get(10)+"', 1, 'Y', 1, to_date('14-10-2015 11:14:49', 'dd-mm-yyyy hh24:mi:ss'), 1, to_date('14-10-2015 11:15:03', 'dd-mm-yyyy hh24:mi:ss'));";
			//渠道方案发放				
			String sqlplan1= "insert into TP_CHANNELPRODUCTPLAN (PLANCODE, CHANNELSETTINGID, CREATEUSERID, CREATEDATE, UPDATEDATE, UPDATEUSERID, REMARK, APPNAME, ELEPAPERFLAG) values ('"+xlsList.get(13)+"', (select channelsettingid from tp_channelsetting t where channelcode = '"+xlsList.get(16)+"' and uniquecode= '"+xlsList.get(11)+"' and productcode='"+xlsList.get(12)+"'), 1, to_date('14-10-2015 00:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('14-10-2015 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 1, null, '"+xlsList.get(15)+"', '0');";
			insertXmList.add("--"+xlsList.get(0)+"分公司");
			insertXmList.add("--渠道");
			insertXmList.add(sqlchannel);
			insertXmList.add("--经办人");
			insertXmList.add(sqlhander);
			insertXmList.add("--下发方案");
			insertXmList.add(sqlplan1);
			if (xlsList.get(14) != null && !"".equals(xlsList.get(14))) {
				String sqlplan2= "insert into TP_CHANNELPRODUCTPLAN (PLANCODE, CHANNELSETTINGID, CREATEUSERID, CREATEDATE, UPDATEDATE, UPDATEUSERID, REMARK, APPNAME, ELEPAPERFLAG) values ('"+xlsList.get(14)+"', (select channelsettingid from tp_channelsetting t where channelcode = '"+xlsList.get(16)+"' and uniquecode= '"+xlsList.get(11)+"' and productcode='"+xlsList.get(12)+"'), 1, to_date('14-10-2015 00:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('14-10-2015 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 1, null, '"+xlsList.get(15)+"', '0');";
				insertXmList.add(sqlplan2);
			}
			insertXmList.add("");
			
			//回退sql
			// 渠道方案发放
			String sqlrollbackplan = "";
			if (xlsList.get(14) != null && !"".equals(xlsList.get(14))) {
				sqlrollbackplan = "delete from tp_channelproductplan t where channelsettingid in (select t.channelsettingid from tp_channelsetting t where channelcode='"+xlsList.get(16)+"' and uniquecode in ('"+xlsList.get(11)+"') and productcode in ('"+xlsList.get(12)+"')) and plancode in ('"+xlsList.get(13)+"','"+xlsList.get(14)+"');";
			}else{
				sqlrollbackplan = "delete from tp_channelproductplan t where channelsettingid in (select t.channelsettingid from tp_channelsetting t where channelcode='"+xlsList.get(16)+"' and uniquecode in ('"+xlsList.get(11)+"') and productcode in ('"+xlsList.get(12)+"')) and plancode in ('"+xlsList.get(13)+"');";
			}
			
			// 渠道经办人
			String sqlrollbackHander = "delete from tp_channelhandler t where t.channelsettingid in (select channelsettingid from TP_CHANNELSETTING t where t.channelcode in ('"+xlsList.get(16)+"')  and t.uniquecode in ('"+xlsList.get(11)+"') and t.productcode in ('"+xlsList.get(12)+"'));";
			
			// 渠道配置信息
			String sqlrollbackChannel = "delete from TP_CHANNELSETTING t where t.channelcode='"+xlsList.get(16)+"' and t.uniquecode in ('"+xlsList.get(11)+"') and t.productcode in ('"+xlsList.get(12)+"');";
			rollbackXmList.add("--渠道方案发放");
			rollbackXmList.add(sqlrollbackplan);
			rollbackXmList.add("--渠道经办人");
			rollbackXmList.add(sqlrollbackHander);
			rollbackXmList.add("--渠道配置信息");
			rollbackXmList.add(sqlrollbackChannel);
			rollbackXmList.add("");

		}

	    File f = new File("src/test/java/com/cpic/excelTesty/", getSysDateStringYMD()+"_moreInsert.sql");
	    File rollback = new File("src/test/java/com/cpic/excelTesty/", getSysDateStringYMD()+"_moreRollback.sql");
		try {
			//insert
			f.createNewFile();
			OutputStreamWriter w = new OutputStreamWriter(new FileOutputStream(f),"UTF-8");
			for(String str : insertXmList){
				w.write(str);
				w.write("\n");
			}
			w.close();
			//rollback
			rollback.createNewFile();
			OutputStreamWriter rollbackWriter = new OutputStreamWriter(new FileOutputStream(rollback),"UTF-8");
			for(String str : rollbackXmList){
				rollbackWriter.write(str);
				rollbackWriter.write("\n");
			}
			rollbackWriter.close();
		} catch (IOException e) {
			log.error(e.getMessage());
		}
	} catch (Exception e) {
		log.error(e.getMessage());
	}
}
}