1. First need two jar package Jxl.jar,ojdbc.jar (note version, version inappropriate will report version error)
2. Code:
Java code
- Import Java.io.File;
- Import Java.io.FileInputStream;
- Import java.io.IOException;
- Import Java.io.InputStream;
- Import JXL. Cell;
- Import JXL. Sheet;
- Import JXL. Workbook;
- Import jxl.read.biff.BiffException;
- /**
- * Excel data Import into Oracle
- * @author SH
- * 2010-05-11
- */
- public class InsertData {
- public static void Main (string[] args) throws Exception {
- InsertData in = new InsertData ();
- In.insert ("F:/myjob/hah.xls", "Information");
- }
- /**
- *
- * @param path
- * Path of Excel file to parse
- * @param dataTable
- * The name of the table to write to the database
- * @throws biffexception
- * @throws IOException
- */
- public void Insert (String path,string dataTable) throws Biffexception, IOException {
- File File = new file (path);
- Create a new Excel workbook
- Workbook RWB = null;
- RWB = Workbook.getworkbook (file);
- Getting the first table index in the workbook is the Sheet1,sheet2,sheet3 under Excel ...
- Sheet Sheet = rwb.getsheets () [0];
- int rscolumns = Sheet.getcolumns ();//Number of columns
- int rsrows = Sheet.getrows ();//Number of rows
- String simnumber = "";//data in each cell
- Dbutils jdbc=new dbutils ();
- String str= "";//stitching the column to be inserted
- for (int j = 0; J <rsColumns; J + +) {
- Cell cell = Sheet.getcell (j, 0);
- Simnumber = Cell.getcontents ();
- if (j==rscolumns-1) {
- str + = Simnumber;
- }else{
- str + = simnumber+ ",";
- }
- }
- for (int i = 1; i < rsrows; i++) {
- String sql = "INSERT INTO" +datatable+ "(" +str+ ") VALUES (";//Splicing SQL
- System.out.println (str);
- for (int j = 0; J < Rscolumns; J + +) {
- Cell cell = Sheet.getcell (j, I);
- Simnumber = Cell.getcontents ();
- if (j==rscolumns-1) {
- SQL + = "'" + simnumber+ "'";
- }else{
- SQL + = "'" + simnumber+ "',";
- }
- }
- SQL + = ")";
- Jdbc.executeupdate (SQL);//Execute SQL
- }
- Jdbc.closestmt ();
- Jdbc.closeconnection ();
- }
- }
Util class
Java code
- Import java.sql.Connection;
- Import Java.sql.DriverManager;
- Import Java.sql.ResultSet;
- Import java.sql.SQLException;
- Import java.sql.Statement;
- /**
- * Oracle Database connection
- *
- * @author SH 2010-05-11
- */
- public class Dbutils {
- PRIVATE Connection conn = null;
- Private Statement stmt = null;
- Private ResultSet rs = null;
- /** Oracle Database Connection URL */
- Private final static String Db_url = "Jdbc:oracle:thin: @localhost: 1521:xe";
- /** Oracle Database Connection Driver */
- Private final static String Db_driver = "Oracle.jdbc.driver.OracleDriver";
- /** Database User name */
- Private final static String Db_username = "Test";
- /** Database Password */
- Private final static String Db_password = "Test";
- /**
- * Get database connection
- *
- * @return
- */
- Public Connection getconnection () {
- /** declaring Connection Connection object */
- Connection conn = null;
- try {
- /** uses the Class.forName () method to automatically create an instance of this driver and automatically calls DriverManager to register it */
- Class.forName (Db_driver);
- /** get the database connection via the DriverManager getconnection () method */
- conn = DriverManager
- . getconnection (Db_url, Db_username, Db_password);
- stmt = Conn.createstatement ();
- } catch (Exception ex) {
- Ex.printstacktrace ();
- }
- Return conn;
- }
- /**
- * Query Data section
- *
- * @return ResultSet
- */
- Public ResultSet executeQuery (String sqlstr) {
- if (sqlstr = = NULL | | sqlstr.length () = = 0)
- return null;
- try {
- This.getconnection ();
- rs = Stmt.executequery (SQLSTR);
- Return RS;
- } catch (SQLException ex) {
- Ex.printstacktrace ();
- return null;
- }
- }
- /**
- * Update Data section
- *
- * @return Update is successful
- */
- public boolean executeupdate (String sqlstr) {
- if (sqlstr = = NULL | | sqlstr.length () = = 0)
- return false;
- try {
- This.getconnection ();
- Stmt.executeupdate (SQLSTR);
- return true;
- } catch (SQLException ex) {
- Ex.printstacktrace ();
- return false;
- } finally {
- try {
- if (stmt! = null) {
- Stmt.close ();
- }
- } catch (SQLException e) {
- E.printstacktrace ();
- }
- try {
- IF (conn! = null) {
- Conn.close ();
- }
- } catch (SQLException e) {
- E.printstacktrace ();
- }
- }
- }
- public void closestmt () {
- try {
- if (stmt! = null) {
- Stmt.close ();
- }
- } catch (Exception e) {
- E.printstacktrace ();
- }
- }
- /**
- * Close Database connection
- *
- * @param Connect
- */
- public void CloseConnection () {
- try {
- IF (conn! = null) {
- /** determine if the current connection object is not closed, call the Close method */
- if (!conn.isclosed ()) {
- Conn.close ();
- }
- }
- } catch (Exception ex) {
- Ex.printstacktrace ();
- }
- }
- }