Java实现Excel导入数据库,数据库中的数据导入到Excel
程序员文章站
2022-05-29 11:58:01
连接数据库的工具类 1 package demo; 2 import java.sql.Connection; 3 import java.sql.DriverManager; 4 import java.sql.PreparedStatement; 5 import java.sql.Result ......
连接数据库的工具类
1 package demo; 2 import java.sql.connection; 3 import java.sql.drivermanager; 4 import java.sql.preparedstatement; 5 import java.sql.resultset; 6 import java.sql.sqlexception; 7 8 public class dbhepler { 9 /* 10 * string driver = "com.microsoft.sqlserver.jdbc.sqlserverdriver"; string 11 * url = "jdbc:sqlserver://127.0.0.1;databasename=javenforexcel"; 12 */ 13 14 string driver = "com.mysql.jdbc.driver"; 15 string url = "jdbc:mysql://127.0.0.1:3306/javenforexcel"; 16 17 connection con = null; 18 resultset res = null; 19 20 public void database() { 21 try { 22 class.forname(driver); 23 con = drivermanager.getconnection(url, "root", "root"); 24 } catch (classnotfoundexception e) { 25 // todo auto-generated catch block 26 system.err.println("装载 jdbc/odbc 驱动程序失败。"); 27 e.printstacktrace(); 28 } catch (sqlexception e) { 29 // todo auto-generated catch block 30 system.err.println("无法连接数据库"); 31 e.printstacktrace(); 32 } 33 } 34 35 // 查询 36 public resultset search(string sql, string str[]) { 37 database(); 38 try { 39 preparedstatement pst = con.preparestatement(sql); 40 if (str != null) { 41 for (int i = 0; i < str.length; i++) { 42 pst.setstring(i + 1, str[i]); 43 } 44 } 45 res = pst.executequery(); 46 47 } catch (exception e) { 48 // todo auto-generated catch block 49 e.printstacktrace(); 50 } 51 return res; 52 } 53 54 // 增删修改 55 public int addu(string sql, string str[]) { 56 int a = 0; 57 database(); 58 try { 59 preparedstatement pst = con.preparestatement(sql); 60 if (str != null) { 61 for (int i = 0; i < str.length; i++) { 62 pst.setstring(i + 1, str[i]); 63 } 64 } 65 a = pst.executeupdate(); 66 } catch (exception e) { 67 // todo auto-generated catch block 68 e.printstacktrace(); 69 } 70 return a; 71 } 72 73 }
1 package demo; 2 public class stuentity { 3 private int id; 4 private string name; 5 private string sex; 6 private int num; 7 8 public stuentity() { 9 } 10 11 public stuentity(int id, string name, string sex, int num) { 12 this.id = id; 13 this.name = name; 14 this.sex = sex; 15 this.num = num; 16 } 17 18 @override 19 public string tostring() { 20 return "stuentity [id=" + id + ", name=" + name + ", sex=" + sex 21 + ", num=" + num + "]"; 22 } 23 24 public int getid() { 25 return id; 26 } 27 28 public void setid(int id) { 29 this.id = id; 30 } 31 32 public string getname() { 33 return name; 34 } 35 36 public void setname(string name) { 37 this.name = name; 38 } 39 40 public string getsex() { 41 return sex; 42 } 43 44 public void setsex(string sex) { 45 this.sex = sex; 46 } 47 48 public int getnum() { 49 return num; 50 } 51 52 public void setnum(int num) { 53 this.num = num; 54 } 55 56 }
java实现excel导入数据核心类 读取excel表中所有的数据、操作数据(查询、更新)
1 package demo; 2 3 4 5 import java.io.file; 6 7 import java.sql.resultset; 8 9 import java.sql.sqlexception; 10 11 import java.util.arraylist; 12 13 import java.util.list; 14 15 16 17 import jxl.sheet; 18 19 import jxl.workbook; 20 21 22 23 import com.javen.db.dbhepler; 24 25 import com.javen.entity.stuentity; 26 27 28 29 public class stuservice { 30 31 /** 32 33 * 查询stu表中所有的数据 34 35 * 36 37 * @return 38 39 */ 40 41 public static list<stuentity> getallbydb() { 42 43 list<stuentity> list = new arraylist<stuentity>(); 44 45 try { 46 47 dbhepler db = new dbhepler(); 48 49 string sql = "select * from stu"; 50 51 resultset rs = db.search(sql, null); 52 53 while (rs.next()) { 54 55 int id = rs.getint("id"); 56 57 string name = rs.getstring("name"); 58 59 string sex = rs.getstring("sex"); 60 61 int num = rs.getint("num"); 62 63 64 65 // system.out.println(id+" "+name+" "+sex+ " "+num); 66 67 list.add(new stuentity(id, name, sex, num)); 68 69 } 70 71 72 73 } catch (sqlexception e) { 74 75 // todo auto-generated catch block 76 77 e.printstacktrace(); 78 79 } 80 81 return list; 82 83 } 84 85 86 87 /** 88 89 * 查询指定目录中电子表格中所有的数据 90 91 * 92 93 * @param file 94 95 * 文件完整路径 96 97 * @return 98 99 */ 100 101 public static list<stuentity> getallbyexcel(string file) { 102 103 list<stuentity> list = new arraylist<stuentity>(); 104 105 try { 106 107 workbook rwb = workbook.getworkbook(new file(file)); 108 109 sheet rs = rwb.getsheet("test shee 1");// 或者rwb.getsheet(0) 110 111 int clos = rs.getcolumns();// 得到所有的列 112 113 int rows = rs.getrows();// 得到所有的行 114 115 116 117 system.out.println(clos + " rows:" + rows); 118 119 for (int i = 1; i < rows; i++) { 120 121 for (int j = 0; j < clos; j++) { 122 123 // 第一个是列数,第二个是行数 124 125 string id = rs.getcell(j++, i).getcontents();// 默认最左边编号也算一列 126 127 // 所以这里得j++ 128 129 string name = rs.getcell(j++, i).getcontents(); 130 131 string sex = rs.getcell(j++, i).getcontents(); 132 133 string num = rs.getcell(j++, i).getcontents(); 134 135 136 137 system.out.println("id:" + id + " name:" + name + " sex:" 138 139 + sex + " num:" + num); 140 141 list.add(new stuentity(integer.parseint(id), name, sex, 142 143 integer.parseint(num))); 144 145 } 146 147 } 148 149 } catch (exception e) { 150 151 // todo auto-generated catch block 152 153 e.printstacktrace(); 154 155 } 156 157 return list; 158 159 160 161 } 162 163 164 165 /** 166 167 * 通过id判断是否存在 168 169 * 170 171 * @param id 172 173 * @return 174 175 */ 176 177 public static boolean isexist(int id) { 178 179 try { 180 181 dbhepler db = new dbhepler(); 182 183 resultset rs = db.search("select * from stu where id=?", 184 185 new string[] { id + "" }); 186 187 if (rs.next()) { 188 189 return true; 190 191 } 192 193 } catch (sqlexception e) { 194 195 // todo auto-generated catch block 196 197 e.printstacktrace(); 198 199 } 200 201 return false; 202 203 } 204 205 206 207 public static void main(string[] args) { 208 209 /* 210 211 * list<stuentity> all=getallbydb(); for (stuentity stuentity : all) { 212 213 * system.out.println(stuentity.tostring()); } 214 215 */ 216 217 218 219 system.out.println(isexist(1)); 220 221 222 223 } 224 225 226 227 }
数据的数据导入到excel表
1 package demo; 2 3 import java.io.file; 4 5 import java.util.list; 6 7 8 9 import com.javen.entity.stuentity; 10 11 import com.javen.service.stuservice; 12 13 14 15 import jxl.workbook; 16 17 import jxl.write.label; 18 19 import jxl.write.writablesheet; 20 21 import jxl.write.writableworkbook; 22 23 24 25 public class testdbtoexcel { 26 27 28 29 public static void main(string[] args) { 30 31 try { 32 33 writableworkbook wwb = null; 34 35 36 37 // 创建可写入的excel工作簿 38 39 string filename = "d://book.xls"; 40 41 file file=new file(filename); 42 43 if (!file.exists()) { 44 45 file.createnewfile(); 46 47 } 48 49 //以filename为文件名来创建一个workbook 50 51 wwb = workbook.createworkbook(file); 52 53 54 55 // 创建工作表 56 57 writablesheet ws = wwb.createsheet("test shee 1", 0); 58 59 60 61 //查询数据库中所有的数据 62 63 list<stuentity> list= stuservice.getallbydb(); 64 65 //要插入到的excel表格的行号,默认从0开始 66 67 label labelid= new label(0, 0, "编号(id)");//表示第 68 69 label labelname= new label(1, 0, "姓名(name)"); 70 71 label labelsex= new label(2, 0, "性别(sex)"); 72 73 label labelnum= new label(3, 0, "薪水(num)"); 74 75 76 77 ws.addcell(labelid); 78 79 ws.addcell(labelname); 80 81 ws.addcell(labelsex); 82 83 ws.addcell(labelnum); 84 85 for (int i = 0; i < list.size(); i++) { 86 87 88 89 label labelid_i= new label(0, i+1, list.get(i).getid()+""); 90 91 label labelname_i= new label(1, i+1, list.get(i).getname()); 92 93 label labelsex_i= new label(2, i+1, list.get(i).getsex()); 94 95 label labelnum_i= new label(3, i+1, list.get(i).getnum()+""); 96 97 ws.addcell(labelid_i); 98 99 ws.addcell(labelname_i); 100 101 ws.addcell(labelsex_i); 102 103 ws.addcell(labelnum_i); 104 105 } 106 107 108 109 //写进文档 110 111 wwb.write(); 112 113 // 关闭excel工作簿对象 114 115 wwb.close(); 116 117 118 119 } catch (exception e) { 120 121 // todo auto-generated catch block 122 123 e.printstacktrace(); 124 125 } 126 127 } 128 129 }
excel表中的数据导入到mysql数据库
1 package demo; 2 3 import java.util.list; 4 5 6 7 import com.javen.db.dbhepler; 8 9 import com.javen.entity.stuentity; 10 11 import com.javen.service.stuservice; 12 13 public class testexceltodb { 14 15 public static void main(string[] args) { 16 17 //得到表格中所有的数据 18 19 list<stuentity> listexcel=stuservice.getallbyexcel("d://book.xls"); 20 21 /*//得到数据库表中所有的数据 22 23 list<stuentity> listdb=stuservice.getallbydb();*/ 24 25 26 27 dbhepler db=new dbhepler(); 28 29 30 31 for (stuentity stuentity : listexcel) { 32 33 int id=stuentity.getid(); 34 35 if (!stuservice.isexist(id)) { 36 37 //不存在就添加 38 39 string sql="insert into stu (name,sex,num) values(?,?,?)"; 40 41 string[] str=new string[]{stuentity.getname(),stuentity.getsex(),stuentity.getnum()+""}; 42 43 db.addu(sql, str); 44 45 }else { 46 47 //存在就更新 48 49 string sql="update stu set name=?,sex=?,num=? where id=?"; 50 51 string[] str=new string[]{stuentity.getname(),stuentity.getsex(),stuentity.getnum()+"",id+""}; 52 53 db.addu(sql, str); 54 55 } 56 57 } 58 59 } 60 61 }
上一篇: jQuery$.postjson用法实例
下一篇: Win10常见问题记录