Java实现Excel导入数据库，数据库中的数据导入到Excel

连接数据库的工具类

 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 }