Java实现把excel xls中数据转为可直接插入数据库的sql文件
程序员文章站
2024-03-09 18:06:23
我的一贯风格,代码说明一切。。
废话不多说了,直接给大家贴代码了,具体代码如下所示:
package tools;
import java.io.buffer...
我的一贯风格,代码说明一切。。
废话不多说了,直接给大家贴代码了,具体代码如下所示:
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文件的知识,小编就给大家介绍这么多,希望对大家有所帮助!