Java code for importing Excel data into Oracle

1. First need two jar package Jxl.jar,ojdbc.jar (note version, version inappropriate will report version error)
2. Code:

Java code

2. Import Java.io.File;
4. Import Java.io.FileInputStream;
6. Import java.io.IOException;
8. Import Java.io.InputStream;
10. Import JXL. Cell;
12. Import JXL. Sheet;
14. Import JXL. Workbook;
16. Import jxl.read.biff.BiffException;
18. /**
20. * Excel data Import into Oracle
22. * @author SH
24. * 2010-05-11
26. */
28. public class InsertData {
30. public static void Main (string[] args) throws Exception {
34. InsertData in = new InsertData ();
36. In.insert ("F:/myjob/hah.xls", "Information");
40. }
44. /**
46. *
48. * @param path
50. * Path of Excel file to parse
52. * @param dataTable
54. * The name of the table to write to the database
56. * @throws biffexception
58. * @throws IOException
60. */
62. public void Insert (String path,string dataTable) throws Biffexception, IOException {
66. File File = new file (path);
68. Create a new Excel workbook
70. Workbook RWB = null;
72. RWB = Workbook.getworkbook (file);
76. Getting the first table index in the workbook is the Sheet1,sheet2,sheet3 under Excel ...
78. Sheet Sheet = rwb.getsheets () [0];
80. int rscolumns = Sheet.getcolumns ();//Number of columns
82. int rsrows = Sheet.getrows ();//Number of rows
84. String simnumber = "";//data in each cell
88. Dbutils jdbc=new dbutils ();
92. String str= "";//stitching the column to be inserted
94. for (int j = 0; J <rsColumns; J + +) {
96. Cell cell = Sheet.getcell (j, 0);
98. Simnumber = Cell.getcontents ();
100. if (j==rscolumns-1) {
102. str + = Simnumber;
104. }else{
106. str + = simnumber+ ",";
108. }
112. }
114. for (int i = 1; i < rsrows; i++) {
118. String sql = "INSERT INTO" +datatable+ "(" +str+ ") VALUES (";//Splicing SQL
120. System.out.println (str);
122. for (int j = 0; J < Rscolumns; J + +) {
124. Cell cell = Sheet.getcell (j, I);
126. Simnumber = Cell.getcontents ();
128. if (j==rscolumns-1) {
130. SQL + = "'" + simnumber+ "'";
132. }else{
134. SQL + = "'" + simnumber+ "',";
136. }
140. }
142. SQL + = ")";
144. Jdbc.executeupdate (SQL);//Execute SQL
148. }
150. Jdbc.closestmt ();
152. Jdbc.closeconnection ();
154. }
158. }

Util class

Java code

2. Import java.sql.Connection;
4. Import Java.sql.DriverManager;
6. Import Java.sql.ResultSet;
8. Import java.sql.SQLException;
10. Import java.sql.Statement;
14. /**
16. * Oracle Database connection
18. *
20. * @author SH 2010-05-11
22. */
24. public class Dbutils {
28. PRIVATE Connection conn = null;
30. Private Statement stmt = null;
32. Private ResultSet rs = null;
36. /** Oracle Database Connection URL */
38. Private final static String Db_url = "Jdbc:oracle:thin: @localhost: 1521:xe";
42. /** Oracle Database Connection Driver */
44. Private final static String Db_driver = "Oracle.jdbc.driver.OracleDriver";
48. /** Database User name */
50. Private final static String Db_username = "Test";
54. /** Database Password */
56. Private final static String Db_password = "Test";
60. /**
62. * Get database connection
64. *
66. * @return
68. */
70. Public Connection getconnection () {
72. /** declaring Connection Connection object */
74. Connection conn = null;
76. try {
78. /** uses the Class.forName () method to automatically create an instance of this driver and automatically calls DriverManager to register it */
80. Class.forName (Db_driver);
82. /** get the database connection via the DriverManager getconnection () method */
84. conn = DriverManager
86. . getconnection (Db_url, Db_username, Db_password);
88. stmt = Conn.createstatement ();
90. } catch (Exception ex) {
92. Ex.printstacktrace ();
94. }
96. Return conn;
98. }
102. /**
104. * Query Data section
106. *
108. * @return ResultSet
110. */
112. Public ResultSet executeQuery (String sqlstr) {
114. if (sqlstr = = NULL | | sqlstr.length () = = 0)
116. return null;
118. try {
120. This.getconnection ();
122. rs = Stmt.executequery (SQLSTR);
124. Return RS;
126. } catch (SQLException ex) {
128. Ex.printstacktrace ();
130. return null;
132. }
136. }
140. /**
142. * Update Data section
144. *
146. * @return Update is successful
148. */
150. public boolean executeupdate (String sqlstr) {
154. if (sqlstr = = NULL | | sqlstr.length () = = 0)
156. return false;
158. try {
160. This.getconnection ();
162. Stmt.executeupdate (SQLSTR);
164. return true;
166. } catch (SQLException ex) {
168. Ex.printstacktrace ();
170. return false;
172. } finally {
174. try {
176. if (stmt! = null) {
178. Stmt.close ();
180. }
182. } catch (SQLException e) {
184. E.printstacktrace ();
186. }
188. try {
190. IF (conn! = null) {
192. Conn.close ();
194. }
196. } catch (SQLException e) {
198. E.printstacktrace ();
200. }
204. }
208. }
212. public void closestmt () {
214. try {
216. if (stmt! = null) {
218. Stmt.close ();
220. }
222. } catch (Exception e) {
224. E.printstacktrace ();
226. }
228. }
232. /**
234. * Close Database connection
236. *
238. * @param Connect
240. */
242. public void CloseConnection () {
244. try {
246. IF (conn! = null) {
248. /** determine if the current connection object is not closed, call the Close method */
250. if (!conn.isclosed ()) {
252. Conn.close ();
254. }
256. }
258. } catch (Exception ex) {
260. Ex.printstacktrace ();
262. }
264. }
268. }