excel导入数据库及反向导出.docx
- 文档编号:6610357
- 上传时间:2023-01-08
- 格式:DOCX
- 页数:12
- 大小:19.53KB
excel导入数据库及反向导出.docx
《excel导入数据库及反向导出.docx》由会员分享,可在线阅读,更多相关《excel导入数据库及反向导出.docx(12页珍藏版)》请在冰豆网上搜索。
excel导入数据库及反向导出
EXCEL导入数据库及数据库数据导出到EXCEL
关键字:
报表、jel
很久没有发BLOG,呵呵,是因为最近在帮助朋友在做一个项目,很忙。
呵呵,我觉得生活就应该是这样,年轻时候的忙碌是为年老时候的悠闲吧。
主要涉及内容及技术:
javaexcelapi
jasonreport及编辑工具ireport和数据库技术。
导入及导出EXCEL解决思路:
使用javaexcelapi导入普通的EXCEL表格,就是没有合并单元格的规范数据,如果导出有规则的EXCEL也使用JAVAEXECELAPI,对中文支持比较好。
如果要导出报表可打印的EXCEL,使用ireport为工具图形化画出报表,并使用jasonreprt控制导出,展示给客户端。
主要代码有:
连接数据库的方法:
java代码
1.import java.sql.Connection;
2.import java.sql.ResultSet;
3.import java.sql.SQLException;
4.import java.sql.Statement;
5.import java.util.Hashtable;
6.import java.util.Vector;
7.import org.apache.log4j.Logger;
8.public class DBOperate {
9.
10. /**
11. * 得到sequence的nextval
12. * @param sequenceName
13. * @return
14. */
15. public static String getSequence(String sequenceName) {
16. String nextval = "";
17. if(sequenceName == null || "".equals(sequenceName)) {
18. System.out.println("sequenceName name is null!
");
19. return null;
20. } else {
21. sequenceName = sequenceName.toUpperCase();
22. String sql = "select " + sequenceName + ".nextval nextid from user_sequences where sequence_name = '"
23. + sequenceName + "'";
24.
25. Statement stmt = null;
26. Connection conn = null;
27. ResultSet rs = null;
28.
29. try {
30. conn = TreatDB.getConnDB("");
31. if (conn !
= null) {
32. stmt = conn.createStatement();
33. rs = stmt.executeQuery(sql);
34.
35. if(rs !
= null) {
36. while(rs.next()) {
37. nextval = rs.getString("nextid");
38. }
39. }
40. }
41. } catch (SQLException e) {
42. System.out.println("SQLException :
" + e);
43. } finally {
44. try {
45. if(rs !
= null) rs.close();
46. if(stmt !
= null) stmt.close();
47. if(conn !
= null) conn.close();
48. } catch (SQLException e) {
49. // TODO Auto-generated catch block
50. }
51. }
52. return nextval;
53. }
54. }
55./**
56. * 一个插入、更新数据表的通用方法,传入一个sql脚本
57. * @param sql :
要进行操作的脚本
58. * @return :
发生变化的条数
59. */
60. public static int updateSql(String sql) {
61. String dbName = "";
62. Connection conn = null;
63. Statement stmt = null;
64. int result = 0;
65. try {
66. conn = TreatDB.getConnDB(dbName);
67. if (conn !
= null) {
68. stmt = conn.createStatement();
69. //logger.info(sql);
70. result = stmt.executeUpdate(sql);
71. mit();
72. }
73.
74. } catch (SQLException e) {
75. try {
76. conn.rollback();
77. } catch (SQLException e1) {
78. System.out.println("update sql error:
"+e);
79.
80. }
81. System.out.println("update sql error:
"+e);
82. System.out.println("sql:
" + sql);
83. } finally {
84. try {
85. if (stmt !
= null)
86. stmt.close();
87. if (conn !
= null)
88. conn.close();
89. } catch (Exception ex) {
90. //do nothing;
91. }
92.
93. }
94. return result;
95. }
96.
97.}
导入EXCEL的程序代码:
java代码
1./**
2. * 将Excel文件中的数据添加到数据库中
3. * 新的监理信息 modify by heweiya
4. *
5. * @param xlsFile,Excel文件
6. * @return
7. */
8. public String addJLXlsForm(InputStream is) {
9.
10. String errStr = "";
11. String result = "";
12.
13. try {
14. Workbook rwb = Workbook.getWorkbook(is);
15. // Workbook rwb = Workbook.getWorkbook(new File(filePath+fileName));
16. // 获取第一张Sheet表
17. Sheet sheet = rwb.getSheet(0);
18. // 获取总列数
19. int rsColumns = sheet.getColumns();
20. System.out.println("rsColumns = " + rsColumns);
21.
22. // 获取总行数
23. int rsRows = sheet.getRows();
24. System.out.println("rsRows = " + rsRows);
25.
26. if (rsColumns < 19) {
27. errStr = "错误原因:
字段不全。
";
28. vErr.addElement(errStr);
29. }
30.
31. int i = 0;
32. int startRows = 1;
33. int startColumn = 0;
34. while (startRows < rsRows) {
35. Hashtable ht = new Hashtable();
36.
37. String zj_id = DBOperate.getSequence("S_JIANLI");
38.
39.
40. Cell tmp = sheet.getCell(0,startRows);
41. String sfzh = tmp.getContents();
42.
43. tmp = sheet.getCell(1,startRows);
44. String issueDate = tmp.getContents();
45. String issueDate_new = "";
46. if(issueDate !
= null && issueDate.length()>=6) issueDate_new = issueDate.substring(6) + issueDate.substring(3,5) + issueDate.substring(0,2);
47.
48. tmp = sheet.getCell(2,startRows);
49. String approveDate = tmp.getContents();
50. String approve_date_new = "";
51. if(approveDate !
= null && approveDate.length()>=6) approve_date_new = approveDate.substring(6) + approveDate.substring(3,5) + approveDate.substring(0,2);
52.
53. tmp = sheet.getCell(3,startRows);
54. String cerNo = tmp.getContents();
55.
56. tmp = sheet.getCell(4,startRows);
57. String jianliMajor = tmp.getContents();
58.
59. tmp = sheet.getCell(5,startRows);
60. String department = tmp.getContents();
61.
62. tmp = sheet.getCell(6,startRows);
63. String jianliName = tmp.getContents();
64.
65. tmp = sheet.getCell(7,startRows);
66. String jianliSex = tmp.getContents();
67.
68. //毕业院校
69. tmp = sheet.getCell(8,startRows);
70. String degree = tmp.getContents();
71.
72. //毕业时间
73. tmp = sheet.getCell(9,startRows);
74. String cooleage = tmp.getContents();
75.
76. //所学专业
77. tmp = sheet.getCell(10,startRows);
78. String company = tmp.getContents();
79.
80. //职务
81. tmp = sheet.getCell(11,startRows);
82. String tech_post = tmp.getContents();
83.
84.// 工作单位
85. tmp = sheet.getCell(12,startRows);
86. String address = tmp.getContents();
87.
88.// 职称
89. tmp = sheet.getCell(13,startRows);
90. String postcode = tmp.getContents();
91.
92. tmp = sheet.getCell(14,startRows);
93. String jianliTel = tmp.getContents();
94.
95. tmp = sheet.getCell(15,startRows);
96. String mobile = tmp.getContents();
97.
98. tmp = sheet.getCell(16,startRows);
99. String email = tmp.getContents();
100.
101. tmp = sheet.getCell(17,startRows);
102. String birthday = tmp.getContents();
103. String birthday_new = "";
104. if(birthday !
= null && birthday.length()>=6) birthday_new = birthday.substring(6) + birthday.substring(3,5) + birthday.substring(0,2);
105.
106.
107. tmp = sheet.getCell(18,startRows);
108. String jianli_type = tmp.getContents();
109. //加入密码 modify by heweiya 2007/05/17
110. String sql = "insert into jianli values('" + zj_id + "', '" + sfzh + "', to_date('" + issueDate_new + "','yyyymmdd'),to_date('"
111. + approve_date_new
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- excel 导入 数据库 反向 导出
![提示](https://static.bdocx.com/images/bang_tan.gif)