Java实现把excel xls中数据转为可直接插入数据库的sql文件

我的一贯风格，代码说明一切。。

废话不多说了，直接给大家贴代码了，具体代码如下所示：

package tools;
import java.io.bufferedwriter;
import java.io.file;
import java.io.filewriter;
import java.io.ioexception;
import java.lang.reflect.field;
import java.util.arraylist;
import java.util.list;
import jxl.sheet;
import jxl.workbook;
import entity.student;
public class convertxmstosql {
/**
* 从xls表格中获取数据，生成可执行的sql文件，用以插入数据库，注意需要引入jxl包！支持int,integer,long,long,string
* ,可自行扩展
* 
* @param args
* @throws illegalargumentexception
* @throws illegalaccessexception
*/
public static void main(string[] args) throws illegalargumentexception,
illegalaccessexception {
// 得到表格中所有的数据
list<student> listexcel = getallbyexcel("c:\\users\\xxx\\desktop\\zzz.xls");
try {
string path = "c:\\users\\xxx\\desktop\\convert.sql";// 文件保存路径、名字
file file = new file(path);
bufferedwriter ow = new bufferedwriter(new filewriter(file));
for (student c : listexcel) {
string sql = "insert into cfg_avatar values (" + outsql(c)
+ ")";
ow.write(sql + ";" + "\n");
}// 写入内容
ow.close();
} catch (ioexception e) {
e.printstacktrace();
}
}
/**
* 从xls中获取数据
* 
* @param file
* @return
*/
public static list<student> getallbyexcel(string file) {
list<student> list = new arraylist<student>();
try {
workbook rwb = workbook.getworkbook(new file(file));
sheet rs = rwb.getsheet(0);
int clos = rs.getcolumns();// 得到所有的列
int rows = rs.getrows();// 得到所有的行
// 样例中，数据从第三列第一行开始
for (int i = 2; i < rows; i++) {
// 取得的每一行的所有数据存入liststring
list<string> liststring = new arraylist<string>();
for (int j = 0; j < clos; j++) {
string str = rs.getcell(j, i).getcontents();
liststring.add(str);
}
student student = (student) newobject(new student(), liststring);
list.add(student);
}
} catch (exception e) {
e.printstacktrace();
}
return list;
}
/**
* 使用反射设置数据。此例中可设置的数据类型有限，没有的请自己添加！！！
* 
* @param obj
* @param list
* @return
* @throws illegalargumentexception
* @throws illegalaccessexception
*/
public static object newobject(object obj, list<string> list)
throws illegalargumentexception, illegalaccessexception {
field[] field = obj.getclass().getdeclaredfields();
for (int i = 0; i < field.length; i++) {
field f = field[i];
f.setaccessible(true);
if (f.gettype() == string.class) {
f.set(obj, list.get(i));
}
if (f.gettype() == integer.class) {
f.set(obj, integer.parseint(list.get(i)));
}
if (f.gettype() == int.class) {
f.set(obj, integer.parseint(list.get(i)));
}
if (f.gettype() == long.class) {
f.set(obj, long.parselong(list.get(i)));
}
if (f.gettype() == long.class) {
f.set(obj, long.parselong(list.get(i)));
}
}
return obj;
}
/**
* 
* @param obj
* @return
* @throws illegalargumentexception
* @throws illegalaccessexception
*/
public static string outsql(object obj) throws illegalargumentexception,
illegalaccessexception {
stringbuffer buffer = new stringbuffer();
field[] field = obj.getclass().getdeclaredfields();
for (int i = 0; i < field.length; i++) {
field f = field[i];
f.setaccessible(true);
if (f.gettype() == string.class) {
buffer.append("'");
}
buffer.append(f.get(obj));
if (f.gettype() == string.class) {
buffer.append("'");
}
if (i < field.length - 1) {
buffer.append(",");
}
}
return buffer.tostring();
}
}

有关java实现把excel xls中数据转为可直接插入数据库的sql文件的知识，小编就给大家介绍这么多，希望对大家有所帮助！