1、vo-》数据库表模型

     public class POJO implements Serializable {

               对应表的属性信息

    }

2、DAO-》处理接口

    public interface POJODAO {

    public boolean POJOFromExcelToDataBase(业务参数);}

2、Impl-》接口实现类

     public class POJODAOImpl implements POJODAO {

privateConnectionconn=DataBase.getConnection();

publicbooleanPOJOFromExcelToDataBase(业务参数){

Statementsm=null;

ReadExcelreadExcel=newReadExcel();//创建读取excel实例

try{

conn.setAutoCommit(false);

sm=conn.createStatement();

List<String>sqlList=readExcel.ReadExcelToSql(excelPath,+业务参数).getSql();//读取excel返回SQL

for(Stringsql:sqlList){

sm.addBatch(sql);

}

int[]successRun=sm.executeBatch();

if(successRun.length==sqlList.size()){

conn.commit();

returntrue;

}else{

conn.rollback();

returnfalse;

}

}catch(SQLExceptione){

try{

conn.rollback();

returnfalse;

}catch(SQLExceptione1){

//TODOAuto-generatedcatchblock

e1.printStackTrace();

}

}finally{

try{

sm.close();

}catch(SQLExceptione){

//TODOAuto-generatedcatchblock

e.printStackTrace();

}

}

returnfalse;

    }

}

3、读取Excel文件信息  返回制定的Sql集合

public class ReadExcel {

privateSimpleDateFormatformat=newSimpleDateFormat("yyyy-MM-dd");

publicSqlCollectionReadExcelToSql(StringexcelPath,+业务参数){

SqlCollectionsqlCollection=newSqlCollection();//创建封装sql的集合

sqlCollection.setTableName(POJO.class.getName());

Field[]fields=POJO.class.getDeclaredFields();

StringID;

StringRCid="000000000000000000000000000000";

StringRCFIRST;

StringRCKEEP="000000000000000000000000000000";

StringBufferwrite=newStringBuffer();

StringargDate=format.format(newDate());

try{

WorkbookworkBook=Workbook.getWorkbook(newFile(excelPath));

Sheetsheet=workBook.getSheet(0);

Cellcell=null;

intcolumns=sheet.getColumns();

introws=sheet.getRows();

System.out.println("columns:"+columns+"rows:"+rows);

for(introw=1;row<rows;row++){

for(intcolumn=0;column<columns;column++){

cell=sheet.getCell(column,row);

if(column==0){

RCFIRST=cell.getContents();

ID=UUID.randomUUID().toString();

if(null!=RCFIRST&&!"".equals(RCFIRST)){

RCID=ID;

RCKEEP=RCFIRST;

write.append("row:").append(row).append("")

.append(RCKEEP);

}

sqlCollection.addData(fields[0].getName(),ID);

sqlCollection.addData(fields[1].getName(),RCID);

sqlCollection.addData(fields[2].getName(),registerID);

sqlCollection.addData(fields[3].getName(),baseID);

sqlCollection.addData(fields[4].getName(),courseID);

sqlCollection.addData(fields[5].getName(),argDate);

sqlCollection.addData(fields[6].getName(),String.valueOf(row));

sqlCollection.addData(fields[7].getName(),RCID);

sqlCollection.addData(fields[8].getName(),RCKEEP);

}else{

Stringarg0;

arg0=(null==cell.getContents()

||"".equals(cell.getContents())?"#":cell

.getContents());

System.out.println("column="+column);

System.out.println(fields[column+8].getName());

sqlCollection.addData(fields[column+8].getName(),

arg0);

write.append("").append(arg0);

}

}

sqlCollection.addSql();

System.out.println(write.toString());

write=newStringBuffer("");

}

}catch(BiffExceptione){

//TODOAuto-generatedcatchblock

e.printStackTrace();

}catch(IOExceptione){

//TODOAuto-generatedcatchblock

e.printStackTrace();

}

returnsqlCollection;

    }

4、将Excel读取出来的信息进行封装成SQL语句

public class SqlCollection implements Serializable {

privateList<String>sql=null;

privateMap<String,String>dataKT=null;

privateStringtableName;

publicList<String>getSql(){

if(null==sql||sql.isEmpty())

returnnull;

returnsql;

}

publicvoidaddSql(){

if(null==sql||sql.isEmpty()){

sql=newArrayList<String>();

}

sql.add(this.toString());

dataKT.clear();

}

publicvoidaddData(StringcolumnData,Stringdata){

if(null==dataKT){

dataKT=newLinkedHashMap<String,String>();

}

dataKT.put(columnData,data);

}

publicvoidsetTableName(StringtableName){

intbeginIndex=tableName.lastIndexOf(".");

this.tableName=tableName.substring(beginIndex+1);

}

publicStringtoString(){

StringBuffersqlData=newStringBuffer();

StringBuffercolumnData=newStringBuffer();

StringBufferdata=newStringBuffer();

sqlData.append("insertinto").append(this.tableName);

columnData.append("(");

data.append("values('");

Iterator<Entry<String,String>>it=dataKT.entrySet().iterator();

while(it.hasNext()){

Entry<String,String>entry=it.next();

if(it.hasNext()){

columnData.append(entry.getKey()).append(",");

data.append(entry.getValue()).append("','");

}else{

columnData.append(entry.getKey()).append(")");

data.append(entry.getValue()).append("')");

}

}

sqlData.append(columnData).append(data);

returnsqlData.toString();

}

publicStringbuilderSql(List<POJO>rcs){

StringBuffersql=newStringBuffer("select*fromPOJO-》表名称where1<>1");

returnsql.toString();

}

}