java excel sql_java将excel单元格数据转换成sql语句 | 学步园

首先要有jxl类库。

程序如下：

import java.io.*;

import jxl.*;

public class ExcelToSql {

public static void main(String args[])

{

try

{

Workbook book= Workbook.getWorkbook(new File("D://workspaceuse6//foundation//test//com//shane//security//service//zdb.xls"));

//获得第一个工作表对象

Sheet sheet=book.getSheet(0);

StringBuffer sql = new StringBuffer();

for(int j = 1; j <= 929; j++)

{

//  getCell(列,行)起始为0

Cell cell1=sheet.getCell(0,j);

Cell cell2=sheet.getCell(1,j);

Cell cell3=sheet.getCell(2,j);

Cell cell4=sheet.getCell(3,j);

Cell cell5=sheet.getCell(4,j);

String result1=cell1.getContents();

String result2=cell2.getContents();

String result3=cell3.getContents();

String result4=cell4.getContents();

String result5=cell5.getContents();

System.out.print(result1);

System.out.print(result2);

System.out.print(result3);

System.out.print(result4);

System.out.print(result5);

System.out.print("/n");

sql.append("insert into tablename (citycode,cityname,qucode,quname,qu) values ('");

sql.append(result1);

sql.append("','");

sql.append(result2);

sql.append("','");

sql.append(result3);

sql.append("','");

sql.append(result4);

sql.append("','");

sql.append(result5);

sql.append("');");

sql.append("/n");

}

book.close();

writer(sql);

}catch(Exception e)

{

System.out.println(e);

}

}

/**

* 写sql写入文件

* @param buffer

* @throws Exception

*/

private static void writer(StringBuffer buffer) throws Exception{

PrintStream out = null ;

try{

out = new PrintStream(new FileOutputStream(new File("d://dic.sql"),true)) ;

System.err.println(buffer.toString()) ;

out.print(buffer.toString()) ;

}catch(Exception e){

e.printStackTrace();

}finally{

if(out!=null){

out.close() ;

}

}

}

}

excel数据样式：

073001

原卡片

073002

网络直报

1

网报

073003

社区提供

2

社区

073004

公安部门

3

公安

073005

残联部门

4

残联

073006

专科门诊

5

门诊

生成的sql脚本:

insert into tablename (citycode,cityname,qucode,quname,qu) values ('073002','网络直报','1','网报','BM_XXLY');

insert into tablename (citycode,cityname,qucode,quname,qu) values ('073003','社区提供','2','社区','BM_XXLY');

insert into tablename (citycode,cityname,qucode,quname,qu) values ('073004','公安部门','3','公安','BM_XXLY');

insert into tablename (citycode,cityname,qucode,quname,qu) values ('073005','残联部门','4','残联','BM_XXLY');