Java code imports Excel data to Oracle

The Excel format that this article deals with is xlsx:

1. Create a new MAVEN project

<dependencies>

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi</artifactId>

<version>3.14</version>

</dependency>

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

<version>3.14</version>

</dependency>

</dependencies>

( another: Ojdbc jar package due to copyright issues can not be directly introduced, it is necessary to manually download this time is Ojdbc7.jar. )

2. File (1.xlsx (data) data inserted into Table A (deptno int,dname varchar,loc varchar))

DEPTNO	Dname	LOC
10	ACCU	NewYork
20	Bb	Bb
30	VV	VV
40	Cc	Cc

3. Code section

package exceltest;import java.io.file;import java.io.fileinputstream;import  java.io.inputstream;import java.sql.connection;import java.sql.drivermanager;import  Java.sql.sqlexception;import java.sql.statement;import org.apache.poi.xssf.usermodel.xssfcell;import  org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import  org.apache.poi.xssf.usermodel.xssfworkbook;public class execloperate {//Get database connection public  Connection conn () {          try {           //First step: Load JDBC driver            class.forname ("Oracle.jdbc.driver.OracleDriver");           //Step Two: Create a database connection           Connection con  =drivermanager.getconnection ("Jdbc:oracle:thin:@127.0.0.1:1521:orcl ", " Scott ", " Tiger ");           return con;          }catch ( CLASSNOTFOUNDEXCEPTION&NBSP;CNF) {             System.out.println ("Driver not find:" +cnf);             return null;          }catch ( Sqlexception sqle) {             System.out.println ("Can ' t connection db:" +sqle);             return null;          }             catch  (exception e)  {           system.out.println ("Failed to load jdbc/odbc driver. ");           return null;           }      }  public void getexcel ()  throws exception {  inputstream is = new fileinputstream (new  file ("d:\\1.xlsx"));        xssfworkbook xssfworkbook  = new xssfworkbook (IS);        //  get each workbook          for  (int numsheet = 0; numsheet <  Xssfworkbook.getnumberofsheets ();  numsheet++)  {             xssfsheet xssfsheet = xssfworkbook.getsheetat (NumSheet);             if  (XSSFSHEET&NBSp;== null)  {                 continue;            }             //  get each line of the current workbook              for  (int rownum = 1; rownum <=  xssfsheet.getlastrownum ();  rownum++)  {                 xssfrow xssfrow = xssfsheet.getrow (RowNum);                 if  (Xssfrow  != null)  {                     //reading the first column of data                  &nBsp;   string a = getvalue (Xssfrow.getcell (0));                     integer one  = integer.parseint (A.substring (0,a.indexof (".")));                      //reading the second column of data                      string two = getvalue (Xssfrow.getcell (1));                     // Reading the third column of data                      string three = getvalue (Xssfrow.getcell (2));                                          string insert= "Insert into a values (" +one+ "," +two+ "', '" +three+ "')";                       system.out.println ("SQL:" +insert);                                            insert (insert);                 }             }        } }    //Convert Data Format     private string getvalue (XSSFCell  Xssfrow)  {        if  (Xssfrow.getcelltype ()  == xssfrow.cell_type_boolean)  {             return string.valueof ( Xssfrow.getbooleancellvalue ());        } else if  ( Xssfrow.getcelltype ()  == xssfrow.cell_type_numeric)  {             return string.valueof (Xssfrow.getnumericcellvalue ());         } else {             return string.valueof (Xssfrow.getstringcellvalue ());         }    }             Add Data     public int insert (String insert)  throws sqlexception{           connection conn = this.conn ();           int re = 0;          try{               conn.setautocommit (False) ;//Transaction Start                                Statement sm =  Conn.createstatement ();               re  = sm.executeupdate (insert);               if (re < 0) {                //insertion Failure                    coNn.rollback ();       //rollback                    sm.close ();                   conn.close ();                     return re;               }               conn.commit ();             //inserting normal                sm.close ();               conn.close ();                 return re;          }           catch (exception e) {               e.printstacktrace ();          }           conn.close ();             return 0;                }           //Test      Public static void main (String[] args)  throws Exception {           execloperate e=new execloperate ();           e.getexcel ();           System.out.println ("Import complete！ ");    } }

This article is from the "CW" blog, so be sure to keep this source http://cw666.blog.51cto.com/12488150/1946379

Java code imports Excel data to Oracle