欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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 }